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

On 27/12/12 17:21, François Beausoleil wrote:
>
> Le 2012-12-27 à 12:10, Nikolas Everett a écrit :
>
>> 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?
>>

Is it possible you missed an optimisation setting in the migration
process? I made that mistake, and much later found performance was
somewhat degraded (but surprisingly not as much as I'd expected) by my
having failed to set effective_cache_size.

Also, if you just did a dump/restore, it might help to run Analyse once
(it seems that Analyse is normally run automatically via vacuum, but the
first time you insert the data, it may not happen).

A side-effect of Analyse it that it will pull all the tables into the OS
memory cache (or try to) - which may give significantly faster results
(try running the same query twice in succession: it's often 5x faster
the 2nd time).

HTH,

Richard