From 553818a0aaffdbf62fd038eb0a7d3dd3e830cf59 Mon Sep 17 00:00:00 2001 From: drh Date: Wed, 23 Jul 2014 18:36:55 +0000 Subject: [PATCH] Improve the performance of the ANALYZE command by taking advantage of the fact that every row of a UNIQUE index is distinct. FossilOrigin-Name: 3e1e79e1335f7ad33cd35f384f2a063c4aa2253b --- manifest | 12 ++--- manifest.uuid | 2 +- src/analyze.c | 124 +++++++++++++++++++++++++++++--------------------- 3 files changed, 79 insertions(+), 59 deletions(-) diff --git a/manifest b/manifest index 449ddf282d..f572551082 100644 --- a/manifest +++ b/manifest @@ -1,5 +1,5 @@ -C Updated\sdocumentation\son\ssqlite3_temp_directory.\s\sNo\schanges\sto\scode. -D 2014-07-23T15:51:29.276 +C Improve\sthe\sperformance\sof\sthe\sANALYZE\scommand\sby\staking\sadvantage\sof\sthe\nfact\sthat\severy\srow\sof\sa\sUNIQUE\sindex\sis\sdistinct. +D 2014-07-23T18:36:55.821 F Makefile.arm-wince-mingw32ce-gcc d6df77f1f48d690bd73162294bbba7f59507c72f F Makefile.in 5eb79e334a5de69c87740edd56af6527dd219308 F Makefile.linux-gcc 91d710bdc4998cb015f39edf3cb314ec4f4d7e23 @@ -161,7 +161,7 @@ F sqlite.pc.in 42b7bf0d02e08b9e77734a47798d1a55a9e0716b F sqlite3.1 3d8b83c91651f53472ca17599dae3457b8b89494 F sqlite3.pc.in 48fed132e7cb71ab676105d2a4dc77127d8c1f3a F src/alter.c b00900877f766f116f9e16116f1ccacdc21d82f1 -F src/analyze.c ab1e44158c25490bfda557f3d690217b2bb509e2 +F src/analyze.c c72bb66997ebdbc76936cfe96d7cc9da6ffe2b22 F src/attach.c 3801129015ef59d76bf23c95ef9b0069d18a0c52 F src/auth.c 523da7fb4979469955d822ff9298352d6b31de34 F src/backup.c a729e63cf5cd1829507cb7b8e89f99b95141bb53 @@ -1183,7 +1183,7 @@ F tool/vdbe_profile.tcl 67746953071a9f8f2f668b73fe899074e2c6d8c1 F tool/warnings-clang.sh f6aa929dc20ef1f856af04a730772f59283631d4 F tool/warnings.sh 0abfd78ceb09b7f7c27c688c8e3fe93268a13b32 F tool/win/sqlite.vsix deb315d026cc8400325c5863eef847784a219a2f -P 48f40861db4fbd10725a2b8b606d44fe16d5bd27 -R c881f4a63c48850e8a9fb96aa00a5db1 +P e6225a7bf77a700b318563b1a854b4b3a9e031e1 +R fa098d0ebca9c403761ac8a7cdd64221 U drh -Z cd8aaf55159956edb23d2b840f6a6165 +Z 71590bde4580a255d675842a920968d0 diff --git a/manifest.uuid b/manifest.uuid index 470adaeeca..2ecff9239b 100644 --- a/manifest.uuid +++ b/manifest.uuid @@ -1 +1 @@ -e6225a7bf77a700b318563b1a854b4b3a9e031e1 \ No newline at end of file +3e1e79e1335f7ad33cd35f384f2a063c4aa2253b \ No newline at end of file diff --git a/src/analyze.c b/src/analyze.c index c614350ae3..2c31f81f3a 100644 --- a/src/analyze.c +++ b/src/analyze.c @@ -371,15 +371,20 @@ static void stat4Destructor(void *pOld){ /* ** Implementation of the stat_init(N,K,C) SQL function. The three parameters ** are: -** N: The number of columns in the index including the rowid/pk -** K: The number of columns in the index excluding the rowid/pk -** C: The number of rows in the index +** N: The number of columns in the index including the rowid/pk (note 1) +** K: The number of columns in the index excluding the rowid/pk. +** C: The number of rows in the index (note 2) ** -** C is only used for STAT3 and STAT4. +** Note 1: In the special case of the covering index that implements a +** WITHOUT ROWID table, N is the number of PRIMARY KEY columns, not the +** total number of columns in the table. ** -** For ordinary rowid tables, N==K+1. But for WITHOUT ROWID tables, -** N=K+P where P is the number of columns in the primary key. For the -** covering index that implements the original WITHOUT ROWID table, N==K. +** Note 2: C is only used for STAT3 and STAT4. +** +** For indexes on ordinary rowid tables, N==K+1. But for indexes on +** WITHOUT ROWID tables, N=K+P where P is the number of columns in the +** PRIMARY KEY of the table. The covering index that implements the +** original WITHOUT ROWID table as N==K as a special case. ** ** This routine allocates the Stat4Accum object in heap memory. The return ** value is a pointer to the the Stat4Accum object encoded as a blob (i.e. @@ -689,7 +694,10 @@ static void samplePushPrevious(Stat4Accum *p, int iChng){ ** R Rowid for the current row. Might be a key record for ** WITHOUT ROWID tables. ** -** The SQL function always returns NULL. +** This SQL function always returns NULL. It's purpose it to accumulate +** statistical data and/or samples in the Stat4Accum object about the +** index being analyzed. The stat_get() SQL function will later be used to +** extract relevant information for constructing the sqlite_statN tables. ** ** The R parameter is only used for STAT3 and STAT4 */ @@ -783,7 +791,10 @@ static const FuncDef statPushFuncdef = { /* ** Implementation of the stat_get(P,J) SQL function. This routine is -** used to query the results. Content is returned for parameter J +** used to query statistical information that has been gathered into +** the Stat4Accum object by prior calls to stat_push(). The P parameter +** is a BLOB which is decoded into a pointer to the Stat4Accum objects. +** The content to returned is determined by the parameter J ** which is one of the STAT_GET_xxxx values defined above. ** ** If neither STAT3 nor STAT4 are enabled, then J is always @@ -1002,23 +1013,25 @@ static void analyzeOneTable( sqlite3VdbeAddOp4(v, OP_String8, 0, regTabname, 0, pTab->zName, 0); for(pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext){ - int nCol; /* Number of columns indexed by pIdx */ + int nCol; /* Number of columns in pIdx. "N" */ int *aGotoChng; /* Array of jump instruction addresses */ int addrRewind; /* Address of "OP_Rewind iIdxCur" */ - int addrGotoChng0; /* Address of "Goto addr_chng_0" */ int addrNextRow; /* Address of "next_row:" */ const char *zIdxName; /* Name of the index */ + int nColTest; /* Number of columns to test for changes */ if( pOnlyIdx && pOnlyIdx!=pIdx ) continue; if( pIdx->pPartIdxWhere==0 ) needTableCnt = 0; if( !HasRowid(pTab) && IsPrimaryKeyIndex(pIdx) ){ nCol = pIdx->nKeyCol; zIdxName = pTab->zName; + nColTest = nCol - 1; }else{ nCol = pIdx->nColumn; zIdxName = pIdx->zName; + nColTest = pIdx->onError==OE_None ? nCol-1 : pIdx->nKeyCol-1; } - aGotoChng = sqlite3DbMallocRaw(db, sizeof(int)*(nCol+1)); + aGotoChng = sqlite3DbMallocRaw(db, sizeof(int)*(nColTest+1)); if( aGotoChng==0 ) continue; /* Populate the register containing the index name. */ @@ -1061,7 +1074,7 @@ static void analyzeOneTable( ** the regPrev array and a trailing rowid (the rowid slot is required ** when building a record to insert into the sample column of ** the sqlite_stat4 table. */ - pParse->nMem = MAX(pParse->nMem, regPrev+nCol); + pParse->nMem = MAX(pParse->nMem, regPrev+nColTest); /* Open a read-only cursor on the index being analyzed. */ assert( iDb==sqlite3SchemaToIndex(db, pIdx->pSchema) ); @@ -1071,10 +1084,13 @@ static void analyzeOneTable( /* Invoke the stat_init() function. The arguments are: ** - ** (1) the number of columns in the index including the rowid, - ** (2) the number of rows in the index, + ** (1) the number of columns in the index including the rowid + ** (or for a WITHOUT ROWID table, the number of PK columns), + ** (2) the number of columns in the key without the rowid/pk + ** (3) the number of rows in the index, + ** ** - ** The second argument is only used for STAT3 and STAT4 + ** The third argument is only used for STAT3 and STAT4 */ #ifdef SQLITE_ENABLE_STAT3_OR_STAT4 sqlite3VdbeAddOp2(v, OP_Count, iIdxCur, regStat4+3); @@ -1096,44 +1112,49 @@ static void analyzeOneTable( addrRewind = sqlite3VdbeAddOp1(v, OP_Rewind, iIdxCur); VdbeCoverage(v); sqlite3VdbeAddOp2(v, OP_Integer, 0, regChng); - addrGotoChng0 = sqlite3VdbeAddOp0(v, OP_Goto); - - /* - ** next_row: - ** regChng = 0 - ** if( idx(0) != regPrev(0) ) goto chng_addr_0 - ** regChng = 1 - ** if( idx(1) != regPrev(1) ) goto chng_addr_1 - ** ... - ** regChng = N - ** goto chng_addr_N - */ addrNextRow = sqlite3VdbeCurrentAddr(v); - for(i=0; iazColl[i]); - sqlite3VdbeAddOp2(v, OP_Integer, i, regChng); - sqlite3VdbeAddOp3(v, OP_Column, iIdxCur, i, regTemp); - aGotoChng[i] = - sqlite3VdbeAddOp4(v, OP_Ne, regTemp, 0, regPrev+i, pColl, P4_COLLSEQ); - sqlite3VdbeChangeP5(v, SQLITE_NULLEQ); - VdbeCoverage(v); - } - sqlite3VdbeAddOp2(v, OP_Integer, nCol-1, regChng); - aGotoChng[nCol] = sqlite3VdbeAddOp0(v, OP_Goto); - /* - ** chng_addr_0: - ** regPrev(0) = idx(0) - ** chng_addr_1: - ** regPrev(1) = idx(1) - ** ... - */ - sqlite3VdbeJumpHere(v, addrGotoChng0); - for(i=0; i0 ){ + /* + ** next_row: + ** regChng = 0 + ** if( idx(0) != regPrev(0) ) goto chng_addr_0 + ** regChng = 1 + ** if( idx(1) != regPrev(1) ) goto chng_addr_1 + ** ... + ** regChng = N + ** goto chng_addr_N + */ + sqlite3VdbeAddOp0(v, OP_Goto); + addrNextRow = sqlite3VdbeCurrentAddr(v); + for(i=0; iazColl[i]); + sqlite3VdbeAddOp2(v, OP_Integer, i, regChng); + sqlite3VdbeAddOp3(v, OP_Column, iIdxCur, i, regTemp); + aGotoChng[i] = + sqlite3VdbeAddOp4(v, OP_Ne, regTemp, 0, regPrev+i, pColl, P4_COLLSEQ); + sqlite3VdbeChangeP5(v, SQLITE_NULLEQ); + VdbeCoverage(v); + } + sqlite3VdbeAddOp2(v, OP_Integer, nColTest, regChng); + aGotoChng[nColTest] = sqlite3VdbeAddOp0(v, OP_Goto); + + + /* + ** chng_addr_0: + ** regPrev(0) = idx(0) + ** chng_addr_1: + ** regPrev(1) = idx(1) + ** ... + */ + sqlite3VdbeJumpHere(v, addrNextRow-1); + for(i=0; i