Hi,
After pondering on the problem for quite some time and discussing it on IRC
with RhodiumToad I thought the most sensible thing is to post the problem
here (as RhodiumToad suggested as well).
The original (although already quite reduced) problematic query and the
related plan:
http://anarazel.de/postgres/orig_query.sql
http://anarazel.de/postgres/orig_query.plan
I.e. it builds the right side of the LEFT JOIN for all elements it could
possibly contain and not only for the ones which exist on the left side.
(Database is freshly VACUUM ANALYZE'd)
Perhaps I expect to much from the planner here?
With this query this is not much of a problem, but the plan is the same if the
inner part of the query yields some million rows (and possibly is not only
In order to make testing easier I tried to reproduce the problem (with help of
RhodiumToad):
http://anarazel.de/postgres/create_testtables.sql
Testquery:
SELECT *
FROMab LEFT OUTER JOIN ( bc JOIN cd ON bc.c = cd.d)ON ab.b = bc.b
WHEREab.a = 20000
As ab.a = 20000 occurs only once in ab one would expect that it just does an
index scan on bc for ab.b = bc.b.
Unfortunately it builds the complete right side of the join first, and then
selects the one element it needs...
Queryplan:
http://anarazel.de/postgres/testtable_query1.plan
If there is no relatively easy fix for this, any idea how to work around that
problem?
Thanks,
Andres Freund
PS: Tested with 8.3.3 and 8.2.7. The problem was the same since 8.0 though (I
didn't test earlier versions )