Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Changes In Branch view-optimization Excluding Merge-Ins
This is equivalent to a diff from afc6db9b10 to 1df8cbbea6
2015-06-12
| ||
12:54 | Add the OP_ColumnsUsed opcode (when compiled with SQLITE_ENABLE_COLUMN_USED_MASK) as a hint to the b-tree layer as to which columns of a btree cursor will be used. (check-in: 711a176cbf user: drh tags: trunk) | |
2015-06-11
| ||
18:01 | Merge recent trunk enhancements and fixes. (check-in: c39cb0e257 user: drh tags: sessions) | |
17:58 | Merge trunk fixes. (Leaf check-in: 1df8cbbea6 user: drh tags: view-optimization) | |
14:19 | Remove stray outputs from the test suite. (check-in: afc6db9b10 user: drh tags: trunk) | |
13:58 | When generating code for partial indexes, be sure not to modify the index condition expression in the schema. (check-in: e63d01c69c user: drh tags: trunk) | |
2015-06-10
| ||
23:02 | More test output refinements. (Closed-Leaf check-in: e64a568179 user: mistachkin tags: testerOutput) | |
20:00 | Merge enhancements from trunk. (check-in: 0e23a079bd user: drh tags: view-optimization) | |
Changes to src/delete.c.
︙ | ︙ | |||
393 394 395 396 397 398 399 | addrEphOpen = sqlite3VdbeAddOp2(v, OP_OpenEphemeral, iEphCur, nPk); sqlite3VdbeSetP4KeyInfo(pParse, pPk); } /* Construct a query to find the rowid or primary key for every row ** to be deleted, based on the WHERE clause. */ | | | 393 394 395 396 397 398 399 400 401 402 403 404 405 406 407 | addrEphOpen = sqlite3VdbeAddOp2(v, OP_OpenEphemeral, iEphCur, nPk); sqlite3VdbeSetP4KeyInfo(pParse, pPk); } /* Construct a query to find the rowid or primary key for every row ** to be deleted, based on the WHERE clause. */ pWInfo = sqlite3WhereBegin(pParse, 0, pTabList, pWhere, 0, 0, WHERE_ONEPASS_DESIRED|WHERE_DUPLICATES_OK, iTabCur+1); if( pWInfo==0 ) goto delete_from_cleanup; okOnePass = sqlite3WhereOkOnePass(pWInfo, aiCurOnePass); /* Keep track of the number of rows to be deleted */ if( db->flags & SQLITE_CountRows ){ |
︙ | ︙ |
Changes to src/fkey.c.
︙ | ︙ | |||
627 628 629 630 631 632 633 | sNameContext.pSrcList = pSrc; sNameContext.pParse = pParse; sqlite3ResolveExprNames(&sNameContext, pWhere); /* Create VDBE to loop through the entries in pSrc that match the WHERE ** clause. For each row found, increment either the deferred or immediate ** foreign key constraint counter. */ | | | 627 628 629 630 631 632 633 634 635 636 637 638 639 640 641 | sNameContext.pSrcList = pSrc; sNameContext.pParse = pParse; sqlite3ResolveExprNames(&sNameContext, pWhere); /* Create VDBE to loop through the entries in pSrc that match the WHERE ** clause. For each row found, increment either the deferred or immediate ** foreign key constraint counter. */ pWInfo = sqlite3WhereBegin(pParse, 0, pSrc, pWhere, 0, 0, 0, 0); sqlite3VdbeAddOp2(v, OP_FkCounter, pFKey->isDeferred, nIncr); if( pWInfo ){ sqlite3WhereEnd(pWInfo); } /* Clean up the WHERE clause constructed above. */ sqlite3ExprDelete(db, pWhere); |
︙ | ︙ |
Changes to src/select.c.
︙ | ︙ | |||
3784 3785 3786 3787 3788 3789 3790 3791 3792 3793 3794 3795 3796 3797 | pSubq = pSubq->pPrior; } } return nChng; } #endif /* !defined(SQLITE_OMIT_SUBQUERY) || !defined(SQLITE_OMIT_VIEW) */ /* ** Based on the contents of the AggInfo structure indicated by the first ** argument, this function checks if the following are true: ** ** * the query contains just a single aggregate function, ** * the aggregate function is either min() or max(), and ** * the argument to the aggregate function is a column value. | > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > | 3784 3785 3786 3787 3788 3789 3790 3791 3792 3793 3794 3795 3796 3797 3798 3799 3800 3801 3802 3803 3804 3805 3806 3807 3808 3809 3810 3811 3812 3813 3814 3815 3816 3817 3818 3819 3820 3821 3822 3823 3824 3825 3826 3827 3828 3829 3830 3831 3832 3833 3834 3835 3836 3837 3838 3839 3840 3841 3842 3843 3844 3845 3846 3847 3848 3849 3850 3851 3852 3853 3854 3855 3856 3857 3858 3859 3860 3861 3862 3863 3864 3865 3866 3867 3868 3869 3870 3871 3872 3873 3874 3875 3876 3877 3878 3879 3880 3881 3882 3883 3884 3885 3886 3887 3888 3889 3890 3891 3892 3893 3894 3895 3896 3897 3898 3899 3900 3901 3902 3903 3904 3905 3906 3907 3908 3909 3910 3911 3912 | pSubq = pSubq->pPrior; } } return nChng; } #endif /* !defined(SQLITE_OMIT_SUBQUERY) || !defined(SQLITE_OMIT_VIEW) */ /* ** Generate code for all sub-queries in the FROM clause */ #if !defined(SQLITE_OMIT_SUBQUERY) || !defined(SQLITE_OMIT_VIEW) void sqlite3ManifestSubqueries( Parse *pParse, /* Parsing context */ Select *p, /* SELECT statement being generated */ SrcList *pTabList /* The FROM clause */ ){ int i; sqlite3 *db = pParse->db; Vdbe *v = pParse->pVdbe; for(i=0; i<pTabList->nSrc; i++){ struct SrcList_item *pItem = &pTabList->a[i]; SelectDest dest; Select *pSub = pItem->pSelect; if( pSub==0 ) continue; /* Sometimes the code for a subquery will be generated more than ** once, if the subquery is part of the WHERE clause in a LEFT JOIN, ** for example. In that case, do not regenerate the code to manifest ** a view or the co-routine to implement a view. The first instance ** is sufficient, though the subroutine to manifest the view does need ** to be invoked again. */ if( pItem->addrFillSub ){ if( pItem->viaCoroutine==0 ){ sqlite3VdbeAddOp2(v, OP_Gosub, pItem->regReturn, pItem->addrFillSub); } continue; } /* Increment Parse.nHeight by the height of the largest expression ** tree referred to by this, the parent select. The child select ** may contain expression trees of at most ** (SQLITE_MAX_EXPR_DEPTH-Parse.nHeight) height. This is a bit ** more conservative than necessary, but much easier than enforcing ** an exact limit. */ pParse->nHeight += sqlite3SelectExprHeight(p); /* Make copies of constant WHERE-clause terms in the outer query down ** inside the subquery. This can help the subquery to run more efficiently. */ if( (pItem->jointype & JT_OUTER)==0 && pushDownWhereTerms(db, pSub, p->pWhere, pItem->iCursor) ){ #if SELECTTRACE_ENABLED if( sqlite3SelectTrace & 0x100 ){ SELECTTRACE(0x100,pParse,p,("After WHERE-clause push-down:\n")); sqlite3TreeViewSelect(0, p, 0); } #endif } /* Generate code to implement the subquery */ if( pTabList->nSrc==1 && (p->selFlags & SF_All)==0 && OptimizationEnabled(db, SQLITE_SubqCoroutine) ){ /* Implement a co-routine that will return a single row of the result ** set on each invocation. */ int addrTop = sqlite3VdbeCurrentAddr(v)+1; pItem->regReturn = ++pParse->nMem; sqlite3VdbeAddOp3(v, OP_InitCoroutine, pItem->regReturn, 0, addrTop); VdbeComment((v, "%s", pItem->pTab->zName)); pItem->addrFillSub = addrTop; sqlite3SelectDestInit(&dest, SRT_Coroutine, pItem->regReturn); explainSetInteger(pItem->iSelectId, (u8)pParse->iNextSelectId); sqlite3Select(pParse, pSub, &dest); pItem->pTab->nRowLogEst = sqlite3LogEst(pSub->nSelectRow); pItem->viaCoroutine = 1; pItem->regResult = dest.iSdst; sqlite3VdbeAddOp1(v, OP_EndCoroutine, pItem->regReturn); sqlite3VdbeJumpHere(v, addrTop-1); sqlite3ClearTempRegCache(pParse); }else{ /* Generate a subroutine that will fill an ephemeral table with ** the content of this subquery. pItem->addrFillSub will point ** to the address of the generated subroutine. pItem->regReturn ** is a register allocated to hold the subroutine return address */ int topAddr; int onceAddr = 0; int retAddr; assert( pItem->addrFillSub==0 ); pItem->regReturn = ++pParse->nMem; topAddr = sqlite3VdbeAddOp2(v, OP_Integer, 0, pItem->regReturn); pItem->addrFillSub = topAddr+1; if( pItem->isCorrelated==0 ){ /* If the subquery is not correlated and if we are not inside of ** a trigger, then we only need to compute the value of the subquery ** once. */ onceAddr = sqlite3CodeOnce(pParse); VdbeCoverage(v); VdbeComment((v, "materialize \"%s\"", pItem->pTab->zName)); }else{ VdbeNoopComment((v, "materialize \"%s\"", pItem->pTab->zName)); } sqlite3SelectDestInit(&dest, SRT_EphemTab, pItem->iCursor); explainSetInteger(pItem->iSelectId, (u8)pParse->iNextSelectId); sqlite3Select(pParse, pSub, &dest); pItem->pTab->nRowLogEst = sqlite3LogEst(pSub->nSelectRow); if( onceAddr ) sqlite3VdbeJumpHere(v, onceAddr); retAddr = sqlite3VdbeAddOp1(v, OP_Return, pItem->regReturn); VdbeComment((v, "end %s", pItem->pTab->zName)); sqlite3VdbeChangeP1(v, topAddr, retAddr); sqlite3ClearTempRegCache(pParse); } if( db->mallocFailed ) break; pParse->nHeight -= sqlite3SelectExprHeight(p); } } #endif /* ** Based on the contents of the AggInfo structure indicated by the first ** argument, this function checks if the following are true: ** ** * the query contains just a single aggregate function, ** * the aggregate function is either min() or max(), and ** * the argument to the aggregate function is a column value. |
︙ | ︙ | |||
4880 4881 4882 4883 4884 4885 4886 | SELECTTRACE(1,pParse,p,("end compound-select processing\n")); pParse->nSelectIndent--; #endif return rc; } #endif | < < < < < < < < | < < < < < < < < < < < < | < < < < < < < < | < < < < < < < < < < < < < | < < | < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < | 4995 4996 4997 4998 4999 5000 5001 5002 5003 5004 5005 5006 5007 5008 5009 5010 5011 5012 5013 5014 5015 | SELECTTRACE(1,pParse,p,("end compound-select processing\n")); pParse->nSelectIndent--; #endif return rc; } #endif if( !OptimizationEnabled(db, SQLITE_LateSubquery) ){ /* Manifest the subqueries. This needs to be done before calling ** sqlite3WhereBegin() so that the Table.nRowLogEst value can be set ** correctly for the subqueries. */ sqlite3ManifestSubqueries(pParse, p, pTabList); if( db->mallocFailed ) goto select_end; } /* Various elements of the SELECT copied into local variables for ** convenience */ pEList = p->pEList; pWhere = p->pWhere; pGroupBy = p->pGroupBy; pHaving = p->pHaving; |
︙ | ︙ | |||
5082 5083 5084 5085 5086 5087 5088 | } if( !isAgg && pGroupBy==0 ){ /* No aggregate functions and no GROUP BY clause */ u16 wctrlFlags = (sDistinct.isTnct ? WHERE_WANT_DISTINCT : 0); /* Begin the database scan. */ | | | 5100 5101 5102 5103 5104 5105 5106 5107 5108 5109 5110 5111 5112 5113 5114 | } if( !isAgg && pGroupBy==0 ){ /* No aggregate functions and no GROUP BY clause */ u16 wctrlFlags = (sDistinct.isTnct ? WHERE_WANT_DISTINCT : 0); /* Begin the database scan. */ pWInfo = sqlite3WhereBegin(pParse, p, pTabList, pWhere, sSort.pOrderBy, p->pEList, wctrlFlags, 0); if( pWInfo==0 ) goto select_end; if( sqlite3WhereOutputRowCount(pWInfo) < p->nSelectRow ){ p->nSelectRow = sqlite3WhereOutputRowCount(pWInfo); } if( sDistinct.isTnct && sqlite3WhereIsDistinct(pWInfo) ){ sDistinct.eTnctType = sqlite3WhereIsDistinct(pWInfo); |
︙ | ︙ | |||
5238 5239 5240 5241 5242 5243 5244 | /* Begin a loop that will extract all source rows in GROUP BY order. ** This might involve two separate loops with an OP_Sort in between, or ** it might be a single loop that uses an index to extract information ** in the right order to begin with. */ sqlite3VdbeAddOp2(v, OP_Gosub, regReset, addrReset); | | | 5256 5257 5258 5259 5260 5261 5262 5263 5264 5265 5266 5267 5268 5269 5270 | /* Begin a loop that will extract all source rows in GROUP BY order. ** This might involve two separate loops with an OP_Sort in between, or ** it might be a single loop that uses an index to extract information ** in the right order to begin with. */ sqlite3VdbeAddOp2(v, OP_Gosub, regReset, addrReset); pWInfo = sqlite3WhereBegin(pParse, p, pTabList, pWhere, pGroupBy, 0, WHERE_GROUPBY | (orderByGrp ? WHERE_SORTBYGROUP : 0), 0 ); if( pWInfo==0 ) goto select_end; if( sqlite3WhereIsOrdered(pWInfo)==pGroupBy->nExpr ){ /* The optimizer is able to deliver rows in group by order so ** we do not have to sort. The OP_OpenEphemeral table will be ** cancelled later because we still need to use the pKeyInfo |
︙ | ︙ | |||
5533 5534 5535 5536 5537 5538 5539 | } /* This case runs if the aggregate has no GROUP BY clause. The ** processing is much simpler since there is only a single row ** of output. */ resetAccumulator(pParse, &sAggInfo); | | | 5551 5552 5553 5554 5555 5556 5557 5558 5559 5560 5561 5562 5563 5564 5565 | } /* This case runs if the aggregate has no GROUP BY clause. The ** processing is much simpler since there is only a single row ** of output. */ resetAccumulator(pParse, &sAggInfo); pWInfo = sqlite3WhereBegin(pParse,p,pTabList, pWhere, pMinMax,0,flag,0); if( pWInfo==0 ){ sqlite3ExprListDelete(db, pDel); goto select_end; } updateAccumulator(pParse, &sAggInfo); assert( pMinMax==0 || pMinMax->nExpr==1 ); if( sqlite3WhereIsOrdered(pWInfo)>0 ){ |
︙ | ︙ |
Changes to src/sqliteInt.h.
︙ | ︙ | |||
1269 1270 1271 1272 1273 1274 1275 1276 1277 1278 1279 1280 1281 1282 | #define SQLITE_DistinctOpt 0x0020 /* DISTINCT using indexes */ #define SQLITE_CoverIdxScan 0x0040 /* Covering index scans */ #define SQLITE_OrderByIdxJoin 0x0080 /* ORDER BY of joins via index */ #define SQLITE_SubqCoroutine 0x0100 /* Evaluate subqueries as coroutines */ #define SQLITE_Transitive 0x0200 /* Transitive constraints */ #define SQLITE_OmitNoopJoin 0x0400 /* Omit unused tables in joins */ #define SQLITE_Stat34 0x0800 /* Use STAT3 or STAT4 data */ #define SQLITE_AllOpts 0xffff /* All optimizations */ /* ** Macros for testing whether or not optimizations are enabled or disabled. */ #ifndef SQLITE_OMIT_BUILTIN_TEST #define OptimizationDisabled(db, mask) (((db)->dbOptFlags&(mask))!=0) | > | 1269 1270 1271 1272 1273 1274 1275 1276 1277 1278 1279 1280 1281 1282 1283 | #define SQLITE_DistinctOpt 0x0020 /* DISTINCT using indexes */ #define SQLITE_CoverIdxScan 0x0040 /* Covering index scans */ #define SQLITE_OrderByIdxJoin 0x0080 /* ORDER BY of joins via index */ #define SQLITE_SubqCoroutine 0x0100 /* Evaluate subqueries as coroutines */ #define SQLITE_Transitive 0x0200 /* Transitive constraints */ #define SQLITE_OmitNoopJoin 0x0400 /* Omit unused tables in joins */ #define SQLITE_Stat34 0x0800 /* Use STAT3 or STAT4 data */ #define SQLITE_LateSubquery 0x1000 /* Plan main Q before rendering subQ */ #define SQLITE_AllOpts 0xffff /* All optimizations */ /* ** Macros for testing whether or not optimizations are enabled or disabled. */ #ifndef SQLITE_OMIT_BUILTIN_TEST #define OptimizationDisabled(db, mask) (((db)->dbOptFlags&(mask))!=0) |
︙ | ︙ | |||
3281 3282 3283 3284 3285 3286 3287 3288 3289 3290 3291 3292 3293 3294 3295 3296 3297 3298 3299 | void sqlite3SrcListAssignCursors(Parse*, SrcList*); void sqlite3IdListDelete(sqlite3*, IdList*); void sqlite3SrcListDelete(sqlite3*, SrcList*); Index *sqlite3AllocateIndexObject(sqlite3*,i16,int,char**); Index *sqlite3CreateIndex(Parse*,Token*,Token*,SrcList*,ExprList*,int,Token*, Expr*, int, int); void sqlite3DropIndex(Parse*, SrcList*, int); int sqlite3Select(Parse*, Select*, SelectDest*); Select *sqlite3SelectNew(Parse*,ExprList*,SrcList*,Expr*,ExprList*, Expr*,ExprList*,u16,Expr*,Expr*); void sqlite3SelectDelete(sqlite3*, Select*); Table *sqlite3SrcListLookup(Parse*, SrcList*); int sqlite3IsReadOnly(Parse*, Table*, int); void sqlite3OpenTable(Parse*, int iCur, int iDb, Table*, int); #if defined(SQLITE_ENABLE_UPDATE_DELETE_LIMIT) && !defined(SQLITE_OMIT_SUBQUERY) Expr *sqlite3LimitWhere(Parse*,SrcList*,Expr*,ExprList*,Expr*,Expr*,char*); #endif void sqlite3DeleteFrom(Parse*, SrcList*, Expr*); void sqlite3Update(Parse*, SrcList*, ExprList*, Expr*, int); | > | > | 3282 3283 3284 3285 3286 3287 3288 3289 3290 3291 3292 3293 3294 3295 3296 3297 3298 3299 3300 3301 3302 3303 3304 3305 3306 3307 3308 3309 3310 | void sqlite3SrcListAssignCursors(Parse*, SrcList*); void sqlite3IdListDelete(sqlite3*, IdList*); void sqlite3SrcListDelete(sqlite3*, SrcList*); Index *sqlite3AllocateIndexObject(sqlite3*,i16,int,char**); Index *sqlite3CreateIndex(Parse*,Token*,Token*,SrcList*,ExprList*,int,Token*, Expr*, int, int); void sqlite3DropIndex(Parse*, SrcList*, int); void sqlite3ManifestSubqueries(Parse*, Select*, SrcList*); int sqlite3Select(Parse*, Select*, SelectDest*); Select *sqlite3SelectNew(Parse*,ExprList*,SrcList*,Expr*,ExprList*, Expr*,ExprList*,u16,Expr*,Expr*); void sqlite3SelectDelete(sqlite3*, Select*); Table *sqlite3SrcListLookup(Parse*, SrcList*); int sqlite3IsReadOnly(Parse*, Table*, int); void sqlite3OpenTable(Parse*, int iCur, int iDb, Table*, int); #if defined(SQLITE_ENABLE_UPDATE_DELETE_LIMIT) && !defined(SQLITE_OMIT_SUBQUERY) Expr *sqlite3LimitWhere(Parse*,SrcList*,Expr*,ExprList*,Expr*,Expr*,char*); #endif void sqlite3DeleteFrom(Parse*, SrcList*, Expr*); void sqlite3Update(Parse*, SrcList*, ExprList*, Expr*, int); WhereInfo *sqlite3WhereBegin(Parse*,Select*,SrcList*,Expr*,ExprList*, ExprList*,u16,int); void sqlite3WhereEnd(WhereInfo*); u64 sqlite3WhereOutputRowCount(WhereInfo*); int sqlite3WhereIsDistinct(WhereInfo*); int sqlite3WhereIsOrdered(WhereInfo*); int sqlite3WhereIsSorted(WhereInfo*); int sqlite3WhereContinueLabel(WhereInfo*); int sqlite3WhereBreakLabel(WhereInfo*); |
︙ | ︙ |
Changes to src/update.c.
︙ | ︙ | |||
343 344 345 346 347 348 349 | } /* Begin the database scan */ if( HasRowid(pTab) ){ sqlite3VdbeAddOp3(v, OP_Null, 0, regRowSet, regOldRowid); pWInfo = sqlite3WhereBegin( | | | 343 344 345 346 347 348 349 350 351 352 353 354 355 356 357 | } /* Begin the database scan */ if( HasRowid(pTab) ){ sqlite3VdbeAddOp3(v, OP_Null, 0, regRowSet, regOldRowid); pWInfo = sqlite3WhereBegin( pParse, 0, pTabList, pWhere, 0, 0, WHERE_ONEPASS_DESIRED, iIdxCur ); if( pWInfo==0 ) goto update_cleanup; okOnePass = sqlite3WhereOkOnePass(pWInfo, aiCurOnePass); /* Remember the rowid of every item to be updated. */ sqlite3VdbeAddOp2(v, OP_Rowid, iDataCur, regOldRowid); |
︙ | ︙ | |||
372 373 374 375 376 377 378 | iPk = pParse->nMem+1; pParse->nMem += nPk; regKey = ++pParse->nMem; iEph = pParse->nTab++; sqlite3VdbeAddOp2(v, OP_Null, 0, iPk); addrOpen = sqlite3VdbeAddOp2(v, OP_OpenEphemeral, iEph, nPk); sqlite3VdbeSetP4KeyInfo(pParse, pPk); | | | 372 373 374 375 376 377 378 379 380 381 382 383 384 385 386 | iPk = pParse->nMem+1; pParse->nMem += nPk; regKey = ++pParse->nMem; iEph = pParse->nTab++; sqlite3VdbeAddOp2(v, OP_Null, 0, iPk); addrOpen = sqlite3VdbeAddOp2(v, OP_OpenEphemeral, iEph, nPk); sqlite3VdbeSetP4KeyInfo(pParse, pPk); pWInfo = sqlite3WhereBegin(pParse, 0, pTabList, pWhere, 0, 0, WHERE_ONEPASS_DESIRED, iIdxCur); if( pWInfo==0 ) goto update_cleanup; okOnePass = sqlite3WhereOkOnePass(pWInfo, aiCurOnePass); for(i=0; i<nPk; i++){ sqlite3ExprCodeGetColumnOfTable(v, pTab, iDataCur, pPk->aiColumn[i], iPk+i); } |
︙ | ︙ |
Changes to src/where.c.
︙ | ︙ | |||
2551 2552 2553 2554 2555 2556 2557 | pNew->nSkip = 0; pNew->u.btree.pIndex = 0; pNew->nLTerm = 1; pNew->aLTerm[0] = pTerm; /* TUNING: One-time cost for computing the automatic index is ** estimated to be X*N*log2(N) where N is the number of rows in ** the table being indexed and where X is 7 (LogEst=28) for normal | | | | > | 2551 2552 2553 2554 2555 2556 2557 2558 2559 2560 2561 2562 2563 2564 2565 2566 2567 2568 2569 2570 2571 2572 2573 2574 | pNew->nSkip = 0; pNew->u.btree.pIndex = 0; pNew->nLTerm = 1; pNew->aLTerm[0] = pTerm; /* TUNING: One-time cost for computing the automatic index is ** estimated to be X*N*log2(N) where N is the number of rows in ** the table being indexed and where X is 7 (LogEst=28) for normal ** tables or 0.3333 (LogEst=-16) for views and subqueries. The value ** of X is smaller for views and subqueries so that the query planner ** will be more aggressive about generating automatic indexes for ** those objects, since there is no opportunity to add schema ** indexes on subqueries and views. */ pNew->rSetup = rLogSize + rSize - 16; if( pTab->pSelect==0 && (pTab->tabFlags & TF_Ephemeral)==0 ){ pNew->rSetup += 44; } if( pNew->rSetup<1 ) pNew->rSetup = 1; ApplyCostMultiplier(pNew->rSetup, pTab->costMult); /* TUNING: Each index lookup yields 20 rows in the table. This ** is more than the usual guess of 10 rows, since we have no way ** of knowing how selective the index will ultimately be. It would ** not be unreasonable to make this value much larger. */ pNew->nOut = 43; assert( 43==sqlite3LogEst(20) ); pNew->rRun = sqlite3LogEstAdd(rLogSize,pNew->nOut); |
︙ | ︙ | |||
3892 3893 3894 3895 3896 3897 3898 3899 3900 3901 3902 3903 3904 3905 | ** specific cursor. If WHERE_ONEPASS_DESIRED is set, then iIdxCur is ** the first cursor in an array of cursors for all indices. iIdxCur should ** be used to compute the appropriate cursor depending on which index is ** used. */ WhereInfo *sqlite3WhereBegin( Parse *pParse, /* The parser context */ SrcList *pTabList, /* FROM clause: A list of all tables to be scanned */ Expr *pWhere, /* The WHERE clause */ ExprList *pOrderBy, /* An ORDER BY (or GROUP BY) clause, or NULL */ ExprList *pResultSet, /* Result set of the query */ u16 wctrlFlags, /* One of the WHERE_* flags defined in sqliteInt.h */ int iIdxCur /* If WHERE_ONETABLE_ONLY is set, index cursor number */ ){ | > | 3893 3894 3895 3896 3897 3898 3899 3900 3901 3902 3903 3904 3905 3906 3907 | ** specific cursor. If WHERE_ONEPASS_DESIRED is set, then iIdxCur is ** the first cursor in an array of cursors for all indices. iIdxCur should ** be used to compute the appropriate cursor depending on which index is ** used. */ WhereInfo *sqlite3WhereBegin( Parse *pParse, /* The parser context */ Select *pSelect, /* SELECT stmt that owns this WHERE. Might be NULL */ SrcList *pTabList, /* FROM clause: A list of all tables to be scanned */ Expr *pWhere, /* The WHERE clause */ ExprList *pOrderBy, /* An ORDER BY (or GROUP BY) clause, or NULL */ ExprList *pResultSet, /* Result set of the query */ u16 wctrlFlags, /* One of the WHERE_* flags defined in sqliteInt.h */ int iIdxCur /* If WHERE_ONETABLE_ONLY is set, index cursor number */ ){ |
︙ | ︙ | |||
4165 4166 4167 4168 4169 4170 4171 4172 4173 4174 4175 4176 4177 4178 | if( (wctrlFlags & WHERE_ONEPASS_DESIRED)!=0 && (pWInfo->a[0].pWLoop->wsFlags & WHERE_ONEROW)!=0 ){ pWInfo->okOnePass = 1; if( HasRowid(pTabList->a[0].pTab) ){ pWInfo->a[0].pWLoop->wsFlags &= ~WHERE_IDX_ONLY; } } /* Open all tables in the pTabList and any indices selected for ** searching those tables. */ for(ii=0, pLevel=pWInfo->a; ii<nTabList; ii++, pLevel++){ Table *pTab; /* Table to open */ int iDb; /* Index of database containing table/index */ | > > > > > > > > > > | 4167 4168 4169 4170 4171 4172 4173 4174 4175 4176 4177 4178 4179 4180 4181 4182 4183 4184 4185 4186 4187 4188 4189 4190 | if( (wctrlFlags & WHERE_ONEPASS_DESIRED)!=0 && (pWInfo->a[0].pWLoop->wsFlags & WHERE_ONEROW)!=0 ){ pWInfo->okOnePass = 1; if( HasRowid(pTabList->a[0].pTab) ){ pWInfo->a[0].pWLoop->wsFlags &= ~WHERE_IDX_ONLY; } } /* If this WHERE clause is part of a SELECT statement, then there ** might be subqueries in the FROM clause that need to be manifested. ** This works mostly - except the Table.nRowLogEst value is not set ** correctly for the subquery, resulting in a bad plan in some cases. */ if( OptimizationEnabled(db, SQLITE_LateSubquery) && pSelect!=0 ){ sqlite3ManifestSubqueries(pParse, pSelect, pTabList); if( db->mallocFailed ) goto whereBeginError; } /* Open all tables in the pTabList and any indices selected for ** searching those tables. */ for(ii=0, pLevel=pWInfo->a; ii<nTabList; ii++, pLevel++){ Table *pTab; /* Table to open */ int iDb; /* Index of database containing table/index */ |
︙ | ︙ |
Changes to src/wherecode.c.
︙ | ︙ | |||
1261 1262 1263 1264 1265 1266 1267 | int j1 = 0; /* Address of jump operation */ if( pAndExpr && !ExprHasProperty(pOrExpr, EP_FromJoin) ){ pAndExpr->pLeft = pOrExpr; pOrExpr = pAndExpr; } /* Loop through table entries that match term pOrTerm. */ WHERETRACE(0xffff, ("Subplan for OR-clause:\n")); | | | 1261 1262 1263 1264 1265 1266 1267 1268 1269 1270 1271 1272 1273 1274 1275 | int j1 = 0; /* Address of jump operation */ if( pAndExpr && !ExprHasProperty(pOrExpr, EP_FromJoin) ){ pAndExpr->pLeft = pOrExpr; pOrExpr = pAndExpr; } /* Loop through table entries that match term pOrTerm. */ WHERETRACE(0xffff, ("Subplan for OR-clause:\n")); pSubWInfo = sqlite3WhereBegin(pParse, 0, pOrTab, pOrExpr, 0, 0, wctrlFlags, iCovCur); assert( pSubWInfo || pParse->nErr || db->mallocFailed ); if( pSubWInfo ){ WhereLoop *pSubLoop; int addrExplain = sqlite3WhereExplainOneScan( pParse, pOrTab, &pSubWInfo->a[0], iLevel, pLevel->iFrom, 0 ); |
︙ | ︙ |
Changes to test/eqp.test.
︙ | ︙ | |||
77 78 79 80 81 82 83 | 0 0 0 {USE TEMP B-TREE FOR GROUP BY} 0 0 0 {USE TEMP B-TREE FOR DISTINCT} } do_eqp_test 1.7 { SELECT * FROM t3 JOIN (SELECT 1) } { | < | > < | > < | > < | > < | > | 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 | 0 0 0 {USE TEMP B-TREE FOR GROUP BY} 0 0 0 {USE TEMP B-TREE FOR DISTINCT} } do_eqp_test 1.7 { SELECT * FROM t3 JOIN (SELECT 1) } { 0 0 0 {SCAN TABLE t3} 0 1 1 {SCAN SUBQUERY 1} } do_eqp_test 1.8 { SELECT * FROM t3 JOIN (SELECT 1 UNION SELECT 2) } { 1 0 0 {COMPOUND SUBQUERIES 2 AND 3 USING TEMP B-TREE (UNION)} 0 0 0 {SCAN TABLE t3} 0 1 1 {SCAN SUBQUERY 1} } do_eqp_test 1.9 { SELECT * FROM t3 JOIN (SELECT 1 EXCEPT SELECT a FROM t3 LIMIT 17) } { 3 0 0 {SCAN TABLE t3} 1 0 0 {COMPOUND SUBQUERIES 2 AND 3 USING TEMP B-TREE (EXCEPT)} 0 0 0 {SCAN TABLE t3} 0 1 1 {SCAN SUBQUERY 1} } do_eqp_test 1.10 { SELECT * FROM t3 JOIN (SELECT 1 INTERSECT SELECT a FROM t3 LIMIT 17) } { 3 0 0 {SCAN TABLE t3} 1 0 0 {COMPOUND SUBQUERIES 2 AND 3 USING TEMP B-TREE (INTERSECT)} 0 0 0 {SCAN TABLE t3} 0 1 1 {SCAN SUBQUERY 1} } do_eqp_test 1.11 { SELECT * FROM t3 JOIN (SELECT 1 UNION ALL SELECT a FROM t3 LIMIT 17) } { 3 0 0 {SCAN TABLE t3} 1 0 0 {COMPOUND SUBQUERIES 2 AND 3 (UNION ALL)} 0 0 0 {SCAN TABLE t3} 0 1 1 {SCAN SUBQUERY 1} } #------------------------------------------------------------------------- # Test cases eqp-2.* - tests for single select statements. # drop_all_tables do_execsql_test 2.1 { |
︙ | ︙ |