Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
Comment: | This is the beginning of an attempt to backport recent query planner enhancements to version 3.7.2. The code in this version builds and runs and seems to give correct answers, but it generates suboptimal query plans and hence many of the test cases fail. The test script gives up after 1000 errors. |
---|---|
Downloads: | Tarball | ZIP archive |
Timelines: | family | ancestors | descendants | both | branch-3.7.2 |
Files: | files | file ages | folders |
SHA1: |
e72cf118cb25e9fed96f8d5cebbc0f63 |
User & Date: | drh 2011-02-12 01:59:22.979 |
Original Comment: | This is the beginning of an attempt to backport recent query planner enhancements to version 3.7.2. The code in this version builds and runs and seems to give correct answers, but it generates suboptimal query plans and hence many of the test cases fail. The test script gives up after 1000 errors. |
Context
2011-02-12
| ||
05:34 | Fix problems in the backport, reducing the number of errors in the TCL tests to just a few dozen. Most of the remaining errors seem to be real and desirable changes of behavior. (check-in: 9d2b0af266 user: drh tags: branch-3.7.2) | |
01:59 | This is the beginning of an attempt to backport recent query planner enhancements to version 3.7.2. The code in this version builds and runs and seems to give correct answers, but it generates suboptimal query plans and hence many of the test cases fail. The test script gives up after 1000 errors. (check-in: e72cf118cb user: drh tags: branch-3.7.2) | |
2010-08-24
| ||
00:40 | Version 3.7.2 (check-in: 42537b6056 user: drh tags: trunk, release, version-3.7.2) | |
Changes
Changes to src/analyze.c.
︙ | ︙ | |||
109 110 111 112 113 114 115 | sqlite3 *db = pParse->db; /* Database handle */ Index *pIdx; /* An index to being analyzed */ int iIdxCur; /* Cursor open on index being analyzed */ Vdbe *v; /* The virtual machine being built up */ int i; /* Loop counter */ int topOfLoop; /* The top of the loop */ int endOfLoop; /* The end of the loop */ | | > | > > > > > > > | > | < < < | 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 | sqlite3 *db = pParse->db; /* Database handle */ Index *pIdx; /* An index to being analyzed */ int iIdxCur; /* Cursor open on index being analyzed */ Vdbe *v; /* The virtual machine being built up */ int i; /* Loop counter */ int topOfLoop; /* The top of the loop */ int endOfLoop; /* The end of the loop */ int addr = 0; /* The address of an instruction */ int jZeroRows = 0; /* Jump from here if number of rows is zero */ int iDb; /* Index of database containing pTab */ int regTabname = iMem++; /* Register containing table name */ int regIdxname = iMem++; /* Register containing index name */ int regSampleno = iMem++; /* Register containing next sample number */ int regCol = iMem++; /* Content of a column analyzed table */ int regRec = iMem++; /* Register holding completed record */ int regTemp = iMem++; /* Temporary use register */ int regRowid = iMem++; /* Rowid for the inserted record */ #ifdef SQLITE_ENABLE_STAT2 int regTemp2 = iMem++; /* Temporary use register */ int regSamplerecno = iMem++; /* Index of next sample to record */ int regRecno = iMem++; /* Current sample index */ int regLast = iMem++; /* Index of last sample to record */ int regFirst = iMem++; /* Index of first sample to record */ #endif v = sqlite3GetVdbe(pParse); if( v==0 || NEVER(pTab==0) ){ return; } if( pTab->tnum==0 ){ /* Do not gather statistics on views or virtual tables */ return; } if( memcmp(pTab->zName, "sqlite_", 7)==0 ){ /* Do not gather statistics on system tables */ return; } assert( sqlite3BtreeHoldsAllMutexes(db) ); iDb = sqlite3SchemaToIndex(db, pTab->pSchema); assert( iDb>=0 ); #ifndef SQLITE_OMIT_AUTHORIZATION if( sqlite3AuthCheck(pParse, SQLITE_ANALYZE, pTab->zName, 0, db->aDb[iDb].zName ) ){ return; } #endif /* Establish a read-lock on the table at the shared-cache level. */ sqlite3TableLock(pParse, iDb, pTab->tnum, 0, pTab->zName); iIdxCur = pParse->nTab++; sqlite3VdbeAddOp4(v, OP_String8, 0, regTabname, 0, pTab->zName, 0); for(pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext){ int nCol = pIdx->nColumn; KeyInfo *pKey = sqlite3IndexKeyinfo(pParse, pIdx); if( iMem+1+(nCol*2)>pParse->nMem ){ pParse->nMem = iMem+1+(nCol*2); } /* Open a cursor to the index to be analyzed. */ assert( iDb==sqlite3SchemaToIndex(db, pIdx->pSchema) ); sqlite3VdbeAddOp4(v, OP_OpenRead, iIdxCur, pIdx->tnum, iDb, (char *)pKey, P4_KEYINFO_HANDOFF); VdbeComment((v, "%s", pIdx->zName)); /* Populate the register containing the index name. */ sqlite3VdbeAddOp4(v, OP_String8, 0, regIdxname, 0, pIdx->zName, 0); #ifdef SQLITE_ENABLE_STAT2 /* If this iteration of the loop is generating code to analyze the ** first index in the pTab->pIndex list, then register regLast has ** not been populated. In this case populate it now. */ |
︙ | ︙ | |||
223 224 225 226 227 228 229 230 | ** the index b-tree. */ endOfLoop = sqlite3VdbeMakeLabel(v); sqlite3VdbeAddOp2(v, OP_Rewind, iIdxCur, endOfLoop); topOfLoop = sqlite3VdbeCurrentAddr(v); sqlite3VdbeAddOp2(v, OP_AddImm, iMem, 1); for(i=0; i<nCol; i++){ sqlite3VdbeAddOp3(v, OP_Column, iIdxCur, i, regCol); | > < > | 229 230 231 232 233 234 235 236 237 238 239 240 241 242 243 244 245 246 | ** the index b-tree. */ endOfLoop = sqlite3VdbeMakeLabel(v); sqlite3VdbeAddOp2(v, OP_Rewind, iIdxCur, endOfLoop); topOfLoop = sqlite3VdbeCurrentAddr(v); sqlite3VdbeAddOp2(v, OP_AddImm, iMem, 1); for(i=0; i<nCol; i++){ CollSeq *pColl; sqlite3VdbeAddOp3(v, OP_Column, iIdxCur, i, regCol); if( i==0 ){ #ifdef SQLITE_ENABLE_STAT2 /* Check if the record that cursor iIdxCur points to contains a ** value that should be stored in the sqlite_stat2 table. If so, ** store it. */ int ne = sqlite3VdbeAddOp3(v, OP_Ne, regRecno, 0, regSamplerecno); assert( regTabname+1==regIdxname && regTabname+2==regSampleno && regTabname+3==regCol |
︙ | ︙ | |||
254 255 256 257 258 259 260 | sqlite3VdbeAddOp2(v, OP_Integer, SQLITE_INDEX_SAMPLES, regTemp2); sqlite3VdbeAddOp3(v, OP_Subtract, regSampleno, regTemp2, regTemp2); sqlite3VdbeAddOp3(v, OP_Divide, regTemp2, regTemp, regTemp); sqlite3VdbeAddOp3(v, OP_Add, regSamplerecno, regTemp, regSamplerecno); sqlite3VdbeJumpHere(v, ne); sqlite3VdbeAddOp2(v, OP_AddImm, regRecno, 1); | < > > > > > > | < > | | > > > > | 261 262 263 264 265 266 267 268 269 270 271 272 273 274 275 276 277 278 279 280 281 282 283 284 285 286 287 288 289 290 291 292 293 294 295 296 297 298 299 300 | sqlite3VdbeAddOp2(v, OP_Integer, SQLITE_INDEX_SAMPLES, regTemp2); sqlite3VdbeAddOp3(v, OP_Subtract, regSampleno, regTemp2, regTemp2); sqlite3VdbeAddOp3(v, OP_Divide, regTemp2, regTemp, regTemp); sqlite3VdbeAddOp3(v, OP_Add, regSamplerecno, regTemp, regSamplerecno); sqlite3VdbeJumpHere(v, ne); sqlite3VdbeAddOp2(v, OP_AddImm, regRecno, 1); #endif /* Always record the very first row */ sqlite3VdbeAddOp1(v, OP_IfNot, iMem+1); } assert( pIdx->azColl!=0 ); assert( pIdx->azColl[i]!=0 ); pColl = sqlite3LocateCollSeq(pParse, pIdx->azColl[i]); sqlite3VdbeAddOp4(v, OP_Ne, regCol, 0, iMem+nCol+i+1, (char*)pColl, P4_COLLSEQ); sqlite3VdbeChangeP5(v, SQLITE_NULLEQ); } if( db->mallocFailed ){ /* If a malloc failure has occurred, then the result of the expression ** passed as the second argument to the call to sqlite3VdbeJumpHere() ** below may be negative. Which causes an assert() to fail (or an ** out-of-bounds write if SQLITE_DEBUG is not defined). */ return; } sqlite3VdbeAddOp2(v, OP_Goto, 0, endOfLoop); for(i=0; i<nCol; i++){ int addr2 = sqlite3VdbeCurrentAddr(v) - (nCol*2); if( i==0 ){ sqlite3VdbeJumpHere(v, addr2-1); /* Set jump dest for the OP_IfNot */ } sqlite3VdbeJumpHere(v, addr2); /* Set jump dest for the OP_Ne */ sqlite3VdbeAddOp2(v, OP_AddImm, iMem+i+1, 1); sqlite3VdbeAddOp3(v, OP_Column, iIdxCur, i, iMem+nCol+i+1); } /* End of the analysis loop. */ sqlite3VdbeResolveLabel(v, endOfLoop); sqlite3VdbeAddOp2(v, OP_Next, iIdxCur, topOfLoop); |
︙ | ︙ | |||
298 299 300 301 302 303 304 | ** ** I = (K+D-1)/D ** ** If K==0 then no entry is made into the sqlite_stat1 table. ** If K>0 then it is always the case the D>0 so division by zero ** is never possible. */ | < > > > > > > > > > > > > > > > > > > > > > > > > > | | 314 315 316 317 318 319 320 321 322 323 324 325 326 327 328 329 330 331 332 333 334 335 336 337 338 339 340 341 342 343 344 345 346 347 348 349 350 351 352 353 354 355 356 357 358 359 360 361 362 363 364 365 366 367 368 369 370 371 372 373 | ** ** I = (K+D-1)/D ** ** If K==0 then no entry is made into the sqlite_stat1 table. ** If K>0 then it is always the case the D>0 so division by zero ** is never possible. */ sqlite3VdbeAddOp2(v, OP_SCopy, iMem, regSampleno); if( jZeroRows==0 ){ jZeroRows = sqlite3VdbeAddOp1(v, OP_IfNot, iMem); } for(i=0; i<nCol; i++){ sqlite3VdbeAddOp4(v, OP_String8, 0, regTemp, 0, " ", 0); sqlite3VdbeAddOp3(v, OP_Concat, regTemp, regSampleno, regSampleno); sqlite3VdbeAddOp3(v, OP_Add, iMem, iMem+i+1, regTemp); sqlite3VdbeAddOp2(v, OP_AddImm, regTemp, -1); sqlite3VdbeAddOp3(v, OP_Divide, iMem+i+1, regTemp, regTemp); sqlite3VdbeAddOp1(v, OP_ToInt, regTemp); sqlite3VdbeAddOp3(v, OP_Concat, regTemp, regSampleno, regSampleno); } sqlite3VdbeAddOp4(v, OP_MakeRecord, regTabname, 3, regRec, "aaa", 0); sqlite3VdbeAddOp2(v, OP_NewRowid, iStatCur, regRowid); sqlite3VdbeAddOp3(v, OP_Insert, iStatCur, regRec, regRowid); sqlite3VdbeChangeP5(v, OPFLAG_APPEND); } /* If the table has no indices, create a single sqlite_stat1 entry ** containing NULL as the index name and the row count as the content. */ if( pTab->pIndex==0 ){ sqlite3VdbeAddOp3(v, OP_OpenRead, iIdxCur, pTab->tnum, iDb); VdbeComment((v, "%s", pTab->zName)); sqlite3VdbeAddOp2(v, OP_Count, iIdxCur, regSampleno); sqlite3VdbeAddOp1(v, OP_Close, iIdxCur); }else{ assert( jZeroRows>0 ); addr = sqlite3VdbeAddOp0(v, OP_Goto); sqlite3VdbeJumpHere(v, jZeroRows); } sqlite3VdbeAddOp2(v, OP_Null, 0, regIdxname); sqlite3VdbeAddOp4(v, OP_MakeRecord, regTabname, 3, regRec, "aaa", 0); sqlite3VdbeAddOp2(v, OP_NewRowid, iStatCur, regRowid); sqlite3VdbeAddOp3(v, OP_Insert, iStatCur, regRec, regRowid); sqlite3VdbeChangeP5(v, OPFLAG_APPEND); if( pParse->nMem<regRec ) pParse->nMem = regRec; if( jZeroRows ){ sqlite3VdbeJumpHere(v, addr); } } /* ** Generate code that will cause the most recent index analysis to ** be loaded into internal hash tables where is can be used. */ static void loadAnalysis(Parse *pParse, int iDb){ Vdbe *v = sqlite3GetVdbe(pParse); if( v ){ sqlite3VdbeAddOp1(v, OP_LoadAnalysis, iDb); } } |
︙ | ︙ | |||
449 450 451 452 453 454 455 | const char *zDatabase; }; /* ** This callback is invoked once for each index when reading the ** sqlite_stat1 table. ** | | > | > > > > | | | | | > > > > > > | | > > | 489 490 491 492 493 494 495 496 497 498 499 500 501 502 503 504 505 506 507 508 509 510 511 512 513 514 515 516 517 518 519 520 521 522 523 524 525 526 527 528 529 530 531 532 533 534 535 536 537 538 539 540 541 542 | const char *zDatabase; }; /* ** This callback is invoked once for each index when reading the ** sqlite_stat1 table. ** ** argv[0] = name of the table ** argv[1] = name of the index (might be NULL) ** argv[2] = results of analysis - on integer for each column ** ** Entries for which argv[1]==NULL simply record the number of rows in ** the table. */ static int analysisLoader(void *pData, int argc, char **argv, char **NotUsed){ analysisInfo *pInfo = (analysisInfo*)pData; Index *pIndex; Table *pTable; int i, c, n; unsigned int v; const char *z; assert( argc==3 ); UNUSED_PARAMETER2(NotUsed, argc); if( argv==0 || argv[0]==0 || argv[2]==0 ){ return 0; } pTable = sqlite3FindTable(pInfo->db, argv[0], pInfo->zDatabase); if( pTable==0 ){ return 0; } if( argv[1] ){ pIndex = sqlite3FindIndex(pInfo->db, argv[1], pInfo->zDatabase); }else{ pIndex = 0; } n = pIndex ? pIndex->nColumn : 0; z = argv[2]; for(i=0; *z && i<=n; i++){ v = 0; while( (c=z[0])>='0' && c<='9' ){ v = v*10 + c - '0'; z++; } if( i==0 ) pTable->nRowEst = v; if( pIndex==0 ) break; pIndex->aiRowEst[i] = v; if( *z==' ' ) z++; } return 0; } /* |
︙ | ︙ | |||
551 552 553 554 555 556 557 | sInfo.zDatabase = db->aDb[iDb].zName; if( sqlite3FindTable(db, "sqlite_stat1", sInfo.zDatabase)==0 ){ return SQLITE_ERROR; } /* Load new statistics out of the sqlite_stat1 table */ zSql = sqlite3MPrintf(db, | | | 604 605 606 607 608 609 610 611 612 613 614 615 616 617 618 | sInfo.zDatabase = db->aDb[iDb].zName; if( sqlite3FindTable(db, "sqlite_stat1", sInfo.zDatabase)==0 ){ return SQLITE_ERROR; } /* Load new statistics out of the sqlite_stat1 table */ zSql = sqlite3MPrintf(db, "SELECT tbl, idx, stat FROM %Q.sqlite_stat1", sInfo.zDatabase); if( zSql==0 ){ rc = SQLITE_NOMEM; }else{ rc = sqlite3_exec(db, zSql, analysisLoader, &sInfo, 0); sqlite3DbFree(db, zSql); } |
︙ | ︙ | |||
579 580 581 582 583 584 585 | }else{ rc = sqlite3_prepare(db, zSql, -1, &pStmt, 0); sqlite3DbFree(db, zSql); } if( rc==SQLITE_OK ){ while( sqlite3_step(pStmt)==SQLITE_ROW ){ | > > > | | | 632 633 634 635 636 637 638 639 640 641 642 643 644 645 646 647 648 649 650 | }else{ rc = sqlite3_prepare(db, zSql, -1, &pStmt, 0); sqlite3DbFree(db, zSql); } if( rc==SQLITE_OK ){ while( sqlite3_step(pStmt)==SQLITE_ROW ){ char *zIndex; /* Index name */ Index *pIdx; /* Pointer to the index object */ zIndex = (char *)sqlite3_column_text(pStmt, 0); pIdx = zIndex ? sqlite3FindIndex(db, zIndex, sInfo.zDatabase) : 0; if( pIdx ){ int iSample = sqlite3_column_int(pStmt, 1); if( iSample<SQLITE_INDEX_SAMPLES && iSample>=0 ){ int eType = sqlite3_column_type(pStmt, 2); if( pIdx->aSample==0 ){ static const int sz = sizeof(IndexSample)*SQLITE_INDEX_SAMPLES; |
︙ | ︙ |
Changes to src/sqliteInt.h.
︙ | ︙ | |||
1220 1221 1222 1223 1224 1225 1226 1227 1228 1229 1230 1231 1232 1233 | struct Table { char *zName; /* Name of the table or view */ int iPKey; /* If not negative, use aCol[iPKey] as the primary key */ int nCol; /* Number of columns in this table */ Column *aCol; /* Information about each column */ Index *pIndex; /* List of SQL indexes on this table. */ int tnum; /* Root BTree node for this table (see note above) */ Select *pSelect; /* NULL for tables. Points to definition if a view. */ u16 nRef; /* Number of pointers to this Table */ u8 tabFlags; /* Mask of TF_* values */ u8 keyConf; /* What to do in case of uniqueness conflict on iPKey */ FKey *pFKey; /* Linked list of all foreign keys in this table */ char *zColAff; /* String defining the affinity of each column */ #ifndef SQLITE_OMIT_CHECK | > | 1220 1221 1222 1223 1224 1225 1226 1227 1228 1229 1230 1231 1232 1233 1234 | struct Table { char *zName; /* Name of the table or view */ int iPKey; /* If not negative, use aCol[iPKey] as the primary key */ int nCol; /* Number of columns in this table */ Column *aCol; /* Information about each column */ Index *pIndex; /* List of SQL indexes on this table. */ int tnum; /* Root BTree node for this table (see note above) */ unsigned nRowEst; /* Estimated rows in table - from sqlite_stat1 table */ Select *pSelect; /* NULL for tables. Points to definition if a view. */ u16 nRef; /* Number of pointers to this Table */ u8 tabFlags; /* Mask of TF_* values */ u8 keyConf; /* What to do in case of uniqueness conflict on iPKey */ FKey *pFKey; /* Linked list of all foreign keys in this table */ char *zColAff; /* String defining the affinity of each column */ #ifndef SQLITE_OMIT_CHECK |
︙ | ︙ | |||
1788 1789 1790 1791 1792 1793 1794 1795 1796 1797 1798 1799 1800 1801 | char *zName; /* Name of the table */ char *zAlias; /* The "B" part of a "A AS B" phrase. zName is the "A" */ Table *pTab; /* An SQL table corresponding to zName */ Select *pSelect; /* A SELECT statement used in place of a table name */ u8 isPopulated; /* Temporary table associated with SELECT is populated */ u8 jointype; /* Type of join between this able and the previous */ u8 notIndexed; /* True if there is a NOT INDEXED clause */ int iCursor; /* The VDBE cursor number used to access this table */ Expr *pOn; /* The ON clause of a join */ IdList *pUsing; /* The USING clause of a join */ Bitmask colUsed; /* Bit N (1<<N) set if column N of pTab is used */ char *zIndex; /* Identifier from "INDEXED BY <zIndex>" clause */ Index *pIndex; /* Index structure corresponding to zIndex, if any */ } a[1]; /* One entry for each identifier on the list */ | > > > | 1789 1790 1791 1792 1793 1794 1795 1796 1797 1798 1799 1800 1801 1802 1803 1804 1805 | char *zName; /* Name of the table */ char *zAlias; /* The "B" part of a "A AS B" phrase. zName is the "A" */ Table *pTab; /* An SQL table corresponding to zName */ Select *pSelect; /* A SELECT statement used in place of a table name */ u8 isPopulated; /* Temporary table associated with SELECT is populated */ u8 jointype; /* Type of join between this able and the previous */ u8 notIndexed; /* True if there is a NOT INDEXED clause */ #ifndef SQLITE_OMIT_EXPLAIN u8 iSelectId; /* If pSelect!=0, the id of the sub-select in EQP */ #endif int iCursor; /* The VDBE cursor number used to access this table */ Expr *pOn; /* The ON clause of a join */ IdList *pUsing; /* The USING clause of a join */ Bitmask colUsed; /* Bit N (1<<N) set if column N of pTab is used */ char *zIndex; /* Identifier from "INDEXED BY <zIndex>" clause */ Index *pIndex; /* Index structure corresponding to zIndex, if any */ } a[1]; /* One entry for each identifier on the list */ |
︙ | ︙ | |||
1826 1827 1828 1829 1830 1831 1832 1833 1834 1835 1836 1837 1838 1839 | ** pTerm is only used when wsFlags&WHERE_MULTI_OR is true. And pVtabIdx ** is only used when wsFlags&WHERE_VIRTUALTABLE is true. It is never the ** case that more than one of these conditions is true. */ struct WherePlan { u32 wsFlags; /* WHERE_* flags that describe the strategy */ u32 nEq; /* Number of == constraints */ union { Index *pIdx; /* Index when WHERE_INDEXED is true */ struct WhereTerm *pTerm; /* WHERE clause term for OR-search */ sqlite3_index_info *pVtabIdx; /* Virtual table index to use */ } u; }; | > | 1830 1831 1832 1833 1834 1835 1836 1837 1838 1839 1840 1841 1842 1843 1844 | ** pTerm is only used when wsFlags&WHERE_MULTI_OR is true. And pVtabIdx ** is only used when wsFlags&WHERE_VIRTUALTABLE is true. It is never the ** case that more than one of these conditions is true. */ struct WherePlan { u32 wsFlags; /* WHERE_* flags that describe the strategy */ u32 nEq; /* Number of == constraints */ double nRow; /* Estimated number of rows (for EQP) */ union { Index *pIdx; /* Index when WHERE_INDEXED is true */ struct WhereTerm *pTerm; /* WHERE clause term for OR-search */ sqlite3_index_info *pVtabIdx; /* Virtual table index to use */ } u; }; |
︙ | ︙ | |||
1910 1911 1912 1913 1914 1915 1916 1917 1918 1919 1920 1921 1922 1923 | SrcList *pTabList; /* List of tables in the join */ int iTop; /* The very beginning of the WHERE loop */ int iContinue; /* Jump here to continue with next record */ int iBreak; /* Jump here to break out of the loop */ int nLevel; /* Number of nested loop */ struct WhereClause *pWC; /* Decomposition of the WHERE clause */ double savedNQueryLoop; /* pParse->nQueryLoop outside the WHERE loop */ WhereLevel a[1]; /* Information about each nest loop in WHERE */ }; /* ** A NameContext defines a context in which to resolve table and column ** names. The context consists of a list of tables (the pSrcList) field and ** a list of named expression (pEList). The named expression list may | > | 1915 1916 1917 1918 1919 1920 1921 1922 1923 1924 1925 1926 1927 1928 1929 | SrcList *pTabList; /* List of tables in the join */ int iTop; /* The very beginning of the WHERE loop */ int iContinue; /* Jump here to continue with next record */ int iBreak; /* Jump here to break out of the loop */ int nLevel; /* Number of nested loop */ struct WhereClause *pWC; /* Decomposition of the WHERE clause */ double savedNQueryLoop; /* pParse->nQueryLoop outside the WHERE loop */ double nRowOut; /* Estimated number of output rows */ WhereLevel a[1]; /* Information about each nest loop in WHERE */ }; /* ** A NameContext defines a context in which to resolve table and column ** names. The context consists of a list of tables (the pSrcList) field and ** a list of named expression (pEList). The named expression list may |
︙ | ︙ | |||
2180 2181 2182 2183 2184 2185 2186 2187 2188 2189 2190 2191 2192 2193 | u8 declareVtab; /* True if inside sqlite3_declare_vtab() */ int nVtabLock; /* Number of virtual tables to lock */ Table **apVtabLock; /* Pointer to virtual tables needing locking */ #endif int nHeight; /* Expression tree height of current sub-select */ Table *pZombieTab; /* List of Table objects to delete after code gen */ TriggerPrg *pTriggerPrg; /* Linked list of coded triggers */ }; #ifdef SQLITE_OMIT_VIRTUALTABLE #define IN_DECLARE_VTAB 0 #else #define IN_DECLARE_VTAB (pParse->declareVtab) #endif | > > > > | 2186 2187 2188 2189 2190 2191 2192 2193 2194 2195 2196 2197 2198 2199 2200 2201 2202 2203 | u8 declareVtab; /* True if inside sqlite3_declare_vtab() */ int nVtabLock; /* Number of virtual tables to lock */ Table **apVtabLock; /* Pointer to virtual tables needing locking */ #endif int nHeight; /* Expression tree height of current sub-select */ Table *pZombieTab; /* List of Table objects to delete after code gen */ TriggerPrg *pTriggerPrg; /* Linked list of coded triggers */ #ifndef SQLITE_OMIT_EXPLAIN int iSelectId; /* Subquery ID for query planning */ int iNextSelectId; /* Next available subquery ID */ #endif }; #ifdef SQLITE_OMIT_VIRTUALTABLE #define IN_DECLARE_VTAB 0 #else #define IN_DECLARE_VTAB (pParse->declareVtab) #endif |
︙ | ︙ |
Changes to src/where.c.
︙ | ︙ | |||
13 14 15 16 17 18 19 20 21 22 23 24 25 26 | ** the WHERE clause of SQL statements. This module is responsible for ** generating the code that loops through a table looking for applicable ** rows. Indices are selected and used to speed the search when doing ** so is applicable. Because this module is responsible for selecting ** indices, you might also think of this module as the "query optimizer". */ #include "sqliteInt.h" /* ** Trace output macros */ #if defined(SQLITE_TEST) || defined(SQLITE_DEBUG) int sqlite3WhereTrace = 0; #endif | > | 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 | ** the WHERE clause of SQL statements. This module is responsible for ** generating the code that loops through a table looking for applicable ** rows. Indices are selected and used to speed the search when doing ** so is applicable. Because this module is responsible for selecting ** indices, you might also think of this module as the "query optimizer". */ #include "sqliteInt.h" /* ** Trace output macros */ #if defined(SQLITE_TEST) || defined(SQLITE_DEBUG) int sqlite3WhereTrace = 0; #endif |
︙ | ︙ | |||
113 114 115 116 117 118 119 120 121 122 123 124 125 126 | #define TERM_DYNAMIC 0x01 /* Need to call sqlite3ExprDelete(db, pExpr) */ #define TERM_VIRTUAL 0x02 /* Added by the optimizer. Do not code */ #define TERM_CODED 0x04 /* This term is already coded */ #define TERM_COPIED 0x08 /* Has a child */ #define TERM_ORINFO 0x10 /* Need to free the WhereTerm.u.pOrInfo object */ #define TERM_ANDINFO 0x20 /* Need to free the WhereTerm.u.pAndInfo obj */ #define TERM_OR_OK 0x40 /* Used during OR-clause processing */ /* ** An instance of the following structure holds all information about a ** WHERE clause. Mostly this is a container for one or more WhereTerms. */ struct WhereClause { Parse *pParse; /* The parser context */ | > > > > > | 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 | #define TERM_DYNAMIC 0x01 /* Need to call sqlite3ExprDelete(db, pExpr) */ #define TERM_VIRTUAL 0x02 /* Added by the optimizer. Do not code */ #define TERM_CODED 0x04 /* This term is already coded */ #define TERM_COPIED 0x08 /* Has a child */ #define TERM_ORINFO 0x10 /* Need to free the WhereTerm.u.pOrInfo object */ #define TERM_ANDINFO 0x20 /* Need to free the WhereTerm.u.pAndInfo obj */ #define TERM_OR_OK 0x40 /* Used during OR-clause processing */ #ifdef SQLITE_ENABLE_STAT2 # define TERM_VNULL 0x80 /* Manufactured x>NULL or x<=NULL term */ #else # define TERM_VNULL 0x00 /* Disabled if not using stat2 */ #endif /* ** An instance of the following structure holds all information about a ** WHERE clause. Mostly this is a container for one or more WhereTerms. */ struct WhereClause { Parse *pParse; /* The parser context */ |
︙ | ︙ | |||
188 189 190 191 192 193 194 | /* ** A WhereCost object records a lookup strategy and the estimated ** cost of pursuing that strategy. */ struct WhereCost { WherePlan plan; /* The lookup strategy */ double rCost; /* Overall cost of pursuing this search strategy */ | < > | 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 | /* ** A WhereCost object records a lookup strategy and the estimated ** cost of pursuing that strategy. */ struct WhereCost { WherePlan plan; /* The lookup strategy */ double rCost; /* Overall cost of pursuing this search strategy */ Bitmask used; /* Bitmask of cursors used by this plan */ }; /* ** Bitmasks for the operators that indices are able to exploit. An ** OR-ed combination of these values can be used when searching for ** terms in the where clause. */ #define WO_IN 0x001 #define WO_EQ 0x002 #define WO_LT (WO_EQ<<(TK_LT-TK_EQ)) #define WO_LE (WO_EQ<<(TK_LE-TK_EQ)) #define WO_GT (WO_EQ<<(TK_GT-TK_EQ)) #define WO_GE (WO_EQ<<(TK_GE-TK_EQ)) #define WO_MATCH 0x040 #define WO_ISNULL 0x080 #define WO_OR 0x100 /* Two or more OR-connected terms */ #define WO_AND 0x200 /* Two or more AND-connected terms */ #define WO_NOOP 0x800 /* This term does not restrict search space */ #define WO_ALL 0xfff /* Mask of all possible WO_* values */ #define WO_SINGLE 0x0ff /* Mask of all non-compound WO_* values */ /* ** Value for wsFlags returned by bestIndex() and stored in ** WhereLevel.wsFlags. These flags determine which search |
︙ | ︙ | |||
231 232 233 234 235 236 237 | #define WHERE_ROWID_EQ 0x00001000 /* rowid=EXPR or rowid IN (...) */ #define WHERE_ROWID_RANGE 0x00002000 /* rowid<EXPR and/or rowid>EXPR */ #define WHERE_COLUMN_EQ 0x00010000 /* x=EXPR or x IN (...) or x IS NULL */ #define WHERE_COLUMN_RANGE 0x00020000 /* x<EXPR and/or x>EXPR */ #define WHERE_COLUMN_IN 0x00040000 /* x IN (...) */ #define WHERE_COLUMN_NULL 0x00080000 /* x IS NULL */ #define WHERE_INDEXED 0x000f0000 /* Anything that uses an index */ | | > | 237 238 239 240 241 242 243 244 245 246 247 248 249 250 251 252 253 254 255 | #define WHERE_ROWID_EQ 0x00001000 /* rowid=EXPR or rowid IN (...) */ #define WHERE_ROWID_RANGE 0x00002000 /* rowid<EXPR and/or rowid>EXPR */ #define WHERE_COLUMN_EQ 0x00010000 /* x=EXPR or x IN (...) or x IS NULL */ #define WHERE_COLUMN_RANGE 0x00020000 /* x<EXPR and/or x>EXPR */ #define WHERE_COLUMN_IN 0x00040000 /* x IN (...) */ #define WHERE_COLUMN_NULL 0x00080000 /* x IS NULL */ #define WHERE_INDEXED 0x000f0000 /* Anything that uses an index */ #define WHERE_NOT_FULLSCAN 0x100f3000 /* Does not do a full table scan */ #define WHERE_IN_ABLE 0x000f1000 /* Able to support an IN operator */ #define WHERE_TOP_LIMIT 0x00100000 /* x<EXPR or x<=EXPR constraint */ #define WHERE_BTM_LIMIT 0x00200000 /* x>EXPR or x>=EXPR constraint */ #define WHERE_BOTH_LIMIT 0x00300000 /* Both x>EXPR and x<EXPR */ #define WHERE_IDX_ONLY 0x00800000 /* Use index only - omit table */ #define WHERE_ORDERBY 0x01000000 /* Output will appear in correct order */ #define WHERE_REVERSE 0x02000000 /* Scan in reverse order */ #define WHERE_UNIQUE 0x04000000 /* Selects no more than one row */ #define WHERE_VIRTUALTABLE 0x08000000 /* Use virtual-table processing */ #define WHERE_MULTI_OR 0x10000000 /* OR using multiple indices */ #define WHERE_TEMP_INDEX 0x20000000 /* Uses an ephemeral index */ |
︙ | ︙ | |||
665 666 667 668 669 670 671 | pRight = pList->a[0].pExpr; op = pRight->op; if( op==TK_REGISTER ){ op = pRight->op2; } if( op==TK_VARIABLE ){ Vdbe *pReprepare = pParse->pReprepare; | > | | | | 672 673 674 675 676 677 678 679 680 681 682 683 684 685 686 687 688 689 690 691 692 693 694 695 696 697 698 699 700 701 702 703 704 705 706 707 708 709 | pRight = pList->a[0].pExpr; op = pRight->op; if( op==TK_REGISTER ){ op = pRight->op2; } if( op==TK_VARIABLE ){ Vdbe *pReprepare = pParse->pReprepare; int iCol = pRight->iColumn; pVal = sqlite3VdbeGetValue(pReprepare, iCol, SQLITE_AFF_NONE); if( pVal && sqlite3_value_type(pVal)==SQLITE_TEXT ){ z = (char *)sqlite3_value_text(pVal); } sqlite3VdbeSetVarmask(pParse->pVdbe, iCol); /* IMP: R-23257-02778 */ assert( pRight->op==TK_VARIABLE || pRight->op==TK_REGISTER ); }else if( op==TK_STRING ){ z = pRight->u.zToken; } if( z ){ cnt = 0; while( (c=z[cnt])!=0 && c!=wc[0] && c!=wc[1] && c!=wc[2] ){ cnt++; } if( cnt!=0 && 255!=(u8)z[cnt-1] ){ Expr *pPrefix; *pisComplete = c==wc[0] && z[cnt+1]==0; pPrefix = sqlite3Expr(db, TK_STRING, z); if( pPrefix ) pPrefix->u.zToken[cnt] = 0; *ppPrefix = pPrefix; if( op==TK_VARIABLE ){ Vdbe *v = pParse->pVdbe; sqlite3VdbeSetVarmask(v, pRight->iColumn); /* IMP: R-23257-02778 */ if( *pisComplete && pRight->u.zToken[1] ){ /* If the rhs of the LIKE expression is a variable, and the current ** value of the variable means there is no need to invoke the LIKE ** function, then no OP_Variable will be added to the program. ** This causes problems for the sqlite3_bind_parameter_name() ** API. To workaround them, add a dummy OP_Variable here. */ |
︙ | ︙ | |||
1055 1056 1057 1058 1059 1060 1061 | exprAnalyze(pSrc, pWC, idxNew); pTerm = &pWC->a[idxTerm]; pWC->a[idxNew].iParent = idxTerm; pTerm->nChild = 1; }else{ sqlite3ExprListDelete(db, pList); } | | | 1063 1064 1065 1066 1067 1068 1069 1070 1071 1072 1073 1074 1075 1076 1077 | exprAnalyze(pSrc, pWC, idxNew); pTerm = &pWC->a[idxTerm]; pWC->a[idxNew].iParent = idxTerm; pTerm->nChild = 1; }else{ sqlite3ExprListDelete(db, pList); } pTerm->eOperator = WO_NOOP; /* case 1 trumps case 2 */ } } } #endif /* !SQLITE_OMIT_OR_OPTIMIZATION && !SQLITE_OMIT_SUBQUERY */ /* |
︙ | ︙ | |||
1319 1320 1321 1322 1323 1324 1325 1326 1327 1328 1329 1330 1331 1332 | pTerm->nChild = 1; pTerm->wtFlags |= TERM_COPIED; pNewTerm->prereqAll = pTerm->prereqAll; } } #endif /* SQLITE_OMIT_VIRTUALTABLE */ /* Prevent ON clause terms of a LEFT JOIN from being used to drive ** an index for tables to the left of the join. */ pTerm->prereqRight |= extraRight; } /* | > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > | 1327 1328 1329 1330 1331 1332 1333 1334 1335 1336 1337 1338 1339 1340 1341 1342 1343 1344 1345 1346 1347 1348 1349 1350 1351 1352 1353 1354 1355 1356 1357 1358 1359 1360 1361 1362 1363 1364 1365 1366 1367 1368 1369 1370 1371 1372 1373 1374 1375 1376 1377 1378 | pTerm->nChild = 1; pTerm->wtFlags |= TERM_COPIED; pNewTerm->prereqAll = pTerm->prereqAll; } } #endif /* SQLITE_OMIT_VIRTUALTABLE */ #ifdef SQLITE_ENABLE_STAT2 /* When sqlite_stat2 histogram data is available an operator of the ** form "x IS NOT NULL" can sometimes be evaluated more efficiently ** as "x>NULL" if x is not an INTEGER PRIMARY KEY. So construct a ** virtual term of that form. ** ** Note that the virtual term must be tagged with TERM_VNULL. This ** TERM_VNULL tag will suppress the not-null check at the beginning ** of the loop. Without the TERM_VNULL flag, the not-null check at ** the start of the loop will prevent any results from being returned. */ if( pExpr->op==TK_NOTNULL && pExpr->pLeft->iColumn>=0 ){ Expr *pNewExpr; Expr *pLeft = pExpr->pLeft; int idxNew; WhereTerm *pNewTerm; pNewExpr = sqlite3PExpr(pParse, TK_GT, sqlite3ExprDup(db, pLeft, 0), sqlite3PExpr(pParse, TK_NULL, 0, 0, 0), 0); idxNew = whereClauseInsert(pWC, pNewExpr, TERM_VIRTUAL|TERM_DYNAMIC|TERM_VNULL); if( idxNew ){ pNewTerm = &pWC->a[idxNew]; pNewTerm->prereqRight = 0; pNewTerm->leftCursor = pLeft->iTable; pNewTerm->u.leftColumn = pLeft->iColumn; pNewTerm->eOperator = WO_GT; pNewTerm->iParent = idxTerm; pTerm = &pWC->a[idxTerm]; pTerm->nChild = 1; pTerm->wtFlags |= TERM_COPIED; pNewTerm->prereqAll = pTerm->prereqAll; } } #endif /* SQLITE_ENABLE_STAT2 */ /* Prevent ON clause terms of a LEFT JOIN from being used to drive ** an index for tables to the left of the join. */ pTerm->prereqRight |= extraRight; } /* |
︙ | ︙ | |||
1371 1372 1373 1374 1375 1376 1377 1378 1379 1380 1381 1382 1383 1384 | static int isSortingIndex( Parse *pParse, /* Parsing context */ WhereMaskSet *pMaskSet, /* Mapping from table cursor numbers to bitmaps */ Index *pIdx, /* The index we are testing */ int base, /* Cursor number for the table to be sorted */ ExprList *pOrderBy, /* The ORDER BY clause */ int nEqCol, /* Number of index columns with == constraints */ int *pbRev /* Set to 1 if ORDER BY is DESC */ ){ int i, j; /* Loop counters */ int sortOrder = 0; /* XOR of index and ORDER BY sort direction */ int nTerm; /* Number of ORDER BY terms */ struct ExprList_item *pTerm; /* A term of the ORDER BY clause */ sqlite3 *db = pParse->db; | > | 1417 1418 1419 1420 1421 1422 1423 1424 1425 1426 1427 1428 1429 1430 1431 | static int isSortingIndex( Parse *pParse, /* Parsing context */ WhereMaskSet *pMaskSet, /* Mapping from table cursor numbers to bitmaps */ Index *pIdx, /* The index we are testing */ int base, /* Cursor number for the table to be sorted */ ExprList *pOrderBy, /* The ORDER BY clause */ int nEqCol, /* Number of index columns with == constraints */ int wsFlags, /* Index usages flags */ int *pbRev /* Set to 1 if ORDER BY is DESC */ ){ int i, j; /* Loop counters */ int sortOrder = 0; /* XOR of index and ORDER BY sort direction */ int nTerm; /* Number of ORDER BY terms */ struct ExprList_item *pTerm; /* A term of the ORDER BY clause */ sqlite3 *db = pParse->db; |
︙ | ︙ | |||
1476 1477 1478 1479 1480 1481 1482 1483 1484 1485 1486 | *pbRev = sortOrder!=0; if( j>=nTerm ){ /* All terms of the ORDER BY clause are covered by this index so ** this index can be used for sorting. */ return 1; } if( pIdx->onError!=OE_None && i==pIdx->nColumn && !referencesOtherTables(pOrderBy, pMaskSet, j, base) ){ /* All terms of this index match some prefix of the ORDER BY clause ** and the index is UNIQUE and no terms on the tail of the ORDER BY ** clause reference other tables in a join. If this is all true then | > | > > | 1523 1524 1525 1526 1527 1528 1529 1530 1531 1532 1533 1534 1535 1536 1537 1538 1539 1540 1541 1542 1543 1544 | *pbRev = sortOrder!=0; if( j>=nTerm ){ /* All terms of the ORDER BY clause are covered by this index so ** this index can be used for sorting. */ return 1; } if( pIdx->onError!=OE_None && i==pIdx->nColumn && (wsFlags & WHERE_COLUMN_NULL)==0 && !referencesOtherTables(pOrderBy, pMaskSet, j, base) ){ /* All terms of this index match some prefix of the ORDER BY clause ** and the index is UNIQUE and no terms on the tail of the ORDER BY ** clause reference other tables in a join. If this is all true then ** the order by clause is superfluous. Not that if the matching ** condition is IS NULL then the result is not necessarily unique ** even on a UNIQUE index, so disallow those cases. */ return 1; } return 0; } /* ** Prepare a crude estimate of the logarithm of the input value. |
︙ | ︙ | |||
1551 1552 1553 1554 1555 1556 1557 | #define TRACE_IDX_OUTPUTS(A) #endif /* ** Required because bestIndex() is called by bestOrClauseIndex() */ static void bestIndex( | | > | > | > | | 1601 1602 1603 1604 1605 1606 1607 1608 1609 1610 1611 1612 1613 1614 1615 1616 1617 1618 1619 1620 1621 1622 1623 1624 1625 1626 1627 1628 1629 1630 1631 1632 1633 1634 1635 1636 1637 1638 1639 1640 1641 1642 | #define TRACE_IDX_OUTPUTS(A) #endif /* ** Required because bestIndex() is called by bestOrClauseIndex() */ static void bestIndex( Parse*, WhereClause*, struct SrcList_item*, Bitmask, Bitmask, ExprList*, WhereCost*); /* ** This routine attempts to find an scanning strategy that can be used ** to optimize an 'OR' expression that is part of a WHERE clause. ** ** The table associated with FROM clause term pSrc may be either a ** regular B-Tree table or a virtual table. */ static void bestOrClauseIndex( Parse *pParse, /* The parsing context */ WhereClause *pWC, /* The WHERE clause */ struct SrcList_item *pSrc, /* The FROM clause term to search */ Bitmask notReady, /* Mask of cursors not available for indexing */ Bitmask notValid, /* Cursors not available for any purpose */ ExprList *pOrderBy, /* The ORDER BY clause */ WhereCost *pCost /* Lowest cost query plan */ ){ #ifndef SQLITE_OMIT_OR_OPTIMIZATION const int iCur = pSrc->iCursor; /* The cursor of the table to be accessed */ const Bitmask maskSrc = getMask(pWC->pMaskSet, iCur); /* Bitmask for pSrc */ WhereTerm * const pWCEnd = &pWC->a[pWC->nTerm]; /* End of pWC->a[] */ WhereTerm *pTerm; /* A single term of the WHERE clause */ /* No OR-clause optimization allowed if the INDEXED BY or NOT INDEXED clauses ** are used */ if( pSrc->notIndexed || pSrc->pIndex!=0 ){ return; } /* Search the WHERE clause terms for a usable WO_OR term. */ for(pTerm=pWC->a; pTerm<pWCEnd; pTerm++){ if( pTerm->eOperator==WO_OR && ((pTerm->prereqAll & ~maskSrc) & notReady)==0 |
︙ | ︙ | |||
1600 1601 1602 1603 1604 1605 1606 | for(pOrTerm=pOrWC->a; pOrTerm<pOrWCEnd; pOrTerm++){ WhereCost sTermCost; WHERETRACE(("... Multi-index OR testing for term %d of %d....\n", (pOrTerm - pOrWC->a), (pTerm - pWC->a) )); if( pOrTerm->eOperator==WO_AND ){ WhereClause *pAndWC = &pOrTerm->u.pAndInfo->wc; | | | | < > | 1653 1654 1655 1656 1657 1658 1659 1660 1661 1662 1663 1664 1665 1666 1667 1668 1669 1670 1671 1672 1673 1674 1675 1676 1677 1678 1679 1680 1681 1682 1683 1684 1685 1686 1687 1688 1689 1690 1691 1692 1693 1694 1695 1696 1697 1698 1699 1700 | for(pOrTerm=pOrWC->a; pOrTerm<pOrWCEnd; pOrTerm++){ WhereCost sTermCost; WHERETRACE(("... Multi-index OR testing for term %d of %d....\n", (pOrTerm - pOrWC->a), (pTerm - pWC->a) )); if( pOrTerm->eOperator==WO_AND ){ WhereClause *pAndWC = &pOrTerm->u.pAndInfo->wc; bestIndex(pParse, pAndWC, pSrc, notReady, notValid, 0, &sTermCost); }else if( pOrTerm->leftCursor==iCur ){ WhereClause tempWC; tempWC.pParse = pWC->pParse; tempWC.pMaskSet = pWC->pMaskSet; tempWC.op = TK_AND; tempWC.a = pOrTerm; tempWC.nTerm = 1; bestIndex(pParse, &tempWC, pSrc, notReady, notValid, 0, &sTermCost); }else{ continue; } rTotal += sTermCost.rCost; nRow += sTermCost.plan.nRow; used |= sTermCost.used; if( rTotal>=pCost->rCost ) break; } /* If there is an ORDER BY clause, increase the scan cost to account ** for the cost of the sort. */ if( pOrderBy!=0 ){ WHERETRACE(("... sorting increases OR cost %.9g to %.9g\n", rTotal, rTotal+nRow*estLog(nRow))); rTotal += nRow*estLog(nRow); } /* If the cost of scanning using this OR term for optimization is ** less than the current cost stored in pCost, replace the contents ** of pCost. */ WHERETRACE(("... multi-index OR cost=%.9g nrow=%.9g\n", rTotal, nRow)); if( rTotal<pCost->rCost ){ pCost->rCost = rTotal; pCost->used = used; pCost->plan.nRow = nRow; pCost->plan.wsFlags = flags; pCost->plan.u.pTerm = pTerm; } } } #endif /* SQLITE_OMIT_OR_OPTIMIZATION */ } |
︙ | ︙ | |||
1701 1702 1703 1704 1705 1706 1707 | if( pSrc->notIndexed ){ /* The NOT INDEXED clause appears in the SQL. */ return; } assert( pParse->nQueryLoop >= (double)1 ); pTable = pSrc->pTab; | | | | | 1754 1755 1756 1757 1758 1759 1760 1761 1762 1763 1764 1765 1766 1767 1768 1769 1770 1771 1772 1773 1774 1775 1776 1777 1778 1779 1780 1781 1782 1783 1784 | if( pSrc->notIndexed ){ /* The NOT INDEXED clause appears in the SQL. */ return; } assert( pParse->nQueryLoop >= (double)1 ); pTable = pSrc->pTab; nTableRow = pTable->nRowEst; logN = estLog(nTableRow); costTempIdx = 2*logN*(nTableRow/pParse->nQueryLoop + 1); if( costTempIdx>=pCost->rCost ){ /* The cost of creating the transient table would be greater than ** doing the full table scan */ return; } /* Search for any equality comparison term */ pWCEnd = &pWC->a[pWC->nTerm]; for(pTerm=pWC->a; pTerm<pWCEnd; pTerm++){ if( termCanDriveIndex(pTerm, pSrc, notReady) ){ WHERETRACE(("auto-index reduces cost from %.1f to %.1f\n", pCost->rCost, costTempIdx)); pCost->rCost = costTempIdx; pCost->plan.nRow = logN + 1; pCost->plan.wsFlags = WHERE_TEMP_INDEX; pCost->used = pTerm->prereqRight; break; } } } #else |
︙ | ︙ | |||
1835 1836 1837 1838 1839 1840 1841 | int iCol = pTerm->u.leftColumn; Bitmask cMask = iCol>=BMS ? ((Bitmask)1)<<(BMS-1) : ((Bitmask)1)<<iCol; if( (idxCols & cMask)==0 ){ Expr *pX = pTerm->pExpr; idxCols |= cMask; pIdx->aiColumn[n] = pTerm->u.leftColumn; pColl = sqlite3BinaryCompareCollSeq(pParse, pX->pLeft, pX->pRight); | > | | 1888 1889 1890 1891 1892 1893 1894 1895 1896 1897 1898 1899 1900 1901 1902 1903 | int iCol = pTerm->u.leftColumn; Bitmask cMask = iCol>=BMS ? ((Bitmask)1)<<(BMS-1) : ((Bitmask)1)<<iCol; if( (idxCols & cMask)==0 ){ Expr *pX = pTerm->pExpr; idxCols |= cMask; pIdx->aiColumn[n] = pTerm->u.leftColumn; pColl = sqlite3BinaryCompareCollSeq(pParse, pX->pLeft, pX->pRight); assert( pColl!=0 || pParse->nErr>0 ); pIdx->azColl[n] = pColl ? pColl->zName : "BINARY"; n++; } } } assert( (u32)n==pLevel->plan.nEq ); /* Add additional columns needed to make the automatic index into |
︙ | ︙ | |||
2055 2056 2057 2058 2059 2060 2061 | ** routine takes care of freeing the sqlite3_index_info structure after ** everybody has finished with it. */ static void bestVirtualIndex( Parse *pParse, /* The parsing context */ WhereClause *pWC, /* The WHERE clause */ struct SrcList_item *pSrc, /* The FROM clause term to search */ | | > | 2109 2110 2111 2112 2113 2114 2115 2116 2117 2118 2119 2120 2121 2122 2123 2124 | ** routine takes care of freeing the sqlite3_index_info structure after ** everybody has finished with it. */ static void bestVirtualIndex( Parse *pParse, /* The parsing context */ WhereClause *pWC, /* The WHERE clause */ struct SrcList_item *pSrc, /* The FROM clause term to search */ Bitmask notReady, /* Mask of cursors not available for index */ Bitmask notValid, /* Cursors not valid for any purpose */ ExprList *pOrderBy, /* The order by clause */ WhereCost *pCost, /* Lowest cost query plan */ sqlite3_index_info **ppIdxInfo /* Index information passed to xBestIndex */ ){ Table *pTab = pSrc->pTab; sqlite3_index_info *pIdxInfo; struct sqlite3_index_constraint *pIdxCons; |
︙ | ︙ | |||
2185 2186 2187 2188 2189 2190 2191 | } pCost->plan.nEq = 0; pIdxInfo->nOrderBy = nOrderBy; /* Try to find a more efficient access pattern by using multiple indexes ** to optimize an OR expression within the WHERE clause. */ | | | | | > > | > | > > > > > > | > > > > > > > > > > | 2240 2241 2242 2243 2244 2245 2246 2247 2248 2249 2250 2251 2252 2253 2254 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 2286 2287 2288 2289 2290 2291 2292 2293 2294 2295 2296 2297 2298 2299 2300 2301 2302 2303 2304 2305 2306 2307 2308 | } pCost->plan.nEq = 0; pIdxInfo->nOrderBy = nOrderBy; /* Try to find a more efficient access pattern by using multiple indexes ** to optimize an OR expression within the WHERE clause. */ bestOrClauseIndex(pParse, pWC, pSrc, notReady, notValid, pOrderBy, pCost); } #endif /* SQLITE_OMIT_VIRTUALTABLE */ /* ** Argument pIdx is a pointer to an index structure that has an array of ** SQLITE_INDEX_SAMPLES evenly spaced samples of the first indexed column ** stored in Index.aSample. These samples divide the domain of values stored ** the index into (SQLITE_INDEX_SAMPLES+1) regions. ** Region 0 contains all values less than the first sample value. Region ** 1 contains values between the first and second samples. Region 2 contains ** values between samples 2 and 3. And so on. Region SQLITE_INDEX_SAMPLES ** contains values larger than the last sample. ** ** If the index contains many duplicates of a single value, then it is ** possible that two or more adjacent samples can hold the same value. ** When that is the case, the smallest possible region code is returned ** when roundUp is false and the largest possible region code is returned ** when roundUp is true. ** ** If successful, this function determines which of the regions value ** pVal lies in, sets *piRegion to the region index (a value between 0 ** and SQLITE_INDEX_SAMPLES+1, inclusive) and returns SQLITE_OK. ** Or, if an OOM occurs while converting text values between encodings, ** SQLITE_NOMEM is returned and *piRegion is undefined. */ #ifdef SQLITE_ENABLE_STAT2 static int whereRangeRegion( Parse *pParse, /* Database connection */ Index *pIdx, /* Index to consider domain of */ sqlite3_value *pVal, /* Value to consider */ int roundUp, /* Return largest valid region if true */ int *piRegion /* OUT: Region of domain in which value lies */ ){ assert( roundUp==0 || roundUp==1 ); if( ALWAYS(pVal) ){ IndexSample *aSample = pIdx->aSample; int i = 0; int eType = sqlite3_value_type(pVal); if( eType==SQLITE_INTEGER || eType==SQLITE_FLOAT ){ double r = sqlite3_value_double(pVal); for(i=0; i<SQLITE_INDEX_SAMPLES; i++){ if( aSample[i].eType==SQLITE_NULL ) continue; if( aSample[i].eType>=SQLITE_TEXT ) break; if( roundUp ){ if( aSample[i].u.r>r ) break; }else{ if( aSample[i].u.r>=r ) break; } } }else if( eType==SQLITE_NULL ){ i = 0; if( roundUp ){ while( i<SQLITE_INDEX_SAMPLES && aSample[i].eType==SQLITE_NULL ) i++; } }else{ sqlite3 *db = pParse->db; CollSeq *pColl; const u8 *z; int n; |
︙ | ︙ | |||
2251 2252 2253 2254 2255 2256 2257 | return SQLITE_NOMEM; } assert( z && pColl && pColl->xCmp ); } n = sqlite3ValueBytes(pVal, pColl->enc); for(i=0; i<SQLITE_INDEX_SAMPLES; i++){ | | | | | | 2325 2326 2327 2328 2329 2330 2331 2332 2333 2334 2335 2336 2337 2338 2339 2340 2341 2342 2343 2344 2345 2346 2347 2348 2349 2350 2351 2352 2353 2354 2355 2356 2357 2358 2359 2360 | return SQLITE_NOMEM; } assert( z && pColl && pColl->xCmp ); } n = sqlite3ValueBytes(pVal, pColl->enc); for(i=0; i<SQLITE_INDEX_SAMPLES; i++){ int c; int eSampletype = aSample[i].eType; if( eSampletype==SQLITE_NULL || eSampletype<eType ) continue; if( (eSampletype!=eType) ) break; #ifndef SQLITE_OMIT_UTF16 if( pColl->enc!=SQLITE_UTF8 ){ int nSample; char *zSample = sqlite3Utf8to16( db, pColl->enc, aSample[i].u.z, aSample[i].nByte, &nSample ); if( !zSample ){ assert( db->mallocFailed ); return SQLITE_NOMEM; } c = pColl->xCmp(pColl->pUser, nSample, zSample, n, z); sqlite3DbFree(db, zSample); }else #endif { c = pColl->xCmp(pColl->pUser, aSample[i].nByte, aSample[i].u.z, n, z); } if( c-roundUp>=0 ) break; } } assert( i>=0 && i<=SQLITE_INDEX_SAMPLES ); *piRegion = i; } return SQLITE_OK; |
︙ | ︙ | |||
2306 2307 2308 2309 2310 2311 2312 | #ifdef SQLITE_ENABLE_STAT2 static int valueFromExpr( Parse *pParse, Expr *pExpr, u8 aff, sqlite3_value **pp ){ | < < | | > | | 2380 2381 2382 2383 2384 2385 2386 2387 2388 2389 2390 2391 2392 2393 2394 2395 2396 2397 2398 | #ifdef SQLITE_ENABLE_STAT2 static int valueFromExpr( Parse *pParse, Expr *pExpr, u8 aff, sqlite3_value **pp ){ if( pExpr->op==TK_VARIABLE || (pExpr->op==TK_REGISTER && pExpr->op2==TK_VARIABLE) ){ int iVar = pExpr->iColumn; sqlite3VdbeSetVarmask(pParse->pVdbe, iVar); /* IMP: R-23257-02778 */ *pp = sqlite3VdbeGetValue(pParse->pReprepare, iVar, aff); return SQLITE_OK; } return sqlite3ValueFromExpr(pParse->db, pExpr, SQLITE_UTF8, aff, pp); } #endif |
︙ | ︙ | |||
2356 2357 2358 2359 2360 2361 2362 | ** value of 1 indicates that the proposed range scan is expected to visit ** approximately 1/100th (1%) of the rows selected by the nEq equality ** constraints (if any). A return value of 100 indicates that it is expected ** that the range scan will visit every row (100%) selected by the equality ** constraints. ** ** In the absence of sqlite_stat2 ANALYZE data, each range inequality | | | | > > > > > > | | | | > | | > | < > | | > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > | > > > | > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > | | | | > | 2429 2430 2431 2432 2433 2434 2435 2436 2437 2438 2439 2440 2441 2442 2443 2444 2445 2446 2447 2448 2449 2450 2451 2452 2453 2454 2455 2456 2457 2458 2459 2460 2461 2462 2463 2464 2465 2466 2467 2468 2469 2470 2471 2472 2473 2474 2475 2476 2477 2478 2479 2480 2481 2482 2483 2484 2485 2486 2487 2488 2489 2490 2491 2492 2493 2494 2495 2496 2497 2498 2499 2500 2501 2502 2503 2504 2505 2506 2507 2508 2509 2510 2511 2512 2513 2514 2515 2516 2517 2518 2519 2520 2521 2522 2523 2524 2525 2526 2527 2528 2529 2530 2531 2532 2533 2534 2535 2536 2537 2538 2539 2540 2541 2542 2543 2544 2545 2546 2547 2548 2549 2550 2551 2552 2553 2554 2555 2556 2557 2558 2559 2560 2561 2562 2563 2564 2565 2566 2567 2568 2569 2570 2571 2572 2573 2574 2575 2576 2577 2578 2579 2580 2581 2582 2583 2584 2585 2586 2587 2588 2589 2590 2591 2592 2593 2594 2595 2596 2597 2598 2599 2600 2601 2602 2603 2604 2605 2606 2607 2608 2609 2610 2611 2612 2613 2614 2615 2616 2617 2618 2619 2620 2621 2622 2623 2624 2625 2626 2627 2628 2629 2630 2631 2632 2633 2634 2635 2636 2637 2638 2639 2640 2641 2642 2643 2644 2645 2646 2647 2648 2649 2650 2651 2652 2653 2654 2655 2656 2657 2658 2659 2660 2661 2662 2663 2664 2665 2666 2667 2668 2669 2670 2671 2672 2673 2674 2675 2676 2677 2678 2679 2680 2681 2682 2683 2684 2685 2686 2687 2688 2689 2690 | ** value of 1 indicates that the proposed range scan is expected to visit ** approximately 1/100th (1%) of the rows selected by the nEq equality ** constraints (if any). A return value of 100 indicates that it is expected ** that the range scan will visit every row (100%) selected by the equality ** constraints. ** ** In the absence of sqlite_stat2 ANALYZE data, each range inequality ** reduces the search space by 3/4ths. Hence a single constraint (x>?) ** results in a return of 25 and a range constraint (x>? AND x<?) results ** in a return of 6. */ static int whereRangeScanEst( Parse *pParse, /* Parsing & code generating context */ Index *p, /* The index containing the range-compared column; "x" */ int nEq, /* index into p->aCol[] of the range-compared column */ WhereTerm *pLower, /* Lower bound on the range. ex: "x>123" Might be NULL */ WhereTerm *pUpper, /* Upper bound on the range. ex: "x<455" Might be NULL */ int *piEst /* OUT: Return value */ ){ int rc = SQLITE_OK; #ifdef SQLITE_ENABLE_STAT2 if( nEq==0 && p->aSample ){ sqlite3_value *pLowerVal = 0; sqlite3_value *pUpperVal = 0; int iEst; int iLower = 0; int iUpper = SQLITE_INDEX_SAMPLES; int roundUpUpper; int roundUpLower; u8 aff = p->pTable->aCol[p->aiColumn[0]].affinity; if( pLower ){ Expr *pExpr = pLower->pExpr->pRight; rc = valueFromExpr(pParse, pExpr, aff, &pLowerVal); assert( pLower->eOperator==WO_GT || pLower->eOperator==WO_GE ); roundUpLower = (pLower->eOperator==WO_GT) ?1:0; } if( rc==SQLITE_OK && pUpper ){ Expr *pExpr = pUpper->pExpr->pRight; rc = valueFromExpr(pParse, pExpr, aff, &pUpperVal); assert( pUpper->eOperator==WO_LT || pUpper->eOperator==WO_LE ); roundUpUpper = (pUpper->eOperator==WO_LE) ?1:0; } if( rc!=SQLITE_OK || (pLowerVal==0 && pUpperVal==0) ){ sqlite3ValueFree(pLowerVal); sqlite3ValueFree(pUpperVal); goto range_est_fallback; }else if( pLowerVal==0 ){ rc = whereRangeRegion(pParse, p, pUpperVal, roundUpUpper, &iUpper); if( pLower ) iLower = iUpper/2; }else if( pUpperVal==0 ){ rc = whereRangeRegion(pParse, p, pLowerVal, roundUpLower, &iLower); if( pUpper ) iUpper = (iLower + SQLITE_INDEX_SAMPLES + 1)/2; }else{ rc = whereRangeRegion(pParse, p, pUpperVal, roundUpUpper, &iUpper); if( rc==SQLITE_OK ){ rc = whereRangeRegion(pParse, p, pLowerVal, roundUpLower, &iLower); } } WHERETRACE(("range scan regions: %d..%d\n", iLower, iUpper)); iEst = iUpper - iLower; testcase( iEst==SQLITE_INDEX_SAMPLES ); assert( iEst<=SQLITE_INDEX_SAMPLES ); if( iEst<1 ){ *piEst = 50/SQLITE_INDEX_SAMPLES; }else{ *piEst = (iEst*100)/SQLITE_INDEX_SAMPLES; } sqlite3ValueFree(pLowerVal); sqlite3ValueFree(pUpperVal); return rc; } range_est_fallback: #else UNUSED_PARAMETER(pParse); UNUSED_PARAMETER(p); UNUSED_PARAMETER(nEq); #endif assert( pLower || pUpper ); *piEst = 100; if( pLower && (pLower->wtFlags & TERM_VNULL)==0 ) *piEst /= 4; if( pUpper ) *piEst /= 4; return rc; } #ifdef SQLITE_ENABLE_STAT2 /* ** Estimate the number of rows that will be returned based on ** an equality constraint x=VALUE and where that VALUE occurs in ** the histogram data. This only works when x is the left-most ** column of an index and sqlite_stat2 histogram data is available ** for that index. ** ** Write the estimated row count into *pnRow and return SQLITE_OK. ** If unable to make an estimate, leave *pnRow unchanged and return ** non-zero. ** ** This routine can fail if it is unable to load a collating sequence ** required for string comparison, or if unable to allocate memory ** for a UTF conversion required for comparison. The error is stored ** in the pParse structure. */ int whereEqualScanEst( Parse *pParse, /* Parsing & code generating context */ Index *p, /* The index whose left-most column is pTerm */ Expr *pExpr, /* Expression for VALUE in the x=VALUE constraint */ double *pnRow /* Write the revised row estimate here */ ){ sqlite3_value *pRhs = 0; /* VALUE on right-hand side of pTerm */ int iLower, iUpper; /* Range of histogram regions containing pRhs */ u8 aff; /* Column affinity */ int rc; /* Subfunction return code */ double nRowEst; /* New estimate of the number of rows */ assert( p->aSample!=0 ); aff = p->pTable->aCol[p->aiColumn[0]].affinity; rc = valueFromExpr(pParse, pExpr, aff, &pRhs); if( rc ) goto whereEqualScanEst_cancel; if( pRhs==0 ) return SQLITE_NOTFOUND; rc = whereRangeRegion(pParse, p, pRhs, 0, &iLower); if( rc ) goto whereEqualScanEst_cancel; rc = whereRangeRegion(pParse, p, pRhs, 1, &iUpper); if( rc ) goto whereEqualScanEst_cancel; WHERETRACE(("equality scan regions: %d..%d\n", iLower, iUpper)); if( iLower>=iUpper ){ nRowEst = p->aiRowEst[0]/(SQLITE_INDEX_SAMPLES*2); if( nRowEst<*pnRow ) *pnRow = nRowEst; }else{ nRowEst = (iUpper-iLower)*p->aiRowEst[0]/SQLITE_INDEX_SAMPLES; *pnRow = nRowEst; } whereEqualScanEst_cancel: sqlite3ValueFree(pRhs); return rc; } #endif /* defined(SQLITE_ENABLE_STAT2) */ #ifdef SQLITE_ENABLE_STAT2 /* ** Estimate the number of rows that will be returned based on ** an IN constraint where the right-hand side of the IN operator ** is a list of values. Example: ** ** WHERE x IN (1,2,3,4) ** ** Write the estimated row count into *pnRow and return SQLITE_OK. ** If unable to make an estimate, leave *pnRow unchanged and return ** non-zero. ** ** This routine can fail if it is unable to load a collating sequence ** required for string comparison, or if unable to allocate memory ** for a UTF conversion required for comparison. The error is stored ** in the pParse structure. */ int whereInScanEst( Parse *pParse, /* Parsing & code generating context */ Index *p, /* The index whose left-most column is pTerm */ ExprList *pList, /* The value list on the RHS of "x IN (v1,v2,v3,...)" */ double *pnRow /* Write the revised row estimate here */ ){ sqlite3_value *pVal = 0; /* One value from list */ int iLower, iUpper; /* Range of histogram regions containing pRhs */ u8 aff; /* Column affinity */ int rc = SQLITE_OK; /* Subfunction return code */ double nRowEst; /* New estimate of the number of rows */ int nSpan = 0; /* Number of histogram regions spanned */ int nSingle = 0; /* Histogram regions hit by a single value */ int nNotFound = 0; /* Count of values that are not constants */ int i; /* Loop counter */ u8 aSpan[SQLITE_INDEX_SAMPLES+1]; /* Histogram regions that are spanned */ u8 aSingle[SQLITE_INDEX_SAMPLES+1]; /* Histogram regions hit once */ assert( p->aSample!=0 ); aff = p->pTable->aCol[p->aiColumn[0]].affinity; memset(aSpan, 0, sizeof(aSpan)); memset(aSingle, 0, sizeof(aSingle)); for(i=0; i<pList->nExpr; i++){ sqlite3ValueFree(pVal); rc = valueFromExpr(pParse, pList->a[i].pExpr, aff, &pVal); if( rc ) break; if( pVal==0 || sqlite3_value_type(pVal)==SQLITE_NULL ){ nNotFound++; continue; } rc = whereRangeRegion(pParse, p, pVal, 0, &iLower); if( rc ) break; rc = whereRangeRegion(pParse, p, pVal, 1, &iUpper); if( rc ) break; if( iLower>=iUpper ){ aSingle[iLower] = 1; }else{ assert( iLower>=0 && iUpper<=SQLITE_INDEX_SAMPLES ); while( iLower<iUpper ) aSpan[iLower++] = 1; } } if( rc==SQLITE_OK ){ for(i=nSpan=0; i<=SQLITE_INDEX_SAMPLES; i++){ if( aSpan[i] ){ nSpan++; }else if( aSingle[i] ){ nSingle++; } } nRowEst = (nSpan*2+nSingle)*p->aiRowEst[0]/(2*SQLITE_INDEX_SAMPLES) + nNotFound*p->aiRowEst[1]; if( nRowEst > p->aiRowEst[0] ) nRowEst = p->aiRowEst[0]; *pnRow = nRowEst; WHERETRACE(("IN row estimate: nSpan=%d, nSingle=%d, nNotFound=%d, est=%g\n", nSpan, nSingle, nNotFound, nRowEst)); } sqlite3ValueFree(pVal); return rc; } #endif /* defined(SQLITE_ENABLE_STAT2) */ /* ** Find the best query plan for accessing a particular table. Write the ** best query plan and its cost into the WhereCost object supplied as the ** last parameter. ** ** The lowest cost plan wins. The cost is an estimate of the amount of ** CPU and disk I/O needed to process the requested result. ** Factors that influence cost include: ** ** * The estimated number of rows that will be retrieved. (The ** fewer the better.) ** ** * Whether or not sorting must occur. ** ** * Whether or not there must be separate lookups in the ** index and in the main table. ** ** If there was an INDEXED BY clause (pSrc->pIndex) attached to the table in ** the SQL statement, then this function only considers plans using the ** named index. If no such plan is found, then the returned cost is ** SQLITE_BIG_DBL. If a plan is found that uses the named index, ** then the cost is calculated in the usual way. ** ** If a NOT INDEXED clause (pSrc->notIndexed!=0) was attached to the table ** in the SELECT statement, then no indexes are considered. However, the ** selected plan may still take advantage of the built-in rowid primary key ** index. */ static void bestBtreeIndex( Parse *pParse, /* The parsing context */ WhereClause *pWC, /* The WHERE clause */ struct SrcList_item *pSrc, /* The FROM clause term to search */ Bitmask notReady, /* Mask of cursors not available for indexing */ Bitmask notValid, /* Cursors not available for any purpose */ ExprList *pOrderBy, /* The ORDER BY clause */ WhereCost *pCost /* Lowest cost query plan */ ){ int iCur = pSrc->iCursor; /* The cursor of the table to be accessed */ Index *pProbe; /* An index we are evaluating */ Index *pIdx; /* Copy of pProbe, or zero for IPK index */ int eqTermMask; /* Current mask of valid equality operators */ |
︙ | ︙ | |||
2501 2502 2503 2504 2505 2506 2507 | if( pSrc->pIndex ){ /* An INDEXED BY clause specifies a particular index to use */ pIdx = pProbe = pSrc->pIndex; wsFlagMask = ~(WHERE_ROWID_EQ|WHERE_ROWID_RANGE); eqTermMask = idxEqTermMask; }else{ | | | > > | < > > > > < < < < < < < < < < > | > > | 2711 2712 2713 2714 2715 2716 2717 2718 2719 2720 2721 2722 2723 2724 2725 2726 2727 2728 2729 2730 2731 2732 2733 2734 2735 2736 2737 2738 2739 2740 2741 2742 2743 2744 2745 2746 2747 2748 2749 2750 2751 2752 2753 2754 2755 2756 2757 2758 2759 2760 2761 2762 2763 2764 2765 2766 2767 2768 2769 2770 | if( pSrc->pIndex ){ /* An INDEXED BY clause specifies a particular index to use */ pIdx = pProbe = pSrc->pIndex; wsFlagMask = ~(WHERE_ROWID_EQ|WHERE_ROWID_RANGE); eqTermMask = idxEqTermMask; }else{ /* There is no INDEXED BY clause. Create a fake Index object in local ** 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.nColumn = 1; sPk.aiColumn = &aiColumnPk; sPk.aiRowEst = aiRowEstPk; sPk.onError = OE_Replace; sPk.pTable = pSrc->pTab; aiRowEstPk[0] = pSrc->pTab->nRowEst; aiRowEstPk[1] = 1; pFirst = pSrc->pTab->pIndex; if( pSrc->notIndexed==0 ){ /* The real indices of the table are only considered if the ** NOT INDEXED qualifier is omitted from the FROM clause */ sPk.pNext = pFirst; } pProbe = &sPk; wsFlagMask = ~( WHERE_COLUMN_IN|WHERE_COLUMN_EQ|WHERE_COLUMN_NULL|WHERE_COLUMN_RANGE ); eqTermMask = WO_EQ|WO_IN; pIdx = 0; } /* Loop over all indices looking for the best one to use */ for(; pProbe; pIdx=pProbe=pProbe->pNext){ const unsigned int * const aiRowEst = pProbe->aiRowEst; double cost; /* Cost of using pProbe */ double nRow; /* Estimated number of rows in result set */ double log10N; /* base-10 logarithm of nRow (inexact) */ int rev; /* True to scan in reverse order */ int wsFlags = 0; Bitmask used = 0; /* The following variables are populated based on the properties of ** index being evaluated. They are then used to determine the expected ** cost and number of rows returned. ** ** nEq: ** Number of equality terms that can be implemented using the index. ** In other words, the number of initial fields in the index that ** are used in == or IN or NOT NULL constraints of the WHERE clause. ** ** nInMul: ** The "in-multiplier". This is an estimate of how many seek operations ** SQLite must perform on the index in question. For example, if the ** WHERE clause is: ** ** WHERE a IN (1, 2, 3) AND b IN (4, 5, 6) |
︙ | ︙ | |||
2572 2573 2574 2575 2576 2577 2578 | ** ** If there exists a WHERE term of the form "x IN (SELECT ...)", then ** the sub-select is assumed to return 25 rows for the purposes of ** determining nInMul. ** ** bInEst: ** Set to true if there was at least one "x IN (SELECT ...)" term used | | > > | | | > | | | | | > > | | | | | | | | > > > > | > | > > > | 2780 2781 2782 2783 2784 2785 2786 2787 2788 2789 2790 2791 2792 2793 2794 2795 2796 2797 2798 2799 2800 2801 2802 2803 2804 2805 2806 2807 2808 2809 2810 2811 2812 2813 2814 2815 2816 2817 2818 2819 2820 2821 2822 2823 2824 2825 2826 2827 2828 2829 2830 2831 2832 2833 2834 2835 2836 2837 2838 2839 2840 2841 2842 2843 2844 2845 2846 2847 2848 2849 2850 2851 2852 2853 2854 2855 2856 2857 2858 2859 2860 2861 | ** ** If there exists a WHERE term of the form "x IN (SELECT ...)", then ** the sub-select is assumed to return 25 rows for the purposes of ** determining nInMul. ** ** bInEst: ** Set to true if there was at least one "x IN (SELECT ...)" term used ** in determining the value of nInMul. Note that the RHS of the ** IN operator must be a SELECT, not a value list, for this variable ** to be true. ** ** estBound: ** An estimate on the amount of the table that must be searched. A ** value of 100 means the entire table is searched. Range constraints ** might reduce this to a value less than 100 to indicate that only ** a fraction of the table needs searching. In the absence of ** sqlite_stat2 ANALYZE data, a single inequality reduces the search ** space to 1/4rd its original size. So an x>? constraint reduces ** estBound to 25. Two constraints (x>? AND x<?) reduce estBound to 6. ** ** bSort: ** Boolean. True if there is an ORDER BY clause that will require an ** external sort (i.e. scanning the index being evaluated will not ** correctly order records). ** ** bLookup: ** Boolean. True if a table lookup is required for each index entry ** visited. In other words, true if this is not a covering index. ** This is always false for the rowid primary key index of a table. ** For other indexes, it is true unless all the columns of the table ** used by the SELECT statement are present in the index (such an ** index is sometimes described as a covering index). ** For example, given the index on (a, b), the second of the following ** two queries requires table b-tree lookups in order to find the value ** of column c, but the first does not because columns a and b are ** both available in the index. ** ** SELECT a, b FROM tbl WHERE a = 1; ** SELECT a, b, c FROM tbl WHERE a = 1; */ int nEq; /* Number of == or IN terms matching index */ int bInEst = 0; /* True if "x IN (SELECT...)" seen */ int nInMul = 1; /* Number of distinct equalities to lookup */ int estBound = 100; /* Estimated reduction in search space */ int nBound = 0; /* Number of range constraints seen */ int bSort = 0; /* True if external sort required */ int bLookup = 0; /* True if not a covering index */ WhereTerm *pTerm; /* A single term of the WHERE clause */ #ifdef SQLITE_ENABLE_STAT2 WhereTerm *pFirstTerm = 0; /* First term matching the index */ #endif /* Determine the values of nEq and nInMul */ for(nEq=0; nEq<pProbe->nColumn; nEq++){ int j = pProbe->aiColumn[nEq]; pTerm = findTerm(pWC, iCur, j, notReady, eqTermMask, pIdx); if( pTerm==0 ) break; wsFlags |= (WHERE_COLUMN_EQ|WHERE_ROWID_EQ); if( pTerm->eOperator & WO_IN ){ Expr *pExpr = pTerm->pExpr; wsFlags |= WHERE_COLUMN_IN; if( ExprHasProperty(pExpr, EP_xIsSelect) ){ /* "x IN (SELECT ...)": Assume the SELECT returns 25 rows */ nInMul *= 25; bInEst = 1; }else if( ALWAYS(pExpr->x.pList && pExpr->x.pList->nExpr) ){ /* "x IN (value, value, ...)" */ nInMul *= pExpr->x.pList->nExpr; } }else if( pTerm->eOperator & WO_ISNULL ){ wsFlags |= WHERE_COLUMN_NULL; } #ifdef SQLITE_ENABLE_STAT2 if( nEq==0 && pProbe->aSample ) pFirstTerm = pTerm; #endif used |= pTerm->prereqRight; } /* Determine the value of estBound. */ if( nEq<pProbe->nColumn ){ int j = pProbe->aiColumn[nEq]; if( findTerm(pWC, iCur, j, notReady, WO_LT|WO_LE|WO_GT|WO_GE, pIdx) ){ |
︙ | ︙ | |||
2662 2663 2664 2665 2666 2667 2668 | } /* If there is an ORDER BY clause and the index being considered will ** naturally scan rows in the required order, set the appropriate flags ** in wsFlags. Otherwise, if there is an ORDER BY clause but the index ** will scan rows in a different order, set the bSort variable. */ if( pOrderBy ){ | | | > | 2883 2884 2885 2886 2887 2888 2889 2890 2891 2892 2893 2894 2895 2896 2897 2898 2899 | } /* If there is an ORDER BY clause and the index being considered will ** naturally scan rows in the required order, set the appropriate flags ** in wsFlags. Otherwise, if there is an ORDER BY clause but the index ** will scan rows in a different order, set the bSort variable. */ if( pOrderBy ){ if( (wsFlags & WHERE_COLUMN_IN)==0 && isSortingIndex(pParse, pWC->pMaskSet, pProbe, iCur, pOrderBy, nEq, wsFlags, &rev) ){ wsFlags |= WHERE_ROWID_RANGE|WHERE_COLUMN_RANGE|WHERE_ORDERBY; wsFlags |= (rev ? WHERE_REVERSE : 0); }else{ bSort = 1; } } |
︙ | ︙ | |||
2694 2695 2696 2697 2698 2699 2700 | wsFlags |= WHERE_IDX_ONLY; }else{ bLookup = 1; } } /* | | | > | > | < > | > > > > | > > > | | > > > > > > > > > > > > > > > | > > > > > > | > > > > > | > > > > > > > > > > > | | > > > > > > | > | > > | > | | | > | < < | < > | > > | | | | | | > > > > | | | | < > | 2916 2917 2918 2919 2920 2921 2922 2923 2924 2925 2926 2927 2928 2929 2930 2931 2932 2933 2934 2935 2936 2937 2938 2939 2940 2941 2942 2943 2944 2945 2946 2947 2948 2949 2950 2951 2952 2953 2954 2955 2956 2957 2958 2959 2960 2961 2962 2963 2964 2965 2966 2967 2968 2969 2970 2971 2972 2973 2974 2975 2976 2977 2978 2979 2980 2981 2982 2983 2984 2985 2986 2987 2988 2989 2990 2991 2992 2993 2994 2995 2996 2997 2998 2999 3000 3001 3002 3003 3004 3005 3006 3007 3008 3009 3010 3011 3012 3013 3014 3015 3016 3017 3018 3019 3020 3021 3022 3023 3024 3025 3026 3027 3028 3029 3030 3031 3032 3033 3034 3035 3036 3037 3038 3039 3040 3041 3042 3043 3044 3045 3046 3047 3048 3049 3050 3051 3052 3053 3054 3055 3056 3057 3058 3059 3060 3061 3062 3063 3064 3065 3066 3067 3068 3069 3070 3071 3072 3073 3074 3075 3076 3077 3078 3079 3080 3081 3082 3083 3084 3085 3086 3087 3088 3089 3090 3091 3092 3093 3094 3095 3096 3097 3098 | wsFlags |= WHERE_IDX_ONLY; }else{ bLookup = 1; } } /* ** Estimate the number of rows of output. For an "x IN (SELECT...)" ** constraint, do not let the estimate exceed half the rows in the table. */ nRow = (double)(aiRowEst[nEq] * nInMul); if( bInEst && nRow*2>aiRowEst[0] ){ nRow = aiRowEst[0]/2; nInMul = (int)(nRow / aiRowEst[nEq]); } #ifdef SQLITE_ENABLE_STAT2 /* If the constraint is of the form x=VALUE and histogram ** data is available for column x, then it might be possible ** to get a better estimate on the number of rows based on ** VALUE and how common that value is according to the histogram. */ if( nRow>(double)1 && nEq==1 && pFirstTerm!=0 ){ if( pFirstTerm->eOperator==WO_EQ ){ whereEqualScanEst(pParse, pProbe, pFirstTerm->pExpr->pRight, &nRow); }else if( pFirstTerm->eOperator==WO_IN && bInEst==0 ){ whereInScanEst(pParse, pProbe, pFirstTerm->pExpr->x.pList, &nRow); } } #endif /* SQLITE_ENABLE_STAT2 */ /* Adjust the number of output rows and downward to reflect rows ** that are excluded by range constraints. */ nRow = (nRow * (double)estBound) / (double)100; if( nRow<1 ) nRow = 1; /* Experiments run on real SQLite databases show that the time needed ** to do a binary search to locate a row in a table or index is roughly ** log10(N) times the time to move from one row to the next row within ** a table or index. The actual times can vary, with the size of ** records being an important factor. Both moves and searches are ** slower with larger records, presumably because fewer records fit ** on one page and hence more pages have to be fetched. ** ** The ANALYZE command and the sqlite_stat1 and sqlite_stat2 tables do ** not give us data on the relative sizes of table and index records. ** So this computation assumes table records are about twice as big ** as index records */ if( (wsFlags & WHERE_NOT_FULLSCAN)==0 ){ /* The cost of a full table scan is a number of move operations equal ** to the number of rows in the table. ** ** We add an additional 4x penalty to full table scans. This causes ** the cost function to err on the side of choosing an index over ** choosing a full scan. This 4x full-scan penalty is an arguable ** decision and one which we expect to revisit in the future. But ** it seems to be working well enough at the moment. */ cost = aiRowEst[0]*4; }else{ log10N = estLog(aiRowEst[0]); cost = nRow; if( pIdx ){ if( bLookup ){ /* For an index lookup followed by a table lookup: ** nInMul index searches to find the start of each index range ** + nRow steps through the index ** + nRow table searches to lookup the table entry using the rowid */ cost += (nInMul + nRow)*log10N; }else{ /* For a covering index: ** nInMul index searches to find the initial entry ** + nRow steps through the index */ cost += nInMul*log10N; } }else{ /* For a rowid primary key lookup: ** nInMult table searches to find the initial entry for each range ** + nRow steps through the table */ cost += nInMul*log10N; } } /* Add in the estimated cost of sorting the result. Actual experimental ** measurements of sorting performance in SQLite show that sorting time ** adds C*N*log10(N) to the cost, where N is the number of rows to be ** sorted and C is a factor between 1.95 and 4.3. We will split the ** difference and select C of 3.0. */ if( bSort ){ cost += nRow*estLog(nRow)*3; } /**** Cost of using this index has now been computed ****/ /* If there are additional constraints on this table that cannot ** be used with the current index, but which might lower the number ** of output rows, adjust the nRow value accordingly. This only ** matters if the current index is the least costly, so do not bother ** with this step if we already know this index will not be chosen. ** Also, never reduce the output row count below 2 using this step. ** ** It is critical that the notValid mask be used here instead of ** the notReady mask. When computing an "optimal" index, the notReady ** mask will only have one bit set - the bit for the current table. ** The notValid mask, on the other hand, always has all bits set for ** tables that are not in outer loops. If notReady is used here instead ** of notValid, then a optimal index that depends on inner joins loops ** might be selected even when there exists an optimal index that has ** no such dependency. */ if( nRow>2 && cost<=pCost->rCost ){ int k; /* Loop counter */ int nSkipEq = nEq; /* Number of == constraints to skip */ int nSkipRange = nBound; /* Number of < constraints to skip */ Bitmask thisTab; /* Bitmap for pSrc */ thisTab = getMask(pWC->pMaskSet, iCur); for(pTerm=pWC->a, k=pWC->nTerm; nRow>2 && k; k--, pTerm++){ if( pTerm->wtFlags & TERM_VIRTUAL ) continue; if( (pTerm->prereqAll & notValid)!=thisTab ) continue; if( pTerm->eOperator & (WO_EQ|WO_IN|WO_ISNULL) ){ if( nSkipEq ){ /* Ignore the first nEq equality matches since the index ** has already accounted for these */ nSkipEq--; }else{ /* Assume each additional equality match reduces the result ** set size by a factor of 10 */ nRow /= 10; } }else if( pTerm->eOperator & (WO_LT|WO_LE|WO_GT|WO_GE) ){ if( nSkipRange ){ /* Ignore the first nSkipRange range constraints since the index ** has already accounted for these */ nSkipRange--; }else{ /* Assume each additional range constraint reduces the result ** set size by a factor of 3. Indexed range constraints reduce ** the search space by a larger factor: 4. We make indexed range ** more selective intentionally because of the subjective ** observation that indexed range constraints really are more ** selective in practice, on average. */ nRow /= 3; } }else if( pTerm->eOperator!=WO_NOOP ){ /* Any other expression lowers the output row count by half */ nRow /= 2; } } if( nRow<2 ) nRow = 2; } WHERETRACE(( "%s(%s): nEq=%d nInMul=%d estBound=%d bSort=%d bLookup=%d wsFlags=0x%x\n" " notReady=0x%llx log10N=%.1f nRow=%.1f cost=%.1f used=0x%llx\n", pSrc->pTab->zName, (pIdx ? pIdx->zName : "ipk"), nEq, nInMul, estBound, bSort, bLookup, wsFlags, notReady, log10N, nRow, cost, used )); /* If this index is the best we have seen so far, then record this ** index and its cost in the pCost structure. */ if( (!pIdx || wsFlags) && (cost<pCost->rCost || (cost<=pCost->rCost && nRow<pCost->plan.nRow)) ){ pCost->rCost = cost; pCost->used = used; pCost->plan.nRow = nRow; pCost->plan.wsFlags = (wsFlags&wsFlagMask); pCost->plan.nEq = nEq; pCost->plan.u.pIdx = pIdx; } /* If there was an INDEXED BY clause, then only that one index is ** considered. */ |
︙ | ︙ | |||
2837 2838 2839 2840 2841 2842 2843 | ); WHERETRACE(("best index is: %s\n", ((pCost->plan.wsFlags & WHERE_NOT_FULLSCAN)==0 ? "none" : pCost->plan.u.pIdx ? pCost->plan.u.pIdx->zName : "ipk") )); | | | > | | | 3120 3121 3122 3123 3124 3125 3126 3127 3128 3129 3130 3131 3132 3133 3134 3135 3136 3137 3138 3139 3140 3141 3142 3143 3144 3145 3146 3147 3148 3149 3150 3151 3152 3153 3154 3155 3156 3157 3158 3159 3160 3161 3162 3163 3164 3165 | ); WHERETRACE(("best index is: %s\n", ((pCost->plan.wsFlags & WHERE_NOT_FULLSCAN)==0 ? "none" : pCost->plan.u.pIdx ? pCost->plan.u.pIdx->zName : "ipk") )); bestOrClauseIndex(pParse, pWC, pSrc, notReady, notValid, pOrderBy, pCost); bestAutomaticIndex(pParse, pWC, pSrc, notReady, pCost); pCost->plan.wsFlags |= eqTermMask; } /* ** Find the query plan for accessing table pSrc->pTab. Write the ** best query plan and its cost into the WhereCost object supplied ** as the last parameter. This function may calculate the cost of ** both real and virtual table scans. */ static void bestIndex( Parse *pParse, /* The parsing context */ WhereClause *pWC, /* The WHERE clause */ struct SrcList_item *pSrc, /* The FROM clause term to search */ Bitmask notReady, /* Mask of cursors not available for indexing */ Bitmask notValid, /* Cursors not available for any purpose */ ExprList *pOrderBy, /* The ORDER BY clause */ WhereCost *pCost /* Lowest cost query plan */ ){ #ifndef SQLITE_OMIT_VIRTUALTABLE if( IsVirtual(pSrc->pTab) ){ sqlite3_index_info *p = 0; bestVirtualIndex(pParse, pWC, pSrc, notReady, notValid, pOrderBy, pCost,&p); if( p->needToFreeIdxStr ){ sqlite3_free(p->idxStr); } sqlite3DbFree(pParse->db, p); }else #endif { bestBtreeIndex(pParse, pWC, pSrc, notReady, notValid, pOrderBy, pCost); } } /* ** Disable a term in the WHERE clause. Except, do not disable the term ** if it controls a LEFT OUTER JOIN and it did not originate in the ON ** or USING clause of that join. |
︙ | ︙ | |||
3128 3129 3130 3131 3132 3133 3134 3135 3136 3137 3138 3139 3140 3141 | } } } } *pzAff = zAff; return regBase; } /* ** Generate code for the start of the iLevel-th loop in the WHERE clause ** implementation described by pWInfo. */ static Bitmask codeOneLoopStart( WhereInfo *pWInfo, /* Complete information about the WHERE clause */ | > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > | 3412 3413 3414 3415 3416 3417 3418 3419 3420 3421 3422 3423 3424 3425 3426 3427 3428 3429 3430 3431 3432 3433 3434 3435 3436 3437 3438 3439 3440 3441 3442 3443 3444 3445 3446 3447 3448 3449 3450 3451 3452 3453 3454 3455 3456 3457 3458 3459 3460 3461 3462 3463 3464 3465 3466 3467 3468 3469 3470 3471 3472 3473 3474 3475 3476 3477 3478 3479 3480 3481 3482 3483 3484 3485 3486 3487 3488 3489 3490 3491 3492 3493 3494 3495 3496 3497 3498 3499 3500 3501 3502 3503 3504 3505 3506 3507 3508 3509 3510 3511 3512 3513 3514 3515 3516 3517 3518 3519 3520 3521 3522 3523 3524 3525 3526 3527 3528 3529 3530 3531 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 3557 3558 3559 3560 3561 3562 3563 3564 3565 3566 3567 3568 3569 3570 3571 3572 3573 3574 3575 3576 3577 3578 3579 3580 | } } } } *pzAff = zAff; return regBase; } #ifndef SQLITE_OMIT_EXPLAIN /* ** This routine is a helper for explainIndexRange() below ** ** pStr holds the text of an expression that we are building up one term ** at a time. This routine adds a new term to the end of the expression. ** Terms are separated by AND so add the "AND" text for second and subsequent ** terms only. */ static void explainAppendTerm( StrAccum *pStr, /* The text expression being built */ int iTerm, /* Index of this term. First is zero */ const char *zColumn, /* Name of the column */ const char *zOp /* Name of the operator */ ){ if( iTerm ) sqlite3StrAccumAppend(pStr, " AND ", 5); sqlite3StrAccumAppend(pStr, zColumn, -1); sqlite3StrAccumAppend(pStr, zOp, 1); sqlite3StrAccumAppend(pStr, "?", 1); } /* ** Argument pLevel describes a strategy for scanning table pTab. This ** function returns a pointer to a string buffer containing a description ** of the subset of table rows scanned by the strategy in the form of an ** SQL expression. Or, if all rows are scanned, NULL is returned. ** ** For example, if the query: ** ** SELECT * FROM t1 WHERE a=1 AND b>2; ** ** is run and there is an index on (a, b), then this function returns a ** string similar to: ** ** "a=? AND b>?" ** ** The returned pointer points to memory obtained from sqlite3DbMalloc(). ** It is the responsibility of the caller to free the buffer when it is ** no longer required. */ static char *explainIndexRange(sqlite3 *db, WhereLevel *pLevel, Table *pTab){ WherePlan *pPlan = &pLevel->plan; Index *pIndex = pPlan->u.pIdx; int nEq = pPlan->nEq; int i, j; Column *aCol = pTab->aCol; int *aiColumn = pIndex->aiColumn; StrAccum txt; if( nEq==0 && (pPlan->wsFlags & (WHERE_BTM_LIMIT|WHERE_TOP_LIMIT))==0 ){ return 0; } sqlite3StrAccumInit(&txt, 0, 0, SQLITE_MAX_LENGTH); txt.db = db; sqlite3StrAccumAppend(&txt, " (", 2); for(i=0; i<nEq; i++){ explainAppendTerm(&txt, i, aCol[aiColumn[i]].zName, "="); } j = i; if( pPlan->wsFlags&WHERE_BTM_LIMIT ){ explainAppendTerm(&txt, i++, aCol[aiColumn[j]].zName, ">"); } if( pPlan->wsFlags&WHERE_TOP_LIMIT ){ explainAppendTerm(&txt, i, aCol[aiColumn[j]].zName, "<"); } sqlite3StrAccumAppend(&txt, ")", 1); return sqlite3StrAccumFinish(&txt); } /* ** This function is a no-op unless currently processing an EXPLAIN QUERY PLAN ** command. If the query being compiled is an EXPLAIN QUERY PLAN, a single ** record is added to the output to describe the table scan strategy in ** pLevel. */ static void explainOneScan( Parse *pParse, /* Parse context */ SrcList *pTabList, /* Table list this loop refers to */ WhereLevel *pLevel, /* Scan to write OP_Explain opcode for */ int iLevel, /* Value for "level" column of output */ int iFrom, /* Value for "from" column of output */ u16 wctrlFlags /* Flags passed to sqlite3WhereBegin() */ ){ if( pParse->explain==2 ){ u32 flags = pLevel->plan.wsFlags; struct SrcList_item *pItem = &pTabList->a[pLevel->iFrom]; Vdbe *v = pParse->pVdbe; /* VM being constructed */ sqlite3 *db = pParse->db; /* Database handle */ char *zMsg; /* Text to add to EQP output */ sqlite3_int64 nRow; /* Expected number of rows visited by scan */ int iId = pParse->iSelectId; /* Select id (left-most output column) */ int isSearch; /* True for a SEARCH. False for SCAN. */ if( (flags&WHERE_MULTI_OR) || (wctrlFlags&WHERE_ONETABLE_ONLY) ) return; isSearch = (pLevel->plan.nEq>0) || (flags&(WHERE_BTM_LIMIT|WHERE_TOP_LIMIT))!=0 || (wctrlFlags&(WHERE_ORDERBY_MIN|WHERE_ORDERBY_MAX)); zMsg = sqlite3MPrintf(db, "%s", isSearch?"SEARCH":"SCAN"); if( pItem->pSelect ){ zMsg = sqlite3MAppendf(db, zMsg, "%s SUBQUERY %d", zMsg,pItem->iSelectId); }else{ zMsg = sqlite3MAppendf(db, zMsg, "%s TABLE %s", zMsg, pItem->zName); } if( pItem->zAlias ){ zMsg = sqlite3MAppendf(db, zMsg, "%s AS %s", zMsg, pItem->zAlias); } if( (flags & WHERE_INDEXED)!=0 ){ char *zWhere = explainIndexRange(db, pLevel, pItem->pTab); zMsg = sqlite3MAppendf(db, zMsg, "%s USING %s%sINDEX%s%s%s", zMsg, ((flags & WHERE_TEMP_INDEX)?"AUTOMATIC ":""), ((flags & WHERE_IDX_ONLY)?"COVERING ":""), ((flags & WHERE_TEMP_INDEX)?"":" "), ((flags & WHERE_TEMP_INDEX)?"": pLevel->plan.u.pIdx->zName), zWhere ); sqlite3DbFree(db, zWhere); }else if( flags & (WHERE_ROWID_EQ|WHERE_ROWID_RANGE) ){ zMsg = sqlite3MAppendf(db, zMsg, "%s USING INTEGER PRIMARY KEY", zMsg); if( flags&WHERE_ROWID_EQ ){ zMsg = sqlite3MAppendf(db, zMsg, "%s (rowid=?)", zMsg); }else if( (flags&WHERE_BOTH_LIMIT)==WHERE_BOTH_LIMIT ){ zMsg = sqlite3MAppendf(db, zMsg, "%s (rowid>? AND rowid<?)", zMsg); }else if( flags&WHERE_BTM_LIMIT ){ zMsg = sqlite3MAppendf(db, zMsg, "%s (rowid>?)", zMsg); }else if( flags&WHERE_TOP_LIMIT ){ zMsg = sqlite3MAppendf(db, zMsg, "%s (rowid<?)", zMsg); } } #ifndef SQLITE_OMIT_VIRTUALTABLE else if( (flags & WHERE_VIRTUALTABLE)!=0 ){ sqlite3_index_info *pVtabIdx = pLevel->plan.u.pVtabIdx; zMsg = sqlite3MAppendf(db, zMsg, "%s VIRTUAL TABLE INDEX %d:%s", zMsg, pVtabIdx->idxNum, pVtabIdx->idxStr); } #endif if( wctrlFlags&(WHERE_ORDERBY_MIN|WHERE_ORDERBY_MAX) ){ testcase( wctrlFlags & WHERE_ORDERBY_MIN ); nRow = 1; }else{ nRow = (sqlite3_int64)pLevel->plan.nRow; } zMsg = sqlite3MAppendf(db, zMsg, "%s (~%lld rows)", zMsg, nRow); sqlite3VdbeAddOp4(v, OP_Explain, iId, iLevel, iFrom, zMsg, P4_DYNAMIC); } } #else # define explainOneScan(u,v,w,x,y,z) #endif /* SQLITE_OMIT_EXPLAIN */ /* ** Generate code for the start of the iLevel-th loop in the WHERE clause ** implementation described by pWInfo. */ static Bitmask codeOneLoopStart( WhereInfo *pWInfo, /* Complete information about the WHERE clause */ |
︙ | ︙ | |||
3458 3459 3460 3461 3462 3463 3464 | start_constraints = pRangeStart || nEq>0; /* Seek the index cursor to the start of the range. */ nConstraint = nEq; if( pRangeStart ){ Expr *pRight = pRangeStart->pExpr->pRight; sqlite3ExprCode(pParse, pRight, regBase+nEq); | > | > | 3897 3898 3899 3900 3901 3902 3903 3904 3905 3906 3907 3908 3909 3910 3911 3912 3913 | start_constraints = pRangeStart || nEq>0; /* Seek the index cursor to the start of the range. */ nConstraint = nEq; if( pRangeStart ){ Expr *pRight = pRangeStart->pExpr->pRight; sqlite3ExprCode(pParse, pRight, regBase+nEq); if( (pRangeStart->wtFlags & TERM_VNULL)==0 ){ sqlite3ExprCodeIsNullJump(v, pRight, regBase+nEq, addrNxt); } if( zStartAff ){ if( sqlite3CompareAffinity(pRight, zStartAff[nEq])==SQLITE_AFF_NONE){ /* Since the comparison is to be performed with no conversions ** applied to the operands, set the affinity to apply to pRight to ** SQLITE_AFF_NONE. */ zStartAff[nEq] = SQLITE_AFF_NONE; } |
︙ | ︙ | |||
3497 3498 3499 3500 3501 3502 3503 | ** range (if any). */ nConstraint = nEq; if( pRangeEnd ){ Expr *pRight = pRangeEnd->pExpr->pRight; sqlite3ExprCacheRemove(pParse, regBase+nEq, 1); sqlite3ExprCode(pParse, pRight, regBase+nEq); | > | > | 3938 3939 3940 3941 3942 3943 3944 3945 3946 3947 3948 3949 3950 3951 3952 3953 3954 | ** range (if any). */ nConstraint = nEq; if( pRangeEnd ){ Expr *pRight = pRangeEnd->pExpr->pRight; sqlite3ExprCacheRemove(pParse, regBase+nEq, 1); sqlite3ExprCode(pParse, pRight, regBase+nEq); if( (pRangeEnd->wtFlags & TERM_VNULL)==0 ){ sqlite3ExprCodeIsNullJump(v, pRight, regBase+nEq, addrNxt); } if( zEndAff ){ if( sqlite3CompareAffinity(pRight, zEndAff[nEq])==SQLITE_AFF_NONE){ /* Since the comparison is to be performed with no conversions ** applied to the operands, set the affinity to apply to pRight to ** SQLITE_AFF_NONE. */ zEndAff[nEq] = SQLITE_AFF_NONE; } |
︙ | ︙ | |||
3536 3537 3538 3539 3540 3541 3542 | /* If there are inequality constraints, check that the value ** of the table column that the inequality contrains is not NULL. ** If it is, jump to the next iteration of the loop. */ r1 = sqlite3GetTempReg(pParse); testcase( pLevel->plan.wsFlags & WHERE_BTM_LIMIT ); testcase( pLevel->plan.wsFlags & WHERE_TOP_LIMIT ); | | | 3979 3980 3981 3982 3983 3984 3985 3986 3987 3988 3989 3990 3991 3992 3993 | /* If there are inequality constraints, check that the value ** of the table column that the inequality contrains is not NULL. ** If it is, jump to the next iteration of the loop. */ r1 = sqlite3GetTempReg(pParse); testcase( pLevel->plan.wsFlags & WHERE_BTM_LIMIT ); testcase( pLevel->plan.wsFlags & WHERE_TOP_LIMIT ); if( (pLevel->plan.wsFlags & (WHERE_BTM_LIMIT|WHERE_TOP_LIMIT))!=0 ){ sqlite3VdbeAddOp3(v, OP_Column, iIdxCur, nEq, r1); sqlite3VdbeAddOp2(v, OP_IsNull, r1, addrCont); } sqlite3ReleaseTempReg(pParse, r1); /* Seek the table cursor, if required */ disableTerm(pLevel, pRangeStart); |
︙ | ︙ | |||
3670 3671 3672 3673 3674 3675 3676 3677 3678 3679 3680 3681 3682 3683 | if( pOrTerm->leftCursor==iCur || pOrTerm->eOperator==WO_AND ){ WhereInfo *pSubWInfo; /* Info for single OR-term scan */ /* Loop through table entries that match term pOrTerm. */ pSubWInfo = sqlite3WhereBegin(pParse, pOrTab, pOrTerm->pExpr, 0, WHERE_OMIT_OPEN | WHERE_OMIT_CLOSE | WHERE_FORCE_TABLE | WHERE_ONETABLE_ONLY); if( pSubWInfo ){ if( (wctrlFlags & WHERE_DUPLICATES_OK)==0 ){ int iSet = ((ii==pOrWc->nTerm-1)?-1:ii); int r; r = sqlite3ExprCodeGetColumn(pParse, pTabItem->pTab, -1, iCur, regRowid); sqlite3VdbeAddOp4Int(v, OP_RowSetTest, regRowset, sqlite3VdbeCurrentAddr(v)+2, r, iSet); | > > > | 4113 4114 4115 4116 4117 4118 4119 4120 4121 4122 4123 4124 4125 4126 4127 4128 4129 | if( pOrTerm->leftCursor==iCur || pOrTerm->eOperator==WO_AND ){ WhereInfo *pSubWInfo; /* Info for single OR-term scan */ /* Loop through table entries that match term pOrTerm. */ pSubWInfo = sqlite3WhereBegin(pParse, pOrTab, pOrTerm->pExpr, 0, WHERE_OMIT_OPEN | WHERE_OMIT_CLOSE | WHERE_FORCE_TABLE | WHERE_ONETABLE_ONLY); if( pSubWInfo ){ explainOneScan( pParse, pOrTab, &pSubWInfo->a[0], iLevel, pLevel->iFrom, 0 ); if( (wctrlFlags & WHERE_DUPLICATES_OK)==0 ){ int iSet = ((ii==pOrWc->nTerm-1)?-1:ii); int r; r = sqlite3ExprCodeGetColumn(pParse, pTabItem->pTab, -1, iCur, regRowid); sqlite3VdbeAddOp4Int(v, OP_RowSetTest, regRowset, sqlite3VdbeCurrentAddr(v)+2, r, iSet); |
︙ | ︙ | |||
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 | Bitmask m; /* Bitmask value for j or bestJ */ int isOptimal; /* Iterator for optimal/non-optimal search */ int nUnconstrained; /* Number tables without INDEXED BY */ Bitmask notIndexed; /* Mask of tables that cannot use an index */ memset(&bestPlan, 0, sizeof(bestPlan)); bestPlan.rCost = SQLITE_BIG_DBL; /* Loop through the remaining entries in the FROM clause to find the ** next nested loop. The loop tests all FROM clause entries ** either once or twice. ** ** The first test is always performed if there are two or more entries ** remaining and never performed if there is only one FROM clause entry ** to choose from. The first test looks for an "optimal" scan. In ** this context an optimal scan is one that uses the same strategy ** for the given FROM clause entry as would be selected if the entry ** were used as the innermost nested loop. In other words, a table ** is chosen such that the cost of running that table cannot be reduced ** by waiting for other tables to run first. This "optimal" test works ** by first assuming that the FROM clause is on the inner loop and finding ** its query plan, then checking to see if that query plan uses any ** other FROM clause terms that are notReady. If no notReady terms are ** used then the "optimal" query plan works. ** ** The second loop iteration is only performed if no optimal scan | > > > > > > > | | | | > > | > | > | 4511 4512 4513 4514 4515 4516 4517 4518 4519 4520 4521 4522 4523 4524 4525 4526 4527 4528 4529 4530 4531 4532 4533 4534 4535 4536 4537 4538 4539 4540 4541 4542 4543 4544 4545 4546 4547 4548 4549 4550 4551 4552 4553 4554 4555 4556 4557 4558 4559 4560 4561 4562 4563 4564 4565 4566 4567 4568 4569 4570 4571 4572 4573 4574 4575 4576 4577 4578 4579 4580 4581 4582 4583 4584 4585 4586 4587 4588 4589 4590 4591 4592 4593 4594 4595 4596 4597 4598 4599 4600 4601 4602 | Bitmask m; /* Bitmask value for j or bestJ */ int isOptimal; /* Iterator for optimal/non-optimal search */ int nUnconstrained; /* Number tables without INDEXED BY */ Bitmask notIndexed; /* Mask of tables that cannot use an index */ memset(&bestPlan, 0, sizeof(bestPlan)); bestPlan.rCost = SQLITE_BIG_DBL; WHERETRACE(("*** Begin search for loop %d ***\n", i)); /* Loop through the remaining entries in the FROM clause to find the ** next nested loop. The loop tests all FROM clause entries ** either once or twice. ** ** The first test is always performed if there are two or more entries ** remaining and never performed if there is only one FROM clause entry ** to choose from. The first test looks for an "optimal" scan. In ** this context an optimal scan is one that uses the same strategy ** for the given FROM clause entry as would be selected if the entry ** were used as the innermost nested loop. In other words, a table ** is chosen such that the cost of running that table cannot be reduced ** by waiting for other tables to run first. This "optimal" test works ** by first assuming that the FROM clause is on the inner loop and finding ** its query plan, then checking to see if that query plan uses any ** other FROM clause terms that are notReady. If no notReady terms are ** used then the "optimal" query plan works. ** ** Note that the WhereCost.nRow parameter for an optimal scan might ** not be as small as it would be if the table really were the innermost ** join. The nRow value can be reduced by WHERE clause constraints ** that do not use indices. But this nRow reduction only happens if the ** table really is the innermost join. ** ** The second loop iteration is only performed if no optimal scan ** strategies were found by the first iteration. This second iteration ** is used to search for the lowest cost scan overall. ** ** Previous versions of SQLite performed only the second iteration - ** the next outermost loop was always that with the lowest overall ** cost. However, this meant that SQLite could select the wrong plan ** for scripts such as the following: ** ** CREATE TABLE t1(a, b); ** CREATE TABLE t2(c, d); ** SELECT * FROM t2, t1 WHERE t2.rowid = t1.a; ** ** The best strategy is to iterate through table t1 first. However it ** is not possible to determine this with a simple greedy algorithm. ** Since the cost of a linear scan through table t2 is the same ** as the cost of a linear scan through table t1, a simple greedy ** algorithm may choose to use t2 for the outer loop, which is a much ** costlier approach. */ nUnconstrained = 0; notIndexed = 0; for(isOptimal=(iFrom<nTabList-1); isOptimal>=0 && bestJ<0; isOptimal--){ Bitmask mask; /* Mask of tables not yet ready */ for(j=iFrom, pTabItem=&pTabList->a[j]; j<nTabList; j++, pTabItem++){ int doNotReorder; /* True if this table should not be reordered */ WhereCost sCost; /* Cost information from best[Virtual]Index() */ ExprList *pOrderBy; /* ORDER BY clause for index to optimize */ doNotReorder = (pTabItem->jointype & (JT_LEFT|JT_CROSS))!=0; if( j!=iFrom && doNotReorder ) break; m = getMask(pMaskSet, pTabItem->iCursor); if( (m & notReady)==0 ){ if( j==iFrom ) iFrom++; continue; } mask = (isOptimal ? m : notReady); pOrderBy = ((i==0 && ppOrderBy )?*ppOrderBy:0); if( pTabItem->pIndex==0 ) nUnconstrained++; WHERETRACE(("=== trying table %d with isOptimal=%d ===\n", j, isOptimal)); assert( pTabItem->pTab ); #ifndef SQLITE_OMIT_VIRTUALTABLE if( IsVirtual(pTabItem->pTab) ){ sqlite3_index_info **pp = &pWInfo->a[j].pIdxInfo; bestVirtualIndex(pParse, pWC, pTabItem, mask, notReady, pOrderBy, &sCost, pp); }else #endif { bestBtreeIndex(pParse, pWC, pTabItem, mask, notReady, pOrderBy, &sCost); } assert( isOptimal || (sCost.used¬Ready)==0 ); /* If an INDEXED BY clause is present, then the plan must use that ** index if it uses any index at all */ assert( pTabItem->pIndex==0 || (sCost.plan.wsFlags & WHERE_NOT_FULLSCAN)==0 |
︙ | ︙ | |||
4171 4172 4173 4174 4175 4176 4177 | */ if( (sCost.used¬Ready)==0 /* (1) */ && (bestJ<0 || (notIndexed&m)!=0 /* (2) */ || (sCost.plan.wsFlags & WHERE_NOT_FULLSCAN)!=0) && (nUnconstrained==0 || pTabItem->pIndex==0 /* (3) */ || NEVER((sCost.plan.wsFlags & WHERE_NOT_FULLSCAN)!=0)) && (bestJ<0 || sCost.rCost<bestPlan.rCost /* (4) */ | | > > | | | > | > | > | 4628 4629 4630 4631 4632 4633 4634 4635 4636 4637 4638 4639 4640 4641 4642 4643 4644 4645 4646 4647 4648 4649 4650 4651 4652 4653 4654 4655 4656 4657 4658 4659 4660 4661 4662 4663 4664 4665 4666 4667 4668 4669 4670 4671 4672 4673 4674 4675 | */ if( (sCost.used¬Ready)==0 /* (1) */ && (bestJ<0 || (notIndexed&m)!=0 /* (2) */ || (sCost.plan.wsFlags & WHERE_NOT_FULLSCAN)!=0) && (nUnconstrained==0 || pTabItem->pIndex==0 /* (3) */ || NEVER((sCost.plan.wsFlags & WHERE_NOT_FULLSCAN)!=0)) && (bestJ<0 || sCost.rCost<bestPlan.rCost /* (4) */ || (sCost.rCost<=bestPlan.rCost && sCost.plan.nRow<bestPlan.plan.nRow)) ){ WHERETRACE(("=== table %d is best so far" " with cost=%g and nRow=%g\n", j, sCost.rCost, sCost.plan.nRow)); bestPlan = sCost; bestJ = j; } if( doNotReorder ) break; } } assert( bestJ>=0 ); assert( notReady & getMask(pMaskSet, pTabList->a[bestJ].iCursor) ); WHERETRACE(("*** Optimizer selects table %d for loop %d" " with cost=%g and nRow=%g\n", bestJ, pLevel-pWInfo->a, bestPlan.rCost, bestPlan.plan.nRow)); if( (bestPlan.plan.wsFlags & WHERE_ORDERBY)!=0 ){ *ppOrderBy = 0; } andFlags &= bestPlan.plan.wsFlags; pLevel->plan = bestPlan.plan; testcase( bestPlan.plan.wsFlags & WHERE_INDEXED ); testcase( bestPlan.plan.wsFlags & WHERE_TEMP_INDEX ); if( bestPlan.plan.wsFlags & (WHERE_INDEXED|WHERE_TEMP_INDEX) ){ pLevel->iIdxCur = pParse->nTab++; }else{ pLevel->iIdxCur = -1; } notReady &= ~getMask(pMaskSet, pTabList->a[bestJ].iCursor); pLevel->iFrom = (u8)bestJ; if( bestPlan.plan.nRow>=(double)1 ){ pParse->nQueryLoop *= bestPlan.plan.nRow; } /* Check that if the table scanned by this loop iteration had an ** INDEXED BY clause attached to it, that the named index is being ** used for the scan. If not, then query compilation has failed. ** Return an error. */ pIdx = pTabList->a[bestJ].pIndex; |
︙ | ︙ | |||
4247 4248 4249 4250 4251 4252 4253 4254 4255 4256 4257 | } /* Open all tables in the pTabList and any indices selected for ** searching those tables. */ sqlite3CodeVerifySchema(pParse, -1); /* Insert the cookie verifier Goto */ notReady = ~(Bitmask)0; for(i=0, pLevel=pWInfo->a; i<nTabList; i++, pLevel++){ Table *pTab; /* Table to open */ int iDb; /* Index of database containing table/index */ | > < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < > | 4709 4710 4711 4712 4713 4714 4715 4716 4717 4718 4719 4720 4721 4722 4723 4724 4725 4726 4727 4728 4729 4730 4731 | } /* Open all tables in the pTabList and any indices selected for ** searching those tables. */ sqlite3CodeVerifySchema(pParse, -1); /* Insert the cookie verifier Goto */ notReady = ~(Bitmask)0; pWInfo->nRowOut = (double)1; for(i=0, pLevel=pWInfo->a; i<nTabList; i++, pLevel++){ Table *pTab; /* Table to open */ int iDb; /* Index of database containing table/index */ pTabItem = &pTabList->a[pLevel->iFrom]; pTab = pTabItem->pTab; pLevel->iTabCur = pTabItem->iCursor; pWInfo->nRowOut *= pLevel->plan.nRow; iDb = sqlite3SchemaToIndex(db, pTab->pSchema); if( (pTab->tabFlags & TF_Ephemeral)!=0 || pTab->pSelect ){ /* Do nothing */ }else #ifndef SQLITE_OMIT_VIRTUALTABLE if( (pLevel->plan.wsFlags & WHERE_VIRTUALTABLE)!=0 ){ const char *pVTab = (const char *)sqlite3GetVTable(db, pTab); |
︙ | ︙ | |||
4340 4341 4342 4343 4344 4345 4346 4347 | /* Generate the code to do the search. Each iteration of the for ** loop below generates code for a single nested loop of the VM ** program. */ notReady = ~(Bitmask)0; for(i=0; i<nTabList; i++){ notReady = codeOneLoopStart(pWInfo, i, wctrlFlags, notReady); | > > | | 4773 4774 4775 4776 4777 4778 4779 4780 4781 4782 4783 4784 4785 4786 4787 4788 4789 4790 | /* Generate the code to do the search. Each iteration of the for ** loop below generates code for a single nested loop of the VM ** program. */ notReady = ~(Bitmask)0; for(i=0; i<nTabList; i++){ pLevel = &pWInfo->a[i]; explainOneScan(pParse, pTabList, pLevel, i, pLevel->iFrom, wctrlFlags); notReady = codeOneLoopStart(pWInfo, i, wctrlFlags, notReady); pWInfo->iContinue = pLevel->addrCont; } #ifdef SQLITE_TEST /* For testing and debugging use only */ /* Record in the query plan information about the current table ** and the index used to access it (if any). If the table itself ** is not used, its name is just '{}'. If no index is used ** the index is listed as "{}". If the primary key is used the |
︙ | ︙ |
Changes to test/autoindex1.test.
︙ | ︙ | |||
120 121 122 123 124 125 126 | SELECT count(*) FROM t4; } } {4096} do_test autoindex1-401 { db eval { SELECT count(*) FROM t4 AS x1 | | | | | 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 | SELECT count(*) FROM t4; } } {4096} do_test autoindex1-401 { db eval { SELECT count(*) FROM t4 AS x1 /* JOIN t4 AS x2 ON x2.a=x1.b JOIN t4 AS x3 ON x3.a=x2.b JOIN t4 AS x4 ON x4.a=x3.b JOIN t4 AS x5 ON x5.a=x4.b JOIN t4 AS x6 ON x6.a=x5.b JOIN t4 AS x7 ON x7.a=x6.b JOIN t4 AS x8 ON x8.a=x7.b JOIN t4 AS x9 ON x9.a=x8.b JOIN t4 AS x10 ON x10.a=x9.b;*/ } } {4087 FIXME} # Ticket [8011086c85c6c404014c947fcf3eb9f42b184a0d] from 2010-07-08 # Make sure automatic indices are not created for the RHS of an IN expression # that is not a correlated subquery. # do_test autoindex1-500 { db eval { |
︙ | ︙ |