/ Check-in [13ed5ac1]
Login

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

Overview
Comment:Change the way samples for the sqlite_stat4 table are collected.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | sqlite_stat4
Files: files | file ages | folders
SHA1: 13ed5ac13562e7a39905d70fd47059f4d8001bba
User & Date: dan 2013-08-07 16:15:32
Context
2013-08-07
16:38
Fix typos in a comment in analyze.c. No code changes. check-in: 812ed0c5 user: dan tags: sqlite_stat4
16:15
Change the way samples for the sqlite_stat4 table are collected. check-in: 13ed5ac1 user: dan tags: sqlite_stat4
16:04
Fix the ".dump" command on the command-line shell so that it works for "sqlite_stat4" in addition to "sqlite_stat1". check-in: 1e80c4b1 user: drh tags: sqlite_stat4
Changes
Hide Diffs Unified Diffs Show Whitespace Changes Patch

Changes to src/analyze.c.

214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
...
345
346
347
348
349
350
351
352
353
354
355
356
357
358






359
360





361
362
363
364
365











366
367
368
369

370
371
372
373
374
375
376
...
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411



412
413
414
415
416
417
418
** share an instance of the following structure to hold their state
** information.
*/
typedef struct Stat4Accum Stat4Accum;
struct Stat4Accum {
  tRowcnt nRow;             /* Number of rows in the entire table */
  tRowcnt nPSample;         /* How often to do a periodic sample */
  int iMin;                 /* Index of entry with minimum nSumEq and hash */
  int mxSample;             /* Maximum number of samples to accumulate */
  int nSample;              /* Current number of samples */
  int nCol;                 /* Number of columns in the index */
  u32 iPrn;                 /* Pseudo-random number used for sampling */
  struct Stat4Sample {
    i64 iRowid;                /* Rowid in main table of the key */
    tRowcnt nSumEq;            /* Sum of anEq[] values */
    tRowcnt *anEq;             /* sqlite_stat4.nEq */
    tRowcnt *anLt;             /* sqlite_stat4.nLt */
    tRowcnt *anDLt;            /* sqlite_stat4.nDLt */
    u8 isPSample;              /* True if a periodic sample */
    u32 iHash;                 /* Tiebreaker hash */
  } *a;                     /* An array of samples */
};
................................................................................

  UNUSED_PARAMETER(context);
  UNUSED_PARAMETER(argc);

  assert( p->nCol>0 );
  assert( argc==(2 + 3*p->nCol) );

  /* Set nSumEq to the sum of all nEq parameters. */
  for(i=0; i<p->nCol; i++){
    nSumEq += sqlite3_value_int64(aEq[i]);
  }
  if( nSumEq==0 ) return;

  /* Figure out if this sample will be used. Set isPSample to true if this






  ** is a periodic sample, or false if it is being captured because of a
  ** large nSumEq value. If the sample will not be used, return early.  */





  h = p->iPrn = p->iPrn*1103515245 + 12345;
  if( (nLt/p->nPSample)!=((nEq+nLt)/p->nPSample) ){
    doInsert = isPSample = 1;
  }else if( (p->nSample<p->mxSample)
         || (nSumEq>p->a[iMin].nSumEq)











         || (nSumEq==p->a[iMin].nSumEq && h>p->a[iMin].iHash) 
  ){
    doInsert = 1;
  }

  if( !doInsert ) return;

  /* Fill in the new Stat4Sample object. */
  if( p->nSample==p->mxSample ){
    struct Stat4Sample *pMin = &p->a[iMin];
    tRowcnt *anEq = pMin->anEq;
    tRowcnt *anDLt = pMin->anDLt;
................................................................................
    pSample->anLt = anLt;
  }else{
    pSample = &p->a[p->nSample++];
  }
  pSample->iRowid = rowid;
  pSample->iHash = h;
  pSample->isPSample = isPSample;
  pSample->nSumEq = nSumEq;
  for(i=0; i<p->nCol; i++){
    pSample->anEq[i] = sqlite3_value_int64(aEq[i]);
    pSample->anLt[i] = sqlite3_value_int64(aLt[i]);
    pSample->anDLt[i] = sqlite3_value_int64(aDLt[i])-1;
    assert( sqlite3_value_int64(aDLt[i])>0 );
  } 

  /* Find the new minimum */
  if( p->nSample==p->mxSample ){
    u32 iHash = 0;                /* Hash corresponding to iMin/nSumEq entry */
    i64 nMinEq = LARGEST_INT64;   /* Smallest nSumEq seen so far */
    assert( iMin = -1 );

    for(i=0; i<p->mxSample; i++){
      if( p->a[i].isPSample ) continue;
      if( (p->a[i].nSumEq<nMinEq)
       || (p->a[i].nSumEq==nMinEq && p->a[i].iHash<iHash)
      ){
        iMin = i;
        nMinEq = p->a[i].nSumEq;
        iHash = p->a[i].iHash;



      }
    }
    assert( iMin>=0 );
    p->iMin = iMin;
  }
}
static const FuncDef stat4PushFuncdef = {







|






<







 







<
<
<
<
<
<
|
>
>
>
>
>
>
|
|
>
>
>
>
>



|
<
>
>
>
>
>
>
>
>
>
>
>
|
<
|
|
>







 







<









|
|
|
|
|
|
|
|
|
|
|
|
>
>
>







214
215
216
217
218
219
220
221
222
223
224
225
226
227

228
229
230
231
232
233
234
...
344
345
346
347
348
349
350






351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368

369
370
371
372
373
374
375
376
377
378
379
380

381
382
383
384
385
386
387
388
389
390
...
397
398
399
400
401
402
403

404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
** share an instance of the following structure to hold their state
** information.
*/
typedef struct Stat4Accum Stat4Accum;
struct Stat4Accum {
  tRowcnt nRow;             /* Number of rows in the entire table */
  tRowcnt nPSample;         /* How often to do a periodic sample */
  int iMin;                 /* Index of entry with minimum nEq and hash */
  int mxSample;             /* Maximum number of samples to accumulate */
  int nSample;              /* Current number of samples */
  int nCol;                 /* Number of columns in the index */
  u32 iPrn;                 /* Pseudo-random number used for sampling */
  struct Stat4Sample {
    i64 iRowid;                /* Rowid in main table of the key */

    tRowcnt *anEq;             /* sqlite_stat4.nEq */
    tRowcnt *anLt;             /* sqlite_stat4.nLt */
    tRowcnt *anDLt;            /* sqlite_stat4.nDLt */
    u8 isPSample;              /* True if a periodic sample */
    u32 iHash;                 /* Tiebreaker hash */
  } *a;                     /* An array of samples */
};
................................................................................

  UNUSED_PARAMETER(context);
  UNUSED_PARAMETER(argc);

  assert( p->nCol>0 );
  assert( argc==(2 + 3*p->nCol) );







  /* Figure out if this sample will be used. There are two reasons a
  ** sample may be used:
  **
  **   1. It may be a periodic sample. In this case set isPSample to true
  **      as well. Or,
  **
  **   2. Less than p->mxSample samples have been collected so far, or
  **
  **   3. It is more desirable than some other non-periodic sample that has
  **      already been collected. Samples are compared based on the values
  **      in the anEq array, starting from last (right-most index column)
  **      to first (left-most index column). If all elements of the anEq
  **      array are equal, samples are compared by hash value.
  */
  h = p->iPrn = p->iPrn*1103515245 + 12345;
  if( (nLt/p->nPSample)!=((nEq+nLt)/p->nPSample) ){
    doInsert = isPSample = 1;
  }else if( p->nSample<p->mxSample ){

    doInsert = 1;
  }else{
    tRowcnt *aMinEq = p->a[iMin].anEq;
    for(i=p->nCol-1; i>=0; i--){
      i64 nEq = sqlite3_value_int64(aEq[i]);
      if( nEq<aMinEq[i] ) break;
      if( nEq>aMinEq[i] ){
        doInsert = 1;
        break;
      }
    }
    if( i<0 && h>p->a[iMin].iHash ){

      doInsert = 1;
    }
  }
  if( !doInsert ) return;

  /* Fill in the new Stat4Sample object. */
  if( p->nSample==p->mxSample ){
    struct Stat4Sample *pMin = &p->a[iMin];
    tRowcnt *anEq = pMin->anEq;
    tRowcnt *anDLt = pMin->anDLt;
................................................................................
    pSample->anLt = anLt;
  }else{
    pSample = &p->a[p->nSample++];
  }
  pSample->iRowid = rowid;
  pSample->iHash = h;
  pSample->isPSample = isPSample;

  for(i=0; i<p->nCol; i++){
    pSample->anEq[i] = sqlite3_value_int64(aEq[i]);
    pSample->anLt[i] = sqlite3_value_int64(aLt[i]);
    pSample->anDLt[i] = sqlite3_value_int64(aDLt[i])-1;
    assert( sqlite3_value_int64(aDLt[i])>0 );
  } 

  /* Find the new minimum */
  if( p->nSample==p->mxSample ){
    iMin = -1;
    for(i=0; i<p->mxSample; i++){
      if( p->a[i].isPSample ) continue;
      if( iMin<0 ){
        iMin = i;
      }else{
        int j;
        for(j=p->nCol-1; j>=0; j++){
          i64 iCmp = (p->a[iMin].anEq[j] - p->a[i].anEq[j]);
          if( iCmp<0 ){ iMin = i; }
          if( iCmp ) break;
        }
        if( j==0 && p->a[iMin].iHash<p->a[i].iHash ){
          iMin = i;
        }
      }
    }
    assert( iMin>=0 );
    p->iMin = iMin;
  }
}
static const FuncDef stat4PushFuncdef = {