Re: Inner join on two OR conditions dont use index

From: Tom Lane
Subject: Re: Inner join on two OR conditions dont use index
Date: ,
Msg-id: 2679.1117057399@sss.pgh.pa.us
(view: Whole thread, Raw)
In response to: Inner join on two OR conditions dont use index  (Jocelyn Turcotte)
List: pgsql-performance

Tree view

Inner join on two OR conditions dont use index  (Jocelyn Turcotte, )
 Re: Inner join on two OR conditions dont use index  (John A Meinel, )
  Re: Inner join on two OR conditions dont use index  (Jocelyn Turcotte, )
 Re: Inner join on two OR conditions dont use index  (Tom Lane, )

Jocelyn Turcotte <> 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


pgsql-performance by date:

From: Christopher Kings-Lynne
Date:
Subject: Re: [PORTS] Which library has these symbols? -- Eureka
From: Sam Mason
Date:
Subject: Optimising queries involving unions