[
https://issues.apache.org/jira/browse/DERBY-6784?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Mike Matrigali reassigned DERBY-6784:
-------------------------------------
Assignee: Mike Matrigali
> change optimizer to choose in list multiprobe more often
> --------------------------------------------------------
>
> Key: DERBY-6784
> URL: https://issues.apache.org/jira/browse/DERBY-6784
> Project: Derby
> Issue Type: Improvement
> Components: SQL
> Affects Versions: 10.11.1.1
> Reporter: Mike Matrigali
> Assignee: Mike Matrigali
>
> 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.
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)
