Re: [PERFORM] full outer performance problem

[Kim Bisgaard]

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 <[EMAIL PROTECTED]> 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 [EMAIL PROTECTED] 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)