> Hi all, > > on our PostgreSQL 8.3.1 (CentOS 5.3 64-bit) two different query plans > for one of our (weird) queries are generated. One of the query plans > seems to be good (and is used most of the time). The other one is bad - > the query takes about 2 minutes and the database process, which is > executing the query, is cpu bound during this time. > > After several tries I was able to reproduce the problem when executing > the query with EXPLAIN ANALYZE. The bad query plan was generated only > seconds after the good one was used when executing the query. What's the > reasond for the different query plans? Statistics are up to date. > > ... Hi, please, when posting an explain plan, either save it into a file and provide a URL (attachments are not allowed here), or use explain.depesz.com or something like that. This wrapping makes the plan unreadable so it's much more difficult to help you. I've used the explain.depesz.com (this time): - good plan: http://explain.depesz.com/s/HX - bad plan: http://explain.depesz.com/s/gcr It seems the whole problem is caused by the 'Index Scan using ind_atobjval on atobjval t9' - in the first case it's executed only 775x, but in the second case it's moved to the nested loop (one level deeper) and suddenly it's executed 271250x. And that causes the huge increase in cost. Why is this happening? I'm not sure, but I'm not quite sure the statistics are up to data and precise enough - some of the steps state 'rows=1' estimate, but 'rows=775' in the actual results. Have you tried to increase target on the tables? That might provide more accurate stats, thus better estimates. regards Tomas -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
