From: Kim Bisgaard <kib@dmi.dk>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: pgsql-performance@postgresql.org
Subject: Re: full outer performance problem
Date: Wed, 11 May 2005 09:05:05 +0200
Message-ID: <4281AEA1.8020600@dmi.dk> (raw)
In-Reply-To: <10318.1115736342@sss.pgh.pa.us>
References: <428078E6.90007@dmi.dk>
<10318.1115736342@sss.pgh.pa.us>
Sorry for not listing the exact layout of temp_XXXX:
obsdb=> \d temp_dry_at_2m
Table "public.temp_dry_at_2m"
Column | Type | Modifiers
----------------+-----------------------------+-----------
obshist_id | integer | not null
station_id | integer | not null
timeobs | timestamp without time zone | not null
temp_dry_at_2m | real | not null
Indexes:
"temp_dry_at_2m_pkey" primary key, btree (obshist_id)
"temp_dry_at_2m_idx" btree (station_id, timeobs)
The difference between the two queries is if a (station_id,timeobs) row
is missing in one table, then the first returns one record(null,9.3)
while the second return no records.
Regards,
Kim Bisgaard.
Tom Lane wrote:
>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
>
>---------------------------(end of broadcast)---------------------------
>TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly
>
>
>
--
Kim Bisgaard
Computer Department Phone: +45 3915 7562 (direct)
Danish Meteorological Institute Fax: +45 3915 7460 (division)
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: kib@dmi.dk, tgl@sss.pgh.pa.us
Subject: Re: full outer performance problem
In-Reply-To: <4281AEA1.8020600@dmi.dk>
* 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