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: Tue, 10 May 2005 10:45:42 -0400
Message-ID: <10318.1115736342@sss.pgh.pa.us> (raw)
In-Reply-To: <428078E6.90007@dmi.dk>
References: <428078E6.90007@dmi.dk>
Kim Bisgaard <kib+pg@dmi.dk> writes:
> I have two BIG tables (virtually identical) with 3 NOT NULL columns
> Station_id, TimeObs, Temp_XXXX, with indexes on (Station_id, TimeObs)
> and valid ANALYSE (set statistics=100). I want to join the two tables
> with a FULL OUTER JOIN.
I'm confused. If the columns are NOT NULL, why isn't this a valid
transformation of your original query?
> select temp_max_60min,temp_dry_at_2m
> from station s natural join
> temp_dry_at_2m a full outer join temp_max_60min b using (station_id, timeobs)
> where s.wmo_id=6065
> and _a.timeobs='2004-1-1 0:0:0' and b._timeobs='2004-1-1 0:0:0'
> and '2004-1-1 0:0:0' between s.startdate and s.enddate;
Seems like it's not eliminating any rows that would otherwise succeed.
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: <10318.1115736342@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