Skip site navigation (1) Skip section navigation (2)

Planner creating ineffective plans on LEFT OUTER joins

From: Andres Freund <andres(at)anarazel(dot)de>
To: pgsql-hackers(at)postgresql(dot)org
Subject: Planner creating ineffective plans on LEFT OUTER joins
Date: 2008-06-25 15:10:02
Message-ID: 200806251710.08456.andres@anarazel.de (view raw or flat)
Thread:
Lists: pgsql-hackers
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 *
FROM
	ab LEFT OUTER JOIN (
		bc JOIN cd
		ON bc.c = cd.d
	)
	ON ab.b = bc.b
	
WHERE
	ab.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 )

Responses

pgsql-hackers by date

Next:From: Andrew HammondDate: 2008-06-25 16:57:55
Subject: Re: the un-vacuumable table
Previous:From: Domingo Alvarez DuarteDate: 2008-06-25 12:12:07
Subject: Extended security/restriction to any role with login access

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group