X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 06BED54A47 for ; Wed, 11 May 2005 04:06:14 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 55224-03 for ; Wed, 11 May 2005 07:06:11 +0000 (GMT) Received: from mailgw.dmi.dk (mailgw.dmi.dk [130.226.71.185]) by svr1.postgresql.org (Postfix) with ESMTP id A60F65295C for ; Wed, 11 May 2005 04:06:01 -0300 (ADT) Received: from localhost (localhost.dmi.dk [127.0.0.1]) by mailgw.dmi.dk (8.12.11/8.12.11/Debian-1) with ESMTP id j4B75jcV003771; Wed, 11 May 2005 07:05:45 GMT Received: from mailgw.dmi.dk ([127.0.0.1]) by localhost (mailgw.dmi.dk [127.0.0.1]) (amavisd-new, port 10024) with LMTP id 03530-10-2; Wed, 11 May 2005 07:05:29 +0000 (GMT) Received: from postoffice.dmi.dk (postoffice.dmi.dk [130.226.64.60]) by mailgw.dmi.dk (8.12.11/8.12.11/Debian-1) with ESMTP id j4B755dr003753; Wed, 11 May 2005 07:05:05 GMT Received: from [130.226.66.16] (anders [130.226.66.16]) by postoffice.dmi.dk (Postfix) with ESMTP id 6301318042; Wed, 11 May 2005 07:05:05 +0000 (GMT) Message-ID: <4281AEA1.8020600@dmi.dk> Date: Wed, 11 May 2005 09:05:05 +0200 From: Kim Bisgaard User-Agent: Mozilla Thunderbird 1.0.2 (X11/20050317) X-Accept-Language: en-us, en MIME-Version: 1.0 To: Tom Lane Cc: pgsql-performance@postgresql.org Subject: Re: full outer performance problem References: <428078E6.90007@dmi.dk> <10318.1115736342@sss.pgh.pa.us> In-Reply-To: <10318.1115736342@sss.pgh.pa.us> Content-Type: multipart/alternative; boundary="------------080403040502040602020306" X-Virus-Scanned: by amavisd-maia-1.0.0-rc5 (Debian) at dmi.dk X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.066 tagged_above=0 required=5 tests=AWL, HTML_40_50, HTML_MESSAGE, HTML_TITLE_EMPTY X-Spam-Level: X-Archive-Number: 200505/288 X-Sequence-Number: 12452 This is a multi-part message in MIME format. --------------080403040502040602020306 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit 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 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) --------------080403040502040602020306 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit 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)
--------------080403040502040602020306--