SQLite: Check-in [3c933bf9]

Many hyperlinks are disabled.
Use anonymous login to enable hyperlinks.

Overview

Comment:	Take steps to avoid misestimating range query costs based on STAT4 data due to the roundoff error of converting from integers to LogEst and back to integers.
Downloads:	Tarball \| ZIP archive \| SQL archive
Timelines:	family \| ancestors \| descendants \| both \| trunk
Files:	files \| file ages \| folders
SHA1:	3c933bf95f291f7957580d823dce92c981375a5c
User & Date:	drh 2014-10-22 15:27:05

Context

2014-10-23
17:26		Add debugging code to count the number of iterations of each loop made as part of statement execution. (Leaf check-in: c6a5b67a user: dan tags: debug-loopcounters)
2014-10-22
19:57		Change the 0x800 bit of SQLITE_TESTCTRL_OPTIMIZATIONS so that it disables the loading of STAT3 and STAT4 content, not just the using of that content. Change the internal name of that bit to SQLITE_Stat34. (check-in: ca3b00c4 user: drh tags: trunk)
18:42		Merge latest trunk with this branch. (check-in: 854a54c6 user: dan tags: defrag-opt)
16:25		The _beginthreadex() / _endthreadex() functions should only be used when compiling with MSVC. (Closed-Leaf check-in: 1a5a5da3 user: mistachkin tags: msvcThreads)
15:27		Take steps to avoid misestimating range query costs based on STAT4 data due to the roundoff error of converting from integers to LogEst and back to integers. (check-in: 3c933bf9 user: drh tags: trunk)
2014-10-21
21:56		Call fsync() right after ftruncate() when in journal_mode=TRUNCATE and when synchronous=FULL in order to ensure that transactions are durable across a power loss that happens moments after the commit. Proposed fix for https://bugzilla.mozilla.org/show_bug.cgi?id=1072773. (check-in: 3e922208 user: drh tags: trunk)

Changes

Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to src/analyze.c.

        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];
      }


      /* Set nSum to the number of distinct (iCol+1) field prefixes that
      ** 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)

        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. 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)

Changes to src/sqliteInt.h.

  unsigned isResized:1;    /* True if resizeIndexObject() has been called */
  unsigned isCovering:1;   /* True if this is a covering index */
#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 */
  tRowcnt *aiRowEst;       /* Non-logarithmic stat1 data for this table */

#endif
};

/*
** Allowed values for Index.idxType
*/
#define SQLITE_IDXTYPE_APPDEF      0   /* Created using CREATE INDEX */

|
>

  unsigned isResized:1;    /* True if resizeIndexObject() has been called */
  unsigned isCovering:1;   /* True if this is a covering index */
#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 */
  tRowcnt *aiRowEst;       /* Non-logarithmic stat1 data for this index */
  tRowcnt nRowEst0;        /* Non-logarithmic number of rows in the index */
#endif
};

/*
** Allowed values for Index.idxType
*/
#define SQLITE_IDXTYPE_APPDEF      0   /* Created using CREATE INDEX */

Changes to src/where.c.

        aff = SQLITE_AFF_INTEGER;
      }else{
        aff = p->pTable->aCol[p->aiColumn[nEq]].affinity;
      }
      /* Determine iLower and iUpper using ($P) only. */
      if( nEq==0 ){
        iLower = 0;
        iUpper = sqlite3LogEstToInt(p->aiRowLogEst[0]);
      }else{
        /* Note: this call could be optimized away - since the same values must 
        ** have been requested when testing key $P in whereEqualScanEst().  */
        whereKeyStats(pParse, p, pRec, 0, a);
        iLower = a[0];
        iUpper = a[0] + a[1];
      }

        aff = SQLITE_AFF_INTEGER;
      }else{
        aff = p->pTable->aCol[p->aiColumn[nEq]].affinity;
      }
      /* Determine iLower and iUpper using ($P) only. */
      if( nEq==0 ){
        iLower = 0;
        iUpper = p->nRowEst0;
      }else{
        /* Note: this call could be optimized away - since the same values must 
        ** have been requested when testing key $P in whereEqualScanEst().  */
        whereKeyStats(pParse, p, pRec, 0, a);
        iLower = a[0];
        iUpper = a[0] + a[1];
      }