Message-ID: <43973008.2000708@archonet.com>
Date: Wed, 07 Dec 2005 18:55:04 +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> <4396AB05.2010806@archonet.com>
	<43972DB8.90400@terra.com.br>
In-Reply-To: <43972DB8.90400@terra.com.br>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: quoted-printable

Edison Azzi wrote:
> Richard Huxton escreveu:
>> However, even if you removed the condition on origem, I don't think=20
>> 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=
=20
>> developers may correct me.
>=20
>=20
> You are rigth, the planner will not eliminate the join, see:
>=20
> select * from cta_pag a, cta_pag p where a.nrlancto=3Dp.nrlancto and=20
> p.nrlancto =3D 21861;
>=20
> EXPLAIN:
> Nested Loop  (cost=3D0.00..11.48 rows=3D1 width=3D816)
>  ->  Index Scan using cta_pag_pk on cta_pag a  (cost=3D0.00..5.74 rows=3D=
1=20
> width=3D408)
>        Index Cond: (21861::numeric =3D nrlancto)
>  ->  Index Scan using cta_pag_pk on cta_pag p  (cost=3D0.00..5.74 rows=3D=
1=20
> width=3D408)
>        Index Cond: (nrlancto =3D 21861::numeric)
>=20
>=20
> I know that this is too unusual case, but I hoped that the planner coul=
d=20
> deal
> with this condition. I=B4m trying to speed up without have to rewrite a=
=20
> bunch of
> queries. Now I'll have to think another way to work around this issue.

Is the performance really so bad? All the data is guaranteed to be=20
cached for the second index-scan.

--=20
   Richard Huxton
   Archonet Ltd