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 F0CF1542CE for ; Tue, 10 May 2005 11:45:40 -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 54078-02 for ; Tue, 10 May 2005 14:45:39 +0000 (GMT) Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by svr1.postgresql.org (Postfix) with ESMTP id 512DF54257 for ; Tue, 10 May 2005 11:45:39 -0300 (ADT) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.13.1/8.13.1) with ESMTP id j4AEjghw010319; Tue, 10 May 2005 10:45:42 -0400 (EDT) To: Kim Bisgaard Cc: pgsql-performance@postgresql.org Subject: Re: full outer performance problem In-reply-to: <428078E6.90007@dmi.dk> References: <428078E6.90007@dmi.dk> Comments: In-reply-to Kim Bisgaard message dated "Tue, 10 May 2005 11:03:34 +0200" Date: Tue, 10 May 2005 10:45:42 -0400 Message-ID: <10318.1115736342@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.007 tagged_above=0 required=5 tests=AWL X-Spam-Level: X-Archive-Number: 200505/141 X-Sequence-Number: 12305 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