[DERBY-6784] change optimizer to choose in list multiprobe more often

• Type: Improvement
• Status: Open
• Priority: Major
• Resolution: Unresolved
• Affects Version/s: 10.11.1.1
• Component/s: SQL
• Issue & fix info: High Value Fix
• Assignee: Unassigned
• Reporter: Mike Matrigali
• Created: 18/Dec/14 00:01
• Updated: 21/Feb/16 18:33

Description

Using the multi-probe join strategy is an obvious performance win when
the optimizer chooses it. There are cases currently where the costing
makes the optimizer choose other plans which do not perform as well as
the multi-probe strategy.

The class of queries that are affected are those where the number of terms
in the IN LIST is large relative to the number of rows in the table, and there
is a useful index to probe for the column that is referenced by the IN LIST.

There are multiple benefits to choosing the multi-probe strategy, including
the following:
1) often better execution time, where the alternative is to do a full table
merge on the column.
2) The multi-probe strategy results in "pushing" the work into the store,
and this may result in more concurrent behavior (see DERBY-6300 and DERBY-6301). First less rows may
be locked by probing rather than full table scan (and in the worst case
same number if query manages to probe on every value in table).
Second depending on isolation level of the query store will only matching
rows, while in the current implementation all rows that are returned by
store for qualification above store will remain locked whether they
qualify or not. Especially in small table cases other query plan choices
have been changed to favor probing indexes rather than full table scans
even if pure cpu is better with table scan.

Issue Links

• is related to: DERBY-6300 row locks incorrectly taken for rows that do not match SELECT predicate
• is related to: DERBY-47 Some possible improvements to IN optimization