aboutsummaryrefslogtreecommitdiff
path: root/src/analyze.c
diff options
context:
space:
mode:
Diffstat (limited to 'src/analyze.c')
-rw-r--r--src/analyze.c332
1 files changed, 219 insertions, 113 deletions
diff --git a/src/analyze.c b/src/analyze.c
index 4dcd7e8b8..e48380711 100644
--- a/src/analyze.c
+++ b/src/analyze.c
@@ -35,7 +35,7 @@
** not possible to enable both STAT3 and STAT4 at the same time. If they
** are both enabled, then STAT4 takes precedence.
**
-** For most applications, sqlite_stat1 provides all the statisics required
+** For most applications, sqlite_stat1 provides all the statistics required
** for the query planner to make good choices.
**
** Format of sqlite_stat1:
@@ -246,6 +246,7 @@ static void openStatTable(
assert( i<ArraySize(aTable) );
sqlite3VdbeAddOp4Int(v, OP_OpenWrite, iStatCur+i, aRoot[i], iDb, 3);
sqlite3VdbeChangeP5(v, aCreateTbl[i]);
+ VdbeComment((v, aTable[i].zName));
}
}
@@ -281,7 +282,8 @@ struct Stat4Sample {
struct Stat4Accum {
tRowcnt nRow; /* Number of rows in the entire table */
tRowcnt nPSample; /* How often to do a periodic sample */
- int nCol; /* Number of columns in index + rowid */
+ int nCol; /* Number of columns in index + pk/rowid */
+ int nKeyCol; /* Number of index columns w/o the pk/rowid */
int mxSample; /* Maximum number of samples to accumulate */
Stat4Sample current; /* Current row as a Stat4Sample */
u32 iPrn; /* Pseudo-random number used for sampling */
@@ -367,13 +369,27 @@ static void stat4Destructor(void *pOld){
}
/*
-** Implementation of the stat_init(N,C) SQL function. The two parameters
-** are the number of rows in the table or index (C) and the number of columns
-** in the index (N). The second argument (C) is only used for STAT3 and STAT4.
+** 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 (note 1)
+** K: The number of columns in the index excluding the rowid/pk.
+** C: The number of rows in the index (note 2)
+**
+** 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.
+**
+** 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.
-** the size of the blob is sizeof(void*) bytes).
+** value is a pointer to the Stat4Accum object. The datatype of the
+** return value is BLOB, but it is really just a pointer to the Stat4Accum
+** object.
*/
static void statInit(
sqlite3_context *context,
@@ -382,6 +398,7 @@ static void statInit(
){
Stat4Accum *p;
int nCol; /* Number of columns in index being sampled */
+ int nKeyCol; /* Number of key columns */
int nColUp; /* nCol rounded up for alignment */
int n; /* Bytes of space to allocate */
sqlite3 *db; /* Database connection */
@@ -392,8 +409,11 @@ static void statInit(
/* Decode the three function arguments */
UNUSED_PARAMETER(argc);
nCol = sqlite3_value_int(argv[0]);
- assert( nCol>1 ); /* >1 because it includes the rowid column */
+ assert( nCol>0 );
nColUp = sizeof(tRowcnt)<8 ? (nCol+1)&~1 : nCol;
+ nKeyCol = sqlite3_value_int(argv[1]);
+ assert( nKeyCol<=nCol );
+ assert( nKeyCol>0 );
/* Allocate the space required for the Stat4Accum object */
n = sizeof(*p)
@@ -415,6 +435,7 @@ static void statInit(
p->db = db;
p->nRow = 0;
p->nCol = nCol;
+ p->nKeyCol = nKeyCol;
p->current.anDLt = (tRowcnt*)&p[1];
p->current.anEq = &p->current.anDLt[nColUp];
@@ -425,9 +446,9 @@ static void statInit(
p->iGet = -1;
p->mxSample = mxSample;
- p->nPSample = (tRowcnt)(sqlite3_value_int64(argv[1])/(mxSample/3+1) + 1);
+ p->nPSample = (tRowcnt)(sqlite3_value_int64(argv[2])/(mxSample/3+1) + 1);
p->current.anLt = &p->current.anEq[nColUp];
- p->iPrn = nCol*0x689e962d ^ sqlite3_value_int(argv[1])*0xd0944565;
+ p->iPrn = nCol*0x689e962d ^ sqlite3_value_int(argv[2])*0xd0944565;
/* Set up the Stat4Accum.a[] and aBest[] arrays */
p->a = (struct Stat4Sample*)&p->current.anLt[nColUp];
@@ -446,11 +467,14 @@ static void statInit(
}
#endif
- /* Return a pointer to the allocated object to the caller */
- sqlite3_result_blob(context, p, sizeof(p), stat4Destructor);
+ /* Return a pointer to the allocated object to the caller. Note that
+ ** only the pointer (the 2nd parameter) matters. The size of the object
+ ** (given by the 3rd parameter) is never used and can be any positive
+ ** value. */
+ sqlite3_result_blob(context, p, sizeof(*p), stat4Destructor);
}
static const FuncDef statInitFuncdef = {
- 1+IsStat34, /* nArg */
+ 2+IsStat34, /* nArg */
SQLITE_UTF8, /* funcFlags */
0, /* pUserData */
0, /* pNext */
@@ -674,7 +698,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
*/
@@ -691,7 +718,7 @@ static void statPush(
UNUSED_PARAMETER( argc );
UNUSED_PARAMETER( context );
- assert( p->nCol>1 ); /* Includes rowid field */
+ assert( p->nCol>0 );
assert( iChng<p->nCol );
if( p->nRow==0 ){
@@ -768,7 +795,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
+** has type BLOB but it is really just a pointer to the Stat4Accum object.
+** 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
@@ -819,7 +849,7 @@ static void statGet(
char *z;
int i;
- char *zRet = sqlite3MallocZero(p->nCol * 25);
+ char *zRet = sqlite3MallocZero( (p->nKeyCol+1)*25 );
if( zRet==0 ){
sqlite3_result_error_nomem(context);
return;
@@ -827,7 +857,7 @@ static void statGet(
sqlite3_snprintf(24, zRet, "%llu", (u64)p->nRow);
z = zRet + sqlite3Strlen30(zRet);
- for(i=0; i<(p->nCol-1); i++){
+ for(i=0; i<p->nKeyCol; i++){
u64 nDistinct = p->current.anDLt[i] + 1;
u64 iVal = (p->nRow + nDistinct - 1) / nDistinct;
sqlite3_snprintf(24, z, " %llu", iVal);
@@ -987,27 +1017,27 @@ 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 *aGotoChng; /* Array of jump instruction addresses */
+ int nCol; /* Number of columns in pIdx. "N" */
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;
- VdbeNoopComment((v, "Begin analysis of %s", pIdx->zName));
- nCol = pIdx->nKeyCol;
- aGotoChng = sqlite3DbMallocRaw(db, sizeof(int)*(nCol+1));
- if( aGotoChng==0 ) continue;
-
- /* Populate the register containing the index name. */
- if( IsPrimaryKeyIndex(pIdx) && !HasRowid(pTab) ){
+ if( !HasRowid(pTab) && IsPrimaryKeyIndex(pIdx) ){
+ nCol = pIdx->nKeyCol;
zIdxName = pTab->zName;
+ nColTest = nCol - 1;
}else{
+ nCol = pIdx->nColumn;
zIdxName = pIdx->zName;
+ nColTest = pIdx->uniqNotNull ? pIdx->nKeyCol-1 : nCol-1;
}
+
+ /* Populate the register containing the index name. */
sqlite3VdbeAddOp4(v, OP_String8, 0, regIdxname, 0, zIdxName, 0);
+ VdbeComment((v, "Analysis for %s.%s", pTab->zName, zIdxName));
/*
** Pseudo-code for loop that calls stat_push():
@@ -1032,7 +1062,7 @@ static void analyzeOneTable(
** regPrev(1) = idx(1)
** ...
**
- ** chng_addr_N:
+ ** endDistinctTest:
** regRowid = idx(rowid)
** stat_push(P, regChng, regRowid)
** Next csr
@@ -1045,7 +1075,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) );
@@ -1055,18 +1085,22 @@ 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+2);
+ sqlite3VdbeAddOp2(v, OP_Count, iIdxCur, regStat4+3);
#endif
- sqlite3VdbeAddOp2(v, OP_Integer, nCol+1, regStat4+1);
+ sqlite3VdbeAddOp2(v, OP_Integer, nCol, regStat4+1);
+ sqlite3VdbeAddOp2(v, OP_Integer, pIdx->nKeyCol, regStat4+2);
sqlite3VdbeAddOp3(v, OP_Function, 0, regStat4+1, regStat4);
sqlite3VdbeChangeP4(v, -1, (char*)&statInitFuncdef, P4_FUNCDEF);
- sqlite3VdbeChangeP5(v, 1+IsStat34);
+ sqlite3VdbeChangeP5(v, 2+IsStat34);
/* Implementation of the following:
**
@@ -1079,44 +1113,62 @@ 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; i<nCol; i++){
- char *pColl = (char*)sqlite3LocateCollSeq(pParse, pIdx->azColl[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, 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; i<nCol; i++){
- sqlite3VdbeJumpHere(v, aGotoChng[i]);
- sqlite3VdbeAddOp3(v, OP_Column, iIdxCur, i, regPrev+i);
+ if( nColTest>0 ){
+ int endDistinctTest = sqlite3VdbeMakeLabel(v);
+ int *aGotoChng; /* Array of jump instruction addresses */
+ aGotoChng = sqlite3DbMallocRaw(db, sizeof(int)*nColTest);
+ if( aGotoChng==0 ) continue;
+
+ /*
+ ** 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 endDistinctTest
+ */
+ sqlite3VdbeAddOp0(v, OP_Goto);
+ addrNextRow = sqlite3VdbeCurrentAddr(v);
+ if( nColTest==1 && pIdx->nKeyCol==1 && IsUniqueIndex(pIdx) ){
+ /* For a single-column UNIQUE index, once we have found a non-NULL
+ ** row, we know that all the rest will be distinct, so skip
+ ** subsequent distinctness tests. */
+ sqlite3VdbeAddOp2(v, OP_NotNull, regPrev, endDistinctTest);
+ VdbeCoverage(v);
+ }
+ for(i=0; i<nColTest; i++){
+ char *pColl = (char*)sqlite3LocateCollSeq(pParse, pIdx->azColl[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);
+ sqlite3VdbeAddOp2(v, OP_Goto, 0, endDistinctTest);
+
+
+ /*
+ ** chng_addr_0:
+ ** regPrev(0) = idx(0)
+ ** chng_addr_1:
+ ** regPrev(1) = idx(1)
+ ** ...
+ */
+ sqlite3VdbeJumpHere(v, addrNextRow-1);
+ for(i=0; i<nColTest; i++){
+ sqlite3VdbeJumpHere(v, aGotoChng[i]);
+ sqlite3VdbeAddOp3(v, OP_Column, iIdxCur, i, regPrev+i);
+ }
+ sqlite3VdbeResolveLabel(v, endDistinctTest);
+ sqlite3DbFree(db, aGotoChng);
}
-
+
/*
** chng_addr_N:
** regRowid = idx(rowid) // STAT34 only
@@ -1124,7 +1176,6 @@ static void analyzeOneTable(
** Next csr
** if !eof(csr) goto next_row;
*/
- sqlite3VdbeJumpHere(v, aGotoChng[nCol]);
#ifdef SQLITE_ENABLE_STAT3_OR_STAT4
assert( regRowid==(regStat4+2) );
if( HasRowid(pTab) ){
@@ -1150,7 +1201,8 @@ static void analyzeOneTable(
/* Add the entry to the stat1 table. */
callStatGet(v, regStat4, STAT_GET_STAT1, regStat1);
- sqlite3VdbeAddOp4(v, OP_MakeRecord, regTabname, 3, regTemp, "aaa", 0);
+ assert( "BBB"[0]==SQLITE_AFF_TEXT );
+ sqlite3VdbeAddOp4(v, OP_MakeRecord, regTabname, 3, regTemp, "BBB", 0);
sqlite3VdbeAddOp2(v, OP_NewRowid, iStatCur, regNewRowid);
sqlite3VdbeAddOp3(v, OP_Insert, iStatCur, regTemp, regNewRowid);
sqlite3VdbeChangeP5(v, OPFLAG_APPEND);
@@ -1168,7 +1220,7 @@ static void analyzeOneTable(
int addrIsNull;
u8 seekOp = HasRowid(pTab) ? OP_NotExists : OP_NotFound;
- pParse->nMem = MAX(pParse->nMem, regCol+nCol+1);
+ pParse->nMem = MAX(pParse->nMem, regCol+nCol);
addrNext = sqlite3VdbeCurrentAddr(v);
callStatGet(v, regStat4, STAT_GET_ROWID, regSampleRowid);
@@ -1190,7 +1242,7 @@ static void analyzeOneTable(
i16 iCol = pIdx->aiColumn[i];
sqlite3ExprCodeGetColumnOfTable(v, pTab, iTabCur, iCol, regCol+i);
}
- sqlite3VdbeAddOp3(v, OP_MakeRecord, regCol, nCol+1, regSample);
+ sqlite3VdbeAddOp3(v, OP_MakeRecord, regCol, nCol, regSample);
#endif
sqlite3VdbeAddOp3(v, OP_MakeRecord, regTabname, 6, regTemp);
sqlite3VdbeAddOp2(v, OP_NewRowid, iStatCur+1, regNewRowid);
@@ -1202,7 +1254,6 @@ static void analyzeOneTable(
/* End of analysis */
sqlite3VdbeJumpHere(v, addrRewind);
- sqlite3DbFree(db, aGotoChng);
}
@@ -1214,7 +1265,8 @@ static void analyzeOneTable(
sqlite3VdbeAddOp2(v, OP_Count, iTabCur, regStat1);
jZeroRows = sqlite3VdbeAddOp1(v, OP_IfNot, regStat1); VdbeCoverage(v);
sqlite3VdbeAddOp2(v, OP_Null, 0, regIdxname);
- sqlite3VdbeAddOp4(v, OP_MakeRecord, regTabname, 3, regTemp, "aaa", 0);
+ assert( "BBB"[0]==SQLITE_AFF_TEXT );
+ sqlite3VdbeAddOp4(v, OP_MakeRecord, regTabname, 3, regTemp, "BBB", 0);
sqlite3VdbeAddOp2(v, OP_NewRowid, iStatCur, regNewRowid);
sqlite3VdbeAddOp3(v, OP_Insert, iStatCur, regTemp, regNewRowid);
sqlite3VdbeChangeP5(v, OPFLAG_APPEND);
@@ -1303,6 +1355,7 @@ void sqlite3Analyze(Parse *pParse, Token *pName1, Token *pName2){
Table *pTab;
Index *pIdx;
Token *pTableName;
+ Vdbe *v;
/* Read the database schema. If an error occurs, leave an error message
** and code in pParse and return NULL. */
@@ -1350,6 +1403,8 @@ void sqlite3Analyze(Parse *pParse, Token *pName1, Token *pName2){
}
}
}
+ v = sqlite3GetVdbe(pParse);
+ if( v ) sqlite3VdbeAddOp0(v, OP_Expire);
}
/*
@@ -1382,7 +1437,7 @@ static void decodeIntArray(
#ifdef SQLITE_ENABLE_STAT3_OR_STAT4
if( z==0 ) z = "";
#else
- if( NEVER(z==0) ) z = "";
+ assert( z!=0 );
#endif
for(i=0; *z && i<nOut; i++){
v = 0;
@@ -1391,30 +1446,38 @@ static void decodeIntArray(
z++;
}
#ifdef SQLITE_ENABLE_STAT3_OR_STAT4
- if( aOut ){
- aOut[i] = v;
- }else
+ if( aOut ) aOut[i] = v;
+ if( aLog ) aLog[i] = sqlite3LogEst(v);
#else
assert( aOut==0 );
UNUSED_PARAMETER(aOut);
+ assert( aLog!=0 );
+ aLog[i] = sqlite3LogEst(v);
#endif
- {
- aLog[i] = sqlite3LogEst(v);
- }
if( *z==' ' ) z++;
}
#ifndef SQLITE_ENABLE_STAT3_OR_STAT4
- assert( pIndex!=0 );
+ assert( pIndex!=0 ); {
#else
- if( pIndex )
+ if( pIndex ){
#endif
- {
- if( strcmp(z, "unordered")==0 ){
- pIndex->bUnordered = 1;
- }else if( sqlite3_strglob("sz=[0-9]*", z)==0 ){
- int v32 = 0;
- sqlite3GetInt32(z+3, &v32);
- pIndex->szIdxRow = sqlite3LogEst(v32);
+ pIndex->bUnordered = 0;
+ pIndex->noSkipScan = 0;
+ while( z[0] ){
+ if( sqlite3_strglob("unordered*", z)==0 ){
+ pIndex->bUnordered = 1;
+ }else if( sqlite3_strglob("sz=[0-9]*", z)==0 ){
+ pIndex->szIdxRow = sqlite3LogEst(sqlite3Atoi(z+3));
+ }else if( sqlite3_strglob("noskipscan*", z)==0 ){
+ pIndex->noSkipScan = 1;
+ }
+#ifdef SQLITE_ENABLE_COSTMULT
+ else if( sqlite3_strglob("costmult=[0-9]*",z)==0 ){
+ pIndex->pTable->costMult = sqlite3LogEst(sqlite3Atoi(z+9));
+ }
+#endif
+ while( z[0]!=0 && z[0]!=' ' ) z++;
+ while( z[0]==' ' ) z++;
}
}
}
@@ -1456,11 +1519,24 @@ static int analysisLoader(void *pData, int argc, char **argv, char **NotUsed){
z = argv[2];
if( pIndex ){
- decodeIntArray((char*)z, pIndex->nKeyCol+1, 0, pIndex->aiRowLogEst, pIndex);
+ int nCol = pIndex->nKeyCol+1;
+#ifdef SQLITE_ENABLE_STAT3_OR_STAT4
+ tRowcnt * const aiRowEst = pIndex->aiRowEst = (tRowcnt*)sqlite3MallocZero(
+ sizeof(tRowcnt) * nCol
+ );
+ if( aiRowEst==0 ) pInfo->db->mallocFailed = 1;
+#else
+ tRowcnt * const aiRowEst = 0;
+#endif
+ pIndex->bUnordered = 0;
+ decodeIntArray((char*)z, nCol, aiRowEst, pIndex->aiRowLogEst, pIndex);
if( pIndex->pPartIdxWhere==0 ) pTable->nRowLogEst = pIndex->aiRowLogEst[0];
}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;
}
@@ -1502,30 +1578,52 @@ static void initAvgEq(Index *pIdx){
IndexSample *aSample = pIdx->aSample;
IndexSample *pFinal = &aSample[pIdx->nSample-1];
int iCol;
- for(iCol=0; iCol<pIdx->nKeyCol; iCol++){
+ int nCol = 1;
+ if( pIdx->nSampleCol>1 ){
+ /* If this is stat4 data, then calculate aAvgEq[] values for all
+ ** sample columns except the last. The last is always set to 1, as
+ ** once the trailing PK fields are considered all index keys are
+ ** unique. */
+ nCol = pIdx->nSampleCol-1;
+ pIdx->aAvgEq[nCol] = 1;
+ }
+ for(iCol=0; iCol<nCol; iCol++){
+ int nSample = pIdx->nSample;
int i; /* Used to iterate through samples */
tRowcnt sumEq = 0; /* Sum of the nEq values */
- tRowcnt nSum = 0; /* Number of terms contributing to sumEq */
tRowcnt avgEq = 0;
- tRowcnt nDLt = pFinal->anDLt[iCol];
+ tRowcnt nRow; /* Number of rows in index */
+ i64 nSum100 = 0; /* Number of terms contributing to sumEq */
+ i64 nDist100; /* Number of distinct values in index */
+
+ if( !pIdx->aiRowEst || iCol>=pIdx->nKeyCol || pIdx->aiRowEst[iCol+1]==0 ){
+ nRow = pFinal->anLt[iCol];
+ nDist100 = (i64)100 * pFinal->anDLt[iCol];
+ nSample--;
+ }else{
+ nRow = pIdx->aiRowEst[0];
+ nDist100 = ((i64)100 * pIdx->aiRowEst[0]) / pIdx->aiRowEst[iCol+1];
+ }
+ pIdx->nRowEst0 = nRow;
/* Set nSum to the number of distinct (iCol+1) field prefixes that
- ** occur in the stat4 table for this index before pFinal. Set
- ** sumEq to the sum of the nEq values for column iCol for the same
- ** set (adding the value only once where there exist dupicate
- ** prefixes). */
- for(i=0; i<(pIdx->nSample-1); i++){
- if( aSample[i].anDLt[iCol]!=aSample[i+1].anDLt[iCol] ){
+ ** occur in the stat4 table for this index. Set sumEq to the sum of
+ ** the nEq values for column iCol for the same set (adding the value
+ ** only once where there exist duplicate prefixes). */
+ for(i=0; i<nSample; i++){
+ if( i==(pIdx->nSample-1)
+ || aSample[i].anDLt[iCol]!=aSample[i+1].anDLt[iCol]
+ ){
sumEq += aSample[i].anEq[iCol];
- nSum++;
+ nSum100 += 100;
}
}
- if( nDLt>nSum ){
- avgEq = (pFinal->anLt[iCol] - sumEq)/(nDLt - nSum);
+
+ if( nDist100>nSum100 ){
+ avgEq = ((i64)100 * (nRow - sumEq))/(nDist100 - nSum100);
}
if( avgEq==0 ) avgEq = 1;
pIdx->aAvgEq[iCol] = avgEq;
- if( pIdx->nSampleCol==1 ) break;
}
}
}
@@ -1584,7 +1682,6 @@ static int loadStatTbl(
while( sqlite3_step(pStmt)==SQLITE_ROW ){
int nIdxCol = 1; /* Number of columns in stat4 records */
- int nAvgCol = 1; /* Number of entries in Index.aAvgEq */
char *zIndex; /* Index name */
Index *pIdx; /* Pointer to the index object */
@@ -1602,13 +1699,17 @@ static int loadStatTbl(
** loaded from the stat4 table. In this case ignore stat3 data. */
if( pIdx==0 || pIdx->nSample ) continue;
if( bStat3==0 ){
- nIdxCol = pIdx->nKeyCol+1;
- nAvgCol = pIdx->nKeyCol;
+ assert( !HasRowid(pIdx->pTable) || pIdx->nColumn==pIdx->nKeyCol+1 );
+ if( !HasRowid(pIdx->pTable) && IsPrimaryKeyIndex(pIdx) ){
+ nIdxCol = pIdx->nKeyCol;
+ }else{
+ nIdxCol = pIdx->nColumn;
+ }
}
pIdx->nSampleCol = nIdxCol;
nByte = sizeof(IndexSample) * nSample;
nByte += sizeof(tRowcnt) * nIdxCol * 3 * nSample;
- nByte += nAvgCol * sizeof(tRowcnt); /* Space for Index.aAvgEq[] */
+ nByte += nIdxCol * sizeof(tRowcnt); /* Space for Index.aAvgEq[] */
pIdx->aSample = sqlite3DbMallocZero(db, nByte);
if( pIdx->aSample==0 ){
@@ -1616,7 +1717,7 @@ static int loadStatTbl(
return SQLITE_NOMEM;
}
pSpace = (tRowcnt*)&pIdx->aSample[nSample];
- pIdx->aAvgEq = pSpace; pSpace += nAvgCol;
+ pIdx->aAvgEq = pSpace; pSpace += nIdxCol;
for(i=0; i<nSample; i++){
pIdx->aSample[i].anEq = pSpace; pSpace += nIdxCol;
pIdx->aSample[i].anLt = pSpace; pSpace += nIdxCol;
@@ -1765,12 +1866,17 @@ int sqlite3AnalysisLoad(sqlite3 *db, int iDb){
/* Load the statistics from the sqlite_stat4 table. */
#ifdef SQLITE_ENABLE_STAT3_OR_STAT4
- if( rc==SQLITE_OK ){
+ if( rc==SQLITE_OK && OptimizationEnabled(db, SQLITE_Stat34) ){
int lookasideEnabled = db->lookaside.bEnabled;
db->lookaside.bEnabled = 0;
rc = loadStat4(db, sInfo.zDatabase);
db->lookaside.bEnabled = lookasideEnabled;
}
+ for(i=sqliteHashFirst(&db->aDb[iDb].pSchema->idxHash);i;i=sqliteHashNext(i)){
+ Index *pIdx = sqliteHashData(i);
+ sqlite3_free(pIdx->aiRowEst);
+ pIdx->aiRowEst = 0;
+ }
#endif
if( rc==SQLITE_NOMEM ){
generated by cgit v1.2.3 (git 2.25.1) at 2025-07-19 15:06:18 +0000