Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Changes In Branch in-scan-vs-index Excluding Merge-Ins
This is equivalent to a diff from 43f7ddad80 to dc4172e6b8
2020-09-30
| ||
18:06 | Improved query optimization for multi-column indexes where the second or later columns are constrained by an IN operator and the earlier index columns limit the search to a small number of rows. Use the new OP_SeekScan opcode which does scanning of the relevant range of the index but gives up and falls back to doing a seek if the number of rows scanned grows to large, in order to guard against pathological cases where the estimated number of rows to be scanned is far too small. (check-in: 4a43430fd2 user: drh tags: trunk) | |
18:03 | For the OP_SeekScan opcode, adjust the number of steps run before giving up based on the estimated number of comparisons needed to perform a seek. (Closed-Leaf check-in: dc4172e6b8 user: drh tags: in-scan-vs-index) | |
09:17 | Better understanding of savepoint code (check-in: ce6d8d6215 user: shearer tags: trunk) | |
00:48 | Add an sqlite3FaultSim() call to btreeNext() to make it easier to simulate I/O errors in calls to sqlite3BtreeNext(), and in OP_SeekScan. (check-in: 29cca775d3 user: drh tags: in-scan-vs-index) | |
2020-09-28
| ||
19:51 | Revisiting the IN-scan optimization to try to fix it for the corner case where the statistics deceive the query planner into using a scan when an indexed lookup would be better. This check-in changes the code generation to do the IN-scan using a new OP_SeekScan opcode. That new opcode is designed to abandon the scan and fall back to a seek if it doesn't find a match quickly enough. For this work-in-progress check-in, OP_SeekScan is still a no-op and OP_SeekGE still ends up doing all the work. (check-in: d720b6981e user: drh tags: in-scan-vs-index) | |
15:49 | Small performance improvement and size reduction by reducing the size of the WhereTerm object. (check-in: 43f7ddad80 user: drh tags: trunk) | |
13:34 | Avoid the possibility of integer overflow on the --pagecache option to the CLI. See [forum:10a2892377|forum post 10a2892377] (check-in: d3d13df31a user: drh tags: trunk) | |
Changes to src/btree.c.
5746 5747 5748 5749 5750 5751 5752 5753 5754 5755 5756 5757 5758 5759 5760 |
pCur->eState = CURSOR_VALID; if( pCur->skipNext>0 ) return SQLITE_OK; } } pPage = pCur->pPage; idx = ++pCur->ix; if( !pPage->isInit ){ /* The only known way for this to happen is for there to be a ** recursive SQL function that does a DELETE operation as part of a ** SELECT which deletes content out from under an active cursor ** in a corrupt database file where the table being DELETE-ed from ** has pages in common with the table being queried. See TH3 ** module cov1/btree78.test testcase 220 (2018-06-08) for an ** example. */ |
| |
5746 5747 5748 5749 5750 5751 5752 5753 5754 5755 5756 5757 5758 5759 5760 |
pCur->eState = CURSOR_VALID;
if( pCur->skipNext>0 ) return SQLITE_OK;
}
}
pPage = pCur->pPage;
idx = ++pCur->ix;
if( !pPage->isInit || sqlite3FaultSim(412) ){
/* The only known way for this to happen is for there to be a
** recursive SQL function that does a DELETE operation as part of a
** SELECT which deletes content out from under an active cursor
** in a corrupt database file where the table being DELETE-ed from
** has pages in common with the table being queried. See TH3
** module cov1/btree78.test testcase 220 (2018-06-08) for an
** example. */
|
Changes to src/vdbe.c.
4378
4379
4380
4381
4382
4383
4384
4385
4386
4387
4388
4389
4390
4391
....
5910
5911
5912
5913
5914
5915
5916
5917
5918
5919
5920
5921
5922
5923
5924
5925
5926
5927
5928
5929
5930
5931
5932
5933
5934
5935
|
goto jump_to_p2; }else if( eqOnly ){ assert( pOp[1].opcode==OP_IdxLT || pOp[1].opcode==OP_IdxGT ); pOp++; /* Skip the OP_IdxLt or OP_IdxGT that follows */ } break; } /* Opcode: SeekHit P1 P2 P3 * * ** Synopsis: set P2<=seekHit<=P3 ** ** Increase or decrease the seekHit value for cursor P1, if necessary, ** so that it is no less than P2 and no greater than P3. ** ................................................................................ int i; for(i=0; i<r.nField; i++){ assert( memIsValid(&r.aMem[i]) ); REGISTER_TRACE(pOp->p3+i, &aMem[pOp->p3+i]); } } #endif res = 0; /* Not needed. Only used to silence a warning. */ rc = sqlite3VdbeIdxKeyCompare(db, pC, &r, &res); assert( (OP_IdxLE&1)==(OP_IdxLT&1) && (OP_IdxGE&1)==(OP_IdxGT&1) ); if( (pOp->opcode&1)==(OP_IdxLT&1) ){ assert( pOp->opcode==OP_IdxLE || pOp->opcode==OP_IdxLT ); res = -res; }else{ assert( pOp->opcode==OP_IdxGE || pOp->opcode==OP_IdxGT ); res++; } VdbeBranchTaken(res>0,2); if( rc ) goto abort_due_to_error; if( res>0 ) goto jump_to_p2; break; } /* Opcode: Destroy P1 P2 P3 * * ** ** Delete an entire database table or index whose root page in the database |
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
<
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
|
>
|
|
4378
4379
4380
4381
4382
4383
4384
4385
4386
4387
4388
4389
4390
4391
4392
4393
4394
4395
4396
4397
4398
4399
4400
4401
4402
4403
4404
4405
4406
4407
4408
4409
4410
4411
4412
4413
4414
4415
4416
4417
4418
4419
4420
4421
4422
4423
4424
4425
4426
4427
4428
4429
4430
4431
4432
4433
4434
4435
4436
4437
4438
4439
4440
4441
4442
4443
4444
4445
4446
4447
4448
4449
4450
4451
4452
4453
4454
4455
4456
4457
4458
4459
4460
4461
4462
4463
4464
4465
4466
4467
4468
4469
4470
4471
4472
4473
4474
4475
4476
4477
4478
4479
4480
4481
4482
4483
4484
4485
4486
4487
4488
4489
4490
4491
4492
4493
4494
4495
4496
4497
4498
4499
4500
4501
4502
4503
4504
4505
4506
4507
4508
4509
4510
4511
4512
4513
4514
4515
4516
4517
4518
4519
4520
4521
4522
4523
4524
4525
4526
4527
4528
....
6047
6048
6049
6050
6051
6052
6053
6054
6055
6056
6057
6058
6059
6060
6061
6062
6063
6064
6065
6066
6067
6068
6069
6070
6071
6072
6073
6074
6075
6076
6077
6078
6079
6080
6081
6082
6083
6084
6085
6086
6087
6088
6089
6090
6091
6092
6093
6094
6095
|
goto jump_to_p2; }else if( eqOnly ){ assert( pOp[1].opcode==OP_IdxLT || pOp[1].opcode==OP_IdxGT ); pOp++; /* Skip the OP_IdxLt or OP_IdxGT that follows */ } break; } /* Opcode: SeekScan P1 * * * * ** Synopsis: Scan-ahead up to P1 rows ** ** This opcode is a prefix opcode to OP_SeekGE. In other words, this ** opcode must be immediately followed by OP_SeekGE. Furthermore, the ** OP_SeekGE must be followed by OP_IdxGT. These constraints are ** checked by assert() statements. ** ** This opcode uses the P1 through P4 operands of the subsequent ** OP_SeekGE. In the text that follows, the operands of the subsequent ** OP_SeekGE opcode are denoted as SeekOP.P1 through SeekOP.P4. Only ** the P1 operand of this opcode is used, and it is denoted as This.P1. ** ** This opcode helps to optimize IN operators on a multi-column index ** where the IN operator is on the later terms of the index by avoiding ** unnecessary seeks on the btree, substituting steps to the next row ** of the b-tree instead. A correct answer is obtained if this opcode ** is omitted or is a no-op. ** ** The SeekGE.P3 and SeekGE.P4 operands identify an unpacked key which ** is the desired entry that we want the cursor SeekGE.P1 to be pointing ** to. Call this SeekGE.P4/P5 row the "target". ** ** If the SeekGE.P1 cursor is not currently pointing to a valid row, ** then this opcode is a no-op and control passes through into the OP_SeekGE. ** ** If the SeekGE.P1 cursor is pointing to a valid row, then that row ** might be the target row, or it might be near and slightly before the ** target row. This opcode attempts to position the cursor on the target ** row by, perhaps stepping by invoking sqlite3BtreeStep() on the cursor ** between 0 and This.P1 times. ** ** There are three possible outcomes from this opcode:<ol> ** ** <li> If after This.P1 steps, the cursor is still point to a place that ** is earlier in the btree than the target row, ** then fall through into the subsquence OP_SeekGE opcode. ** ** <li> If the cursor is successfully moved to the target row by 0 or more ** sqlite3BtreeNext() calls, then jump to the first instruction after the ** OP_IdxGT opcode - or in other words, skip the next two opcodes. ** ** <li> If the cursor ends up past the target row (indicating the the target ** row does not exist in the btree) then jump to SeekOP.P2. ** </ol> */ case OP_SeekScan: { VdbeCursor *pC; int res; int n; UnpackedRecord r; assert( pOp[1].opcode==OP_SeekGE ); assert( pOp[2].opcode==OP_IdxGT ); assert( pOp[1].p1==pOp[2].p1 ); assert( pOp[1].p2==pOp[2].p2 ); assert( pOp[1].p3==pOp[2].p3 ); assert( pOp[1].p4.i==pOp[2].p4.i ); assert( pOp->p1>0 ); pC = p->apCsr[pOp[1].p1]; assert( pC!=0 ); assert( pC->eCurType==CURTYPE_BTREE ); assert( !pC->isTable ); if( !sqlite3BtreeCursorIsValidNN(pC->uc.pCursor) ){ #ifdef SQLITE_DEBUG if( db->flags&SQLITE_VdbeTrace ){ printf("... cursor not valid - fall through\n"); } #endif break; } n = pOp->p1; assert( n>=1 ); r.pKeyInfo = pC->pKeyInfo; r.nField = (u16)pOp[1].p4.i; r.default_rc = 0; r.aMem = &aMem[pOp[1].p3]; #ifdef SQLITE_DEBUG { int i; for(i=0; i<r.nField; i++){ assert( memIsValid(&r.aMem[i]) ); REGISTER_TRACE(pOp[1].p3+i, &aMem[pOp[1].p3+i]); } } #endif res = 0; /* Not needed. Only used to silence a warning. */ while(1){ rc = sqlite3VdbeIdxKeyCompare(db, pC, &r, &res); if( rc ) goto abort_due_to_error; if( res>0 ){ seekscan_search_fail: #ifdef SQLITE_DEBUG if( db->flags&SQLITE_VdbeTrace ){ printf("... %d steps and then skip\n", pOp->p1 - n); } #endif VdbeBranchTaken(1,3); pOp++; goto jump_to_p2; } if( res==0 ){ #ifdef SQLITE_DEBUG if( db->flags&SQLITE_VdbeTrace ){ printf("... %d steps and then success\n", pOp->p1 - n); } #endif VdbeBranchTaken(2,3); pOp += 2; break; } if( n<=0 ){ #ifdef SQLITE_DEBUG if( db->flags&SQLITE_VdbeTrace ){ printf("... fall through after %d steps\n", pOp->p1); } #endif VdbeBranchTaken(0,3); break; } n--; rc = sqlite3BtreeNext(pC->uc.pCursor, 0); if( rc ){ if( rc==SQLITE_DONE ){ rc = SQLITE_OK; goto seekscan_search_fail; }else{ goto abort_due_to_error; } } } break; } /* Opcode: SeekHit P1 P2 P3 * * ** Synopsis: set P2<=seekHit<=P3 ** ** Increase or decrease the seekHit value for cursor P1, if necessary, ** so that it is no less than P2 and no greater than P3. ** ................................................................................ int i; for(i=0; i<r.nField; i++){ assert( memIsValid(&r.aMem[i]) ); REGISTER_TRACE(pOp->p3+i, &aMem[pOp->p3+i]); } } #endif /* Inlined version of sqlite3VdbeIdxKeyCompare() */ { i64 nCellKey = 0; BtCursor *pCur; Mem m; assert( pC->eCurType==CURTYPE_BTREE ); pCur = pC->uc.pCursor; assert( sqlite3BtreeCursorIsValid(pCur) ); nCellKey = sqlite3BtreePayloadSize(pCur); /* nCellKey will always be between 0 and 0xffffffff because of the way ** that btreeParseCellPtr() and sqlite3GetVarint32() are implemented */ if( nCellKey<=0 || nCellKey>0x7fffffff ){ rc = SQLITE_CORRUPT_BKPT; goto abort_due_to_error; } sqlite3VdbeMemInit(&m, db, 0); rc = sqlite3VdbeMemFromBtreeZeroOffset(pCur, (u32)nCellKey, &m); if( rc ) goto abort_due_to_error; res = sqlite3VdbeRecordCompareWithSkip(m.n, m.z, &r, 0); sqlite3VdbeMemRelease(&m); } /* End of inlined sqlite3VdbeIdxKeyCompare() */ assert( (OP_IdxLE&1)==(OP_IdxLT&1) && (OP_IdxGE&1)==(OP_IdxGT&1) ); if( (pOp->opcode&1)==(OP_IdxLT&1) ){ assert( pOp->opcode==OP_IdxLE || pOp->opcode==OP_IdxLT ); res = -res; }else{ assert( pOp->opcode==OP_IdxGE || pOp->opcode==OP_IdxGT ); res++; } VdbeBranchTaken(res>0,2); assert( rc==SQLITE_OK ); if( res>0 ) goto jump_to_p2; break; } /* Opcode: Destroy P1 P2 P3 * * ** ** Delete an entire database table or index whose root page in the database |
Changes to src/where.c.
2560 2561 2562 2563 2564 2565 2566 2567 2568 2569 2570 2571 2572 2573 2574 .... 5193 5194 5195 5196 5197 5198 5199 5200 5201 5202 5203 5204 5205 5206 .... 5355 5356 5357 5358 5359 5360 5361 5362 5363 5364 5365 5366 5367 5368 5369 5370 5371 5372 5373 5374 5375 5376 5377 5378 5379 5380 5381 5382 5383 5384 5385 |
M = pProbe->aiRowLogEst[saved_nEq]; logK = estLog(nIn); safetyMargin = 10; /* TUNING: extra weight for indexed IN */ if( M + logK + safetyMargin < nIn + rLogSize ){ WHERETRACE(0x40, ("Scan preferred over IN operator on column %d of \"%s\" (%d<%d)\n", saved_nEq, pProbe->zName, M+logK+10, nIn+rLogSize)); continue; }else{ WHERETRACE(0x40, ("IN operator preferred on column %d of \"%s\" (%d>=%d)\n", saved_nEq, pProbe->zName, M+logK+10, nIn+rLogSize)); } } pNew->wsFlags |= WHERE_COLUMN_IN; ................................................................................ assert( iIndexCur>=0 ); if( op ){ sqlite3VdbeAddOp3(v, op, iIndexCur, pIx->tnum, iDb); sqlite3VdbeSetP4KeyInfo(pParse, pIx); if( (pLoop->wsFlags & WHERE_CONSTRAINT)!=0 && (pLoop->wsFlags & (WHERE_COLUMN_RANGE|WHERE_SKIPSCAN))==0 && (pLoop->wsFlags & WHERE_BIGNULL_SORT)==0 && (pWInfo->wctrlFlags&WHERE_ORDERBY_MIN)==0 && pWInfo->eDistinct!=WHERE_DISTINCT_ORDERED ){ sqlite3VdbeChangeP5(v, OPFLAG_SEEKEQ); } VdbeComment((v, "%s", pIx->zName)); #ifdef SQLITE_ENABLE_COLUMN_USED_MASK ................................................................................ struct InLoop *pIn; int j; sqlite3VdbeResolveLabel(v, pLevel->addrNxt); for(j=pLevel->u.in.nIn, pIn=&pLevel->u.in.aInLoop[j-1]; j>0; j--, pIn--){ sqlite3VdbeJumpHere(v, pIn->addrInTop+1); if( pIn->eEndLoopOp!=OP_Noop ){ if( pIn->nPrefix ){ assert( pLoop->wsFlags & WHERE_IN_EARLYOUT ); if( pLevel->iLeftJoin ){ /* For LEFT JOIN queries, cursor pIn->iCur may not have been ** opened yet. This occurs for WHERE clauses such as ** "a = ? AND b IN (...)", where the index is on (a, b). If ** the RHS of the (a=?) is NULL, then the "b IN (...)" may ** never have been coded, but the body of the loop run to ** return the null-row. So, if the cursor is not open yet, ** jump over the OP_Next or OP_Prev instruction about to ** be coded. */ sqlite3VdbeAddOp2(v, OP_IfNotOpen, pIn->iCur, sqlite3VdbeCurrentAddr(v) + 2 + ((pLoop->wsFlags & WHERE_VIRTUALTABLE)==0) ); VdbeCoverage(v); } if( (pLoop->wsFlags & WHERE_VIRTUALTABLE)==0 ){ sqlite3VdbeAddOp4Int(v, OP_IfNoHope, pLevel->iIdxCur, sqlite3VdbeCurrentAddr(v)+2, pIn->iBase, pIn->nPrefix); VdbeCoverage(v); } } sqlite3VdbeAddOp2(v, pIn->eEndLoopOp, pIn->iCur, pIn->addrInTop); |
| > > > | | < < < > |
2560 2561 2562 2563 2564 2565 2566 2567 2568 2569 2570 2571 2572 2573 2574 .... 5193 5194 5195 5196 5197 5198 5199 5200 5201 5202 5203 5204 5205 5206 5207 .... 5356 5357 5358 5359 5360 5361 5362 5363 5364 5365 5366 5367 5368 5369 5370 5371 5372 5373 5374 5375 5376 5377 5378 5379 5380 5381 5382 5383 5384 5385 5386 |
M = pProbe->aiRowLogEst[saved_nEq]; logK = estLog(nIn); safetyMargin = 10; /* TUNING: extra weight for indexed IN */ if( M + logK + safetyMargin < nIn + rLogSize ){ WHERETRACE(0x40, ("Scan preferred over IN operator on column %d of \"%s\" (%d<%d)\n", saved_nEq, pProbe->zName, M+logK+10, nIn+rLogSize)); pNew->wsFlags |= WHERE_IN_SEEKSCAN; }else{ WHERETRACE(0x40, ("IN operator preferred on column %d of \"%s\" (%d>=%d)\n", saved_nEq, pProbe->zName, M+logK+10, nIn+rLogSize)); } } pNew->wsFlags |= WHERE_COLUMN_IN; ................................................................................ assert( iIndexCur>=0 ); if( op ){ sqlite3VdbeAddOp3(v, op, iIndexCur, pIx->tnum, iDb); sqlite3VdbeSetP4KeyInfo(pParse, pIx); if( (pLoop->wsFlags & WHERE_CONSTRAINT)!=0 && (pLoop->wsFlags & (WHERE_COLUMN_RANGE|WHERE_SKIPSCAN))==0 && (pLoop->wsFlags & WHERE_BIGNULL_SORT)==0 && (pLoop->wsFlags & WHERE_IN_SEEKSCAN)==0 && (pWInfo->wctrlFlags&WHERE_ORDERBY_MIN)==0 && pWInfo->eDistinct!=WHERE_DISTINCT_ORDERED ){ sqlite3VdbeChangeP5(v, OPFLAG_SEEKEQ); } VdbeComment((v, "%s", pIx->zName)); #ifdef SQLITE_ENABLE_COLUMN_USED_MASK ................................................................................ struct InLoop *pIn; int j; sqlite3VdbeResolveLabel(v, pLevel->addrNxt); for(j=pLevel->u.in.nIn, pIn=&pLevel->u.in.aInLoop[j-1]; j>0; j--, pIn--){ sqlite3VdbeJumpHere(v, pIn->addrInTop+1); if( pIn->eEndLoopOp!=OP_Noop ){ if( pIn->nPrefix ){ int bEarlyOut = (pLoop->wsFlags & WHERE_VIRTUALTABLE)==0 && (pLoop->wsFlags & WHERE_IN_EARLYOUT)!=0; if( pLevel->iLeftJoin ){ /* For LEFT JOIN queries, cursor pIn->iCur may not have been ** opened yet. This occurs for WHERE clauses such as ** "a = ? AND b IN (...)", where the index is on (a, b). If ** the RHS of the (a=?) is NULL, then the "b IN (...)" may ** never have been coded, but the body of the loop run to ** return the null-row. So, if the cursor is not open yet, ** jump over the OP_Next or OP_Prev instruction about to ** be coded. */ sqlite3VdbeAddOp2(v, OP_IfNotOpen, pIn->iCur, sqlite3VdbeCurrentAddr(v) + 2 + bEarlyOut); VdbeCoverage(v); } if( bEarlyOut ){ sqlite3VdbeAddOp4Int(v, OP_IfNoHope, pLevel->iIdxCur, sqlite3VdbeCurrentAddr(v)+2, pIn->iBase, pIn->nPrefix); VdbeCoverage(v); } } sqlite3VdbeAddOp2(v, pIn->eEndLoopOp, pIn->iCur, pIn->addrInTop); |
Changes to src/whereInt.h.
615 616 617 618 619 620 621 622 623 |
#define WHERE_MULTI_OR 0x00002000 /* OR using multiple indices */ #define WHERE_AUTO_INDEX 0x00004000 /* Uses an ephemeral index */ #define WHERE_SKIPSCAN 0x00008000 /* Uses the skip-scan algorithm */ #define WHERE_UNQ_WANTED 0x00010000 /* WHERE_ONEROW would have been helpful*/ #define WHERE_PARTIALIDX 0x00020000 /* The automatic index is partial */ #define WHERE_IN_EARLYOUT 0x00040000 /* Perhaps quit IN loops early */ #define WHERE_BIGNULL_SORT 0x00080000 /* Column nEq of index is BIGNULL */ #endif /* !defined(SQLITE_WHEREINT_H) */ |
> |
615 616 617 618 619 620 621 622 623 624 |
#define WHERE_MULTI_OR 0x00002000 /* OR using multiple indices */
#define WHERE_AUTO_INDEX 0x00004000 /* Uses an ephemeral index */
#define WHERE_SKIPSCAN 0x00008000 /* Uses the skip-scan algorithm */
#define WHERE_UNQ_WANTED 0x00010000 /* WHERE_ONEROW would have been helpful*/
#define WHERE_PARTIALIDX 0x00020000 /* The automatic index is partial */
#define WHERE_IN_EARLYOUT 0x00040000 /* Perhaps quit IN loops early */
#define WHERE_BIGNULL_SORT 0x00080000 /* Column nEq of index is BIGNULL */
#define WHERE_IN_SEEKSCAN 0x00100000 /* Seek-scan optimization for IN */
#endif /* !defined(SQLITE_WHEREINT_H) */
|
Changes to src/wherecode.c.
566 567 568 569 570 571 572 573 574 575 576 577 578 579 580 ... 604 605 606 607 608 609 610 611 612 613 614 615 616 617 618 .... 1800 1801 1802 1803 1804 1805 1806 1807 1808 1809 1810 1811 1812 1813 .... 1902 1903 1904 1905 1906 1907 1908 1909 1910 1911 1912 1913 1914 1915 1916 |
VdbeCoverageIf(v, !bRev); assert( (pLoop->wsFlags & WHERE_MULTI_OR)==0 ); pLoop->wsFlags |= WHERE_IN_ABLE; if( pLevel->u.in.nIn==0 ){ pLevel->addrNxt = sqlite3VdbeMakeLabel(pParse); } if( iEq>0 ){ pLoop->wsFlags |= WHERE_IN_EARLYOUT; } i = pLevel->u.in.nIn; pLevel->u.in.nIn += nEq; pLevel->u.in.aInLoop = sqlite3DbReallocOrFree(pParse->db, pLevel->u.in.aInLoop, ................................................................................ } }else{ pIn->eEndLoopOp = OP_Noop; } pIn++; } } if( iEq>0 ){ sqlite3VdbeAddOp3(v, OP_SeekHit, pLevel->iIdxCur, 0, iEq); } }else{ pLevel->u.in.nIn = 0; } sqlite3DbFree(pParse->db, aiMap); #endif ................................................................................ if( regBignull ){ sqlite3VdbeAddOp2(v, OP_Integer, 1, regBignull); VdbeComment((v, "NULL-scan pass ctr")); } op = aStartOp[(start_constraints<<2) + (startEq<<1) + bRev]; assert( op!=0 ); sqlite3VdbeAddOp4Int(v, op, iIdxCur, addrNxt, regBase, nConstraint); VdbeCoverage(v); VdbeCoverageIf(v, op==OP_Rewind); testcase( op==OP_Rewind ); VdbeCoverageIf(v, op==OP_Last); testcase( op==OP_Last ); VdbeCoverageIf(v, op==OP_SeekGT); testcase( op==OP_SeekGT ); VdbeCoverageIf(v, op==OP_SeekGE); testcase( op==OP_SeekGE ); VdbeCoverageIf(v, op==OP_SeekLE); testcase( op==OP_SeekLE ); ................................................................................ nConstraint+bSeekPastNull); testcase( op==OP_IdxGT ); VdbeCoverageIf(v, op==OP_IdxGT ); testcase( op==OP_IdxGE ); VdbeCoverageIf(v, op==OP_IdxGE ); testcase( op==OP_IdxLT ); VdbeCoverageIf(v, op==OP_IdxLT ); testcase( op==OP_IdxLE ); VdbeCoverageIf(v, op==OP_IdxLE ); } if( pLoop->wsFlags & WHERE_IN_EARLYOUT ){ sqlite3VdbeAddOp3(v, OP_SeekHit, iIdxCur, nEq, nEq); } /* Seek the table cursor, if required */ omitTable = (pLoop->wsFlags & WHERE_IDX_ONLY)!=0 && (pWInfo->wctrlFlags & WHERE_OR_SUBCLAUSE)==0; if( omitTable ){ |
| | > > > > > > > > > > > > > > | |
566 567 568 569 570 571 572 573 574 575 576 577 578 579 580 ... 604 605 606 607 608 609 610 611 612 613 614 615 616 617 618 .... 1800 1801 1802 1803 1804 1805 1806 1807 1808 1809 1810 1811 1812 1813 1814 1815 1816 1817 1818 1819 1820 1821 1822 1823 1824 1825 1826 1827 .... 1916 1917 1918 1919 1920 1921 1922 1923 1924 1925 1926 1927 1928 1929 1930 |
VdbeCoverageIf(v, !bRev); assert( (pLoop->wsFlags & WHERE_MULTI_OR)==0 ); pLoop->wsFlags |= WHERE_IN_ABLE; if( pLevel->u.in.nIn==0 ){ pLevel->addrNxt = sqlite3VdbeMakeLabel(pParse); } if( iEq>0 && (pLoop->wsFlags & WHERE_IN_SEEKSCAN)==0 ){ pLoop->wsFlags |= WHERE_IN_EARLYOUT; } i = pLevel->u.in.nIn; pLevel->u.in.nIn += nEq; pLevel->u.in.aInLoop = sqlite3DbReallocOrFree(pParse->db, pLevel->u.in.aInLoop, ................................................................................ } }else{ pIn->eEndLoopOp = OP_Noop; } pIn++; } } if( iEq>0 && (pLoop->wsFlags & WHERE_IN_SEEKSCAN)==0 ){ sqlite3VdbeAddOp3(v, OP_SeekHit, pLevel->iIdxCur, 0, iEq); } }else{ pLevel->u.in.nIn = 0; } sqlite3DbFree(pParse->db, aiMap); #endif ................................................................................ if( regBignull ){ sqlite3VdbeAddOp2(v, OP_Integer, 1, regBignull); VdbeComment((v, "NULL-scan pass ctr")); } op = aStartOp[(start_constraints<<2) + (startEq<<1) + bRev]; assert( op!=0 ); if( (pLoop->wsFlags & WHERE_IN_SEEKSCAN)!=0 ){ assert( op==OP_SeekGE ); assert( regBignull==0 ); /* TUNING: The OP_SeekScan opcode seeks to reduce the number ** of expensive seek operations by replacing a single seek with ** 1 or more step operations. The question is, how many steps ** should we try before giving up and going with a seek. The cost ** of a seek is proportional to the logarithm of the of the number ** of entries in the tree, so basing the number of steps to try ** on the estimated number of rows in the btree seems like a good ** guess. */ sqlite3VdbeAddOp1(v, OP_SeekScan, (pIdx->aiRowLogEst[0]+9)/10); VdbeCoverage(v); } sqlite3VdbeAddOp4Int(v, op, iIdxCur, addrNxt, regBase, nConstraint); VdbeCoverage(v); VdbeCoverageIf(v, op==OP_Rewind); testcase( op==OP_Rewind ); VdbeCoverageIf(v, op==OP_Last); testcase( op==OP_Last ); VdbeCoverageIf(v, op==OP_SeekGT); testcase( op==OP_SeekGT ); VdbeCoverageIf(v, op==OP_SeekGE); testcase( op==OP_SeekGE ); VdbeCoverageIf(v, op==OP_SeekLE); testcase( op==OP_SeekLE ); ................................................................................ nConstraint+bSeekPastNull); testcase( op==OP_IdxGT ); VdbeCoverageIf(v, op==OP_IdxGT ); testcase( op==OP_IdxGE ); VdbeCoverageIf(v, op==OP_IdxGE ); testcase( op==OP_IdxLT ); VdbeCoverageIf(v, op==OP_IdxLT ); testcase( op==OP_IdxLE ); VdbeCoverageIf(v, op==OP_IdxLE ); } if( (pLoop->wsFlags & WHERE_IN_EARLYOUT)!=0 ){ sqlite3VdbeAddOp3(v, OP_SeekHit, iIdxCur, nEq, nEq); } /* Seek the table cursor, if required */ omitTable = (pLoop->wsFlags & WHERE_IDX_ONLY)!=0 && (pWInfo->wctrlFlags & WHERE_OR_SUBCLAUSE)==0; if( omitTable ){ |