Received: from localhost (maia-1.hub.org [200.46.204.191]) by postgresql.org (Postfix) with ESMTP id 965D39FBA5D for ; Tue, 29 May 2007 22:47:01 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.191]) (amavisd-maia, port 10024) with ESMTP id 96942-03 for ; Tue, 29 May 2007 22:46:54 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.5 Received: from mailhub2.une.edu.au (mailhub.une.edu.au [129.180.1.222]) by postgresql.org (Postfix) with ESMTP id A7A749FB948 for ; Tue, 29 May 2007 22:46:54 -0300 (ADT) Received: from icarus.une.edu.au (icarus.une.edu.au [129.180.47.120]) by mailhub2.une.edu.au (Postfix) with ESMTP id 075A74158; Wed, 30 May 2007 11:46:50 +1000 (EST) Received: from kgb (unknown [129.180.47.225]) by icarus.une.edu.au (Postfix) with SMTP id AB88560197A; Wed, 30 May 2007 11:46:50 +1000 (EST) Date: Wed, 30 May 2007 11:54:21 +1000 From: Klint Gore To: "Tyrrill, Ed" Cc: pgsql-performance@postgresql.org Subject: Re: Very slow left outer join In-Reply-To: References: Message-Id: <465CD94D319.E8C2KG@129.180.47.120> MIME-Version: 1.0 Content-Type: text/plain; charset=US-ASCII Content-Transfer-Encoding: 7bit X-Mailer: Becky! ver 1.25.06 X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200705/595 X-Sequence-Number: 24985 On Tue, 29 May 2007 17:16:57 -0700, "Tyrrill, Ed" wrote: > mdsdb=# explain analyze select backupobjects.record_id from > backupobjects left outer join backup_location using(record_id) where > backup_id = 1071; [...] > > Here are the two tables in the query: > > mdsdb=# \d backup_location > Table "public.backup_location" > Column | Type | Modifiers > -----------+---------+----------- > record_id | bigint | not null > backup_id | integer | not null [...] > > mdsdb=# \d backupobjects > Table "public.backupobjects" > Column | Type | Modifiers > ----------------+-----------------------------+----------- > record_id | bigint | not null > dir_record_id | integer | > name | text | > extension | character varying(64) | > hash | character(40) | > mtime | timestamp without time zone | > size | bigint | > user_id | integer | > group_id | integer | > meta_data_hash | character(40) | Why are you using left join? The where condition is going to force the row to exist. klint. +---------------------------------------+-----------------+ : Klint Gore : "Non rhyming : : EMail : kg@kgb.une.edu.au : slang - the : : Snail : A.B.R.I. : possibilities : : Mail University of New England : are useless" : : Armidale NSW 2351 Australia : L.J.J. : : Fax : +61 2 6772 5376 : : +---------------------------------------+-----------------+