Hi guys, I have a query that runs a lot slower (~5 minutes) when I run it with the default enable_nestloop=true and enable_nestloop=false (~10 secs). The actual query is available here http://pastie.org/2754424 . It is a reporting query with many joins as the database is mainly used for transaction processing. Explain analyse result for both cases: Machine A nestloop=true - http://explain.depesz.com/s/nkj0 (~5 minutes) Machine A nestloop=false - http://explain.depesz.com/s/wBM (~10 secs) On a different slightly slower machine (Machine B), copying the database over and leaving the default enable_nestloop=true it takes ~20 secs. Machine B nestloop=true - http://explain.depesz.com/s/dYO (~ 20secs) For all the cases above I ensured that I did an ANALYZE before running the queries. There were no other queries running in parallel. Both machines are running PostgreSQL 8.4.6. Machine B is using the default configuration provided by the package while for Machine A we applied the changes suggested by pgtune - http://pastie.org/2755113. Machine A is running Ubuntu 10.04 32 bit while Machine B is running Ubuntu 8.04 32 bit. Machine A spec - Intel(R) Xeon(R) CPU X3450 @ 2.67GHz (8 Cores) 8GB RAM (2 x 4GB) 4 x 300GB 15k SAS Machine B spec - Intel(R) Pentium(R) D CPU 2.80GHz x 2 2GB RAM 1 x 80GB SATA HDD 1. For Machine A, what can I do to make the planner choose the faster plan without setting enable_nestloop=false ? 2. From the research I have done it seems to be that the reason the planner is choosing the unoptimal query is because of the huge difference between the estimated and actual rows. How can I get this figure closer ? 3. If I should rewrite the query, what should I change ? 4. Why is it that the planner seems to be doing the right thing for Machine B without setting enable_nestloop=false. What should I be comparing in both the machines to understand the difference in choice that the planner made ? I have tried reading through the manual section "55.1. Row Estimation Examples", "14.2. Statistics Used by the Planner". I am still trying to fully apply the information to my specific case above and hence any help or pointers would be greatly appreciated. In a last ditch effort we also tried upgrading Machine A to PostgresSQL 9.1 and that did not rectify the issue. We have reverted the upgrade for now. Thank you for your time. -- Mohan -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
