Skip site navigation (1) Skip section navigation (2)

Re: Slow indexscan

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: "Mikko Partio" <mpartio(at)gmail(dot)com>
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: Slow indexscan
Date: 2007-06-25 14:24:30
Message-ID: 28166.1182781470@sss.pgh.pa.us (view raw or flat)
Thread:
Lists: pgsql-performance
"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

In response to

pgsql-performance by date

Next:From: Tom LaneDate: 2007-06-25 14:31:49
Subject: Re: PITR Backups
Previous:From: Simon RiggsDate: 2007-06-25 13:26:02
Subject: Re: PITR Backups

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group