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 |
| Timelines: | family | ancestors | descendants | both | trunk |
| Files: | files | file ages | folders |
| SHA3-256: |
9b2879629c34fc0a8e99d94648903eb9 |
| User & Date: | drh 2019-03-01 18:07:05.251 |
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: 3be19e1151 user: drh tags: trunk) | |
Context
|
2019-03-01
| ||
| 21:12 | Fix a minor comment typo. No code changes. (check-in: 9a0a93c89d user: drh tags: trunk) | |
| 18:27 | Merge the latest enhancements from trunk. (check-in: e64ded7b04 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: 9b2879629c user: drh tags: trunk) | |
|
2019-02-28
| ||
| 20:10 | Add the ".parameter" command to the CLI. (check-in: 1f9fa58541 user: drh tags: trunk) | |
Changes
Changes to src/wherecode.c.
| ︙ | ︙ | |||
1154 1155 1156 1157 1158 1159 1160 1161 1162 1163 1164 1165 1166 1167 |
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 */
| > > > > > > > > > > > > > > > > > > > > > > > > > > > > | 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 |
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 */
|
| ︙ | ︙ | |||
1763 1764 1765 1766 1767 1768 1769 1770 1771 1772 1773 1774 1775 1776 |
** 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{
| > > > > > > > > | 1791 1792 1793 1794 1795 1796 1797 1798 1799 1800 1801 1802 1803 1804 1805 1806 1807 1808 1809 1810 1811 1812 |
** 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{
|
| ︙ | ︙ |