From: Jocelyn Turcotte <turcotte.j@gmail.com>
To: pgsql-performance@postgresql.org
Subject: Inner join on two OR conditions dont use index
Date: Wed, 25 May 2005 16:12:00 -0400
Message-ID: <688c0b7505052513127df47894@mail.gmail.com> (raw)
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 *
from rt_node n, rt_edge e
where node_id = 2
and e.start_node_id = n.node_id;
which give me the following query plan:
Nested Loop (cost=0.00..79.46 rows=24 width=60)
-> Index Scan using rt_node_pkey on rt_node n (cost=0.00..5.94
rows=1 width=36)
Index Cond: (node_id = 2)
-> Index Scan using rt_edge_start_node on rt_edge e
(cost=0.00..73.28 rows=24 width=24)
Index Cond: (start_node_id = 2)
But if I plug another condition with a OR like this:
select *
from rt_node n, rt_edge e
where node_id = 2
and (e.start_node_id = n.node_id or e.end_node_id = n.node_id);
I get this plan, it stop using the index!:
Nested Loop (cost=0.00..158.94 rows=4 width=60)
Join Filter: (("inner".start_node_id = "outer".node_id) OR
("inner".end_node_id = "outer".node_id))
-> Index Scan using rt_node_pkey on rt_node n (cost=0.00..5.94
rows=1 width=36)
Index Cond: (node_id = 2)
-> Seq Scan on rt_edge e (cost=0.00..81.60 rows=4760 width=24)
I tried SET enable_seqscan = OFF and it give me this (woah) :
Nested Loop (cost=100000000.00..100000158.94 rows=4 width=60)
Join Filter: (("inner".start_node_id = "outer".node_id) OR
("inner".end_node_id = "outer".node_id))
-> Index Scan using rt_node_pkey on rt_node n (cost=0.00..5.94
rows=1 width=36)
Index Cond: (node_id = 2)
-> Seq Scan on rt_edge e (cost=100000000.00..100000081.60
rows=4760 width=24)
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)
);
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!
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: turcotte.j@gmail.com
Subject: Re: Inner join on two OR conditions dont use index
In-Reply-To: <688c0b7505052513127df47894@mail.gmail.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