The following bug has been logged on the website:

Bug reference: 6426
Logged by: Andrew Schetinin
Email address: aschetinin (AT) gmail (DOT) com
PostgreSQL version: 9.1.2
Operating system: Debian Linux
Description:

I have a complex query that, when unlimited, runs in 2.5 seconds and returns
400+ records.

The same query, with added "LIMIT 20 OFFSET 0" at the end, runs 25 seconds!
(and returns correct 20 records)

I think there is a bug in the query optimizer. Other variations of this
query (with slightly different constraints and joins) work fast with and
without LIMIT.

The same query works fast in PostgreSQL 8.4 (tested on another machine with
Ubuntu and not exactly the same but very-very similar database).

I can provide the query execution plans of the two cases (as long as they
are not published on the Web); but not the database because there is
customer's data in it.

Please let me know if anybody is interested in researching the problem.

Regards,

Andrew Schetinin


--
Sent via pgsql-bugs mailing list (pgsql-bugs (AT) postgresql (DOT) org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

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.

Regards,

Andrew

On Wed, Feb 1, 2012 at 9:42 PM, Alex Lai <alai (AT) sesda2 (DOT) com> wrote:



--
--
Andrew Schetinin

Andrew Schetinin <aschetinin (AT) gmail (DOT) com> writes:
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

--
Sent via pgsql-bugs mailing list (pgsql-bugs (AT) postgresql (DOT) org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

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.pa.us> wrote:



--
--
Andrew Schetinin

Andrew Schetinin <aschetinin (AT) gmail (DOT) com> writes:
If you have more than join_collapse_limit JOINs in the query, the order
can matter.

regards, tom lane

--
Sent via pgsql-bugs mailing list (pgsql-bugs (AT) postgresql (DOT) org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs