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

2012-02-02 Thread Tom Lane
Andrew Schetinin writes: > 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. If you have more than join_collapse_limit JOINs in the query

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

2012-02-02 Thread Andrew Schetinin
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

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

2012-02-01 Thread Tom Lane
Andrew Schetinin 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 n

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

2012-02-01 Thread Andrew Schetinin
Hi Alex, Thank you. I played with subqueries, but in this case I did not see a good enough improvement. 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.

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

2012-02-01 Thread Alex Lai
Andrew Schetinin wrote: Hi Alex, Thank you. I played with subqueries, but in this case I did not see a good enough improvement. 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 L

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

2012-02-01 Thread Alex Lai
ascheti...@gmail.com wrote: The following bug has been logged on the website: Bug reference: 6426 Logged by: Andrew Schetinin Email address: ascheti...@gmail.com PostgreSQL version: 9.1.2 Operating system: Debian Linux Description: I have a complex query that, when

[BUGS] BUG #6426: Complex query runs 10 times longer with "LIMIT 20"

2012-02-01 Thread aschetinin
The following bug has been logged on the website: Bug reference: 6426 Logged by: Andrew Schetinin Email address: ascheti...@gmail.com PostgreSQL version: 9.1.2 Operating system: Debian Linux Description: I have a complex query that, when unlimited, runs in 2.5 seconds