From: Tom Lane <tgl@sss.pgh.pa.us>
To: Kim Bisgaard <kib+pg@dmi.dk>
Cc: pgsql-performance@postgresql.org
Subject: Re: full outer performance problem
Date: Wed, 08 Jun 2005 10:03:35 -0400
Message-ID: <1275.1118239415@sss.pgh.pa.us> (raw)
In-Reply-To: <42A6BC64.4010001@dmi.dk>
References: <42A6BC64.4010001@dmi.dk>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: tgl@sss.pgh.pa.us, kib+pg@dmi.dk
Subject: Re: full outer performance problem
In-Reply-To: <1275.1118239415@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox