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 9E4153A5E0C for ; Thu, 27 Jan 2005 10:18:59 +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 35221-08 for ; Thu, 27 Jan 2005 10:18:52 +0000 (GMT) Received: from anchor-post-30.mail.demon.net (anchor-post-30.mail.demon.net [194.217.242.88]) by svr1.postgresql.org (Postfix) with ESMTP id 4C6CC3A5B21 for ; Thu, 27 Jan 2005 10:18:53 +0000 (GMT) Received: from mwynhau.demon.co.uk ([193.237.186.96] helo=mainbox.archonet.com) by anchor-post-30.mail.demon.net with esmtp (Exim 4.42) id 1Cu6jh-000PPv-1l; Thu, 27 Jan 2005 10:18:52 +0000 Received: from [192.168.1.17] (client17.archonet.com [192.168.1.17]) by mainbox.archonet.com (Postfix) with ESMTP id EE480159B7; Thu, 27 Jan 2005 10:18:38 +0000 (GMT) Message-ID: <41F8BFFB.5020605@archonet.com> Date: Thu, 27 Jan 2005 10:18:35 +0000 From: Richard Huxton User-Agent: Mozilla Thunderbird 1.0 (X11/20041206) X-Accept-Language: en-us, en MIME-Version: 1.0 To: =?ISO-8859-1?Q?Sebastian_B=F6ck?= Cc: pgsql-performance@postgresql.org Subject: Re: Optimizing Outer Joins References: <41F8A41A.5020301@freenet.de> In-Reply-To: <41F8A41A.5020301@freenet.de> Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 8bit X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.069 tagged_above=0 required=5 tests=AWL, FORGED_RCVD_HELO X-Spam-Level: X-Archive-Number: 200501/529 X-Sequence-Number: 10219 Sebastian Böck wrote: > Hello, > > if i have the following (simple) table layout: > > create table a ( > id serial primary key > ); > > create table b ( > id integer references a, > test text > ); > > create view c as > select a.id,b.test from a > left join b > on a.id = b.id; > test=# EXPLAIN SELECT * from g; > test=# EXPLAIN SELECT id from g; > so the only difference is the width estimation. > > 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. It's only unnecessary in the case where there is a 1:1 correspondence between a.id and b.id - if you had more than one matching row in "b" then there'd be repeated rows from "a" in the result. Not sure if PG can tell what the situation is regarding references and pkeys, but in your example you don't have one anyway. -- Richard Huxton Archonet Ltd