From: Tom Lane <tgl@sss.pgh.pa.us>
To: Jocelyn Turcotte <turcotte.j@gmail.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Inner join on two OR conditions dont use index
Date: Wed, 25 May 2005 17:43:19 -0400
Message-ID: <2679.1117057399@sss.pgh.pa.us> (raw)
In-Reply-To: <688c0b7505052513127df47894@mail.gmail.com>
References: <688c0b7505052513127df47894@mail.gmail.com>
Jocelyn Turcotte <turcotte.j@gmail.com> writes:
> 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!:
I'm afraid you're stuck with faking it with a UNION for now; the current
planner is incapable of recognizing that a join OR condition can be
handled with OR indexscans.
FWIW, this is fixed for 8.1 --- in CVS tip, I get this from your
example:
QUERY PLAN
--------------------------------------------------------------------------------------------------
Nested Loop (cost=2.06..17.57 rows=2 width=16)
-> Index Scan using rt_node_pkey on rt_node n (cost=0.00..4.82 rows=1 width=4)
Index Cond: (node_id = 2)
-> Bitmap Heap Scan on rt_edge e (cost=2.06..12.47 rows=18 width=12)
Recheck Cond: ((e.start_node_id = "outer".node_id) OR (e.end_node_id = "outer".node_id))
-> BitmapOr (cost=2.06..2.06 rows=18 width=0)
-> Bitmap Index Scan on rt_edge_start_node (cost=0.00..1.03 rows=9 width=0)
Index Cond: (e.start_node_id = "outer".node_id)
-> Bitmap Index Scan on rt_edge_end_node (cost=0.00..1.03 rows=9 width=0)
Index Cond: (e.end_node_id = "outer".node_id)
(10 rows)
(This is with no data in the tables, so the cost estimates are small,
but it does show that the planner knows how to generate this kind of
query plan now.)
regards, tom lane
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: tgl@sss.pgh.pa.us, turcotte.j@gmail.com
Subject: Re: Inner join on two OR conditions dont use index
In-Reply-To: <2679.1117057399@sss.pgh.pa.us>
* 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