Re: Large table performance and vacuum

Tom Lane wrote:
> Gavin Scott <gavin@ipalsoftware.com> writes:
>
>>The problem is the query "SELECT * FROM log ORDER BY hid
>>LIMIT 1;", which both EXPLAIN and EXPLAIN ANALYZE show as
>>Limit / Index Scan on hid_idx.  This was very fast before we
>>started deleting out old log entries the table, but has
>>started taking an extremely long time, about 341 seconds.
>
>
> I'm suspecting that you need to REINDEX hid_idx.  This is an
> aspect of the pre-7.4 "index bloat" problem: the left end of the index
> now consists of entirely-empty pages, which not only occupy space but
> take time to scan through for a query like this.

Assuming a "normal" usage pattern, regular VACUUMing, and no
instances of corrupted indexes, are there any scenarios in which one
would need to REINDEX either user or system tables post 7.4?

Mike Mascari