X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by postgresql.org (Postfix) with ESMTP id B10199DC824 for ; Sat, 21 Jan 2006 09:51:58 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 63606-10 for ; Sat, 21 Jan 2006 09:52:02 -0400 (AST) Received: from svr4.postgresql.org (svr4.postgresql.org [66.98.251.159]) by postgresql.org (Postfix) with ESMTP id B68E09DC816 for ; Sat, 21 Jan 2006 09:51:56 -0400 (AST) Received: from anchor-post-34.mail.demon.net (anchor-post-34.mail.demon.net [194.217.242.92]) by svr4.postgresql.org (Postfix) with ESMTP id 72A475AF85B for ; Sat, 21 Jan 2006 13:52:01 +0000 (GMT) Received: from alam.demon.co.uk ([80.177.24.103] helo=[127.0.0.1]) by anchor-post-34.mail.demon.net with esmtp (Exim 4.42) id 1F0J9p-000HnR-Em for pgsql-sql@postgresql.org; Sat, 21 Jan 2006 13:51:58 +0000 Message-ID: <43D23C7C.1000108@orange.net> Date: Sat, 21 Jan 2006 13:51:56 +0000 From: Zulq Alam User-Agent: Thunderbird 1.5 (Windows/20051201) MIME-Version: 1.0 To: pgsql-sql Subject: Re: indexing for left join References: <43CFB325.9060306@numerixtechnology.de> In-Reply-To: <43CFB325.9060306@numerixtechnology.de> Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=1.919 required=5 tests=[DNS_FROM_RFC_ABUSE=0.479, DNS_FROM_RFC_POST=1.44, UPPERCASE_25_50=0] X-Spam-Score: 1.919 X-Spam-Level: * X-Archive-Number: 200601/139 X-Sequence-Number: 23928 I am new to PostgreSQL but isn't this query the same as doing an INNER JOIN? For a true LEFT JOIN should it not be as follows? SELECT ITEM.ITEM_PK FROM ITEM LEFT JOIN SERIAL_NO ON SERIAL_NO.ITEM_FK = ITEM.ITEM_PK AND SERIAL_NO.NO ='WX1234' GROUP BY ITEM.ITEM_PK Using an AND instead of WHERE for the predicate on SERIAL_NO.NO results in very different plans despite the immature statistics. The following plan is for the true LEFT JOIN. QUERY PLAN --------------------------------------------------------------------------- HashAggregate (cost=2.10..2.13 rows=3 width=4) -> Hash Left Join (cost=1.04..2.10 rows=3 width=4) Hash Cond: ("outer".item_pk = "inner".item_fk) -> Seq Scan on item (cost=0.00..1.03 rows=3 width=4) -> Hash (cost=1.04..1.04 rows=1 width=4) -> Seq Scan on serial_no (cost=0.00..1.04 rows=1 width=4) Filter: (("no")::text = 'WX1234'::text) (7 rows) The next plan, which is very similary to your original plan, is for the INNER JOIN you described. QUERY PLAN --------------------------------------------------------------------- HashAggregate (cost=2.11..2.12 rows=1 width=4) -> Nested Loop (cost=0.00..2.11 rows=1 width=4) Join Filter: ("outer".item_fk = "inner".item_pk) -> Seq Scan on serial_no (cost=0.00..1.04 rows=1 width=4) Filter: (("no")::text = 'WX1234'::text) -> Seq Scan on item (cost=0.00..1.03 rows=3 width=4) (6 rows) I wont speculate on how these plans would converge or diverge as the tables grew and the statistics matured. - Zulq Alam T E Schmitz wrote: > SELECT ITEM.ITEM_PK FROM ITEM > LEFT JOIN SERIAL_NO ON SERIAL_NO.ITEM_FK = ITEM.ITEM_PK > WHERE SERIAL_NO.NO ='WX1234' > GROUP BY ITEM.ITEM_PK > I ran an EXPLAIN: > HashAggregate (cost=1.06..1.06 rows=1 width=4) > -> Nested Loop (cost=0.00..1.06 rows=1 width=4) > Join Filter: ("inner".item_fk = "outer".item_pk) > -> Seq Scan on item (cost=0.00..0.00 rows=1 width=4) > -> Seq Scan on serial_no (cost=0.00..1.05 rows=1 width=4) > Filter: (("no")::text = 'WX1234'::text)