Message-ID: <4396AB05.2010806@archonet.com>
Date: Wed, 07 Dec 2005 09:27:33 +0000
From: Richard Huxton <dev@archonet.com>
User-Agent: Thunderbird 1.5 (X11/20051025)
MIME-Version: 1.0
To: Edison Azzi <edisonazzi@terra.com.br>
Cc: pgsql-performance@postgresql.org
Subject: Re: Join the same row
References: <4395F317.4040806@terra.com.br>
In-Reply-To: <4395F317.4040806@terra.com.br>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: quoted-printable

Edison Azzi wrote:
> Hi,
>=20
> I=B4m trying to optimize some selects between 2 tables and the best way=
 I=20
> found was
> alter the first table and add the fields of the 2nd table. I adjusted=20
> the contents and
> now a have only one table with all info that I need. Now resides my=20
> problem, because
> of legacy queries I decided to make a Rule that replace the 2nd table.
>=20
> Until now all worked well, but I found when I make a join between de re=
sult
> table and de Rule, even tought is the same row in the same table, the=20
> optimizer
> generete two access for the same row:
> cta_pag is the table and ctapag_adm is the  rule.
>=20
> CREATE OR REPLACE RULE "_RETURN" AS
>    ON SELECT TO ctapag_adm DO INSTEAD  SELECT cta_pag.nrlancto,=20
> cta_pag.codconta, cta_pag.frequencia, cta_pag.nrlanctopai
>   FROM cta_pag
>  WHERE cta_pag.origem =3D 'A'::bpchar;
>=20
> This is one of the legacy queries:
>=20
> select * from cta_pag p , ctapag_adm a where a.nrlancto=3D p.nrlancto a=
nd=20
> p.nrlancto =3D 21861;

OK - and you get a self-join (which is what you asked for, but you'd=20
like the planner to notice that it might not be necessary).

>    Resulting in twice the time for accessing.
>=20
> Acessing just on time the same row:
>=20
> select * from cta_pag p where  p.nrlancto =3D 21861

This isn't the same query though. Your rule has an additional condition=20
origem=3D'A'. This means it wouldn't be correct to eliminate the self-joi=
n=20
even if the planner could.

>    Is there a way to force the optimizer to understand that is the same=
=20
> row?

However, even if you removed the condition on origem, I don't think the=20
planner will notice that it can eliminate the join. It's just too=20
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=20
developers may correct me.
--=20
   Richard Huxton
   Archonet Ltd