Huan Ruan wrote: > is a lot slower than a nested loop join.
Giving actual numbers is more useful than terms like "a lot". Even better is to provide the output of EXPLAIN ANALYZZE rather than just EXPLAIN. This shows estimates against actual numbers, and give timings. For more suggestions see this page: http://wiki.postgresql.org/wiki/SlowQueryQuestions > I don't understand why the optimiser chooses the hash join in > favor of the nested loop. What can I do to get the optimiser to > make a better decision (nested loop in this case)? I have run > analyze on both tables. > Config changes are > > - shared_buffers = 6GB > - effective_cache_size = 18GB > - work_mem = 10MB > - maintenance_work_mem = 3GB As already suggested, there was a change made in 9.2 which may have over-penalized nested loops using index scans. This may be fixed in the next minor release. Also, as already suggested, you may want to reduce random_page cost, to bring it in line with the actual cost relative to seq_page_cost based on your cache hit ratio. Additionally, I just routinely set cpu_tuple_cost higher than the default of 0.01. I find that 0.03 to 0.05 better models the actual relative cost of processing a tuple. -Kevin -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
