Re: BUG #6426: Complex query runs 10 times longer with "LIMIT 20"

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 <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> Andrew Schetinin <aschetinin(at)gmail(dot)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