DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com;
	h=received:message-id:date:from:reply-to:to:subject:cc:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references;
	b=XgKTXcdeUgsk/yNSYMYWHdmF7m9UM5WIw3m1meh5oen6fxA39HVvxnUxZO75mP32nlb+tI9A6VVHo54LPFFjfrJTlqeJSeCF52n248J4banlwv2FwtNdVaBDZAJrwvws8siT6uHl4iQ5Q6bONAyj8IoR3Kl9/tXRbCHDrhhCCWM=
Message-ID: <688c0b7505052514227ee968ce@mail.gmail.com>
Date: Wed, 25 May 2005 17:22:10 -0400
From: Jocelyn Turcotte <turcotte.j@gmail.com>
Reply-To: Jocelyn Turcotte <turcotte.j@gmail.com>
To: John A Meinel <john@arbash-meinel.com>
Subject: Re: Inner join on two OR conditions dont use index
Cc: pgsql-performance@postgresql.org
In-Reply-To: <4294DDAC.1060908@arbash-meinel.com>
Mime-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable
Content-Disposition: inline
References: <688c0b7505052513127df47894@mail.gmail.com>
	<4294DDAC.1060908@arbash-meinel.com>

Thanks John

it dont seems to work, but in my context I only needed data from the
rt_node table so I tried this:

select *
from rt_node n
where node_id =3D 2
and exists (select edge_id from rt_edge where start_node_id =3D
n.node_id or end_node_id =3D n.node_id)

and it gave me this plan (even if I remove the stupid node_id =3D 2 conditi=
on):

 Index Scan using rt_node_pkey on rt_node n  (cost=3D0.00..6.15 rows=3D1 wi=
dth=3D25)
   Index Cond: (node_id =3D 2)
   Filter: (subplan)
   SubPlan
     ->  Index Scan using rt_edge_start_node, rt_edge_end_node on
rt_edge  (cost=3D0.00..12.56 rows=3D4 width=3D4)
           Index Cond: ((start_node_id =3D $0) OR (end_node_id =3D $0))


this time it use my two indexes, maybe because he know that the same
value is compared in the two condition... I should ask my mother if
she got an idea, mothers know a lot of stuff!

On 5/25/05, John A Meinel <john@arbash-meinel.com> wrote:
> Jocelyn Turcotte wrote:
>=20
> >Hi all
> >i dont know if this is normal, but if yes i would like to know why and
> >how I could do it another way other than using unions.
> >
> >
>=20
> The only thing that *might* work is if you used an index on both keys.
> So if you did:
>=20
> CREATE INDEX rt_edge_start_end_node ON rt_edge(start_node_id,end_node_id)=
;
>=20
> The reason is that in an "OR" construct, you have to check both for being=
 true. So in the general case where you don't know the correlation between =
the columns, you have to check all of the entries, because even if you know=
 the status of one side of the OR, you don't know the other.
>=20
> Another possibility would be to try this index:
>=20
> CREATE INDEX rt_edge_stare_or_end ON rt_edge(start_node_id OR end_node_id=
);
>=20
> I'm not sure how smart the planner can be, though.
>=20
> John
> =3D:->
>=20
>=20
>=20
>=20
>