> In response to "Philippe Lang" <philippe.lang@xxxxxxxxxxxxxx>: >> >> 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 - Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
