Re: Join the same row

Edison Azzi wrote:
> Richard Huxton escreveu:
>> However, even if you removed the condition on origem, I don't think
>> the planner will notice that it can eliminate the join. It's just too
>> unusual a case for the planner to have a rule for it.
>>
>> I might be wrong about the planner - I'm just another user. One of the
>> developers may correct me.
>
>
> 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)
>
>
> I know that this is too unusual case, but I hoped that the planner could
> deal
> with this condition. I´m trying to speed up without have to rewrite a
> bunch of
> queries. Now I'll have to think another way to work around this issue.

Is the performance really so bad? All the data is guaranteed to be
cached for the second index-scan.

--
   Richard Huxton
   Archonet Ltd