Re: Join the same row

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