From: Tom Lane <tgl@sss.pgh.pa.us>
To: Edison Azzi <edisonazzi@terra.com.br>
Cc: Richard Huxton <dev@archonet.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Join the same row
Date: Sun, 11 Dec 2005 15:36:25 -0500
Message-ID: <18628.1134333385@sss.pgh.pa.us> (raw)
In-Reply-To: <43972DB8.90400@terra.com.br>
References: <4395F317.4040806@terra.com.br>
<4396AB05.2010806@archonet.com>
<43972DB8.90400@terra.com.br>
Edison Azzi <edisonazzi@terra.com.br> 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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: tgl@sss.pgh.pa.us, edisonazzi@terra.com.br, dev@archonet.com
Subject: Re: Join the same row
In-Reply-To: <18628.1134333385@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox