From: Tom Lane <tgl@sss.pgh.pa.us>
To: Mikko Partio <mpartio@gmail.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Slow indexscan
Date: Mon, 25 Jun 2007 10:24:30 -0400
Message-ID: <28166.1182781470@sss.pgh.pa.us> (raw)
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>
"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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: tgl@sss.pgh.pa.us, mpartio@gmail.com
Subject: Re: Slow indexscan
In-Reply-To: <28166.1182781470@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox