Re: Slow indexscan

"Mikko Partio" <mpartio(at)gmail(dot)com> writes:
> This is already a great improvement compared to the previous 8 seconds. Our
> app developers claim though that previously the same queries have run in
> less than 1 second. The database had a mysterious crash a few months ago
> (some tables lost their content) and the performance has been bad ever
> since. I don't know the details of this crash since I just inherited the
> system recently and unfortunately no logfiles are left. Could the crash
> somehow corrupt catalog files so that the querying gets slower? I know this
> is a long shot but I don't know what else to think of.

I'd wonder more about what was done to recover from the crash. For
instance, if they had to reload the tables, then it seems possible that
this table was previously nicely clustered on the relevant index and
is now quite disordered. You might check to see if any pg_index entries
have pg_index.indisclustered set, and if so try a CLUSTER command to
re-order the table(s).

Another thing to try, which is at least slightly more useful than waving
a dead chicken at the DB, is to REINDEX everything in sight, including
the system catalogs. This'd fix any remaining index corruption, and
probably expose heap corruption if there is any.

regards, tom lane