Re: Inner join on two OR conditions dont use index

От: Tom Lane
Тема: Re: Inner join on two OR conditions dont use index
Дата: ,
Msg-id: 2679.1117057399@sss.pgh.pa.us
(см: обсуждение, исходный текст)
Ответ на: Inner join on two OR conditions dont use index  (Jocelyn Turcotte)
Список: pgsql-performance

Скрыть дерево обсуждения

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 по дате сообщения:

От: Christopher Kings-Lynne
Дата:
Сообщение: Re: [PORTS] Which library has these symbols? -- Eureka
От: Sam Mason
Дата:
Сообщение: Optimising queries involving unions