Received-SPF: pass (mubende.terra.com.br: domain of terra.com.br designates
	200.176.10.8 as permitted sender) client-ip=200.176.10.8;
	envelope-from=edisonazzi@terra.com.br; helo=[192.168.0.10];
Message-ID: <4395F317.4040806@terra.com.br>
Date: Tue, 06 Dec 2005 18:22:47 -0200
From: Edison Azzi <edisonazzi@terra.com.br>
User-Agent: Mozilla Thunderbird 1.0.6 (Windows/20050716)
MIME-Version: 1.0
To: pgsql-performance@postgresql.org
Subject: Join the same row
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 8bit

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>