Index: src/analyze.c ================================================================== --- src/analyze.c +++ src/analyze.c @@ -562,11 +562,11 @@ */ void sqlite3DeleteIndexSamples(sqlite3 *db, Index *pIdx){ #ifdef SQLITE_ENABLE_STAT2 if( pIdx->aSample ){ int j; - for(j=0; jnSample; j++){ IndexSample *p = &pIdx->aSample[j]; if( p->eType==SQLITE_TEXT || p->eType==SQLITE_BLOB ){ sqlite3DbFree(db, p->u.z); } } @@ -612,10 +612,11 @@ for(i=sqliteHashFirst(&db->aDb[iDb].pSchema->idxHash);i;i=sqliteHashNext(i)){ Index *pIdx = sqliteHashData(i); sqlite3DefaultRowEst(pIdx); sqlite3DeleteIndexSamples(db, pIdx); pIdx->aSample = 0; + pIdx->nSample = 0; } /* Check to make sure the sqlite_stat1 table exists */ sInfo.db = db; sInfo.zDatabase = db->aDb[iDb].zName; @@ -641,11 +642,12 @@ } if( rc==SQLITE_OK ){ sqlite3_stmt *pStmt = 0; zSql = sqlite3MPrintf(db, - "SELECT idx,sampleno,sample FROM %Q.sqlite_stat2", sInfo.zDatabase); + "SELECT idx, sampleno, sample FROM %Q.sqlite_stat2" + " ORDER BY rowid DESC", sInfo.zDatabase); if( !zSql ){ rc = SQLITE_NOMEM; }else{ rc = sqlite3_prepare(db, zSql, -1, &pStmt, 0); sqlite3DbFree(db, zSql); @@ -653,55 +655,52 @@ if( rc==SQLITE_OK ){ while( sqlite3_step(pStmt)==SQLITE_ROW ){ char *zIndex; /* Index name */ Index *pIdx; /* Pointer to the index object */ + int iSample; + int eType; + IndexSample *pSample; 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=0 ){ - int eType = sqlite3_column_type(pStmt, 2); - - if( pIdx->aSample==0 ){ - static const int sz = sizeof(IndexSample)*SQLITE_INDEX_SAMPLES; - pIdx->aSample = (IndexSample *)sqlite3DbMallocRaw(0, sz); - if( pIdx->aSample==0 ){ - db->mallocFailed = 1; - break; - } - memset(pIdx->aSample, 0, sz); - } - - assert( pIdx->aSample ); - { - IndexSample *pSample = &pIdx->aSample[iSample]; - pSample->eType = (u8)eType; - if( eType==SQLITE_INTEGER || eType==SQLITE_FLOAT ){ - pSample->u.r = sqlite3_column_double(pStmt, 2); - }else if( eType==SQLITE_TEXT || eType==SQLITE_BLOB ){ - const char *z = (const char *)( - (eType==SQLITE_BLOB) ? - sqlite3_column_blob(pStmt, 2): - sqlite3_column_text(pStmt, 2) - ); - int n = sqlite3_column_bytes(pStmt, 2); - if( n>24 ){ - n = 24; - } - pSample->nByte = (u8)n; - if( n < 1){ - pSample->u.z = 0; - }else{ - pSample->u.z = sqlite3DbStrNDup(0, z, n); - if( pSample->u.z==0 ){ - db->mallocFailed = 1; - break; - } - } - } + if( zIndex==0 ) continue; + pIdx = sqlite3FindIndex(db, zIndex, sInfo.zDatabase); + if( pIdx==0 ) continue; + iSample = sqlite3_column_int(pStmt, 1); + if( iSample>=SQLITE_MAX_SAMPLES || iSample<0 ) continue; + if( pIdx->nSample<=iSample ){ + IndexSample *pNew; + int sz = sizeof(IndexSample)*(iSample+1); + pNew = (IndexSample*)sqlite3Realloc(pIdx->aSample, sz); + if( pNew==0 ){ + db->mallocFailed = 1; + break; + } + pIdx->aSample = pNew; + pIdx->nSample = iSample+1; + } + eType = sqlite3_column_type(pStmt, 2); + pSample = &pIdx->aSample[iSample]; + pSample->eType = (u8)eType; + if( eType==SQLITE_INTEGER || eType==SQLITE_FLOAT ){ + pSample->u.r = sqlite3_column_double(pStmt, 2); + }else if( eType==SQLITE_TEXT || eType==SQLITE_BLOB ){ + const char *z = (const char *)( + (eType==SQLITE_BLOB) ? + sqlite3_column_blob(pStmt, 2): + sqlite3_column_text(pStmt, 2) + ); + int n = sqlite3_column_bytes(pStmt, 2); + if( n>24 ) n = 24; + pSample->nByte = (u8)n; + if( n < 1){ + pSample->u.z = 0; + }else{ + pSample->u.z = sqlite3DbStrNDup(0, z, n); + if( pSample->u.z==0 ){ + db->mallocFailed = 1; + break; } } } } rc = sqlite3_finalize(pStmt); Index: src/sqliteInt.h ================================================================== --- src/sqliteInt.h +++ src/sqliteInt.h @@ -82,10 +82,11 @@ ** and with SQLITE_ENABLE_STAT2 */ #ifndef SQLITE_INDEX_SAMPLES # define SQLITE_INDEX_SAMPLES 10 #endif +#define SQLITE_MAX_SAMPLES 100 /* ** The following macros are used to cast pointers to integers and ** integers to pointers. The way you do this varies from one compiler ** to the next, so we have developed the following set of #if statements @@ -1483,10 +1484,11 @@ Table *pTable; /* The SQL table being indexed */ int tnum; /* Page containing root of this index in database file */ u8 onError; /* OE_Abort, OE_Ignore, OE_Replace, or OE_None */ u8 autoIndex; /* True if is automatically created (ex: by UNIQUE) */ u8 bUnordered; /* Use this index for == or IN queries only */ + u8 nSample; /* Number of slots in aSample[] */ char *zColAff; /* String defining the affinity of each column */ Index *pNext; /* The next index associated with the same table */ Schema *pSchema; /* Schema containing this index */ u8 *aSortOrder; /* Array of size Index.nColumn. True==DESC, False==ASC */ char **azColl; /* Array of collation sequence names for index */ Index: src/where.c ================================================================== --- src/where.c +++ src/where.c @@ -2420,16 +2420,16 @@ } #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 +** pIdx->nSample 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. +** the index into (pIdx->nSample+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 +** values between samples 2 and 3. And so on. Region pIdx->nSample ** 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 @@ -2436,11 +2436,11 @@ ** 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. +** and S+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( @@ -2451,16 +2451,18 @@ int *piRegion /* OUT: Region of domain in which value lies */ ){ assert( roundUp==0 || roundUp==1 ); if( ALWAYS(pVal) ){ IndexSample *aSample = pIdx->aSample; + int nSample = pIdx->nSample; int i = 0; int eType = sqlite3_value_type(pVal); + assert( nSample>0 ); if( eType==SQLITE_INTEGER || eType==SQLITE_FLOAT ){ double r = sqlite3_value_double(pVal); - for(i=0; i=SQLITE_TEXT ) break; if( roundUp ){ if( aSample[i].u.r>r ) break; }else{ @@ -2468,11 +2470,11 @@ } } }else if( eType==SQLITE_NULL ){ i = 0; if( roundUp ){ - while( idb; CollSeq *pColl; const u8 *z; @@ -2498,11 +2500,11 @@ } assert( z && pColl && pColl->xCmp ); } n = sqlite3ValueBytes(pVal, pColl->enc); - for(i=0; i=0 ) break; } } - assert( i>=0 && i<=SQLITE_INDEX_SAMPLES ); + assert( i>=0 && i<=pIdx->nSample ); *piRegion = i; } return SQLITE_OK; } #endif /* #ifdef SQLITE_ENABLE_STAT2 */ @@ -2624,11 +2626,12 @@ if( nEq==0 && p->aSample ){ sqlite3_value *pLowerVal = 0; sqlite3_value *pUpperVal = 0; int iEst; int iLower = 0; - int iUpper = SQLITE_INDEX_SAMPLES; + int nSample = p->nSample; + int iUpper = p->nSample; int roundUpUpper = 0; int roundUpLower = 0; u8 aff = p->pTable->aCol[p->aiColumn[0]].affinity; if( pLower ){ @@ -2651,26 +2654,27 @@ }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; + if( pUpper ) iUpper = (iLower + p->nSample + 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 ); + testcase( iEst==nSample ); + assert( iEst<=nSample ); + assert( nSample>0 ); if( iEst<1 ){ - *piEst = 50/SQLITE_INDEX_SAMPLES; + *piEst = 50/nSample; }else{ - *piEst = (iEst*100)/SQLITE_INDEX_SAMPLES; + *piEst = (iEst*100)/nSample; } sqlite3ValueFree(pLowerVal); sqlite3ValueFree(pUpperVal); return rc; } @@ -2716,10 +2720,11 @@ u8 aff; /* Column affinity */ int rc; /* Subfunction return code */ double nRowEst; /* New estimate of the number of rows */ assert( p->aSample!=0 ); + assert( p->nSample>0 ); aff = p->pTable->aCol[p->aiColumn[0]].affinity; if( pExpr ){ rc = valueFromExpr(pParse, pExpr, aff, &pRhs); if( rc ) goto whereEqualScanEst_cancel; }else{ @@ -2730,14 +2735,14 @@ 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*3); + nRowEst = p->aiRowEst[0]/(p->nSample*3); if( nRowEst<*pnRow ) *pnRow = nRowEst; }else{ - nRowEst = (iUpper-iLower)*p->aiRowEst[0]/SQLITE_INDEX_SAMPLES; + nRowEst = (iUpper-iLower)*p->aiRowEst[0]/p->nSample; *pnRow = nRowEst; } whereEqualScanEst_cancel: sqlite3ValueFree(pRhs); @@ -2774,18 +2779,20 @@ 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 */ + int i; /* Loop counter */ + int nSample = p->nSample; /* Number of samples */ + u8 aSpan[SQLITE_MAX_SAMPLES+1]; /* Histogram regions that are spanned */ + u8 aSingle[SQLITE_MAX_SAMPLES+1]; /* Histogram regions hit once */ assert( p->aSample!=0 ); + assert( nSample>0 ); aff = p->pTable->aCol[p->aiColumn[0]].affinity; - memset(aSpan, 0, sizeof(aSpan)); - memset(aSingle, 0, sizeof(aSingle)); + memset(aSpan, 0, nSample+1); + memset(aSingle, 0, nSample+1); for(i=0; inExpr; i++){ sqlite3ValueFree(pVal); rc = valueFromExpr(pParse, pList->a[i].pExpr, aff, &pVal); if( rc ) break; if( pVal==0 || sqlite3_value_type(pVal)==SQLITE_NULL ){ @@ -2797,23 +2804,23 @@ rc = whereRangeRegion(pParse, p, pVal, 1, &iUpper); if( rc ) break; if( iLower>=iUpper ){ aSingle[iLower] = 1; }else{ - assert( iLower>=0 && iUpper<=SQLITE_INDEX_SAMPLES ); + assert( iLower>=0 && iUpper<=nSample ); while( iLoweraiRowEst[0]/(3*SQLITE_INDEX_SAMPLES) + nRowEst = (nSpan*3+nSingle)*p->aiRowEst[0]/(3*nSample) + 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));