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
--
Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
