Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Changes In Branch experimental-costs Excluding Merge-Ins
This is equivalent to a diff from af2cbe64 to 05e6e16c
2014-04-30
| ||
15:22 | Modify the way the costs of various query plans are estimated. If the user supplies a likelihood() value (or equivalent) on an indexed WHERE constraint, use it to estimate the number of index rows visited. (check-in: 90e36676 user: dan tags: trunk) | |
15:00 | Add text to the header comment of whereLoopAddBtree() describing how the costs of various b-tree loops are estimated. (Closed-Leaf check-in: 05e6e16c user: dan tags: experimental-costs) | |
14:53 | Update a couple of test cases to account for the fact that this branch prefers an index scan and partial sort over a full-table scan and full external sort. (check-in: 9b975bf3 user: dan tags: experimental-costs) | |
2014-04-29
| ||
16:46 | Merge trunk changes into this branch. (check-in: d74299f0 user: dan tags: experimental-costs) | |
2014-04-28
| ||
18:02 | Merge all recent trunk enhancements and fixes into the sessions branch. (check-in: e158812c user: drh tags: sessions) | |
17:56 | Add the sqlite3_rtree_query_callback() API to the RTree virtual table. (Cherrypick from the sessions branch.) (check-in: af2cbe64 user: drh tags: trunk) | |
17:51 | Add the sqlite3_rtree_query_callback() API to the RTree virtual table. (check-in: 3dca2809 user: drh tags: sessions) | |
2014-04-26
| ||
19:23 | Update requirements marks to fix typos in the requirements text. No changes to code. (check-in: f5a26365 user: drh tags: trunk) | |
Changes to src/analyze.c.
︙ | |||
1367 1368 1369 1370 1371 1372 1373 1374 1375 1376 1377 1378 1379 1380 1381 1382 1383 1384 1385 1386 1387 1388 1389 1390 1391 | 1367 1368 1369 1370 1371 1372 1373 1374 1375 1376 1377 1378 1379 1380 1381 1382 1383 1384 1385 1386 1387 1388 1389 1390 1391 1392 1393 1394 1395 1396 1397 1398 1399 1400 1401 1402 1403 1404 | + + - + + + + | ** list of space separated integers. Read the first nOut of these into ** the array aOut[]. */ static void decodeIntArray( char *zIntArray, /* String containing int array to decode */ int nOut, /* Number of slots in aOut[] */ tRowcnt *aOut, /* Store integers here */ LogEst *aLog, /* Or, if aOut==0, here */ Index *pIndex /* Handle extra flags for this index, if not NULL */ ){ char *z = zIntArray; int c; int i; tRowcnt v; #ifdef SQLITE_ENABLE_STAT3_OR_STAT4 if( z==0 ) z = ""; #else if( NEVER(z==0) ) z = ""; #endif for(i=0; *z && i<nOut; i++){ v = 0; while( (c=z[0])>='0' && c<='9' ){ v = v*10 + c - '0'; z++; } if( aOut ){ |
︙ | |||
1441 1442 1443 1444 1445 1446 1447 | 1446 1447 1448 1449 1450 1451 1452 1453 1454 1455 1456 1457 1458 1459 1460 1461 1462 1463 1464 1465 | - - + + - + | pIndex = sqlite3PrimaryKeyIndex(pTable); }else{ pIndex = sqlite3FindIndex(pInfo->db, argv[1], pInfo->zDatabase); } z = argv[2]; if( pIndex ){ |
︙ | |||
1638 1639 1640 1641 1642 1643 1644 | 1643 1644 1645 1646 1647 1648 1649 1650 1651 1652 1653 1654 1655 1656 1657 1658 1659 | - - - + + + | nCol = pIdx->nSampleCol; if( bStat3 && nCol>1 ) continue; if( pIdx!=pPrevIdx ){ initAvgEq(pPrevIdx); pPrevIdx = pIdx; } pSample = &pIdx->aSample[pIdx->nSample]; |
︙ |
Changes to src/build.c.
︙ | |||
901 902 903 904 905 906 907 | 901 902 903 904 905 906 907 908 909 910 911 912 913 914 915 | - + | pParse->nErr++; goto begin_table_error; } pTable->zName = zName; pTable->iPKey = -1; pTable->pSchema = db->aDb[iDb].pSchema; pTable->nRef = 1; |
︙ | |||
2726 2727 2728 2729 2730 2731 2732 | 2726 2727 2728 2729 2730 2731 2732 2733 2734 2735 2736 2737 2738 2739 2740 2741 2742 2743 2744 2745 2746 2747 2748 | - + - - - + + + | char **ppExtra /* Pointer to the "extra" space */ ){ Index *p; /* Allocated index object */ int nByte; /* Bytes of space for Index object + arrays */ nByte = ROUND8(sizeof(Index)) + /* Index structure */ ROUND8(sizeof(char*)*nCol) + /* Index.azColl */ |
︙ | |||
2964 2965 2966 2967 2968 2969 2970 | 2964 2965 2966 2967 2968 2969 2970 2971 2972 2973 2974 2975 2976 2977 2978 | - + | nName = sqlite3Strlen30(zName); nExtraCol = pPk ? pPk->nKeyCol : 1; pIndex = sqlite3AllocateIndexObject(db, pList->nExpr + nExtraCol, nName + nExtra + 1, &zExtra); if( db->mallocFailed ){ goto exit_create_index; } |
︙ | |||
3245 3246 3247 3248 3249 3250 3251 | 3245 3246 3247 3248 3249 3250 3251 3252 3253 3254 3255 3256 3257 3258 3259 3260 3261 3262 3263 3264 3265 3266 3267 3268 3269 3270 3271 3272 3273 3274 3275 3276 3277 3278 3279 3280 3281 3282 3283 3284 3285 3286 3287 3288 3289 3290 | - + + + - + + - + - - - - - - + + + + + + + + + + + - - - - + + + | ** Fill the Index.aiRowEst[] array with default information - information ** to be used when we have not run the ANALYZE command. ** ** aiRowEst[0] is suppose to contain the number of elements in the index. ** Since we do not know, guess 1 million. aiRowEst[1] is an estimate of the ** number of rows in the table that match any particular value of the ** first column of the index. aiRowEst[2] is an estimate of the number |
︙ |
Changes to src/pragma.c.
︙ | |||
1484 1485 1486 1487 1488 1489 1490 | 1484 1485 1486 1487 1488 1489 1490 1491 1492 1493 1494 1495 1496 1497 1498 1499 1500 1501 1502 1503 1504 1505 1506 | - + + - + + | sqlite3VdbeSetColName(v, 3, COLNAME_NAME, "height", SQLITE_STATIC); for(i=sqliteHashFirst(&pDb->pSchema->tblHash); i; i=sqliteHashNext(i)){ Table *pTab = sqliteHashData(i); sqlite3VdbeAddOp4(v, OP_String8, 0, 1, 0, pTab->zName, 0); sqlite3VdbeAddOp2(v, OP_Null, 0, 2); sqlite3VdbeAddOp2(v, OP_Integer, (int)sqlite3LogEstToInt(pTab->szTabRow), 3); |
︙ |
Changes to src/select.c.
︙ | |||
1686 1687 1688 1689 1690 1691 1692 | 1686 1687 1688 1689 1690 1691 1692 1693 1694 1695 1696 1697 1698 1699 1700 | - + | return 0; } /* The sqlite3ResultSetOfSelect() is only used n contexts where lookaside ** is disabled */ assert( db->lookaside.bEnabled==0 ); pTab->nRef = 1; pTab->zName = 0; |
︙ | |||
3825 3826 3827 3828 3829 3830 3831 | 3825 3826 3827 3828 3829 3830 3831 3832 3833 3834 3835 3836 3837 3838 3839 | - + | assert( pFrom->pTab==0 ); pFrom->pTab = pTab = sqlite3DbMallocZero(db, sizeof(Table)); if( pTab==0 ) return WRC_Abort; pTab->nRef = 1; pTab->zName = sqlite3DbStrDup(db, pCte->zName); pTab->iPKey = -1; |
︙ | |||
4001 4002 4003 4004 4005 4006 4007 | 4001 4002 4003 4004 4005 4006 4007 4008 4009 4010 4011 4012 4013 4014 4015 | - + | pFrom->pTab = pTab = sqlite3DbMallocZero(db, sizeof(Table)); if( pTab==0 ) return WRC_Abort; pTab->nRef = 1; pTab->zName = sqlite3MPrintf(db, "sqlite_sq_%p", (void*)pTab); while( pSel->pPrior ){ pSel = pSel->pPrior; } selectColumnsFromExprList(pParse, pSel->pEList, &pTab->nCol, &pTab->aCol); pTab->iPKey = -1; |
︙ | |||
4651 4652 4653 4654 4655 4656 4657 | 4651 4652 4653 4654 4655 4656 4657 4658 4659 4660 4661 4662 4663 4664 4665 | - + | 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); |
︙ | |||
4682 4683 4684 4685 4686 4687 4688 | 4682 4683 4684 4685 4686 4687 4688 4689 4690 4691 4692 4693 4694 4695 4696 | - + | 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); |
︙ |
Changes to src/sqliteInt.h.
︙ | |||
521 522 523 524 525 526 527 | 521 522 523 524 525 526 527 528 529 530 531 532 533 534 535 536 537 538 | - + - + | /* ** Estimated quantities used for query planning are stored as 16-bit ** logarithms. For quantity X, the value stored is 10*log2(X). This ** gives a possible range of values of approximately 1.0e986 to 1e-986. ** But the allowed values are "grainy". Not every value is representable. ** For example, quantities 16 and 17 are both represented by a LogEst |
︙ | |||
1467 1468 1469 1470 1471 1472 1473 | 1467 1468 1469 1470 1471 1472 1473 1474 1475 1476 1477 1478 1479 1480 1481 | - + | Index *pIndex; /* List of SQL indexes on this table. */ Select *pSelect; /* NULL for tables. Points to definition if a view. */ FKey *pFKey; /* Linked list of all foreign keys in this table */ char *zColAff; /* String defining the affinity of each column */ #ifndef SQLITE_OMIT_CHECK ExprList *pCheck; /* All CHECK constraints */ #endif |
︙ | |||
1676 1677 1678 1679 1680 1681 1682 | 1676 1677 1678 1679 1680 1681 1682 1683 1684 1685 1686 1687 1688 1689 1690 | - + | ** and the value of Index.onError indicate the which conflict resolution ** algorithm to employ whenever an attempt is made to insert a non-unique ** element. */ struct Index { char *zName; /* Name of this index */ i16 *aiColumn; /* Which columns are used by this index. 1st is 0 */ |
︙ |
Changes to src/util.c.
︙ | |||
1242 1243 1244 1245 1246 1247 1248 | 1242 1243 1244 1245 1246 1247 1248 1249 1250 1251 1252 1253 1254 1255 1256 1257 | - - + + | if( b>a+49 ) return b; if( b>a+31 ) return b+1; return b+x[b-a]; } } /* |
︙ |
Changes to src/where.c.
︙ | |||
223 224 225 226 227 228 229 | 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237 | - + | } pWC->nSlot = sqlite3DbMallocSize(db, pWC->a)/sizeof(pWC->a[0]); } pTerm = &pWC->a[idx = pWC->nTerm++]; if( p && ExprHasProperty(p, EP_Unlikely) ){ pTerm->truthProb = sqlite3LogEst(p->iTable) - 99; }else{ |
︙ | |||
1952 1953 1954 1955 1956 1957 1958 | 1952 1953 1954 1955 1956 1957 1958 1959 1960 1961 1962 1963 1964 1965 1966 1967 1968 1969 1970 1971 1972 1973 1974 1975 1976 1977 1978 1979 1980 1981 1982 1983 1984 1985 1986 1987 1988 1989 1990 1991 1992 1993 1994 1995 1996 1997 1998 1999 2000 2001 2002 2003 2004 2005 2006 2007 | + - + + + + + + + + + + + + + + + + + + + + + + + + | aStat[1] = aSample[i].anEq[iCol]; }else{ tRowcnt iLower, iUpper, iGap; if( i==0 ){ iLower = 0; iUpper = aSample[0].anLt[iCol]; }else{ i64 nRow0 = sqlite3LogEstToInt(pIdx->aiRowLogEst[0]); |
︙ | |||
2063 2064 2065 2066 2067 2068 2069 | 2087 2088 2089 2090 2091 2092 2093 2094 2095 2096 2097 2098 2099 2100 2101 | - + | aff = SQLITE_AFF_INTEGER; }else{ aff = p->pTable->aCol[p->aiColumn[nEq]].affinity; } /* Determine iLower and iUpper using ($P) only. */ if( nEq==0 ){ iLower = 0; |
︙ | |||
2123 2124 2125 2126 2127 2128 2129 | 2147 2148 2149 2150 2151 2152 2153 2154 2155 2156 2157 2158 2159 2160 2161 2162 2163 2164 2165 2166 2167 2168 2169 2170 2171 2172 | - - - - + - - - - - + + + + + + + + + - - + + | } } #else UNUSED_PARAMETER(pParse); UNUSED_PARAMETER(pBuilder); #endif assert( pLower || pUpper ); |
︙ | |||
2230 2231 2232 2233 2234 2235 2236 2237 2238 2239 2240 2241 2242 2243 2244 | 2255 2256 2257 2258 2259 2260 2261 2262 2263 2264 2265 2266 2267 2268 2269 2270 2271 2272 2273 2274 2275 2276 2277 2278 2279 2280 2281 2282 2283 2284 2285 | + - + - + | static int whereInScanEst( Parse *pParse, /* Parsing & code generating context */ WhereLoopBuilder *pBuilder, ExprList *pList, /* The value list on the RHS of "x IN (v1,v2,v3,...)" */ tRowcnt *pnRow /* Write the revised row estimate here */ ){ Index *p = pBuilder->pNew->u.btree.pIndex; i64 nRow0 = sqlite3LogEstToInt(p->aiRowLogEst[0]); int nRecValid = pBuilder->nRecValid; int rc = SQLITE_OK; /* Subfunction return code */ tRowcnt nEst; /* Number of rows for a single term */ tRowcnt nRowEst = 0; /* New estimate of the number of rows */ int i; /* Loop counter */ assert( p->aSample!=0 ); for(i=0; rc==SQLITE_OK && i<pList->nExpr; i++){ |
︙ | |||
3756 3757 3758 3759 3760 3761 3762 3763 3764 3765 3766 3767 3768 3769 3770 3771 3772 | 3782 3783 3784 3785 3786 3787 3788 3789 3790 3791 3792 3793 3794 3795 3796 3797 3798 3799 3800 | + + | ** ** To say "WhereLoop X is a proper subset of Y" means that X uses fewer ** WHERE clause terms than Y and that every WHERE clause term used by X is ** also used by Y. */ static void whereLoopAdjustCost(const WhereLoop *p, WhereLoop *pTemplate){ if( (pTemplate->wsFlags & WHERE_INDEXED)==0 ) return; if( (pTemplate->wsFlags & WHERE_SKIPSCAN)!=0 ) return; for(; p; p=p->pNextLoop){ if( p->iTab!=pTemplate->iTab ) continue; if( (p->wsFlags & WHERE_INDEXED)==0 ) continue; if( (p->wsFlags & WHERE_SKIPSCAN)!=0 ) continue; if( whereLoopCheaperProperSubset(p, pTemplate) ){ /* Adjust pTemplate cost downward so that it is cheaper than its ** subset p */ pTemplate->rRun = p->rRun; pTemplate->nOut = p->nOut - 1; }else if( whereLoopCheaperProperSubset(pTemplate, p) ){ /* Adjust pTemplate cost upward so that it is costlier than p since |
︙ | |||
3983 3984 3985 3986 3987 3988 3989 | 4011 4012 4013 4014 4015 4016 4017 4018 4019 4020 4021 4022 4023 4024 4025 4026 4027 4028 4029 4030 4031 4032 4033 4034 4035 4036 4037 4038 | - + + + - - + + + + + + + | if( (pTerm->prereqAll & notAllowed)!=0 ) continue; for(j=pLoop->nLTerm-1; j>=0; j--){ pX = pLoop->aLTerm[j]; if( pX==0 ) continue; if( pX==pTerm ) break; if( pX->iParent>=0 && (&pWC->a[pX->iParent])==pTerm ) break; } |
︙ | |||
4015 4016 4017 4018 4019 4020 4021 | 4050 4051 4052 4053 4054 4055 4056 4057 4058 4059 4060 4061 4062 4063 4064 4065 4066 4067 4068 4069 4070 4071 4072 4073 4074 4075 4076 4077 4078 4079 4080 4081 4082 4083 4084 4085 4086 4087 4088 4089 4090 4091 4092 4093 4094 4095 4096 4097 4098 4099 4100 4101 4102 4103 4104 4105 4106 4107 4108 4109 4110 4111 4112 4113 4114 4115 4116 4117 4118 4119 4120 4121 4122 4123 4124 4125 4126 4127 4128 4129 4130 4131 4132 4133 4134 4135 4136 4137 4138 4139 4140 4141 4142 4143 4144 4145 4146 4147 4148 4149 4150 4151 4152 4153 4154 4155 4156 4157 4158 4159 4160 4161 4162 4163 4164 4165 4166 4167 4168 4169 4170 4171 4172 4173 4174 4175 4176 4177 4178 4179 4180 4181 4182 4183 4184 4185 4186 4187 4188 4189 4190 4191 4192 4193 4194 4195 4196 4197 4198 4199 4200 4201 4202 4203 4204 4205 4206 4207 4208 4209 4210 4211 4212 4213 4214 4215 4216 4217 4218 4219 4220 4221 4222 4223 4224 4225 4226 4227 4228 4229 4230 4231 4232 4233 4234 4235 4236 4237 4238 4239 4240 4241 4242 4243 4244 4245 4246 4247 4248 4249 4250 4251 4252 4253 4254 4255 4256 4257 4258 4259 4260 4261 4262 4263 4264 4265 4266 4267 4268 4269 4270 4271 4272 4273 4274 4275 | - - - - - + - + - - - + + + + + + + + - + - + - - - + + + + + - + - - - - + + + + + + + + - + - - - + - - - - - + - - - - + - - - - - - - + + + - - - + + + - - - + + + + + + + + + + + + + - + + + + + + + + + - - - - - - + + + + + + + + - - - - - - + + + + + - - - - - - - - - + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + - - - + - - + + + + + + + + + + + | u16 saved_nLTerm; /* Original value of pNew->nLTerm */ u16 saved_nEq; /* Original value of pNew->u.btree.nEq */ u16 saved_nSkip; /* Original value of pNew->u.btree.nSkip */ u32 saved_wsFlags; /* Original value of pNew->wsFlags */ LogEst saved_nOut; /* Original value of pNew->nOut */ int iCol; /* Index of the column in the table */ int rc = SQLITE_OK; /* Return code */ |
︙ | |||
4269 4270 4271 4272 4273 4274 4275 4276 4277 4278 4279 4280 4281 4282 4283 | 4345 4346 4347 4348 4349 4350 4351 4352 4353 4354 4355 4356 4357 4358 4359 4360 4361 4362 4363 4364 4365 4366 4367 4368 4369 4370 4371 4372 4373 4374 4375 4376 4377 4378 4379 4380 4381 4382 4383 4384 4385 4386 4387 4388 4389 4390 | + + + + + + + + + + + + + + + + + + + + + + + - + | return 0; } /* ** Add all WhereLoop objects for a single table of the join where the table ** is idenfied by pBuilder->pNew->iTab. That table is guaranteed to be ** a b-tree table, not a virtual table. ** ** The costs (WhereLoop.rRun) of the b-tree loops added by this function ** are calculated as follows: ** ** For a full scan, assuming the table (or index) contains nRow rows: ** ** cost = nRow * 3.0 // full-table scan ** cost = nRow * K // scan of covering index ** cost = nRow * (K+3.0) // scan of non-covering index ** ** where K is a value between 1.1 and 3.0 set based on the relative ** estimated average size of the index and table records. ** ** For an index scan, where nVisit is the number of index rows visited ** by the scan, and nSeek is the number of seek operations required on ** the index b-tree: ** ** cost = nSeek * (log(nRow) + K * nVisit) // covering index ** cost = nSeek * (log(nRow) + (K+3.0) * nVisit) // non-covering index ** ** Normally, nSeek is 1. nSeek values greater than 1 come about if the ** WHERE clause includes "x IN (....)" terms used in place of "x=?". Or when ** implicit "x IN (SELECT x FROM tbl)" terms are added for skip-scans. */ static int whereLoopAddBtree( WhereLoopBuilder *pBuilder, /* WHERE clause information */ Bitmask mExtra /* Extra prerequesites for using this table */ ){ WhereInfo *pWInfo; /* WHERE analysis context */ Index *pProbe; /* An index we are evaluating */ Index sPk; /* A fake index object for the primary key */ |
︙ | |||
4312 4313 4314 4315 4316 4317 4318 | 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 | - + + - - + + - + | ** variable sPk to represent the rowid primary key index. Make this ** fake index the first in a chain of Index objects with all of the real ** indices to follow */ Index *pFirst; /* First of real indices on the table */ memset(&sPk, 0, sizeof(Index)); sPk.nKeyCol = 1; sPk.aiColumn = &aiColumnPk; |
︙ | |||
4375 4376 4377 4378 4379 4380 4381 4382 4383 4384 4385 4386 4387 4388 4389 4390 4391 4392 4393 4394 4395 4396 4397 4398 | 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 | + - + - - - + | /* Loop over all indices */ for(; rc==SQLITE_OK && pProbe; pProbe=pProbe->pNext, iSortIdx++){ if( pProbe->pPartIdxWhere!=0 && !whereUsablePartialIndex(pNew->iTab, pWC, pProbe->pPartIdxWhere) ){ continue; /* Partial index inappropriate for this query */ } rSize = pProbe->aiRowLogEst[0]; pNew->u.btree.nEq = 0; pNew->u.btree.nSkip = 0; pNew->nLTerm = 0; pNew->iSortIdx = 0; pNew->rSetup = 0; pNew->prereq = mExtra; pNew->nOut = rSize; pNew->u.btree.pIndex = pProbe; b = indexMightHelpWithOrderBy(pBuilder, pProbe, pSrc->iCursor); /* The ONEPASS_DESIRED flags never occurs together with ORDER BY */ assert( (pWInfo->wctrlFlags & WHERE_ONEPASS_DESIRED)==0 || b==0 ); if( pProbe->tnum<=0 ){ /* Integer primary key index */ pNew->wsFlags = WHERE_IPK; /* Full table scan */ pNew->iSortIdx = b ? iSortIdx : 0; |
︙ | |||
4422 4423 4424 4425 4426 4427 4428 | 4521 4522 4523 4524 4525 4526 4527 4528 4529 4530 4531 4532 4533 4534 4535 4536 4537 4538 4539 4540 4541 4542 4543 4544 | - + - - - - - + - - + - - - - + - - + + - - - - - - - - - - - - + - - + + | && (pProbe->szIdxRow<pTab->szTabRow) && (pWInfo->wctrlFlags & WHERE_ONEPASS_DESIRED)==0 && sqlite3GlobalConfig.bUseCis && OptimizationEnabled(pWInfo->pParse->db, SQLITE_CoverIdxScan) ) ){ pNew->iSortIdx = b ? iSortIdx : 0; |
︙ | |||
4728 4729 4730 4731 4732 4733 4734 | 4808 4809 4810 4811 4812 4813 4814 4815 4816 4817 4818 4819 4820 4821 4822 | - - + | pNew->nLTerm = 1; pNew->aLTerm[0] = pTerm; pNew->wsFlags = WHERE_MULTI_OR; pNew->rSetup = 0; pNew->iSortIdx = 0; memset(&pNew->u, 0, sizeof(pNew->u)); for(i=0; rc==SQLITE_OK && i<sSum.n; i++){ |
︙ | |||
5175 5176 5177 5178 5179 5180 5181 | 5254 5255 5256 5257 5258 5259 5260 5261 5262 5263 5264 5265 5266 5267 5268 5269 5270 5271 5272 5273 5274 5275 5276 5277 5278 5279 5280 5281 5282 5283 5284 5285 5286 5287 5288 | - - - - - + + + + + + + + + - - - - + + + + - + - + + - - + + | nOut = pFrom->nRow + pWLoop->nOut; maskNew = pFrom->maskLoop | pWLoop->maskSelf; if( isOrdered<0 ){ isOrdered = wherePathSatisfiesOrderBy(pWInfo, pWInfo->pOrderBy, pFrom, pWInfo->wctrlFlags, iLoop, pWLoop, &revMask); if( isOrdered>=0 && isOrdered<nOrderBy ){ |
︙ |
Changes to src/whereInt.h.
︙ | |||
454 455 456 457 458 459 460 | 454 455 456 457 458 459 460 461 | + | #define WHERE_VIRTUALTABLE 0x00000400 /* WhereLoop.u.vtab is valid */ #define WHERE_IN_ABLE 0x00000800 /* Able to support an IN operator */ #define WHERE_ONEROW 0x00001000 /* Selects no more than one row */ #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_LIKELIHOOD 0x00020000 /* A likelihood() is affecting nOut */ |
Changes to test/analyze3.test.
︙ | |||
99 100 101 102 103 104 105 106 107 108 109 110 | 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 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237 238 239 240 241 242 243 244 245 246 247 248 249 250 251 252 253 254 255 256 257 258 259 | + + + + + + + + + - + - + - + - + + + + + + - + - + - + - + + + + + - + - + - + - + | ifcapable stat4 { execsql { SELECT count(*)>0 FROM sqlite_stat4; } } else { execsql { SELECT count(*)>0 FROM sqlite_stat3; } } } {1} do_execsql_test analyze3-1.1.x { SELECT count(*) FROM t1 WHERE x>200 AND x<300; SELECT count(*) FROM t1 WHERE x>0 AND x<1100; } {99 1000} # The first of the following two SELECT statements visits 99 rows. So # it is better to use the index. But the second visits every row in # the table (1000 in total) so it is better to do a full-table scan. # do_eqp_test analyze3-1.1.2 { SELECT sum(y) FROM t1 WHERE x>200 AND x<300 } {0 0 0 {SEARCH TABLE t1 USING INDEX i1 (x>? AND x<?)}} do_eqp_test analyze3-1.1.3 { SELECT sum(y) FROM t1 WHERE x>0 AND x<1100 |
︙ |
Changes to test/analyze9.test.
︙ | |||
562 563 564 565 566 567 568 | 562 563 564 565 566 567 568 569 570 571 572 573 574 575 576 577 578 579 580 581 582 583 584 585 586 587 588 589 590 591 592 593 594 595 596 | - + - + - + - + - + | #------------------------------------------------------------------------- # Check that affinities are taken into account when using stat4 data to # estimate the number of rows scanned by a rowid constraint. # drop_all_tables do_test 13.1 { execsql { |
︙ |
Changes to test/autoindex1.test.
︙ | |||
93 94 95 96 97 98 99 100 101 102 103 104 105 106 | 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 | + + | db status autoindex } {0} do_test autoindex1-210 { db eval { PRAGMA automatic_index=ON; ANALYZE; UPDATE sqlite_stat1 SET stat='10000' WHERE tbl='t1'; -- Table t2 actually contains 8 rows. UPDATE sqlite_stat1 SET stat='16' WHERE tbl='t2'; ANALYZE sqlite_master; SELECT b, (SELECT d FROM t2 WHERE c=a) FROM t1; } } {11 911 22 922 33 933 44 944 55 955 66 966 77 977 88 988} do_test autoindex1-211 { db status step } {7} |
︙ |
Added test/cost.test.