/ Check-in [8b4aa0c7]
Login
SQLite training in Houston TX on 2019-11-05 (details)
Part of the 2019 Tcl Conference

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

Overview
Comment:Completely remove the iScanRatio field. The PRAGMA index_list(TABLE) command shows the estimated row size in the forth column. It also generates a row for the table with an index name of NULL. The query planner still does not take row size estimates into account - that is the next step.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | row-size-est
Files: files | file ages | folders
SHA1: 8b4aa0c7a2122bbe60432edadf27e490e31ec987
User & Date: drh 2013-10-05 19:18:00
Context
2013-10-05
20:18
Fix an issue in the test8.c test module that arises because of the change to PRAGMA index_list(). Remove an unused local variable. check-in: 029430c5 user: drh tags: row-size-est
19:18
Completely remove the iScanRatio field. The PRAGMA index_list(TABLE) command shows the estimated row size in the forth column. It also generates a row for the table with an index name of NULL. The query planner still does not take row size estimates into account - that is the next step. check-in: 8b4aa0c7 user: drh tags: row-size-est
18:32
Improvements to the LogEst command-line tool used to convert between ordinary numbers and the LogEst representation. check-in: 5252aeb6 user: drh tags: row-size-est
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to src/analyze.c.

1273
1274
1275
1276
1277
1278
1279
1280
1281
1282
1283
1284
1285
1286
1287
1288
1289
1290
1291
1292
1293
1294
1295
1296
1297
    }
    aOut[i] = v;
    if( *z==' ' ) z++;
  }
  if( pIndex ){
    if( strcmp(z, "unordered")==0 ){
      pIndex->bUnordered = 1;
    }else if( sqlite3_strglob("r=[0-9]*", z)==0 ){
      int v32 = 0;
      sqlite3GetInt32(z+2, &v32);
      if( v32>=200 ){
        v32 = 255;
      }else if( v32<=0 ){
        v32 = 1;
      }else{
        v32 = (128*v32)/100;
      }
      pIndex->iScanRatio = (u8)v32;
    }
  }
}

/*
** This callback is invoked once for each index when reading the
** sqlite_stat1 table.  







|

|
|
<
<
<
<
<
<
<







1273
1274
1275
1276
1277
1278
1279
1280
1281
1282
1283







1284
1285
1286
1287
1288
1289
1290
    }
    aOut[i] = v;
    if( *z==' ' ) z++;
  }
  if( pIndex ){
    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);







    }
  }
}

/*
** This callback is invoked once for each index when reading the
** sqlite_stat1 table.  

Changes to src/build.c.

1504
1505
1506
1507
1508
1509
1510
1511
1512
1513
1514
1515
1516
1517
1518
1519
1520
1521
1522
1523
1524
1525
1526
1527

1528
1529
1530
1531
1532
1533
1534
1535
1536
1537
1538
1539
1540
1541
1542
1543
1544
1545
....
1584
1585
1586
1587
1588
1589
1590
1591
1592
1593
1594
1595
1596
1597
1598
1599
1600
1601
....
2814
2815
2816
2817
2818
2819
2820
2821
2822
2823
2824
2825
2826
2827
2828
2829
2830
  sqlite3_snprintf(n-k, &zStmt[k], "%s", zEnd);
  return zStmt;
}

/*
** Estimate the total row width for a table.
*/
static unsigned estimatedTableWidth(const Table *pTab){
  unsigned wTable = 0;
  const Column *pTabCol;
  int i;
  for(i=pTab->nCol, pTabCol=pTab->aCol; i>0; i--, pTabCol++){
    wTable += pTabCol->szEst;
  }
  if( pTab->iPKey<0 ) wTable++;
  return wTable;
}

/*
** Set the iScanRatio for an index based on estimates of the average
** table row width and average index row width.  Estimates are derived
** from the declared datatypes of the various columns.
*/
static void setIndexScanRatio(Index *pIdx, unsigned wTable){

  unsigned wIndex = 1;
  int i;
  const Column *aCol = pIdx->pTable->aCol;
  for(i=0; i<pIdx->nColumn; i++){
    assert( pIdx->aiColumn[i]>=0 && pIdx->aiColumn[i]<pIdx->pTable->nCol );
    wIndex += aCol[pIdx->aiColumn[i]].szEst;
  }
  assert( 100*wIndex/wTable <= 255 );
  pIdx->iScanRatio = (u8)(128*wIndex/wTable);
  /* printf("%s: wIndex=%d wTable=%d ratio=%d\n",
  ** pIdx->zName, wIndex, wTable, (100*pIdx->iScanRatio)/128); */
}

