Hi all, I am trying to speed up a query on a DB I inherited and I am falling flat on my face . I changed a query from NOT IN to use NOT EXISTS and my query time went from 19000ms to several hours (~50000000 ms). this shocked me so much I pretty much had to post. This seems like a corner case of the planner not knowing that the nested-loops are going to turn out badly in this case. The planner choosing a 13hr nested loop here is basically the reason I am posting. I have played around with rewriting this query using some CTEs and a left join but thus far my results are not encouraging. Given what little I know , it seems like a LEFT JOIN where right_table.col is null gets the same performance and estimates as a NOT EXISTS. (and still picks a nested loop in this case) I can see where it all goes to hell time wise, turning off nested loops seems to keep it from running for hours for this query, but not something I am looking to do globally. The time is not really that much better than just leaving it alone with a NOT IN. two queries are at http://pgsql.privatepaste.com/a0b672bab0# the "pretty" explain versions : NOT IN (with large work mem - 1GB) http://explain.depesz.com/s/ukj NOT IN (with only 64MB for work_mem) http://explain.depesz.com/s/wT0 NOT EXISTS (with 64MB of work_mem) http://explain.depesz.com/s/EuX NOT EXISTS (with nested loop off. and 64MB of work_mem) http://explain.depesz.com/s/UXG LEFT JOIN/CTE (with nested loop off and 1GB of work_mem) http://explain.depesz.com/s/Hwm table defs, with estimated row counts (which all 100% match exact row count) http://pgsql.privatepaste.com/c2ff39b653 tried running an analyze across the whole database, no affect. I haven't gotten creative with explicit join orders yet . postgresql 9.0.2. willing to try stuff for people as I can run things on a VM for days and it is no big deal. I can't do that on production machines. thoughts ? ideas ? -Mark -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
