Re: Query 4-5 times slower after ANALYZE

> In response to "Philippe Lang" <philippe(dot)lang(at)attiksystem(dot)ch>:
>>
>> I'm using Postgresql 8.3.6 under Freebsd 7.1.
>>
>> After a fresh restore of a customer dump (running version 8.2.7 at the
>> moment), a rather big query executes in about 30 seconds. As soon as I
>> run ANALYZE, it is instantly 4-5 times slower. I could check that
>> multiples times.
>>
>> Here is the EXPLAIN ANALYZE before the ANALYZE:
>> http://www.attiksystem.ch/postgresql/query_slower_after_analyze/before.txt
>>
>> And here the the EXPLAIN ANALYZE after the ANALYZE:
>> http://www.attiksystem.ch/postgresql/query_slower_after_analyze/after.txt
>>
>> Any idea what could be turned on/off in order not to have this slowdown
>> after the ANALYZE?
>
> I opened one of those links figuring I'd take a few minutes to see if I
> could
> muster up some advice ... and just started laughing ... definitely not the
> type of query that one can even understand in just a few minutes!
>
> Anyway, the real reason I posted -- I doubt if anyone will be able to make
> sense of a query plan that complex without the actual query, so you'll
> probably want to post it as well.

Yeah, I had the same problem ;-) Anyway the reason why this query is so
slow is scanning the "customers" table - before the analysis an index scan
is used (and it's really fast), while after the analyze a sequential scan
is used instead (it's scanned several times and it takes almost 30 seconds
every time).

The question is why is a sequential scan chosen instead of index scan -
the estimated row counts seem quite precise, so maybe there's something
wrong with the cost settings. Have you modified the _cost parameters? What
are the current values? See this

http://www.postgresql.org/docs/8.3/interactive/runtime-config-query.html

Try to increase the seq_page_cost a (you may do that for the session only)
and see if the performance improves. You may even disable the sequential
scan using enable_seqscan = off.

Another cause might be a low statistics target - try to increase it with

ALTER TABLE SET STATISTICS

but the funny thing is it's not necessarily the "customers" table ;-)

regards
Tomas