From: Tom Lane <tgl@sss.pgh.pa.us>
To: Nikolas Everett <nik9000@gmail.com>
Cc: pgsql-performance@postgresql.org <pgsql-performance@postgresql.org>
Subject: Re: explain analyze reports that my queries are fast but they run very slowly
Date: Thu, 27 Dec 2012 14:21:00 -0500
Message-ID: <17229.1356636060@sss.pgh.pa.us> (raw)
In-Reply-To: <CAPmjWd2XgV9d3zy3zc8L9PJRdoVM720g2zSQWsDKZsJkOqKMjw@mail.gmail.com>
References: <CAPmjWd2XgV9d3zy3zc8L9PJRdoVM720g2zSQWsDKZsJkOqKMjw@mail.gmail.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-performance>
Nikolas Everett <nik9000@gmail.com> writes:
> We just upgraded from 8.3 to 9.1 and we're seeing some performance
> problems. When we EXPLAIN ANALYZE our queries the explain result claim
> that the queries are reasonably fast but the wall clock time is way way
> longer. Does anyone know why this might happen?
> Like so:
> db=>\timing
> db=>EXPLAIN ANALYZE SELECT max(id) FROM foo WHERE blah_id = 1209123;
> The plan is sensible. The estimates are sensible. The actual DB time
> reads like it is very sensible. But the wall clock time is like 11 seconds
> and the \timing report confirms it.
Seems like the extra time would have to be in parsing/planning, or in
waiting to acquire AccessShareLock on the table. It's hard to believe
the former for such a simple query, unless the table has got thousands
of indexes or something silly like that. Lock waits are surely possible
if there is something else contending for exclusive lock on the table,
but it's hard to see how the wait time would be so consistent.
BTW, the explain.depesz.com link you posted clearly does not correspond
to the above query (it's not doing a MAX), so another possibility is
confusion about what query is really causing trouble. We've seen people
remove essential details before while trying to anonymize their query.
regards, tom lane
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
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, nik9000@gmail.com
Subject: Re: explain analyze reports that my queries are fast but they run very slowly
In-Reply-To: <17229.1356636060@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