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 DFD029DC940 for ; Thu, 19 Jan 2006 12:53:10 -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 50894-08 for ; Thu, 19 Jan 2006 12:53:07 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey- Received: from barracuda1.biscit.net (barracuda0-tel.biscit.net [82.195.96.18]) by postgresql.org (Postfix) with ESMTP id EABEA9DC822 for ; Thu, 19 Jan 2006 12:53:06 -0400 (AST) X-ASG-Debug-ID: 1137689585-31481-57-0 X-Barracuda-URL: http://82.195.96.18:80/cgi-bin/mark.cgi Received: from web1-glo.biscit.net (web1-glo.biscit.net [82.195.96.53]) by barracuda1.biscit.net (Spam Firewall) with ESMTP id BE69E14E112 for ; Thu, 19 Jan 2006 16:53:05 +0000 (GMT) Received: from bolek.coachhouse ([62.7.150.137]) by web1-glo.biscit.net (8.10.2/8.10.2) with ESMTP id k0JGr1i05947 for ; Thu, 19 Jan 2006 16:53:02 GMT Received: (Exim 3.36) #1 (Debian)) protocol: esmtp id 1Ezcra-0002Q8-00 ; Thu, 19 Jan 2006 16:42:18 +0000 Message-ID: <43CFC16A.5090604@numerixtechnology.de> Date: Thu, 19 Jan 2006 16:42:18 +0000 From: T E Schmitz Reply-To: mailreg@numerixtechnology.de User-Agent: Mozilla Thunderbird 1.0.6 (Windows/20050716) X-Accept-Language: en-us, en MIME-Version: 1.0 To: pgsql-sql X-ASG-Orig-Subj: Re: [SQL] indexing for left join Subject: Re: indexing for left join References: <43CFB325.9060306@numerixtechnology.de> <200601191703.51554.milorad.poluga@cores.co.yu> In-Reply-To: <200601191703.51554.milorad.poluga@cores.co.yu> Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: by Biscit Outgoing Spam Firewall at biscit.net X-Barracuda-Spam-Score: 0.00 X-Barracuda-Spam-Status: No, SCORE=0.00 using global scores of TAG_LEVEL=1000.0 QUARANTINE_LEVEL=1000.0 KILL_LEVEL=7.0 tests= X-Barracuda-Spam-Report: Code version 3.02, rules version 3.0.7556 Rule breakdown below pts rule name description ---- ---------------------- -------------------------------------------------- X-Barracuda-Rcpt: pgsql-sql@postgresql.org X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=0.08 required=5 tests=[AWL=0.080, UPPERCASE_25_50=0] X-Spam-Score: 0.08 X-Spam-Level: X-Archive-Number: 200601/134 X-Sequence-Number: 23923 Milorad Poluga wrote: > Try to execute this modification of your query : > > 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 > >>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 For my small test DB both queries result in the same strategy. The query will be generated by an object relational interface depending on the user's search criteria. It will definitely be of the form I specified. I wanted to make sure that I have chosen the indices correctly. I am presuming, if the tables are big, that the index on SERIAL_NO.NO will be used for the WHERE clause and the one on SERIAL_NO.ITEM_FK for the join. -- Regards, Tarlika Elisabeth Schmitz