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