Join the same row

Hi,

I´m trying to optimize some selects between 2 tables and the best way I
found was
alter the first table and add the fields of the 2nd table. I adjusted
the contents and
now a have only one table with all info that I need. Now resides my
problem, because
of legacy queries I decided to make a Rule that replace the 2nd table.

Until now all worked well, but I found when I make a join between de result
table and de Rule, even tought is the same row in the same table, the
optimizer
generete two access for the same row:
cta_pag is the table and ctapag_adm is the  rule.

CREATE OR REPLACE RULE "_RETURN" AS
    ON SELECT TO ctapag_adm DO INSTEAD  SELECT cta_pag.nrlancto,
cta_pag.codconta, cta_pag.frequencia, cta_pag.nrlanctopai
   FROM cta_pag
  WHERE cta_pag.origem = 'A'::bpchar;

This is one of the legacy queries:

select * from cta_pag p , ctapag_adm a where a.nrlancto= p.nrlancto and
p.nrlancto = 21861;

EXPLAIN:
Nested Loop  (cost=0.00..11.49 rows=1 width=443) (actual
time=0.081..0.088 rows=1 loops=1)
  ->  Index Scan using cta_pag_pk on cta_pag p  (cost=0.00..5.74 rows=1
width=408) (actual time=0.044..0.046 rows=1 loops=1)
        Index Cond: (nrlancto = 21861::numeric)
  ->  Index Scan using cta_pag_pk on cta_pag  (cost=0.00..5.74 rows=1
width=35) (actual time=0.023..0.025 rows=1 loops=1)
        Index Cond: (21861::numeric = nrlancto)
        Filter: (origem = 'A'::bpchar)
Total runtime: 0.341 ms


    Resulting in twice the time for accessing.

Acessing just on time the same row:

select * from cta_pag p where  p.nrlancto = 21861

EXPLAIN:
Index Scan using cta_pag_pk on cta_pag p  (cost=0.00..5.74 rows=1
width=408) (actual time=0.044..0.047 rows=1 loops=1)
  Index Cond: (nrlancto = 21861::numeric)
Total runtime: 0.161 ms


    Is there a way to force the optimizer to understand that is the same
row?

    Thanks,
    Edison


--
Edison Azzi
<edisonazzi (at ) terra ( dot ) com ( dot ) br>