Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
| Comment: | In a query that uses a partial index, the expression that is the WHERE clause of the partial index must always be true. Use this fact to avoid evaluating identical terms in the WHERE clause of the query. |
|---|---|
| Downloads: | Tarball | ZIP archive | SQL archive |
| Timelines: | family | ancestors | descendants | both | trunk |
| Files: | files | file ages | folders |
| SHA3-256: |
9b2879629c34fc0a8e99d94648903eb9 |
| User & Date: | drh 2019-03-01 18:07:05 |
References
|
2019-11-03
| ||
| 00:07 | The optimization of check-in [9b2879629c34fc0a] is incorrectly reasoned. The WHERE clause of the partial index might not be true if the table of the partial index is the right table of a left join. So disable the optimization in that case. Ticket [623eff57e76d45f6] check-in: 3be19e11 user: drh tags: trunk | |
Context
|
2019-03-01
| ||
| 21:12 | Fix a minor comment typo. No code changes. check-in: 9a0a93c8 user: drh tags: trunk | |
| 18:27 | Merge the latest enhancements from trunk. check-in: e64ded7b user: drh tags: apple-osx | |
| 18:07 | In a query that uses a partial index, the expression that is the WHERE clause of the partial index must always be true. Use this fact to avoid evaluating identical terms in the WHERE clause of the query. check-in: 9b287962 user: drh tags: trunk | |
|
2019-02-28
| ||
| 20:10 | Add the ".parameter" command to the CLI. check-in: 1f9fa585 user: drh tags: trunk | |
Changes
Changes to src/wherecode.c.
1154
1155
1156
1157
1158
1159
1160
1161
1162
1163
1164
1165
1166
1167
....
1763
1764
1765
1766
1767
1768
1769
1770
1771
1772
1773
1774
1775
1776
|
x.iIdxCol = iIdxCol;
x.pIdxExpr = aColExpr->a[iIdxCol].pExpr;
sqlite3WalkExpr(&w, pWInfo->pWhere);
sqlite3WalkExprList(&w, pWInfo->pOrderBy);
sqlite3WalkExprList(&w, pWInfo->pResultSet);
}
}
/*
** Generate code for the start of the iLevel-th loop in the WHERE clause
** implementation described by pWInfo.
*/
Bitmask sqlite3WhereCodeOneLoopStart(
Parse *pParse, /* Parsing context */
................................................................................
** OR clause, since the transformation will become invalid once we
** move forward to the next index.
** https://sqlite.org/src/info/4e8e4857d32d401f
*/
if( pLevel->iLeftJoin==0 && (pWInfo->wctrlFlags & WHERE_OR_SUBCLAUSE)==0 ){
whereIndexExprTrans(pIdx, iCur, iIdxCur, pWInfo);
}
/* Record the instruction used to terminate the loop. */
if( pLoop->wsFlags & WHERE_ONEROW ){
pLevel->op = OP_Noop;
}else if( bRev ){
pLevel->op = OP_Prev;
}else{
|
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
|
1154
1155
1156
1157
1158
1159
1160
1161
1162
1163
1164
1165
1166
1167
1168
1169
1170
1171
1172
1173
1174
1175
1176
1177
1178
1179
1180
1181
1182
1183
1184
1185
1186
1187
1188
1189
1190
1191
1192
1193
1194
1195
....
1791
1792
1793
1794
1795
1796
1797
1798
1799
1800
1801
1802
1803
1804
1805
1806
1807
1808
1809
1810
1811
1812
|
x.iIdxCol = iIdxCol;
x.pIdxExpr = aColExpr->a[iIdxCol].pExpr;
sqlite3WalkExpr(&w, pWInfo->pWhere);
sqlite3WalkExprList(&w, pWInfo->pOrderBy);
sqlite3WalkExprList(&w, pWInfo->pResultSet);
}
}
/*
** The pTruth expression is always tree because it is the WHERE clause
** a partial index that is driving a query loop. Look through all of the
** WHERE clause terms on the query, and if any of those terms must be
** true because pTruth is true, then mark those WHERE clause terms as
** coded.
*/
static void whereApplyPartialIndexConstraints(
Expr *pTruth,
int iTabCur,
WhereClause *pWC
){
int i;
WhereTerm *pTerm;
while( pTruth->op==TK_AND ){
whereApplyPartialIndexConstraints(pTruth->pLeft, iTabCur, pWC);
pTruth = pTruth->pRight;
}
for(i=0, pTerm=pWC->a; i<pWC->nTerm; i++, pTerm++){
Expr *pExpr;
if( pTerm->wtFlags & TERM_CODED ) continue;
pExpr = pTerm->pExpr;
if( sqlite3ExprCompare(0, pExpr, pTruth, iTabCur)==0 ){
pTerm->wtFlags |= TERM_CODED;
}
}
}
/*
** Generate code for the start of the iLevel-th loop in the WHERE clause
** implementation described by pWInfo.
*/
Bitmask sqlite3WhereCodeOneLoopStart(
Parse *pParse, /* Parsing context */
................................................................................
** OR clause, since the transformation will become invalid once we
** move forward to the next index.
** https://sqlite.org/src/info/4e8e4857d32d401f
*/
if( pLevel->iLeftJoin==0 && (pWInfo->wctrlFlags & WHERE_OR_SUBCLAUSE)==0 ){
whereIndexExprTrans(pIdx, iCur, iIdxCur, pWInfo);
}
/* If a partial index is driving the loop, try to eliminate WHERE clause
** terms from the query that must be true due to the WHERE clause of
** the partial index
*/
if( pIdx->pPartIdxWhere ){
whereApplyPartialIndexConstraints(pIdx->pPartIdxWhere, iCur, pWC);
}
/* Record the instruction used to terminate the loop. */
if( pLoop->wsFlags & WHERE_ONEROW ){
pLevel->op = OP_Noop;
}else if( bRev ){
pLevel->op = OP_Prev;
}else{
|