SQLite

Check-in [51bfd63b7f]
Login

Many hyperlinks are disabled.
Use anonymous login to enable hyperlinks.

Overview
Comment:Disable the table selection rule that tried to prevent full table scans from migrating to the outer loop unless they were optimal. The new scaling of outer-loop costs by cost of inner loops obviates the need for that step. And, in fact, that step causes problems with the new inner-loop cost accounting.
Downloads: Tarball | ZIP archive
Timelines: family | ancestors | descendants | both | inner-loop-cost
Files: files | file ages | folders
SHA1: 51bfd63b7f9fe53831570ad124c932cb3582b104
User & Date: drh 2012-11-09 18:22:26.026
Context
2012-11-09
18:32
Take into account the cost of inner loops when selecting which table of a join to use for the next outer loop. (check-in: 3f87f4593b user: drh tags: trunk)
18:22
Disable the table selection rule that tried to prevent full table scans from migrating to the outer loop unless they were optimal. The new scaling of outer-loop costs by cost of inner loops obviates the need for that step. And, in fact, that step causes problems with the new inner-loop cost accounting. (Closed-Leaf check-in: 51bfd63b7f user: drh tags: inner-loop-cost)
17:59
Try to take into account the cost of inner loops when selecting which table of a join to use for the outer loop. (check-in: 942556342a user: drh tags: inner-loop-cost)
Changes
Side-by-Side Diff Ignore Whitespace Patch
Changes to src/where.c.
︙
4740
4741
4742
4743
4744
4745
4746
4747
4748
4749
4750
4751
4752
4753
4754
4755
4756
4757
4758
4759
4760
4761
4762
4763

4740
4741
4742
4743
4744
4745
4746










4747
4748
4749
4750
4751
4752
4753








-
-
-
-
-
-
-
-
-
-








      }
    }
    whereClauseClear(pWInfo->pWC);
    sqlite3DbFree(db, pWInfo);
  }
}

/*
** Return TRUE if the wsFlags indicate that a full table scan (or a
** full scan of a covering index) is indicated.
*/
static int isFullscan(unsigned wsFlags){
  if( wsFlags & WHERE_COVER_SCAN ) return 1;
  if( (wsFlags & WHERE_NOT_FULLSCAN)==0 ) return 1;
  return 0;
}


/*
** Generate the beginning of the loop used for WHERE clause processing.
** The return value is a pointer to an opaque structure that contains
** information needed to terminate the loop.  Later, the calling routine
** should invoke sqlite3WhereEnd() with the return value of this function
** in order to complete the WHERE clause processing.
︙
5128
5129
5130
5131
5132
5133
5134
5135
5136


5137
5138
5139
5140
5141
5142
5143
5144
5145
5146
5147
5148
5149
5150
5151
5152
5153
5154
5155
5156
5157
5158
5159
5160

5118
5119
5120
5121
5122
5123
5124


5125
5126
5127
5128
5129
5130
5131
5132
5133
5134
5135
5136
5137
5138
5139
5140



5141
5142
5143
5144
5145
5146
5147








-
-
+
+














-
-
-









        /* Conditions under which this table becomes the best so far:
        **
        **   (1) The table must not depend on other tables that have not
        **       yet run.  (In other words, it must not depend on tables
        **       in inner loops.)
        **
        **   (2) A full-table-scan plan cannot supercede indexed plan unless
        **       the full-table-scan is an "optimal" plan as defined above.
        **   (2) (This rule was removed on 2012-11-09.  The scaling of the
        **       cost using the optimal scan cost made this rule obsolete.)
        **
        **   (3) All tables have an INDEXED BY clause or this table lacks an
        **       INDEXED BY clause or this table uses the specific
        **       index specified by its INDEXED BY clause.  This rule ensures
        **       that a best-so-far is always selected even if an impossible
        **       combination of INDEXED BY clauses are given.  The error
        **       will be detected and relayed back to the application later.
        **       The NEVER() comes about because rule (2) above prevents
        **       An indexable full-table-scan from reaching rule (3).
        **
        **   (4) The plan cost must be lower than prior plans, where "cost"
        **       is defined by the compareCost() function above. 
        */
        if( (sWBI.cost.used&sWBI.notValid)==0                    /* (1) */
            && (bestJ<0 || (notIndexed&m)!=0                     /* (2) */
                || isFullscan(bestPlan.plan.wsFlags)
                || !isFullscan(sWBI.cost.plan.wsFlags))
            && (nUnconstrained==0 || sWBI.pSrc->pIndex==0        /* (3) */
                || NEVER((sWBI.cost.plan.wsFlags & WHERE_NOT_FULLSCAN)!=0))
            && (bestJ<0 || compareCost(&sWBI.cost, &bestPlan))   /* (4) */
        ){
          WHERETRACE(("   === table %d (%s) is best so far\n"
                      "       cost=%.1f, nRow=%.1f, nOBSat=%d, wsFlags=%08x\n",
                      j, sWBI.pSrc->pTab->zName,
︙