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 3BCBC3A598E for ; Thu, 27 Jan 2005 08:19:49 +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 13567-05 for ; Thu, 27 Jan 2005 08:19:40 +0000 (GMT) Received: from moutng.kundenserver.de (moutng.kundenserver.de [212.227.126.187]) by svr1.postgresql.org (Postfix) with ESMTP id 57F363A5CFB for ; Thu, 27 Jan 2005 08:19:40 +0000 (GMT) Received: from [212.227.126.205] (helo=mrelayng.kundenserver.de) by moutng.kundenserver.de with esmtp (Exim 3.35 #1) id 1Cu4sN-0001Hq-00 for pgsql-performance@postgresql.org; Thu, 27 Jan 2005 09:19:39 +0100 Received: from [217.235.161.8] (helo=p4.local) by mrelayng.kundenserver.de with asmtp (Exim 3.35 #1) id 1Cu4sN-0001h1-00 for pgsql-performance@postgresql.org; Thu, 27 Jan 2005 09:19:39 +0100 Received: from [127.0.0.1] (localhost [127.0.0.1]) by p4.local (Postfix) with ESMTP id 80B7740B5 for ; Thu, 27 Jan 2005 09:19:38 +0100 (CET) Message-ID: <41F8A41A.5020301@freenet.de> Date: Thu, 27 Jan 2005 09:19:38 +0100 From: =?ISO-8859-1?Q?Sebastian_B=F6ck?= User-Agent: Mozilla/5.0 (X11; U; FreeBSD i386; en-US; rv:1.7.5) Gecko/20041225 X-Accept-Language: en-us, en MIME-Version: 1.0 To: pgsql-performance@postgresql.org Subject: Optimizing Outer Joins Content-Type: text/plain; charset=us-ascii; format=flowed Content-Transfer-Encoding: 7bit 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.782 tagged_above=0 required=5 tests=AWL, DNS_FROM_RFC_ABUSE X-Spam-Level: X-Archive-Number: 200501/527 X-Sequence-Number: 10217 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; So if i do a select * from c i get the following: test=# EXPLAIN SELECT * from g; QUERY PLAN ---------------------------------------------------------------- Hash Left Join (cost=2.45..8.91 rows=8 width=36) Hash Cond: ("outer".id = "inner".id) -> Seq Scan on a (cost=0.00..1.08 rows=8 width=4) -> Hash (cost=2.16..2.16 rows=116 width=36) -> Seq Scan on b (cost=0.00..2.16 rows=116 width=36) and a select id from c executes as test=# EXPLAIN SELECT id from g; QUERY PLAN --------------------------------------------------------------- Hash Left Join (cost=2.45..7.02 rows=8 width=4) Hash Cond: ("outer".id = "inner".id) -> Seq Scan on a (cost=0.00..1.08 rows=8 width=4) -> Hash (cost=2.16..2.16 rows=116 width=4) -> Seq Scan on b (cost=0.00..2.16 rows=116 width=4) 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. Is there a way to tell postgres not to do the extra work. My aim is to speed up lookup to complex joins. Thanks Sebastian