Re: full outer performance problem - Mailing list pgsql-performance

From Kim Bisgaard
Subject Re: full outer performance problem
Date
Msg-id 4281AEA1.8020600@dmi.dk
Whole thread Raw
In response to Re: full outer performance problem  (Tom Lane <tgl@sss.pgh.pa.us>)
List pgsql-performance
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)

pgsql-performance by date:

Previous
From: Kim Bisgaard
Date:
Subject: Re: full outer performance problem
Next
From: Greg Stark
Date:
Subject: Re: ok you all win what is best opteron (I dont want a hosed system