Edison Azzi wrote:
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;
OK - and you get a self-join (which is what you asked for, but you'd
like the planner to notice that it might not be necessary).
Resulting in twice the time for accessing.
Acessing just on time the same row:
select * from cta_pag p where p.nrlancto = 21861
This isn't the same query though. Your rule has an additional condition
origem='A'. This means it wouldn't be correct to eliminate the self-join
even if the planner could.
Is there a way to force the optimizer to understand that is the same
row?
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.
--
Richard Huxton
Archonet Ltd
