Re: explain analyze reports that my queries are fast but they run very slowly - Mailing list pgsql-performance

From Tom Lane
Subject Re: explain analyze reports that my queries are fast but they run very slowly
Date
Msg-id 17229.1356636060@sss.pgh.pa.us
Whole thread Raw
In response to explain analyze reports that my queries are fast but they run very slowly  (Nikolas Everett <nik9000@gmail.com>)
Responses Re: explain analyze reports that my queries are fast but they run very slowly  (Nikolas Everett <nik9000@gmail.com>)
List pgsql-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


pgsql-performance by date:

Previous
From: Stephen Frost
Date:
Subject: Re: Performance on Bulk Insert to Partitioned Table
Next
From: Pavel Stehule
Date:
Subject: Re: Performance on Bulk Insert to Partitioned Table