Hi Tom, Thanks for pointing the FAQ out, I did not see it. I especially liked the link to http://explain.depesz.com - it's a useful tool.
I succeeded to fix my problem by changing the order of JOINs (the query remained exactly the same otherwise). According to EXPLAIN ANALIZE, it eliminated those problematic nested loop joins. BTW, changing the order of JOINs did not affect the times of the query without LIMIT - it works the same way, while for the query with LIMIT it fixed the problem. Previously I always thought that the order of JOINs or conditions in WHERE is irrelevant, and query optimizer rearranges the order according to its logic. Now it appears that sometimes it may be important. Regards, Andrew On Wed, Feb 1, 2012 at 11:48 PM, Tom Lane <t...@sss.pgh.pa.us> wrote: > Andrew Schetinin <ascheti...@gmail.com> writes: > > In my specific case, what I've seen from the query execution plans, is > that > > without LIMIT the query uses Hash Joins, but once I add LIMIT, it starts > > using Nested Loop Joins almost everywhere. > > Usually, that's an appropriate change for a small LIMIT. It's certainly > not a priori evidence of a planner bug. > > If you want useful comments about this, please review > http://wiki.postgresql.org/wiki/Slow_Query_Questions > about how to provide an adequate description of your problem. > > regards, tom lane > -- -- Andrew Schetinin