X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by postgresql.org (Postfix) with ESMTP id A0AD19DCAC3 for ; Sun, 11 Dec 2005 16:36:30 -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 31146-06 for ; Sun, 11 Dec 2005 16:36:31 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey- Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by postgresql.org (Postfix) with ESMTP id 8B7EE9DCD40 for ; Sun, 11 Dec 2005 16:36:28 -0400 (AST) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.13.1/8.13.1) with ESMTP id jBBKaPZK018629; Sun, 11 Dec 2005 15:36:25 -0500 (EST) To: Edison Azzi cc: Richard Huxton , pgsql-performance@postgresql.org Subject: Re: Join the same row In-reply-to: <43972DB8.90400@terra.com.br> References: <4395F317.4040806@terra.com.br> <4396AB05.2010806@archonet.com> <43972DB8.90400@terra.com.br> Comments: In-reply-to Edison Azzi message dated "Wed, 07 Dec 2005 16:45:12 -0200" Date: Sun, 11 Dec 2005 15:36:25 -0500 Message-ID: <18628.1134333385@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=0.003 required=5 tests=[AWL=0.003] X-Spam-Score: 0.003 X-Spam-Level: X-Archive-Number: 200512/282 X-Sequence-Number: 16103 Edison Azzi writes: > You are rigth, the planner will not eliminate the join, see: > select * from cta_pag a, cta_pag p where a.nrlancto=p.nrlancto and > p.nrlancto = 21861; > EXPLAIN: > Nested Loop (cost=0.00..11.48 rows=1 width=816) > -> Index Scan using cta_pag_pk on cta_pag a (cost=0.00..5.74 rows=1 > width=408) > Index Cond: (21861::numeric = nrlancto) > -> Index Scan using cta_pag_pk on cta_pag p (cost=0.00..5.74 rows=1 > width=408) > Index Cond: (nrlancto = 21861::numeric) But do you care? That second fetch of the same row isn't going to cost much of anything, since everything it needs to touch will have been sucked into cache already. I don't really see the case for adding logic to the planner to detect this particular flavor of badly-written query. Notice that the planner *is* managing to propagate the constant comparison to both relations. regards, tom lane