Index: src/analyze.c ================================================================== --- src/analyze.c +++ src/analyze.c @@ -1386,44 +1386,32 @@ /* Form 1: Analyze everything */ for(i=0; inDb; i++){ if( i==1 ) continue; /* Do not analyze the TEMP database */ analyzeDatabase(pParse, i); } - }else if( pName2->n==0 ){ - /* Form 2: Analyze the database or table named */ - iDb = sqlite3FindDb(db, pName1); - if( iDb>=0 ){ - analyzeDatabase(pParse, iDb); - }else{ - z = sqlite3NameFromToken(db, pName1); - if( z ){ - if( (pIdx = sqlite3FindIndex(db, z, 0))!=0 ){ - analyzeTable(pParse, pIdx->pTable, pIdx); - }else if( (pTab = sqlite3LocateTable(pParse, 0, z, 0))!=0 ){ - analyzeTable(pParse, pTab, 0); - } - sqlite3DbFree(db, z); - } - } - }else{ - /* Form 3: Analyze the fully qualified table name */ + }else if( pName2->n==0 && (iDb = sqlite3FindDb(db, pName1))>=0 ){ + /* Analyze the schema named as the argument */ + analyzeDatabase(pParse, iDb); + }else{ + /* Form 3: Analyze the table or index named as an argument */ iDb = sqlite3TwoPartName(pParse, pName1, pName2, &pTableName); if( iDb>=0 ){ - zDb = db->aDb[iDb].zDbSName; + zDb = pName2->n ? db->aDb[iDb].zDbSName : 0; z = sqlite3NameFromToken(db, pTableName); if( z ){ if( (pIdx = sqlite3FindIndex(db, z, zDb))!=0 ){ analyzeTable(pParse, pIdx->pTable, pIdx); }else if( (pTab = sqlite3LocateTable(pParse, 0, z, zDb))!=0 ){ analyzeTable(pParse, pTab, 0); } sqlite3DbFree(db, z); } - } + } } - v = sqlite3GetVdbe(pParse); - if( v ) sqlite3VdbeAddOp0(v, OP_Expire); + if( db->nSqlExec==0 && (v = sqlite3GetVdbe(pParse))!=0 ){ + sqlite3VdbeAddOp0(v, OP_Expire); + } } /* ** Used to pass information from the analyzer reader through to the ** callback routine. @@ -1548,19 +1536,24 @@ } aiRowEst = pIndex->aiRowEst; #endif pIndex->bUnordered = 0; decodeIntArray((char*)z, nCol, aiRowEst, pIndex->aiRowLogEst, pIndex); - if( pIndex->pPartIdxWhere==0 ) pTable->nRowLogEst = pIndex->aiRowLogEst[0]; + pIndex->hasStat1 = 1; + if( pIndex->pPartIdxWhere==0 ){ + pTable->nRowLogEst = pIndex->aiRowLogEst[0]; + pTable->tabFlags |= TF_HasStat1; + } }else{ Index fakeIdx; fakeIdx.szIdxRow = pTable->szTabRow; #ifdef SQLITE_ENABLE_COSTMULT fakeIdx.pTable = pTable; #endif decodeIntArray((char*)z, 1, 0, &pTable->nRowLogEst, &fakeIdx); pTable->szTabRow = fakeIdx.szIdxRow; + pTable->tabFlags |= TF_HasStat1; } return 0; } @@ -1851,19 +1844,24 @@ int sqlite3AnalysisLoad(sqlite3 *db, int iDb){ analysisInfo sInfo; HashElem *i; char *zSql; int rc = SQLITE_OK; + Schema *pSchema = db->aDb[iDb].pSchema; assert( iDb>=0 && iDbnDb ); assert( db->aDb[iDb].pBt!=0 ); /* Clear any prior statistics */ assert( sqlite3SchemaMutexHeld(db, iDb, 0) ); - for(i=sqliteHashFirst(&db->aDb[iDb].pSchema->idxHash);i;i=sqliteHashNext(i)){ + for(i=sqliteHashFirst(&pSchema->tblHash); i; i=sqliteHashNext(i)){ + Table *pTab = sqliteHashData(i); + pTab->tabFlags &= ~TF_HasStat1; + } + for(i=sqliteHashFirst(&pSchema->idxHash); i; i=sqliteHashNext(i)){ Index *pIdx = sqliteHashData(i); - pIdx->aiRowLogEst[0] = 0; + pIdx->hasStat1 = 0; #ifdef SQLITE_ENABLE_STAT3_OR_STAT4 sqlite3DeleteIndexSamples(db, pIdx); pIdx->aSample = 0; #endif } @@ -1882,23 +1880,23 @@ } } /* Set appropriate defaults on all indexes not in the sqlite_stat1 table */ assert( sqlite3SchemaMutexHeld(db, iDb, 0) ); - for(i=sqliteHashFirst(&db->aDb[iDb].pSchema->idxHash);i;i=sqliteHashNext(i)){ + for(i=sqliteHashFirst(&pSchema->idxHash); i; i=sqliteHashNext(i)){ Index *pIdx = sqliteHashData(i); - if( pIdx->aiRowLogEst[0]==0 ) sqlite3DefaultRowEst(pIdx); + if( !pIdx->hasStat1 ) sqlite3DefaultRowEst(pIdx); } /* Load the statistics from the sqlite_stat4 table. */ #ifdef SQLITE_ENABLE_STAT3_OR_STAT4 if( rc==SQLITE_OK && OptimizationEnabled(db, SQLITE_Stat34) ){ db->lookaside.bDisable++; rc = loadStat4(db, sInfo.zDatabase); db->lookaside.bDisable--; } - for(i=sqliteHashFirst(&db->aDb[iDb].pSchema->idxHash);i;i=sqliteHashNext(i)){ + for(i=sqliteHashFirst(&pSchema->idxHash); i; i=sqliteHashNext(i)){ Index *pIdx = sqliteHashData(i); sqlite3_free(pIdx->aiRowEst); pIdx->aiRowEst = 0; } #endif Index: src/btree.c ================================================================== --- src/btree.c +++ src/btree.c @@ -5365,10 +5365,34 @@ ** have been deleted? This API will need to change to return an error code ** as well as the boolean result value. */ return (CURSOR_VALID!=pCur->eState); } + +/* +** Return an estimate for the number of rows in the table that pCur is +** pointing to. Return a negative number if no estimate is currently +** available. +*/ +i64 sqlite3BtreeRowCountEst(BtCursor *pCur){ + i64 n; + u8 i; + + assert( cursorOwnsBtShared(pCur) ); + assert( sqlite3_mutex_held(pCur->pBtree->db->mutex) ); + + /* Currently this interface is only called by the OP_IfSmaller + ** opcode, and it that case the cursor will always be valid and + ** will always point to a leaf node. */ + if( NEVER(pCur->eState!=CURSOR_VALID) ) return -1; + if( NEVER(pCur->apPage[pCur->iPage]->leaf==0) ) return -1; + + for(n=1, i=0; i<=pCur->iPage; i++){ + n *= pCur->apPage[i]->nCell; + } + return n; +} /* ** Advance the cursor to the next entry in the database. If ** successful then set *pRes=0. If the cursor ** was already pointing to the last entry in the database before Index: src/btree.h ================================================================== --- src/btree.h +++ src/btree.h @@ -294,10 +294,11 @@ const void *sqlite3BtreePayloadFetch(BtCursor*, u32 *pAmt); u32 sqlite3BtreePayloadSize(BtCursor*); char *sqlite3BtreeIntegrityCheck(Btree*, int *aRoot, int nRoot, int, int*); struct Pager *sqlite3BtreePager(Btree*); +i64 sqlite3BtreeRowCountEst(BtCursor*); #ifndef SQLITE_OMIT_INCRBLOB int sqlite3BtreePayloadChecked(BtCursor*, u32 offset, u32 amt, void*); int sqlite3BtreePutData(BtCursor*, u32 offset, u32 amt, void*); void sqlite3BtreeIncrblobCursor(BtCursor *); Index: src/build.c ================================================================== --- src/build.c +++ src/build.c @@ -3452,10 +3452,13 @@ /* 10, 9, 8, 7, 6 */ LogEst aVal[] = { 33, 32, 30, 28, 26 }; LogEst *a = pIdx->aiRowLogEst; int nCopy = MIN(ArraySize(aVal), pIdx->nKeyCol); int i; + + /* Indexes with default row estimates should not have stat1 data */ + assert( !pIdx->hasStat1 ); /* Set the first entry (number of rows in the index) to the estimated ** number of rows in the table, or half the number of rows in the table ** for a partial index. But do not let the estimate drop below 10. */ a[0] = pIdx->pTable->nRowLogEst; Index: src/pragma.c ================================================================== --- src/pragma.c +++ src/pragma.c @@ -1112,33 +1112,37 @@ } } } break; +#ifdef SQLITE_DEBUG case PragTyp_STATS: { Index *pIdx; HashElem *i; - pParse->nMem = 4; + pParse->nMem = 5; sqlite3CodeVerifySchema(pParse, iDb); for(i=sqliteHashFirst(&pDb->pSchema->tblHash); i; i=sqliteHashNext(i)){ Table *pTab = sqliteHashData(i); - sqlite3VdbeMultiLoad(v, 1, "ssii", + sqlite3VdbeMultiLoad(v, 1, "ssiii", pTab->zName, 0, pTab->szTabRow, - pTab->nRowLogEst); - sqlite3VdbeAddOp2(v, OP_ResultRow, 1, 4); + pTab->nRowLogEst, + pTab->tabFlags); + sqlite3VdbeAddOp2(v, OP_ResultRow, 1, 5); for(pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext){ - sqlite3VdbeMultiLoad(v, 2, "sii", + sqlite3VdbeMultiLoad(v, 2, "siii", pIdx->zName, pIdx->szIdxRow, - pIdx->aiRowLogEst[0]); - sqlite3VdbeAddOp2(v, OP_ResultRow, 1, 4); + pIdx->aiRowLogEst[0], + pIdx->hasStat1); + sqlite3VdbeAddOp2(v, OP_ResultRow, 1, 5); } } } break; +#endif case PragTyp_INDEX_INFO: if( zRight ){ Index *pIdx; Table *pTab; pIdx = sqlite3FindIndex(db, zRight, zDb); @@ -1855,10 +1859,122 @@ */ case PragTyp_SHRINK_MEMORY: { sqlite3_db_release_memory(db); break; } + + /* + ** PRAGMA optimize + ** PRAGMA optimize(MASK) + ** PRAGMA schema.optimize + ** PRAGMA schema.optimize(MASK) + ** + ** Attempt to optimize the database. All schemas are optimized in the first + ** two forms, and only the specified schema is optimized in the latter two. + ** + ** The details of optimizations performed by this pragma does are expected + ** to change and improve over time. Applications should anticipate that + ** this pragma will perform new optimizations in future releases. + ** + ** The optional argument is a bitmask of optimizations to perform: + ** + ** 0x0001 Debugging mode. Do not actually perform any optimizations + ** but instead return one line of text for each optimization + ** that would have been done. Off by default. + ** + ** 0x0002 Run ANALYZE on tables that might benefit. On by default. + ** See below for additional information. + ** + ** 0x0004 (Not yet implemented) Record usage and performance + ** information from the current session in the + ** database file so that it will be available to "optimize" + ** pragmas run by future database connections. + ** + ** 0x0008 (Not yet implemented) Create indexes that might have + ** been helpful to recent queries + ** + ** The default MASK is 0x000e, which means perform all of the optimizations + ** listed above except do not set Debug Mode. New optimizations may be + ** added in future releases but they will be turned off by default. The + ** default MASK will always be 0x0e. + ** + ** DETERMINATION OF WHEN TO RUN ANALYZE + ** + ** In the current implementation, a table is analyzed if only if all of + ** the following are true: + ** + ** (1) MASK bit 0x02 is set. + ** + ** (2) The query planner used sqlite_stat1-style statistics for one or + ** more indexes of the table at some point during the lifetime of + ** the current connection. + ** + ** (3) One or more indexes of the table are currently unanalyzed OR + ** the number of rows in the table has increased by 25 times or more + ** since the last time ANALYZE was run. + ** + ** The rules for when tables are analyzed are likely to change in + ** future releases. + */ + case PragTyp_OPTIMIZE: { + int iDbLast; /* Loop termination point for the schema loop */ + int iTabCur; /* Cursor for a table whose size needs checking */ + HashElem *k; /* Loop over tables of a schema */ + Schema *pSchema; /* The current schema */ + Table *pTab; /* A table in the schema */ + Index *pIdx; /* An index of the table */ + LogEst szThreshold; /* Size threshold above which reanalysis is needd */ + char *zSubSql; /* SQL statement for the OP_SqlExec opcode */ + u32 opMask; /* Mask of operations to perform */ + + if( zRight ){ + opMask = (u32)sqlite3Atoi(zRight); + if( (opMask & 0x02)==0 ) break; + }else{ + opMask = 0xe; + } + iTabCur = pParse->nTab++; + for(iDbLast = zDb?iDb:db->nDb-1; iDb<=iDbLast; iDb++){ + if( iDb==1 ) continue; + sqlite3CodeVerifySchema(pParse, iDb); + pSchema = db->aDb[iDb].pSchema; + for(k=sqliteHashFirst(&pSchema->tblHash); k; k=sqliteHashNext(k)){ + pTab = (Table*)sqliteHashData(k); + + /* If table pTab has not been used in a way that would benefit from + ** having analysis statistics during the current session, then skip it. + ** This also has the effect of skipping virtual tables and views */ + if( (pTab->tabFlags & TF_StatsUsed)==0 ) continue; + + /* Reanalyze if the table is 25 times larger than the last analysis */ + szThreshold = pTab->nRowLogEst + 46; assert( sqlite3LogEst(25)==46 ); + for(pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext){ + if( !pIdx->hasStat1 ){ + szThreshold = 0; /* Always analyze if any index lacks statistics */ + break; + } + } + if( szThreshold ){ + sqlite3OpenTable(pParse, iTabCur, iDb, pTab, OP_OpenRead); + sqlite3VdbeAddOp3(v, OP_IfSmaller, iTabCur, + sqlite3VdbeCurrentAddr(v)+2+(opMask&1), szThreshold); + VdbeCoverage(v); + } + zSubSql = sqlite3MPrintf(db, "ANALYZE \"%w\".\"%w\"", + db->aDb[iDb].zDbSName, pTab->zName); + if( opMask & 0x01 ){ + int r1 = sqlite3GetTempReg(pParse); + sqlite3VdbeAddOp4(v, OP_String8, 0, r1, 0, zSubSql, P4_DYNAMIC); + sqlite3VdbeAddOp2(v, OP_ResultRow, r1, 1); + }else{ + sqlite3VdbeAddOp4(v, OP_SqlExec, 0, 0, 0, zSubSql, P4_DYNAMIC); + } + } + } + sqlite3VdbeAddOp0(v, OP_Expire); + break; + } /* ** PRAGMA busy_timeout ** PRAGMA busy_timeout = N ** Index: src/pragma.h ================================================================== --- src/pragma.h +++ src/pragma.h @@ -28,15 +28,15 @@ #define PragTyp_JOURNAL_SIZE_LIMIT 20 #define PragTyp_LOCK_PROXY_FILE 21 #define PragTyp_LOCKING_MODE 22 #define PragTyp_PAGE_COUNT 23 #define PragTyp_MMAP_SIZE 24 -#define PragTyp_PAGE_SIZE 25 -#define PragTyp_SECURE_DELETE 26 -#define PragTyp_SHRINK_MEMORY 27 -#define PragTyp_SOFT_HEAP_LIMIT 28 -#define PragTyp_STATS 29 +#define PragTyp_OPTIMIZE 25 +#define PragTyp_PAGE_SIZE 26 +#define PragTyp_SECURE_DELETE 27 +#define PragTyp_SHRINK_MEMORY 28 +#define PragTyp_SOFT_HEAP_LIMIT 29 #define PragTyp_SYNCHRONOUS 30 #define PragTyp_TABLE_INFO 31 #define PragTyp_TEMP_STORE 32 #define PragTyp_TEMP_STORE_DIRECTORY 33 #define PragTyp_THREADS 34 @@ -46,10 +46,11 @@ #define PragTyp_HEXKEY 38 #define PragTyp_KEY 39 #define PragTyp_REKEY 40 #define PragTyp_LOCK_STATUS 41 #define PragTyp_PARSER_TRACE 42 +#define PragTyp_STATS 43 /* Property flags associated with various pragma. */ #define PragFlg_NeedSchema 0x01 /* Force schema load before running */ #define PragFlg_NoColumns 0x02 /* OP_ResultRow called with zero columns */ #define PragFlg_NoColumns1 0x04 /* zero columns if RHS argument is present */ @@ -69,51 +70,52 @@ /* 2 */ "name", /* 3 */ "type", /* 4 */ "notnull", /* 5 */ "dflt_value", /* 6 */ "pk", - /* 7 */ "table", /* Used by: stats */ - /* 8 */ "index", - /* 9 */ "width", - /* 10 */ "height", - /* 11 */ "seqno", /* Used by: index_info */ - /* 12 */ "cid", - /* 13 */ "name", - /* 14 */ "seqno", /* Used by: index_xinfo */ - /* 15 */ "cid", - /* 16 */ "name", - /* 17 */ "desc", - /* 18 */ "coll", - /* 19 */ "key", - /* 20 */ "seq", /* Used by: index_list */ - /* 21 */ "name", - /* 22 */ "unique", - /* 23 */ "origin", - /* 24 */ "partial", - /* 25 */ "seq", /* Used by: database_list */ - /* 26 */ "name", - /* 27 */ "file", - /* 28 */ "seq", /* Used by: collation_list */ - /* 29 */ "name", - /* 30 */ "id", /* Used by: foreign_key_list */ - /* 31 */ "seq", - /* 32 */ "table", - /* 33 */ "from", - /* 34 */ "to", - /* 35 */ "on_update", - /* 36 */ "on_delete", - /* 37 */ "match", - /* 38 */ "table", /* Used by: foreign_key_check */ - /* 39 */ "rowid", - /* 40 */ "parent", - /* 41 */ "fkid", - /* 42 */ "busy", /* Used by: wal_checkpoint */ - /* 43 */ "log", - /* 44 */ "checkpointed", - /* 45 */ "timeout", /* Used by: busy_timeout */ - /* 46 */ "database", /* Used by: lock_status */ - /* 47 */ "status", + /* 7 */ "tbl", /* Used by: stats */ + /* 8 */ "idx", + /* 9 */ "wdth", + /* 10 */ "hght", + /* 11 */ "flgs", + /* 12 */ "seqno", /* Used by: index_info */ + /* 13 */ "cid", + /* 14 */ "name", + /* 15 */ "seqno", /* Used by: index_xinfo */ + /* 16 */ "cid", + /* 17 */ "name", + /* 18 */ "desc", + /* 19 */ "coll", + /* 20 */ "key", + /* 21 */ "seq", /* Used by: index_list */ + /* 22 */ "name", + /* 23 */ "unique", + /* 24 */ "origin", + /* 25 */ "partial", + /* 26 */ "seq", /* Used by: database_list */ + /* 27 */ "name", + /* 28 */ "file", + /* 29 */ "seq", /* Used by: collation_list */ + /* 30 */ "name", + /* 31 */ "id", /* Used by: foreign_key_list */ + /* 32 */ "seq", + /* 33 */ "table", + /* 34 */ "from", + /* 35 */ "to", + /* 36 */ "on_update", + /* 37 */ "on_delete", + /* 38 */ "match", + /* 39 */ "table", /* Used by: foreign_key_check */ + /* 40 */ "rowid", + /* 41 */ "parent", + /* 42 */ "fkid", + /* 43 */ "busy", /* Used by: wal_checkpoint */ + /* 44 */ "log", + /* 45 */ "checkpointed", + /* 46 */ "timeout", /* Used by: busy_timeout */ + /* 47 */ "database", /* Used by: lock_status */ + /* 48 */ "status", }; /* Definitions of all built-in pragmas */ typedef struct PragmaName { const char *const zName; /* Name of pragma */ @@ -155,11 +157,11 @@ #endif #endif {/* zName: */ "busy_timeout", /* ePragTyp: */ PragTyp_BUSY_TIMEOUT, /* ePragFlg: */ PragFlg_Result0, - /* ColNames: */ 45, 1, + /* ColNames: */ 46, 1, /* iArg: */ 0 }, #if !defined(SQLITE_OMIT_PAGER_PRAGMAS) {/* zName: */ "cache_size", /* ePragTyp: */ PragTyp_CACHE_SIZE, /* ePragFlg: */ PragFlg_NeedSchema|PragFlg_Result0|PragFlg_SchemaReq|PragFlg_NoColumns1, @@ -192,11 +194,11 @@ #endif #if !defined(SQLITE_OMIT_SCHEMA_PRAGMAS) {/* zName: */ "collation_list", /* ePragTyp: */ PragTyp_COLLATION_LIST, /* ePragFlg: */ PragFlg_Result0, - /* ColNames: */ 28, 2, + /* ColNames: */ 29, 2, /* iArg: */ 0 }, #endif #if !defined(SQLITE_OMIT_COMPILEOPTION_DIAGS) {/* zName: */ "compile_options", /* ePragTyp: */ PragTyp_COMPILE_OPTIONS, @@ -227,11 +229,11 @@ #endif #if !defined(SQLITE_OMIT_SCHEMA_PRAGMAS) {/* zName: */ "database_list", /* ePragTyp: */ PragTyp_DATABASE_LIST, /* ePragFlg: */ PragFlg_NeedSchema|PragFlg_Result0, - /* ColNames: */ 25, 3, + /* ColNames: */ 26, 3, /* iArg: */ 0 }, #endif #if !defined(SQLITE_OMIT_PAGER_PRAGMAS) && !defined(SQLITE_OMIT_DEPRECATED) {/* zName: */ "default_cache_size", /* ePragTyp: */ PragTyp_DEFAULT_CACHE_SIZE, @@ -264,18 +266,18 @@ #endif #if !defined(SQLITE_OMIT_FOREIGN_KEY) && !defined(SQLITE_OMIT_TRIGGER) {/* zName: */ "foreign_key_check", /* ePragTyp: */ PragTyp_FOREIGN_KEY_CHECK, /* ePragFlg: */ PragFlg_NeedSchema, - /* ColNames: */ 38, 4, + /* ColNames: */ 39, 4, /* iArg: */ 0 }, #endif #if !defined(SQLITE_OMIT_FOREIGN_KEY) {/* zName: */ "foreign_key_list", /* ePragTyp: */ PragTyp_FOREIGN_KEY_LIST, /* ePragFlg: */ PragFlg_NeedSchema|PragFlg_Result1|PragFlg_SchemaOpt, - /* ColNames: */ 30, 8, + /* ColNames: */ 31, 8, /* iArg: */ 0 }, #endif #if !defined(SQLITE_OMIT_FLAG_PRAGMAS) #if !defined(SQLITE_OMIT_FOREIGN_KEY) && !defined(SQLITE_OMIT_TRIGGER) {/* zName: */ "foreign_keys", @@ -334,21 +336,21 @@ #endif #if !defined(SQLITE_OMIT_SCHEMA_PRAGMAS) {/* zName: */ "index_info", /* ePragTyp: */ PragTyp_INDEX_INFO, /* ePragFlg: */ PragFlg_NeedSchema|PragFlg_Result1|PragFlg_SchemaOpt, - /* ColNames: */ 11, 3, + /* ColNames: */ 12, 3, /* iArg: */ 0 }, {/* zName: */ "index_list", /* ePragTyp: */ PragTyp_INDEX_LIST, /* ePragFlg: */ PragFlg_NeedSchema|PragFlg_Result1|PragFlg_SchemaOpt, - /* ColNames: */ 20, 5, + /* ColNames: */ 21, 5, /* iArg: */ 0 }, {/* zName: */ "index_xinfo", /* ePragTyp: */ PragTyp_INDEX_INFO, /* ePragFlg: */ PragFlg_NeedSchema|PragFlg_Result1|PragFlg_SchemaOpt, - /* ColNames: */ 14, 6, + /* ColNames: */ 15, 6, /* iArg: */ 1 }, #endif #if !defined(SQLITE_OMIT_INTEGRITY_CHECK) {/* zName: */ "integrity_check", /* ePragTyp: */ PragTyp_INTEGRITY_CHECK, @@ -391,11 +393,11 @@ #endif #if defined(SQLITE_DEBUG) || defined(SQLITE_TEST) {/* zName: */ "lock_status", /* ePragTyp: */ PragTyp_LOCK_STATUS, /* ePragFlg: */ PragFlg_Result0, - /* ColNames: */ 46, 2, + /* ColNames: */ 47, 2, /* iArg: */ 0 }, #endif #if !defined(SQLITE_OMIT_PAGER_PRAGMAS) {/* zName: */ "locking_mode", /* ePragTyp: */ PragTyp_LOCKING_MODE, @@ -410,10 +412,17 @@ {/* zName: */ "mmap_size", /* ePragTyp: */ PragTyp_MMAP_SIZE, /* ePragFlg: */ 0, /* ColNames: */ 0, 0, /* iArg: */ 0 }, +#endif + {/* zName: */ "optimize", + /* ePragTyp: */ PragTyp_OPTIMIZE, + /* ePragFlg: */ PragFlg_Result1, + /* ColNames: */ 0, 0, + /* iArg: */ 0 }, +#if !defined(SQLITE_OMIT_PAGER_PRAGMAS) {/* zName: */ "page_count", /* ePragTyp: */ PragTyp_PAGE_COUNT, /* ePragFlg: */ PragFlg_NeedSchema|PragFlg_Result0|PragFlg_SchemaReq, /* ColNames: */ 0, 0, /* iArg: */ 0 }, @@ -508,15 +517,15 @@ /* ePragFlg: */ PragFlg_Result0|PragFlg_NoColumns1, /* ColNames: */ 0, 0, /* iArg: */ SQLITE_SqlTrace }, #endif #endif -#if !defined(SQLITE_OMIT_SCHEMA_PRAGMAS) +#if !defined(SQLITE_OMIT_SCHEMA_PRAGMAS) && defined(SQLITE_DEBUG) {/* zName: */ "stats", /* ePragTyp: */ PragTyp_STATS, /* ePragFlg: */ PragFlg_NeedSchema|PragFlg_Result0|PragFlg_SchemaReq, - /* ColNames: */ 7, 4, + /* ColNames: */ 7, 5, /* iArg: */ 0 }, #endif #if !defined(SQLITE_OMIT_PAGER_PRAGMAS) {/* zName: */ "synchronous", /* ePragTyp: */ PragTyp_SYNCHRONOUS, @@ -591,11 +600,11 @@ /* ColNames: */ 0, 0, /* iArg: */ 0 }, {/* zName: */ "wal_checkpoint", /* ePragTyp: */ PragTyp_WAL_CHECKPOINT, /* ePragFlg: */ PragFlg_NeedSchema, - /* ColNames: */ 42, 3, + /* ColNames: */ 43, 3, /* iArg: */ 0 }, #endif #if !defined(SQLITE_OMIT_FLAG_PRAGMAS) {/* zName: */ "writable_schema", /* ePragTyp: */ PragTyp_FLAG, @@ -602,6 +611,6 @@ /* ePragFlg: */ PragFlg_Result0|PragFlg_NoColumns1, /* ColNames: */ 0, 0, /* iArg: */ SQLITE_WriteSchema|SQLITE_RecoveryMode }, #endif }; -/* Number of pragmas: 60 on by default, 73 total. */ +/* Number of pragmas: 60 on by default, 74 total. */ Index: src/sqliteInt.h ================================================================== --- src/sqliteInt.h +++ src/sqliteInt.h @@ -1331,10 +1331,11 @@ u8 suppressErr; /* Do not issue error messages if true */ u8 vtabOnConflict; /* Value to return for s3_vtab_on_conflict() */ u8 isTransactionSavepoint; /* True if the outermost savepoint is a TS */ u8 mTrace; /* zero or more SQLITE_TRACE flags */ u8 skipBtreeMutex; /* True if no shared-cache backends */ + u8 nSqlExec; /* Number of pending OP_SqlExec opcodes */ int nextPagesize; /* Pagesize after VACUUM if >0 */ u32 magic; /* Magic number for detect library misuse */ int nChange; /* Value returned by sqlite3_changes() */ int nTotalChange; /* Value returned by sqlite3_total_changes() */ int aLimit[SQLITE_N_LIMIT]; /* Limits */ @@ -1881,16 +1882,17 @@ */ #define TF_Readonly 0x0001 /* Read-only system table */ #define TF_Ephemeral 0x0002 /* An ephemeral table */ #define TF_HasPrimaryKey 0x0004 /* Table has a primary key */ #define TF_Autoincrement 0x0008 /* Integer primary key is autoincrement */ -/* available for reuse: 0x0010 */ +#define TF_HasStat1 0x0010 /* nRowLogEst set from sqlite_stat1 */ #define TF_WithoutRowid 0x0020 /* No rowid. PRIMARY KEY is the key */ #define TF_NoVisibleRowid 0x0040 /* No user-visible "rowid" column */ #define TF_OOOHidden 0x0080 /* Out-of-Order hidden columns */ -#define TF_HasNotNull 0x0100 /* Contains NOT NULL constraints */ - +#define TF_StatsUsed 0x0100 /* Query planner decisions affected by + ** Index.aiRowLogEst[] values */ +#define TF_HasNotNull 0x0200 /* Contains NOT NULL constraints */ /* ** Test to see whether or not a table is a virtual table. This is ** done as a macro so that it will be optimized out when virtual ** table support is omitted from the build. @@ -2129,10 +2131,11 @@ unsigned bUnordered:1; /* Use this index for == or IN queries only */ unsigned uniqNotNull:1; /* True if UNIQUE and NOT NULL for all columns */ unsigned isResized:1; /* True if resizeIndexObject() has been called */ unsigned isCovering:1; /* True if this is a covering index */ unsigned noSkipScan:1; /* Do not try to use skip-scan if true */ + unsigned hasStat1:1; /* aiRowLogEst values come from sqlite_stat1 */ #ifdef SQLITE_ENABLE_STAT3_OR_STAT4 int nSample; /* Number of elements in aSample[] */ int nSampleCol; /* Size of IndexSample.anEq[] and so on */ tRowcnt *aAvgEq; /* Average nEq values for keys not in aSample */ IndexSample *aSample; /* Samples of the left-most key */ Index: src/vdbe.c ================================================================== --- src/vdbe.c +++ src/vdbe.c @@ -4845,10 +4845,37 @@ assert( pOp->p2==0 ); } break; } +/* Opcode: IfSmaller P1 P2 P3 * * +** +** Estimate the number of rows in the table P1. Jump to P2 if that +** estimate is less than approximately 2**(0.1*P3). +*/ +case OP_IfSmaller: { /* jump */ + VdbeCursor *pC; + BtCursor *pCrsr; + int res; + i64 sz; + + assert( pOp->p1>=0 && pOp->p1nCursor ); + pC = p->apCsr[pOp->p1]; + assert( pC!=0 ); + pCrsr = pC->uc.pCursor; + assert( pCrsr ); + rc = sqlite3BtreeFirst(pCrsr, &res); + if( rc ) goto abort_due_to_error; + if( res==0 ){ + sz = sqlite3BtreeRowCountEst(pCrsr); + if( ALWAYS(sz>=0) && sqlite3LogEst((u64)sz)p3 ) res = 1; + } + VdbeBranchTaken(res!=0,2); + if( res ) goto jump_to_p2; + break; +} + /* Opcode: SorterSort P1 P2 * * * ** ** After all records have been inserted into the Sorter object ** identified by P1, invoke this opcode to actually do the sorting. @@ -5488,10 +5515,22 @@ rc = sqlite3BtreeCreateTable(pDb->pBt, &pgno, flags); if( rc ) goto abort_due_to_error; pOut->u.i = pgno; break; } + +/* Opcode: SqlExec * * * P4 * +** +** Run the SQL statement or statements specified in the P4 string. +*/ +case OP_SqlExec: { + db->nSqlExec++; + rc = sqlite3_exec(db, pOp->p4.z, 0, 0, 0); + db->nSqlExec--; + if( rc ) goto abort_due_to_error; + break; +} /* Opcode: ParseSchema P1 * * P4 * ** ** Read and parse all entries from the SQLITE_MASTER table of database P1 ** that match the WHERE clause P4. Index: src/where.c ================================================================== --- src/where.c +++ src/where.c @@ -2385,10 +2385,15 @@ testcase( eOp & WO_IS ); testcase( eOp & WO_ISNULL ); continue; } + if( IsUniqueIndex(pProbe) && saved_nEq==pProbe->nKeyCol-1 ){ + pBuilder->bldFlags |= SQLITE_BLDF_UNIQUE; + }else{ + pBuilder->bldFlags |= SQLITE_BLDF_INDEXED; + } pNew->wsFlags = saved_wsFlags; pNew->u.btree.nEq = saved_nEq; pNew->u.btree.nBtm = saved_nBtm; pNew->u.btree.nTop = saved_nTop; pNew->nLTerm = saved_nLTerm; @@ -2932,11 +2937,19 @@ pNew->nOut = rSize; if( rc ) break; } } + pBuilder->bldFlags = 0; rc = whereLoopAddBtreeIndex(pBuilder, pSrc, pProbe, 0); + if( pBuilder->bldFlags==SQLITE_BLDF_INDEXED ){ + /* If a non-unique index is used, or if a prefix of the key for + ** unique index is used (making the index functionally non-unique) + ** then the sqlite_stat1 data becomes important for scoring the + ** plan */ + pTab->tabFlags |= TF_StatsUsed; + } #ifdef SQLITE_ENABLE_STAT3_OR_STAT4 sqlite3Stat4ProbeFree(pBuilder->pRec); pBuilder->nRecValid = 0; pBuilder->pRec = 0; #endif Index: src/whereInt.h ================================================================== --- src/whereInt.h +++ src/whereInt.h @@ -393,12 +393,17 @@ WhereOrSet *pOrSet; /* Record best loops here, if not NULL */ #ifdef SQLITE_ENABLE_STAT3_OR_STAT4 UnpackedRecord *pRec; /* Probe for stat4 (if required) */ int nRecValid; /* Number of valid fields currently in pRec */ #endif + unsigned int bldFlags; /* SQLITE_BLDF_* flags */ }; +/* Allowed values for WhereLoopBuider.bldFlags */ +#define SQLITE_BLDF_INDEXED 0x0001 /* An index is used */ +#define SQLITE_BLDF_UNIQUE 0x0002 /* All keys of a UNIQUE index used */ + /* ** The WHERE clause processing routine has two halves. The ** first part does the start of the WHERE loop and the second ** half does the tail of the WHERE loop. An instance of ** this structure is returned by the first half and passed ADDED test/autoanalyze1.test Index: test/autoanalyze1.test ================================================================== --- /dev/null +++ test/autoanalyze1.test @@ -0,0 +1,123 @@ +# 2017-02-17 +# +# The author disclaims copyright to this source code. In place of +# a legal notice, here is a blessing: +# +# May you do good and not evil. +# May you find forgiveness for yourself and forgive others. +# May you share freely, never taking more than you give. +# +#*********************************************************************** +# +# This file implements tests for the logic used to estimate when +# running ANALYZE would be beneficial. +# +# Note that this test uses some hard-coded bitmask values from sqliteInt.h. +# If any of the following constants changes: +# +# define TF_HasStat1 0x0010 +# define TF_StatsUsed 0x0100 +# +# then some of the magic numbers in test results below might need to be +# adjusted. +# + +set testdir [file dirname $argv0] +source $testdir/tester.tcl + +# There is nothing to test if ANALYZE is disable for this build. +# These tests also use "PRAGMA stats" which are only enabled for +# debugging builds. +# +ifcapable {!debug || !analyze} { + finish_test + return +} + +do_execsql_test autoanalyze1-100 { + -- Build up a test table with some indexes + CREATE TABLE t1(a INTEGER PRIMARY KEY, b, c, d); + CREATE UNIQUE INDEX t1bc ON t1(b,c); + CREATE INDEX t1d ON t1(d); + WITH RECURSIVE c(x) AS (VALUES(1) UNION ALL SELECT x+1 FROM c WHERE x<100) + INSERT INTO t1(a,b,c,d) SELECT x, x, x, x FROM c; + -- Verify that the hasStat1 flag is clear on on indexes + SELECT idx, flgs FROM pragma_stats + WHERE idx IS NOT NULL + ORDER BY idx; + -- Verify that the TF_HasStat1 flag is clear on the table + SELECT tbl, (flgs & 0x10)!=0 FROM pragma_stats WHERE tbl='t1' AND idx IS NULL; +} {t1bc 0 t1d 0 t1 0} + +# No use of stat1 recorded so far +do_execsql_test autoanalyze1-110 { + SELECT (flgs & 0x0100)!=0 FROM pragma_stats WHERE tbl='t1' AND idx IS NULL; +} {0} + +# Access using a unique index does not set the TF_StatsUsed flag. +# +do_execsql_test autoanalyze1-200 { + SELECT * FROM t1 WHERE a=55; +} {55 55 55 55} +do_execsql_test autoanalyze1-201 { + SELECT (flgs & 0x0100)!=0 FROM pragma_stats WHERE tbl='t1' AND idx IS NULL; +} {0} + +do_execsql_test autoanalyze1-210 { + SELECT * FROM t1 WHERE a IN (55,199,299); +} {55 55 55 55} +do_execsql_test autoanalyze1-211 { + SELECT (flgs & 0x0100)!=0 FROM pragma_stats WHERE tbl='t1' AND idx IS NULL; +} {0} + +do_execsql_test autoanalyze1-220 { + SELECT * FROM t1 WHERE (b,c)=(45,45); +} {45 45 45 45} +do_execsql_test autoanalyze1-221 { + SELECT (flgs & 0x0100)!=0 FROM pragma_stats WHERE tbl='t1' AND idx IS NULL; +} {0} + +# Any use of the non-unique t1d index triggers the use of stats. +# +sqlite3 db test.db +do_execsql_test autoanalyze1-300 { + SELECT * FROM t1 WHERE d=45; +} {45 45 45 45} +do_execsql_test autoanalyze1-301 { + SELECT (flgs & 0x0100)!=0 FROM pragma_stats WHERE tbl='t1' AND idx IS NULL; +} {1} + +sqlite3 db test.db +do_execsql_test autoanalyze1-310 { + SELECT * FROM t1 WHERE d=45 AND a=45; +} {45 45 45 45} +do_execsql_test autoanalyze1-311 { + SELECT (flgs & 0x0100)!=0 FROM pragma_stats WHERE tbl='t1' AND idx IS NULL; +} {0} ;# The ROWID lookup short-circuits the d=45 constraint + +sqlite3 db test.db +do_execsql_test autoanalyze1-320 { + SELECT * FROM t1 WHERE d=45 AND a IN (45,46); +} {45 45 45 45} +do_execsql_test autoanalyze1-321 { + SELECT (flgs & 0x0100)!=0 FROM pragma_stats WHERE tbl='t1' AND idx IS NULL; +} {1} + +# Any use of prefix of a unique index triggers the use of stats +# +sqlite3 db test.db +do_execsql_test autoanalyze1-400 { + SELECT * FROM t1 WHERE b=45; +} {45 45 45 45} +do_execsql_test autoanalyze1-401 { + SELECT (flgs & 0x0100)!=0 FROM pragma_stats WHERE tbl='t1' AND idx IS NULL; +} {1} + +# The TF_StatsUsed flag is reset when the database is reopened +# +sqlite3 db test.db +do_execsql_test autoanalyze1-500 { + SELECT (flgs & 0x0100)!=0 FROM pragma_stats WHERE tbl='t1' AND idx IS NULL; +} {0} + +finish_test Index: tool/mkpragmatab.tcl ================================================================== --- tool/mkpragmatab.tcl +++ tool/mkpragmatab.tcl @@ -223,12 +223,12 @@ COLS: cid name type notnull dflt_value pk IF: !defined(SQLITE_OMIT_SCHEMA_PRAGMAS) NAME: stats FLAG: NeedSchema Result0 SchemaReq - COLS: table index width height - IF: !defined(SQLITE_OMIT_SCHEMA_PRAGMAS) + COLS: tbl idx wdth hght flgs + IF: !defined(SQLITE_OMIT_SCHEMA_PRAGMAS) && defined(SQLITE_DEBUG) NAME: index_info TYPE: INDEX_INFO ARG: 0 FLAG: NeedSchema Result1 SchemaOpt @@ -359,10 +359,13 @@ NAME: soft_heap_limit FLAG: Result0 NAME: threads FLAG: Result0 + + NAME: optimize + FLAG: Result1 } # Open the output file # set destfile "[file dir [file dir [file normal $argv0]]]/src/pragma.h"