Received: from localhost (maia-4.hub.org [200.46.204.183])
	by postgresql.org (Postfix) with ESMTP id A71219FB364
	for <pgsql-performance-postgresql.org@postgresql.org>;
	Mon, 25 Jun 2007 11:24:37 -0300 (ADT)
Received: from postgresql.org ([200.46.204.71])
	by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024)
	with ESMTP id 32969-02 for
	<pgsql-performance-postgresql.org@postgresql.org>;
	Mon, 25 Jun 2007 11:24:32 -0300 (ADT)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.5
Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130])
	by postgresql.org (Postfix) with ESMTP id A346D9F939E
	for <pgsql-performance@postgresql.org>;
	Mon, 25 Jun 2007 11:24:32 -0300 (ADT)
Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1])
	by sss.pgh.pa.us (8.14.1/8.14.1) with ESMTP id l5PEOUVS028167;
	Mon, 25 Jun 2007 10:24:30 -0400 (EDT)
To: "Mikko Partio" <mpartio@gmail.com>
cc: pgsql-performance@postgresql.org
Subject: Re: Slow indexscan 
In-reply-to: <2ca799770706250050x225525f8lad01b4e69e2d1743@mail.gmail.com> 
References: <2ca799770706200702y1074f066s61279b67f93315d0@mail.gmail.com>
	<17907.1182352197@sss.pgh.pa.us>
	<2ca799770706200855w1a6b6a7cy8526b9be0ef9c97c@mail.gmail.com>
	<18952.1182355276@sss.pgh.pa.us>
	<2ca799770706200943l6ae9f6bfif54683e21fa33f35@mail.gmail.com>
	<29484.1182367794@sss.pgh.pa.us>
	<2ca799770706250050x225525f8lad01b4e69e2d1743@mail.gmail.com>
Comments: In-reply-to "Mikko Partio" <mpartio@gmail.com>
	message dated "Mon, 25 Jun 2007 10:50:48 +0300"
Date: Mon, 25 Jun 2007 10:24:30 -0400
Message-ID: <28166.1182781470@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Archive-Number: 200706/569
X-Sequence-Number: 25602

"Mikko Partio" <mpartio@gmail.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