Re: Large table performance and vacuum

Tom Lane wrote:
> Gavin Scott  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