Gavin Scott <gavin(at)ipalsoftware(dot)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.
regards, tom lane