Re: Join the same row

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