From: Edison Azzi <edisonazzi@terra.com.br>
To: pgsql-performance@postgresql.org
Subject: Join the same row
Date: Tue, 06 Dec 2005 18:22:47 -0200
Message-ID: <4395F317.4040806@terra.com.br> (raw)
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>
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: edisonazzi@terra.com.br
Subject: Re: Join the same row
In-Reply-To: <4395F317.4040806@terra.com.br>
* 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