Re: Inner join on two OR conditions dont use index

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