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: (view raw, whole thread or download thread mbox)
Lists: pgsql-hackers

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:

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 

		bc JOIN cd
		ON bc.c = cd.d
	ON ab.b = bc.b
	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...


If there is no relatively easy fix for this, any idea how to work around that 


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 )


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-2017 The PostgreSQL Global Development Group