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 B059A3A5E84 for ; Sat, 29 Jan 2005 12:26:21 +0000 (GMT) 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 78228-03 for ; Sat, 29 Jan 2005 12:26:09 +0000 (GMT) Received: from moutng.kundenserver.de (moutng.kundenserver.de [212.227.126.173]) by svr1.postgresql.org (Postfix) with ESMTP id 39AC83A6393 for ; Sat, 29 Jan 2005 12:26:11 +0000 (GMT) Received: from [212.227.126.205] (helo=mrelayng.kundenserver.de) by moutng.kundenserver.de with esmtp (Exim 3.35 #1) id 1Curg1-00080B-00; Sat, 29 Jan 2005 13:26:09 +0100 Received: from [217.235.166.92] (helo=p4.local) by mrelayng.kundenserver.de with asmtp (Exim 3.35 #1) id 1Curg1-0004l0-00; Sat, 29 Jan 2005 13:26:09 +0100 Received: from [172.16.1.6] (ibook.local [172.16.1.6]) by p4.local (Postfix) with ESMTP id CEE6340CC; Sat, 29 Jan 2005 13:26:09 +0100 (CET) Message-ID: <41FA3065.70003@freenet.de> Date: Fri, 28 Jan 2005 13:30:29 +0100 From: =?ISO-8859-1?Q?Sebastian_B=F6ck?= User-Agent: Mozilla Thunderbird 1.0 (Macintosh/20041206) X-Accept-Language: en-us, en MIME-Version: 1.0 To: Richard Huxton Cc: pgsql-performance@postgresql.org Subject: Re: Optimizing Outer Joins References: <41F8A41A.5020301@freenet.de> <41F8BFFB.5020605@archonet.com> In-Reply-To: <41F8BFFB.5020605@archonet.com> Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: quoted-printable X-Provags-ID: kundenserver.de abuse@kundenserver.de auth:9060023f140952ea5098d12d38573a7d X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.531 tagged_above=0 required=5 tests=AWL, DNS_FROM_RFC_ABUSE X-Spam-Level: X-Archive-Number: 200501/606 X-Sequence-Number: 10296 Richard Huxton wrote: > Sebastian B=F6ck wrote: >> But why is the scan on table b performed? >> If i understand it correctly this is unnecessary because the >> result contains only rows from table a. >=20 >=20 > It's only unnecessary in the case where there is a 1:1 correspondence=20 > between a.id and b.id - if you had more than one matching row in "b"=20 > then there'd be repeated rows from "a" in the result. Not sure if PG ca= n=20 > tell what the situation is regarding references and pkeys, but in your= =20 > example you don't have one anyway. Ok, is there a way to avoid the extra scan if only one row is returned (distinc on for example)? What would be great is if a subselect could work with more than one column returning. Is there a way to achieve this? Thanks Sebastian