DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com;
	h=received:message-id:date:from:reply-to:to:subject:mime-version:content-type:content-transfer-encoding:content-disposition;
	b=nfn/q4j/+HqWNjy03YSTG9Xnn95jmglUkVE33tS9Jf5HKdO7uKfA0q4BPQpQlD5ntX38t5vzRhPOcMgBDQXm216lYPOrIrP2Sl+jVnYY8fk9whcTo910d9qXiUzHgH++0muu7T2xd8VuRIQHVcDS8q0aAMOJJXXLwGqS7Im42f8=
Message-ID: <688c0b7505052513127df47894@mail.gmail.com>
Date: Wed, 25 May 2005 16:12:00 -0400
From: Jocelyn Turcotte <turcotte.j@gmail.com>
Reply-To: Jocelyn Turcotte <turcotte.j@gmail.com>
To: pgsql-performance@postgresql.org
Subject: Inner join on two OR conditions dont use index
Mime-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable
Content-Disposition: inline

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.

(I tried on postgresql 7.4 and 8.0.3, made my vacuum analyse just before)
Here is my simple query:

select *=20
from rt_node n, rt_edge e
where node_id =3D 2=20
and e.start_node_id =3D n.node_id;

which give me the following query plan:

 Nested Loop  (cost=3D0.00..79.46 rows=3D24 width=3D60)
   ->  Index Scan using rt_node_pkey on rt_node n  (cost=3D0.00..5.94
rows=3D1 width=3D36)
         Index Cond: (node_id =3D 2)
   ->  Index Scan using rt_edge_start_node on rt_edge e=20
(cost=3D0.00..73.28 rows=3D24 width=3D24)
         Index Cond: (start_node_id =3D 2)


But if I plug another condition with a OR like this:
select *=20
from rt_node n, rt_edge e
where node_id =3D 2=20
and (e.start_node_id =3D n.node_id or e.end_node_id =3D n.node_id);

I get this plan, it stop using the index!:

 Nested Loop  (cost=3D0.00..158.94 rows=3D4 width=3D60)
   Join Filter: (("inner".start_node_id =3D "outer".node_id) OR
("inner".end_node_id =3D "outer".node_id))
   ->  Index Scan using rt_node_pkey on rt_node n  (cost=3D0.00..5.94
rows=3D1 width=3D36)
         Index Cond: (node_id =3D 2)
   ->  Seq Scan on rt_edge e  (cost=3D0.00..81.60 rows=3D4760 width=3D24)

I tried SET enable_seqscan =3D OFF and it give me this (woah) :

 Nested Loop  (cost=3D100000000.00..100000158.94 rows=3D4 width=3D60)
   Join Filter: (("inner".start_node_id =3D "outer".node_id) OR
("inner".end_node_id =3D "outer".node_id))
   ->  Index Scan using rt_node_pkey on rt_node n  (cost=3D0.00..5.94
rows=3D1 width=3D36)
         Index Cond: (node_id =3D 2)
   ->  Seq Scan on rt_edge e  (cost=3D100000000.00..100000081.60
rows=3D4760 width=3D24)

These are my tables definitions:
CREATE TABLE rt_node (
    node_id         INTEGER     PRIMARY KEY
);

CREATE TABLE rt_edge (
    edge_id         INTEGER     PRIMARY KEY,
    start_node_id   INTEGER     NOT NULL,
    end_node_id     INTEGER     NOT NULL,
    CONSTRAINT start_node_ref FOREIGN KEY (start_node_id) REFERENCES
rt_node(node_id),
    CONSTRAINT end_node_ref FOREIGN KEY (end_node_id) REFERENCES
rt_node(node_id)
    );
  =20
    CREATE INDEX rt_edge_start_node ON rt_edge(start_node_id);
    CREATE INDEX rt_edge_end_node ON rt_edge(end_node_id);


I cant figure why it cant use my index
I know I can use a UNION instead on two query like the first one only
different on "start_node_id"/"end_node_id", and it works,
but this is a part of a bigger query which is already ugly and I hate
using 5 lines for something I could in 5 words.

thank you!