/*
** This routine is called to report the final ")" that terminates
** a CREATE TABLE statement.
**
** The table structure that other action routines have been building
................................................................................
  /* Resolve names in all CHECK constraint expressions.
  */
  if( p->pCheck ){
    sqlite3ResolveSelfReference(pParse, p, NC_IsCheck, 0, p->pCheck);
  }
#endif /* !defined(SQLITE_OMIT_CHECK) */

  /* Compute the iScanRatio of implied indices */
  wTable = estimatedTableWidth(p);
  for(pIdx=p->pIndex; pIdx; pIdx=pIdx->pNext){
    setIndexScanRatio(pIdx, wTable);
  }

  /* If the db->init.busy is 1 it means we are reading the SQL off the
  ** "sqlite_master" or "sqlite_temp_master" table on the disk.
  ** So do not write to the disk again.  Extract the root page number
  ** for the table from the db->init.newTnum field.  (The page number
  ** should have been put there by the sqliteOpenCb routine.)
................................................................................
    }
    pIndex->azColl[i] = zColl;
    requestedSortOrder = pListItem->sortOrder & sortOrderMask;
    pIndex->aSortOrder[i] = (u8)requestedSortOrder;
    if( pTab->aCol[j].notNull==0 ) pIndex->uniqNotNull = 0;
  }
  sqlite3DefaultRowEst(pIndex);
  if( pParse->pNewTable==0 ){
    setIndexScanRatio(pIndex, estimatedTableWidth(pTab));
  }

  if( pTab==pParse->pNewTable ){
    /* This routine has been called to create an automatic index as a
    ** result of a PRIMARY KEY or UNIQUE clause on a column definition, or
    ** a PRIMARY KEY or UNIQUE clause following the column definitions.
    ** i.e. one of:
    **







|







|



|
<
<

<
>







|
<
<
<







 







|
|

|







 







|
<
<







1504
1505
1506
1507
1508
1509
1510
1511
1512
1513
1514
1515
1516
1517
1518
1519
1520
1521
1522
1523


1524

1525
1526
1527
1528
1529
1530
1531
1532
1533



1534
1535
1536
1537
1538
1539
1540
....
1579
1580
1581
1582
1583
1584
1585
1586
1587
1588
1589
1590
1591
1592
1593
1594
1595
1596
....
2809
2810
2811
2812
2813
2814
2815
2816


2817
2818
2819
2820
2821
2822
2823
  sqlite3_snprintf(n-k, &zStmt[k], "%s", zEnd);
  return zStmt;
}

/*
** Estimate the total row width for a table.
*/
static void estimateTableWidth(Table *pTab){
  unsigned wTable = 0;
  const Column *pTabCol;
  int i;
  for(i=pTab->nCol, pTabCol=pTab->aCol; i>0; i--, pTabCol++){
    wTable += pTabCol->szEst;
  }
  if( pTab->iPKey<0 ) wTable++;
  pTab->szTabRow = sqlite3LogEst(wTable*4);
}

/*
** Estimate the average size of a row for an index.


*/

static void estimateIndexWidth(Index *pIdx){
  unsigned wIndex = 1;
  int i;
  const Column *aCol = pIdx->pTable->aCol;
  for(i=0; i<pIdx->nColumn; i++){
    assert( pIdx->aiColumn[i]>=0 && pIdx->aiColumn[i]<pIdx->pTable->nCol );
    wIndex += aCol[pIdx->aiColumn[i]].szEst;
  }
  pIdx->szIdxRow = sqlite3LogEst(wIndex*4);



}

/*
** This routine is called to report the final ")" that terminates
** a CREATE TABLE statement.
**
** The table structure that other action routines have been building
................................................................................
  /* Resolve names in all CHECK constraint expressions.
  */
  if( p->pCheck ){
    sqlite3ResolveSelfReference(pParse, p, NC_IsCheck, 0, p->pCheck);
  }
