To: Kim Bisgaard <kib+pg@dmi.dk>
Cc: pgsql-performance@postgresql.org
Subject: Re: full outer performance problem 
In-reply-to: <42A6BC64.4010001@dmi.dk> 
References: <42A6BC64.4010001@dmi.dk>
Comments: In-reply-to Kim Bisgaard <kib+pg@dmi.dk>
	message dated "Wed, 08 Jun 2005 11:37:40 +0200"
Date: Wed, 08 Jun 2005 10:03:35 -0400
Message-ID: <1275.1118239415@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>

Kim Bisgaard <kib+pg@dmi.dk> writes:
> SELECT station_id, timeobs,temp_grass, temp_dry_at_2m
>         FROM temp_dry_at_2m a
>         FULL OUTER JOIN temp_grass b 
>         USING (station_id, timeobs)
>         WHERE station_id = 52981
>           AND timeobs = '2004-1-1 0:0:0'

> explain analyse SELECT b.station_id, b.timeobs,temp_grass, temp_dry_at_2m
>         FROM temp_dry_at_2m a
>         FULL OUTER JOIN temp_grass b
>         USING (station_id, timeobs)
>         WHERE b.station_id = 52981
>           AND b.timeobs = '2004-1-1 0:0:0'

> Why will PostgreSQL not use the same plan for both these queries - they 
> are virtually identical??

Because they're semantically completely different.  The second query is
effectively a RIGHT JOIN, because join rows in which b is all-null will
be thrown away by the WHERE.  The optimizer sees this (note your second
plan doesn't use a Full Join step anywhere) and is able to produce a
much better plan.  Full outer join is difficult to optimize, in part
because we have no choice but to use a merge join for it --- the other
join types don't support full join.

			regards, tom lane