From: Richard Huxton <dev@archonet.com>
To: Edison Azzi <edisonazzi@terra.com.br>
Cc: pgsql-performance@postgresql.org
Subject: Re: Join the same row
Date: Wed, 07 Dec 2005 09:27:33 +0000
Message-ID: <4396AB05.2010806@archonet.com> (raw)
In-Reply-To: <4395F317.4040806@terra.com.br>
References: <4395F317.4040806@terra.com.br>
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
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: dev@archonet.com, edisonazzi@terra.com.br
Subject: Re: Join the same row
In-Reply-To: <4396AB05.2010806@archonet.com>
* 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