Re: How to avoid seq scans for joins between union-all views (test case included)

Hi Denis and Cédric

Thanks for your answers.

> Fredrick, What indexes Oracle did choose ? (index-only scan ?)

Oracle chooses a plan which looks like this:
SELECT STATEMENT Optimizer=ALL_ROWS (Cost=5 Card=7 Bytes=182)
  VIEW OF 'CONNECTIONS_V' (VIEW) (Cost=5 Card=7 Bytes=182)
    UNION-ALL
      INLIST ITERATOR
        TABLE ACCESS (BY INDEX ROWID) OF 'CONNECTIONS' (TABLE) (Cost=5
Card=6 Bytes=54)
          INDEX (RANGE SCAN) OF 'CONNECTIONS_NODE_IDX' (INDEX) (Cost=4 Card=6)
      INLIST ITERATOR
        TABLE ACCESS (BY INDEX ROWID) OF 'CONNECTIONS_LOCKED' (TABLE)
(Cost=0 Card=1 Bytes=39)
          INDEX (RANGE SCAN) OF 'CONNECTIONS_LOCKED_NODE_IDX' (INDEX)
(Cost=0 Card=1)

This means that only the indexes of connections.node and
connections_locked.node are used.

I don't think that we want to use any index for locked_by here,
we are hoping for the node = <value> predicate to be pushed
into both halves of the union all view (not sure if this is the right
terminology).

For example, in the simplified-but-still-problematic query
select con2.obj_id from  connections_v con2 where con2.node in (select 1015);
we are hoping for the node-index to be used for both connections and
connections_locked.

We hope to get the same plan/performance as for this query:
select con2.obj_id from connections_v con2 where con2.node in (1015);
I don't understand why there is a difference between "in (select
1015)" and "in (1015)"?

> That said, note that index usage depends on your data distribution: postgres
> may identify that it'll read most/all of the table anyway, and opt to do a
> (cheaper) seq scan instead.

Yes, I know, but I've tried to create the test case data distribution in a way
I hope makes this unlikely (0.5 million rows in one table, 25000 in the
other table, two rows in each table for each distinct value of node, only
a few rows returned from the queries.

Thanks again for you answers so far
/Fredrik