Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
| Comment: | Put a limit counter on the query planner that restricts the number of index+constraint options that can be considered for each table in a join. This prevents certain pathological queries from taking up too much time in the query planner. |
|---|---|
| Downloads: | Tarball | ZIP archive |
| Timelines: | family | ancestors | descendants | both | trunk |
| Files: | files | file ages | folders |
| SHA3-256: |
8690b5a0cc08eeb175230de45d4ca9b9 |
| User & Date: | drh 2018-09-21 18:43:51.308 |
Context
|
2018-09-21
| ||
| 19:06 | Comment typo fixed. No code changes. (check-in: 7e68cdab20 user: drh tags: trunk) | |
| 18:43 | Put a limit counter on the query planner that restricts the number of index+constraint options that can be considered for each table in a join. This prevents certain pathological queries from taking up too much time in the query planner. (check-in: 8690b5a0cc user: drh tags: trunk) | |
| 13:07 | Optimization to the OP_MakeRecord opcode makes speed-check.sh run about 1.1 million cycles faster, and results in a slightly smaller library. (check-in: d10e636291 user: drh tags: trunk) | |
Changes
Changes to src/where.c.
| ︙ | ︙ | |||
2121 2122 2123 2124 2125 2126 2127 2128 2129 2130 2131 2132 2133 2134 |
** (4) The template has the same or lower cost than the current loop
*/
static int whereLoopInsert(WhereLoopBuilder *pBuilder, WhereLoop *pTemplate){
WhereLoop **ppPrev, *p;
WhereInfo *pWInfo = pBuilder->pWInfo;
sqlite3 *db = pWInfo->pParse->db;
int rc;
/* If pBuilder->pOrSet is defined, then only keep track of the costs
** and prereqs.
*/
if( pBuilder->pOrSet!=0 ){
if( pTemplate->nLTerm ){
#if WHERETRACE_ENABLED
| > > > > | 2121 2122 2123 2124 2125 2126 2127 2128 2129 2130 2131 2132 2133 2134 2135 2136 2137 2138 |
** (4) The template has the same or lower cost than the current loop
*/
static int whereLoopInsert(WhereLoopBuilder *pBuilder, WhereLoop *pTemplate){
WhereLoop **ppPrev, *p;
WhereInfo *pWInfo = pBuilder->pWInfo;
sqlite3 *db = pWInfo->pParse->db;
int rc;
/* Stop the search once we hit the query planner search limit */
if( pBuilder->iPlanLimit==0 ) return SQLITE_DONE;
pBuilder->iPlanLimit--;
/* If pBuilder->pOrSet is defined, then only keep track of the costs
** and prereqs.
*/
if( pBuilder->pOrSet!=0 ){
if( pTemplate->nLTerm ){
#if WHERETRACE_ENABLED
|
| ︙ | ︙ | |||
3528 3529 3530 3531 3532 3533 3534 3535 3536 3537 3538 3539 3540 3541 3542 3543 3544 |
int rc = SQLITE_OK;
WhereLoop *pNew;
u8 priorJointype = 0;
/* Loop over the tables in the join, from left to right */
pNew = pBuilder->pNew;
whereLoopInit(pNew);
for(iTab=0, pItem=pTabList->a; pItem<pEnd; iTab++, pItem++){
Bitmask mUnusable = 0;
pNew->iTab = iTab;
pNew->maskSelf = sqlite3WhereGetMask(&pWInfo->sMaskSet, pItem->iCursor);
if( ((pItem->fg.jointype|priorJointype) & (JT_LEFT|JT_CROSS))!=0 ){
/* This condition is true when pItem is the FROM clause term on the
** right-hand-side of a LEFT or CROSS JOIN. */
mPrereq = mPrior;
}
priorJointype = pItem->fg.jointype;
| > > > > > > > > | 3532 3533 3534 3535 3536 3537 3538 3539 3540 3541 3542 3543 3544 3545 3546 3547 3548 3549 3550 3551 3552 3553 3554 3555 3556 |
int rc = SQLITE_OK;
WhereLoop *pNew;
u8 priorJointype = 0;
/* Loop over the tables in the join, from left to right */
pNew = pBuilder->pNew;
whereLoopInit(pNew);
/* Some pathological queries provide an unreasonable number of indexing
** options. The iPlanLimit value prevents these queries from taking up
** too much time in the planner. When iPlanLimit reaches zero, no further
** index+constraint options are considered. Seed iPlanLimit to 10K but
** also add an extra 1K to each table of the join, to ensure that each
** table at least gets 1K opportunities. */
pBuilder->iPlanLimit = 10000;
for(iTab=0, pItem=pTabList->a; pItem<pEnd; iTab++, pItem++){
Bitmask mUnusable = 0;
pNew->iTab = iTab;
pBuilder->iPlanLimit += 1000; /* 1000 bonus for each table in the join */
pNew->maskSelf = sqlite3WhereGetMask(&pWInfo->sMaskSet, pItem->iCursor);
if( ((pItem->fg.jointype|priorJointype) & (JT_LEFT|JT_CROSS))!=0 ){
/* This condition is true when pItem is the FROM clause term on the
** right-hand-side of a LEFT or CROSS JOIN. */
mPrereq = mPrior;
}
priorJointype = pItem->fg.jointype;
|
| ︙ | ︙ | |||
3556 3557 3558 3559 3560 3561 3562 |
{
rc = whereLoopAddBtree(pBuilder, mPrereq);
}
if( rc==SQLITE_OK && pBuilder->pWC->hasOr ){
rc = whereLoopAddOr(pBuilder, mPrereq, mUnusable);
}
mPrior |= pNew->maskSelf;
| | > > > > > > > | 3568 3569 3570 3571 3572 3573 3574 3575 3576 3577 3578 3579 3580 3581 3582 3583 3584 3585 3586 3587 3588 3589 |
{
rc = whereLoopAddBtree(pBuilder, mPrereq);
}
if( rc==SQLITE_OK && pBuilder->pWC->hasOr ){
rc = whereLoopAddOr(pBuilder, mPrereq, mUnusable);
}
mPrior |= pNew->maskSelf;
if( rc || db->mallocFailed ){
if( rc==SQLITE_DONE ){
/* We hit the query planner search limit set by iPlanLimit */
rc = SQLITE_OK;
}else{
break;
}
}
}
whereLoopClear(db, pNew);
return rc;
}
/*
|
| ︙ | ︙ |
Changes to src/whereInt.h.
| ︙ | ︙ | |||
398 399 400 401 402 403 404 405 406 407 408 409 410 411 | WhereLoop *pNew; /* Template WhereLoop */ WhereOrSet *pOrSet; /* Record best loops here, if not NULL */ #ifdef SQLITE_ENABLE_STAT3_OR_STAT4 UnpackedRecord *pRec; /* Probe for stat4 (if required) */ int nRecValid; /* Number of valid fields currently in pRec */ #endif unsigned int bldFlags; /* SQLITE_BLDF_* flags */ }; /* Allowed values for WhereLoopBuider.bldFlags */ #define SQLITE_BLDF_INDEXED 0x0001 /* An index is used */ #define SQLITE_BLDF_UNIQUE 0x0002 /* All keys of a UNIQUE index used */ /* | > | 398 399 400 401 402 403 404 405 406 407 408 409 410 411 412 | WhereLoop *pNew; /* Template WhereLoop */ WhereOrSet *pOrSet; /* Record best loops here, if not NULL */ #ifdef SQLITE_ENABLE_STAT3_OR_STAT4 UnpackedRecord *pRec; /* Probe for stat4 (if required) */ int nRecValid; /* Number of valid fields currently in pRec */ #endif unsigned int bldFlags; /* SQLITE_BLDF_* flags */ unsigned int iPlanLimit; /* Search limiter */ }; /* Allowed values for WhereLoopBuider.bldFlags */ #define SQLITE_BLDF_INDEXED 0x0001 /* An index is used */ #define SQLITE_BLDF_UNIQUE 0x0002 /* All keys of a UNIQUE index used */ /* |
| ︙ | ︙ |