#endif /* !defined(SQLITE_OMIT_CHECK) */

  /* Estimate the average row size for the table and for all implied indices */
  estimateTableWidth(p);
  for(pIdx=p->pIndex; pIdx; pIdx=pIdx->pNext){
    estimateIndexWidth(pIdx);
  }

  /* If the db->init.busy is 1 it means we are reading the SQL off the
  ** "sqlite_master" or "sqlite_temp_master" table on the disk.
  ** So do not write to the disk again.  Extract the root page number
  ** for the table from the db->init.newTnum field.  (The page number
  ** should have been put there by the sqliteOpenCb routine.)
................................................................................
    }
    pIndex->azColl[i] = zColl;
    requestedSortOrder = pListItem->sortOrder & sortOrderMask;
    pIndex->aSortOrder[i] = (u8)requestedSortOrder;
    if( pTab->aCol[j].notNull==0 ) pIndex->uniqNotNull = 0;
  }
  sqlite3DefaultRowEst(pIndex);
  if( pParse->pNewTable==0 ) estimateIndexWidth(pIndex);



  if( pTab==pParse->pNewTable ){
    /* This routine has been called to create an automatic index as a
    ** result of a PRIMARY KEY or UNIQUE clause on a column definition, or
    ** a PRIMARY KEY or UNIQUE clause following the column definitions.
    ** i.e. one of:
    **

Changes to src/pragma.c.

1445
1446
1447
1448
1449
1450
1451

1452
1453
1454
1455
1456
1457
1458
1459
1460
1461
1462
1463
1464


1465




1466
1467
1468
1469

1470
1471
1472
1473
1474
1475
1476
1477
1478
1479
1480
    }
  }
  break;

  case PragTyp_INDEX_LIST: if( zRight ){
    Index *pIdx;
    Table *pTab;

    pTab = sqlite3FindTable(db, zRight, zDb);
    if( pTab ){
      v = sqlite3GetVdbe(pParse);
      pIdx = pTab->pIndex;
      if( pIdx ){
        int i = 0; 
        sqlite3VdbeSetNumCols(v, 4);
        pParse->nMem = 4;
        sqlite3CodeVerifySchema(pParse, iDb);
        sqlite3VdbeSetColName(v, 0, COLNAME_NAME, "seq", SQLITE_STATIC);
        sqlite3VdbeSetColName(v, 1, COLNAME_NAME, "name", SQLITE_STATIC);
        sqlite3VdbeSetColName(v, 2, COLNAME_NAME, "unique", SQLITE_STATIC);
        sqlite3VdbeSetColName(v, 3, COLNAME_NAME, "r", SQLITE_STATIC);


        while(pIdx){




          sqlite3VdbeAddOp2(v, OP_Integer, i, 1);
          sqlite3VdbeAddOp4(v, OP_String8, 0, 2, 0, pIdx->zName, 0);
          sqlite3VdbeAddOp2(v, OP_Integer, pIdx->onError!=OE_None, 3);
          sqlite3VdbeAddOp2(v, OP_Integer, (pIdx->iScanRatio*100+127)/128, 4);

          sqlite3VdbeAddOp2(v, OP_ResultRow, 1, 4);
          ++i;
          pIdx = pIdx->pNext;
        }
      }
    }
  }
  break;

  case PragTyp_DATABASE_LIST: {
    int i;







>



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







1445
1446
1447
1448
1449
1450
1451
1452
1453
1454
1455



1456
1457
1458
1459
1460
1461
1462
1463
1464
1465
1466
1467
1468
1469
1470
1471
1472
1473
1474
1475



1476
1477
1478
1479
1480
1481
1482
    }
  }
  break;

  case PragTyp_INDEX_LIST: if( zRight ){
    Index *pIdx;
    Table *pTab;
    int i;
    pTab = sqlite3FindTable(db, zRight, zDb);
    if( pTab ){
      v = sqlite3GetVdbe(pParse);



      sqlite3VdbeSetNumCols(v, 4);
      pParse->nMem = 4;
      sqlite3CodeVerifySchema(pParse, iDb);
      sqlite3VdbeSetColName(v, 0, COLNAME_NAME, "seq", SQLITE_STATIC);
      sqlite3VdbeSetColName(v, 1, COLNAME_NAME, "name", SQLITE_STATIC);
      sqlite3VdbeSetColName(v, 2, COLNAME_NAME, "unique", SQLITE_STATIC);
      sqlite3VdbeSetColName(v, 3, COLNAME_NAME, "avgrowsize", SQLITE_STATIC);
      sqlite3VdbeAddOp2(v, OP_Integer, 0, 1);
      sqlite3VdbeAddOp2(v, OP_Null, 0, 2);
      sqlite3VdbeAddOp2(v, OP_Integer, 1, 3);
      sqlite3VdbeAddOp2(v, OP_Integer,
                           (int)sqlite3LogEstToInt(pTab->szTabRow), 4);
      sqlite3VdbeAddOp2(v, OP_ResultRow, 1, 4);
      for(pIdx=pTab->pIndex, i=1; pIdx; pIdx=pIdx->pNext, i++){
        sqlite3VdbeAddOp2(v, OP_Integer, i, 1);
        sqlite3VdbeAddOp4(v, OP_String8, 0, 2, 0, pIdx->zName, 0);
        sqlite3VdbeAddOp2(v, OP_Integer, pIdx->onError!=OE_None, 3);
        sqlite3VdbeAddOp2(v, OP_Integer,
                             (int)sqlite3LogEstToInt(pIdx->szIdxRow), 4);
        sqlite3VdbeAddOp2(v, OP_ResultRow, 1, 4);



      }
    }
  }
  break;

  case PragTyp_DATABASE_LIST: {
    int i;

Changes to src/select.c.

4604
4605
4606
4607
4608
4609
4610
4611
4612
4613
4614
4615
4616
4617
4618
4619
4620
        ** (2013-10-03) Do not count the entires in a partial index.
        **
        ** In practice the KeyInfo structure will not be used. It is only 
        ** passed to keep OP_OpenRead happy.
        */
        for(pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext){
          if( pIdx->bUnordered==0
           && pIdx->iScanRatio<128
           && pIdx->pPartIdxWhere==0
           && (!pBest || pIdx->iScanRatio<pBest->iScanRatio)
          ){
            pBest = pIdx;
          }
        }
        if( pBest ){
          iRoot = pBest->tnum;
          pKeyInfo = sqlite3IndexKeyinfo(pParse, pBest);







|

|







4604
4605
4606
4607
4608
4609
4610
4611
4612
4613
4614
4615
4616
4617
4618
4619
4620
        ** (2013-10-03) Do not count the entires in a partial index.
        **
        ** In practice the KeyInfo structure will not be used. It is only 
        ** passed to keep OP_OpenRead happy.
        */
        for(pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext){
          if( pIdx->bUnordered==0
           && pIdx->szIdxRow<pTab->szTabRow
           && pIdx->pPartIdxWhere==0
           && (!pBest || pIdx->szIdxRow<pBest->szIdxRow)
          ){
            pBest = pIdx;
          }
        }
        if( pBest ){
          iRoot = pBest->tnum;
          pKeyInfo = sqlite3IndexKeyinfo(pParse, pBest);

Changes to src/sqliteInt.h.

1584
1585
1586
1587
1588
1589
1590
1591
1592
1593
1594
1595
1596
1597
1598
  Schema *pSchema;         /* Schema containing this index */
  u8 *aSortOrder;          /* for each column: True==DESC, False==ASC */
  char **azColl;           /* Array of collation sequence names for index */
  Expr *pPartIdxWhere;     /* WHERE clause for partial indices */
  int tnum;                /* DB Page containing root of this index */
  LogEst szIdxRow;         /* Estimated average row size in bytes */
  u16 nColumn;             /* Number of columns in table used by this index */
  u8 iScanRatio;           /* Scan rate relative to the main table */
  u8 onError;              /* OE_Abort, OE_Ignore, OE_Replace, or OE_None */
  unsigned autoIndex:2;    /* 1==UNIQUE, 2==PRIMARY KEY, 0==CREATE INDEX */
  unsigned bUnordered:1;   /* Use this index for == or IN queries only */
  unsigned uniqNotNull:1;  /* True if UNIQUE and NOT NULL for all columns */
#ifdef SQLITE_ENABLE_STAT3_OR_STAT4
  int nSample;             /* Number of elements in aSample[] */
  int nSampleCol;          /* Size of IndexSample.anEq[] and so on */







<







1584
1585
1586
1587
1588
1589
1590

1591
1592
1593
1594
1595
1596
1597
  Schema *pSchema;         /* Schema containing this index */
  u8 *aSortOrder;          /* for each column: True==DESC, False==ASC */
  char **azColl;           /* Array of collation sequence names for index */
  Expr *pPartIdxWhere;     /* WHERE clause for partial indices */
  int tnum;                /* DB Page containing root of this index */
  LogEst szIdxRow;         /* Estimated average row size in bytes */
  u16 nColumn;             /* Number of columns in table used by this index */

  u8 onError;              /* OE_Abort, OE_Ignore, OE_Replace, or OE_None */
  unsigned autoIndex:2;    /* 1==UNIQUE, 2==PRIMARY KEY, 0==CREATE INDEX */
  unsigned bUnordered:1;   /* Use this index for == or IN queries only */
  unsigned uniqNotNull:1;  /* True if UNIQUE and NOT NULL for all columns */
#ifdef SQLITE_ENABLE_STAT3_OR_STAT4
  int nSample;             /* Number of elements in aSample[] */
  int nSampleCol;          /* Size of IndexSample.anEq[] and so on */

Changes to src/where.c.

4565
4566
4567
4568
4569
4570
4571
4572
4573
4574
4575
4576
4577
4578
4579
4580
4581
4582
4583
4584
4585
4586
4587
4588
4589
4590
4591
4592
4593
4594
4595
4596
4597
4598
4599
4600
4601
4602
4603
4604
4605
4606
      /* Integer primary key index */
      pNew->wsFlags = WHERE_IPK;

      /* Full table scan */
      pNew->iSortIdx = b ? iSortIdx : 0;
      /* TUNING: Cost of full table scan is 3*(N + log2(N)).
      **  +  The extra 3 factor is to encourage the use of indexed lookups
      **     over full scans. */
      pNew->rRun = sqlite3LogEstAdd(rSize,rLogSize) + 16;
      whereLoopOutputAdjust(pWC, pNew, pSrc->iCursor);
      rc = whereLoopInsert(pBuilder, pNew);
      pNew->nOut = rSize;
      if( rc ) break;
    }else{
      Bitmask m = pSrc->colUsed & ~columnsInIndex(pProbe);
      pNew->wsFlags = (m==0) ? (WHERE_IDX_ONLY|WHERE_INDEXED) : WHERE_INDEXED;

      /* Full scan via index */
      if( b
       || ( m==0
         && pProbe->bUnordered==0
         && pProbe->iScanRatio<128
         && (pWInfo->wctrlFlags & WHERE_ONEPASS_DESIRED)==0
         && sqlite3GlobalConfig.bUseCis
         && OptimizationEnabled(pWInfo->pParse->db, SQLITE_CoverIdxScan)
          )
      ){
        pNew->iSortIdx = b ? iSortIdx : 0;
        if( m==0 ){
          /* TUNING: Cost of a covering index scan is K*(N + log2(N)).
          **  +  The extra factor K of between 1.1 (iScanRatio between 0
          **     and 9) and 2.8 (iScanRatio between 126 and 127) is added
          **     to encourage the use of indexed lookups.
          */
          pNew->rRun = sqlite3LogEstAdd(rSize,rLogSize) + pProbe->iScanRatio/9 + 1;
        }else{
          assert( b!=0 ); 
          /* TUNING: Cost of scanning a non-covering index is (N+1)*log2(N)
          ** which we will simplify to just N*log2(N) */
          pNew->rRun = rSize + rLogSize;
        }
        whereLoopOutputAdjust(pWC, pNew, pSrc->iCursor);







|













<










|

|







4565
4566
4567
4568
4569
4570
4571
4572
4573
4574
4575
4576
4577
4578
4579
4580
4581
4582
4583
4584
4585

4586
4587
4588
4589
4590
4591
4592
4593
4594
4595
4596
4597
4598
4599
4600
4601
4602
4603
4604
4605
      /* Integer primary key index */
      pNew->wsFlags = WHERE_IPK;

      /* Full table scan */
      pNew->iSortIdx = b ? iSortIdx : 0;
      /* TUNING: Cost of full table scan is 3*(N + log2(N)).
      **  +  The extra 3 factor is to encourage the use of indexed lookups
      **     over full scans.  FIXME */
      pNew->rRun = sqlite3LogEstAdd(rSize,rLogSize) + 16;
      whereLoopOutputAdjust(pWC, pNew, pSrc->iCursor);
      rc = whereLoopInsert(pBuilder, pNew);
      pNew->nOut = rSize;
      if( rc ) break;
    }else{
      Bitmask m = pSrc->colUsed & ~columnsInIndex(pProbe);
      pNew->wsFlags = (m==0) ? (WHERE_IDX_ONLY|WHERE_INDEXED) : WHERE_INDEXED;

      /* Full scan via index */
      if( b
       || ( m==0
         && pProbe->bUnordered==0

         && (pWInfo->wctrlFlags & WHERE_ONEPASS_DESIRED)==0
         && sqlite3GlobalConfig.bUseCis
         && OptimizationEnabled(pWInfo->pParse->db, SQLITE_CoverIdxScan)
          )
      ){
        pNew->iSortIdx = b ? iSortIdx : 0;
        if( m==0 ){
          /* TUNING: Cost of a covering index scan is K*(N + log2(N)).
          **  +  The extra factor K of between 1.1 (iScanRatio between 0
          **     and 9) and 2.8 (iScanRatio between 126 and 127) is added
          **     to encourage the use of indexed lookups.  FIXME
          */
          pNew->rRun = sqlite3LogEstAdd(rSize,rLogSize) + 10;
        }else{
          assert( b!=0 ); 
          /* TUNING: Cost of scanning a non-covering index is (N+1)*log2(N)
          ** which we will simplify to just N*log2(N) */
          pNew->rRun = rSize + rLogSize;
        }
        whereLoopOutputAdjust(pWC, pNew, pSrc->iCursor);