Re: explain analyze reports that my queries are fast but they run very slowly

Nikolas Everett <nik9000(at)gmail(dot)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