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 | SQL archive |
| Timelines: | family | ancestors | descendants | both | inner-loop-cost |
| Files: | files | file ages | folders |
| SHA1: |
51bfd63b7f9fe53831570ad124c932cb |
| User & Date: | drh 2012-11-09 18:22:26 |
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: 3f87f459 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: 51bfd63b 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: 94255634 user: drh tags: inner-loop-cost | |
Changes
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 .... 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 |
}
}
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.
................................................................................
/* 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.
**
** (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,
|
< < < < < < < < < < | | < < < |
4740 4741 4742 4743 4744 4745 4746 4747 4748 4749 4750 4751 4752 4753 .... 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 |
}
}
whereClauseClear(pWInfo->pWC);
sqlite3DbFree(db, pWInfo);
}
}
/*
** 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.
................................................................................
/* 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) (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) */
&& (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,
|