Uploaded image for project: 'Derby'
  1. Derby
  2. DERBY-6784

change optimizer to choose in list multiprobe more often



    • Improvement
    • Status: Open
    • Major
    • Resolution: Unresolved
    • None
    • SQL
    • None
    • High Value Fix


      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.


        1. DERBY_6784_diff_1.txt
          2 kB
          Mike Matrigali
        2. current_perf.txt
          3 kB
          Mike Matrigali
        3. Derby47PerformanceTest.java
          38 kB
          Mike Matrigali
        4. patch_1_perf.txt
          3 kB
          Mike Matrigali

        Issue Links



              Unassigned Unassigned
              mikem Mike Matrigali
              0 Vote for this issue
              3 Start watching this issue