/ Check-in [1b25fa10]
Login

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

Overview
Comment:An experimental interface for retrieving the estimated cost and estimated number of output rows for a query.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | cost-est
Files: files | file ages | folders
SHA3-256: 1b25fa108ab2c4ada75935abf919de2b4c3b39553b2a0ab2a485645a02352e7e
User & Date: drh 2019-04-26 17:20:33
Context
2019-04-27
20:39
Add the shardvtab virtual table that uses the new cost estimation functions. Leaf check-in: 9404300a user: drh tags: cost-est
2019-04-26
17:20
An experimental interface for retrieving the estimated cost and estimated number of output rows for a query. check-in: 1b25fa10 user: drh tags: cost-est
2019-04-24
17:04
New test cases in test/fuzzdata8.db. check-in: 7be6222c user: drh tags: trunk
Changes
Hide Diffs Unified Diffs Show Whitespace Changes Patch

Changes to src/shell.c.in.

1768
1769
1770
1771
1772
1773
1774
1775
1776
1777
1778
1779
1780
1781
1782
1783
1784
1785
1786



1787
1788
1789
1790
1791
1792
1793
1794
....
3071
3072
3073
3074
3075
3076
3077
3078
3079
3080
3081
3082
3083
3084
3085
3086
3087
3088
....
3122
3123
3124
3125
3126
3127
3128
3129
3130
3131
3132
3133
3134
3135
3136
    }
  }
}

/*
** Display and reset the EXPLAIN QUERY PLAN data
*/
static void eqp_render(ShellState *p){
  EQPGraphRow *pRow = p->sGraph.pRow;
  if( pRow ){
    if( pRow->zText[0]=='-' ){
      if( pRow->pNext==0 ){
        eqp_reset(p);
        return;
      }
      utf8_printf(p->out, "%s\n", pRow->zText+3);
      p->sGraph.pRow = pRow->pNext;
      sqlite3_free(pRow);
    }else{



      utf8_printf(p->out, "QUERY PLAN\n");
    }
    p->sGraph.zPrefix[0] = 0;
    eqp_render_level(p, 0);
    eqp_reset(p);
  }
}

................................................................................
        zEQP = sqlite3_mprintf("EXPLAIN QUERY PLAN %s", zStmtSql);
        rc = sqlite3_prepare_v2(db, zEQP, -1, &pExplain, 0);
        if( rc==SQLITE_OK ){
          while( sqlite3_step(pExplain)==SQLITE_ROW ){
            const char *zEQPLine = (const char*)sqlite3_column_text(pExplain,3);
            int iEqpId = sqlite3_column_int(pExplain, 0);
            int iParentId = sqlite3_column_int(pExplain, 1);
            if( zEQPLine[0]=='-' ) eqp_render(pArg);
            eqp_append(pArg, iEqpId, iParentId, zEQPLine);
          }
          eqp_render(pArg);
        }
        sqlite3_finalize(pExplain);
        sqlite3_free(zEQP);
        if( pArg->autoEQP>=AUTOEQP_full ){
          /* Also do an EXPLAIN for ".eqp full" mode */
          zEQP = sqlite3_mprintf("EXPLAIN %s", zStmtSql);
          rc = sqlite3_prepare_v2(db, zEQP, -1, &pExplain, 0);
................................................................................
          explain_data_prepare(pArg, pStmt);
        }
      }

      bind_prepared_stmt(pArg, pStmt);
      exec_prepared_stmt(pArg, pStmt);
      explain_data_delete(pArg);
      eqp_render(pArg);

      /* print usage stats if stats on */
      if( pArg && pArg->statsOn ){
        display_stats(db, pArg, 0);
      }

      /* print loop-counters if required */







|











>
>
>
|







 







|


|







 







|







1768
1769
1770
1771
1772
1773
1774
1775
1776
1777
1778
1779
1780
1781
1782
1783
1784
1785
1786
1787
1788
1789
1790
1791
1792
1793
1794
1795
1796
1797
....
3074
3075
3076
3077
3078
3079
3080
3081
3082
3083
3084
3085
3086
3087
3088
3089
3090
3091
....
3125
3126
3127
3128
3129
3130
3131
3132
3133
3134
3135
3136
3137
3138
3139
    }
  }
}

/*
** Display and reset the EXPLAIN QUERY PLAN data
*/
static void eqp_render(ShellState *p, sqlite3_stmt *pStmt){
  EQPGraphRow *pRow = p->sGraph.pRow;
  if( pRow ){
    if( pRow->zText[0]=='-' ){
      if( pRow->pNext==0 ){
        eqp_reset(p);
        return;
      }
      utf8_printf(p->out, "%s\n", pRow->zText+3);
      p->sGraph.pRow = pRow->pNext;
      sqlite3_free(pRow);
    }else{
      int iCost, nRow;
      iCost = sqlite3_stmt_status(pStmt, SQLITE_STMTSTATUS_EST_COST, 0);
      nRow = sqlite3_stmt_status(pStmt, SQLITE_STMTSTATUS_EST_ROWS, 0);
      utf8_printf(p->out, "QUERY PLAN (log est cost=%d rows=%d)\n", iCost, nRow);
    }
    p->sGraph.zPrefix[0] = 0;
    eqp_render_level(p, 0);
    eqp_reset(p);
  }
}

................................................................................
        zEQP = sqlite3_mprintf("EXPLAIN QUERY PLAN %s", zStmtSql);
        rc = sqlite3_prepare_v2(db, zEQP, -1, &pExplain, 0);
        if( rc==SQLITE_OK ){
          while( sqlite3_step(pExplain)==SQLITE_ROW ){
            const char *zEQPLine = (const char*)sqlite3_column_text(pExplain,3);
            int iEqpId = sqlite3_column_int(pExplain, 0);
            int iParentId = sqlite3_column_int(pExplain, 1);
            if( zEQPLine[0]=='-' ) eqp_render(pArg, pExplain);
            eqp_append(pArg, iEqpId, iParentId, zEQPLine);
          }
          eqp_render(pArg, pExplain);
        }
        sqlite3_finalize(pExplain);
        sqlite3_free(zEQP);
        if( pArg->autoEQP>=AUTOEQP_full ){
          /* Also do an EXPLAIN for ".eqp full" mode */
          zEQP = sqlite3_mprintf("EXPLAIN %s", zStmtSql);
          rc = sqlite3_prepare_v2(db, zEQP, -1, &pExplain, 0);
................................................................................
          explain_data_prepare(pArg, pStmt);
        }
      }

      bind_prepared_stmt(pArg, pStmt);
      exec_prepared_stmt(pArg, pStmt);
      explain_data_delete(pArg);
      eqp_render(pArg, pStmt);

      /* print usage stats if stats on */
      if( pArg && pArg->statsOn ){
        display_stats(db, pArg, 0);
      }

      /* print loop-counters if required */

Changes to src/sqlite.h.in.

7843
7844
7845
7846
7847
7848
7849









7850
7851
7852
7853
7854
7855
7856
7857
7858
7859


7860
7861
7862
7863
7864
7865
7866
** cycle.
**
** [[SQLITE_STMTSTATUS_MEMUSED]] <dt>SQLITE_STMTSTATUS_MEMUSED</dt>
** <dd>^This is the approximate number of bytes of heap memory
** used to store the prepared statement.  ^This value is not actually
** a counter, and so the resetFlg parameter to sqlite3_stmt_status()
** is ignored when the opcode is SQLITE_STMTSTATUS_MEMUSED.









** </dd>
** </dl>
*/
#define SQLITE_STMTSTATUS_FULLSCAN_STEP     1
#define SQLITE_STMTSTATUS_SORT              2
#define SQLITE_STMTSTATUS_AUTOINDEX         3
#define SQLITE_STMTSTATUS_VM_STEP           4
#define SQLITE_STMTSTATUS_REPREPARE         5
#define SQLITE_STMTSTATUS_RUN               6
#define SQLITE_STMTSTATUS_MEMUSED           99



/*
** CAPI3REF: Custom Page Cache Object
**
** The sqlite3_pcache type is opaque.  It is implemented by
** the pluggable module.  The SQLite core has no knowledge of
** its size or internal structure and never deals with the







>
>
>
>
>
>
>
>
>










>
>







7843
7844
7845
7846
7847
7848
7849
7850
7851
7852
7853
7854
7855
7856
7857
7858
7859
7860
7861
7862
7863
7864
7865
7866
7867
7868
7869
7870
7871
7872
7873
7874
7875
7876
7877
** cycle.
**
** [[SQLITE_STMTSTATUS_MEMUSED]] <dt>SQLITE_STMTSTATUS_MEMUSED</dt>
** <dd>^This is the approximate number of bytes of heap memory
** used to store the prepared statement.  ^This value is not actually
** a counter, and so the resetFlg parameter to sqlite3_stmt_status()
** is ignored when the opcode is SQLITE_STMTSTATUS_MEMUSED.
**
** [[SQLITE_STMTSTATUS_EST_ROWS]] <dt>SQLITE_STMTSTATUS_EST_ROWS</dt>
** <dd>^A return value of X indicates that the query planner estimated
** that the query will return pow(2,X/10.0) rows.
**
** [[SQLITE_STMTSTATUS_EST_COST]] <dt>SQLITE_STMTSTATUS_EST_COST</dt>
** <dd>^A return value of X indicates that the query planner estimated
** the relative cost of running this statement to completion is
** pow(2,X/10.0).
** </dd>
** </dl>
*/
#define SQLITE_STMTSTATUS_FULLSCAN_STEP     1
#define SQLITE_STMTSTATUS_SORT              2
#define SQLITE_STMTSTATUS_AUTOINDEX         3
#define SQLITE_STMTSTATUS_VM_STEP           4
#define SQLITE_STMTSTATUS_REPREPARE         5
#define SQLITE_STMTSTATUS_RUN               6
#define SQLITE_STMTSTATUS_MEMUSED           99
#define SQLITE_STMTSTATUS_EST_ROWS          100
#define SQLITE_STMTSTATUS_EST_COST          101

/*
** CAPI3REF: Custom Page Cache Object
**
** The sqlite3_pcache type is opaque.  It is implemented by
** the pluggable module.  The SQLite core has no knowledge of
** its size or internal structure and never deals with the

Changes to src/vdbe.h.

260
261
262
263
264
265
266

267
268
269
270
271
272
273
void sqlite3VdbeAddDblquoteStr(sqlite3*,Vdbe*,const char*);
int sqlite3VdbeUsesDoubleQuotedString(Vdbe*,const char*);
#endif
void sqlite3VdbeSwap(Vdbe*,Vdbe*);
VdbeOp *sqlite3VdbeTakeOpArray(Vdbe*, int*, int*);
sqlite3_value *sqlite3VdbeGetBoundValue(Vdbe*, int, u8);
void sqlite3VdbeSetVarmask(Vdbe*, int);

#ifndef SQLITE_OMIT_TRACE
  char *sqlite3VdbeExpandSql(Vdbe*, const char*);
#endif
int sqlite3MemCompare(const Mem*, const Mem*, const CollSeq*);
int sqlite3BlobCompare(const Mem*, const Mem*);

void sqlite3VdbeRecordUnpack(KeyInfo*,int,const void*,UnpackedRecord*);







>







260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
void sqlite3VdbeAddDblquoteStr(sqlite3*,Vdbe*,const char*);
int sqlite3VdbeUsesDoubleQuotedString(Vdbe*,const char*);
#endif
void sqlite3VdbeSwap(Vdbe*,Vdbe*);
VdbeOp *sqlite3VdbeTakeOpArray(Vdbe*, int*, int*);
sqlite3_value *sqlite3VdbeGetBoundValue(Vdbe*, int, u8);
void sqlite3VdbeSetVarmask(Vdbe*, int);
void sqlite3VdbeUpdateCostEstimates(Parse*, LogEst, LogEst);
#ifndef SQLITE_OMIT_TRACE
  char *sqlite3VdbeExpandSql(Vdbe*, const char*);
#endif
int sqlite3MemCompare(const Mem*, const Mem*, const CollSeq*);
int sqlite3BlobCompare(const Mem*, const Mem*);

void sqlite3VdbeRecordUnpack(KeyInfo*,int,const void*,UnpackedRecord*);

Changes to src/vdbeInt.h.

421
422
423
424
425
426
427


428
429
430
431
432
433
434
  bft explain:2;          /* True if EXPLAIN present on SQL command */
  bft doingRerun:1;       /* True if rerunning after an auto-reprepare */
  bft changeCntOn:1;      /* True to update the change-counter */
  bft runOnlyOnce:1;      /* Automatically expire on reset */
  bft usesStmtJournal:1;  /* True if uses a statement journal */
  bft readOnly:1;         /* True for statements that do not write */
  bft bIsReader:1;        /* True for statements that read */


  yDbMask btreeMask;      /* Bitmask of db->aDb[] entries referenced */
  yDbMask lockMask;       /* Subset of btreeMask that requires a lock */
  u32 aCounter[7];        /* Counters used by sqlite3_stmt_status() */
  char *zSql;             /* Text of the SQL statement that generated this */
#ifdef SQLITE_ENABLE_NORMALIZE
  char *zNormSql;         /* Normalization of the associated SQL statement */
  DblquoteStr *pDblStr;   /* List of double-quoted string literals */







>
>







421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
  bft explain:2;          /* True if EXPLAIN present on SQL command */
  bft doingRerun:1;       /* True if rerunning after an auto-reprepare */
  bft changeCntOn:1;      /* True to update the change-counter */
  bft runOnlyOnce:1;      /* Automatically expire on reset */
  bft usesStmtJournal:1;  /* True if uses a statement journal */
  bft readOnly:1;         /* True for statements that do not write */
  bft bIsReader:1;        /* True for statements that read */
  LogEst nRowEst;         /* Query planner of estimated number of output rows */
  LogEst iCostEst;        /* Query planner cost estimate */
  yDbMask btreeMask;      /* Bitmask of db->aDb[] entries referenced */
  yDbMask lockMask;       /* Subset of btreeMask that requires a lock */
  u32 aCounter[7];        /* Counters used by sqlite3_stmt_status() */
  char *zSql;             /* Text of the SQL statement that generated this */
#ifdef SQLITE_ENABLE_NORMALIZE
  char *zNormSql;         /* Normalization of the associated SQL statement */
  DblquoteStr *pDblStr;   /* List of double-quoted string literals */

Changes to src/vdbeapi.c.

1654
1655
1656
1657
1658
1659
1660
1661
1662
1663
1664
1665
1666
1667
1668
1669
1670
1671
1672
1673
1674
1675
1676
1677
1678
1679











1680
1681




1682
1683
1684
1685
1686
1687
1688
}

/*
** Return the value of a status counter for a prepared statement
*/
int sqlite3_stmt_status(sqlite3_stmt *pStmt, int op, int resetFlag){
  Vdbe *pVdbe = (Vdbe*)pStmt;
  u32 v;
#ifdef SQLITE_ENABLE_API_ARMOR
  if( !pStmt 
   || (op!=SQLITE_STMTSTATUS_MEMUSED && (op<0||op>=ArraySize(pVdbe->aCounter)))
  ){
    (void)SQLITE_MISUSE_BKPT;
    return 0;
  }
#endif
  if( op==SQLITE_STMTSTATUS_MEMUSED ){
    sqlite3 *db = pVdbe->db;
    sqlite3_mutex_enter(db->mutex);
    v = 0;
    db->pnBytesFreed = (int*)&v;
    sqlite3VdbeClearObject(db, pVdbe);
    sqlite3DbFree(db, pVdbe);
    db->pnBytesFreed = 0;
    sqlite3_mutex_leave(db->mutex);
  }else{











    v = pVdbe->aCounter[op];
    if( resetFlag ) pVdbe->aCounter[op] = 0;




  }
  return (int)v;
}

/*
** Return the SQL associated with a prepared statement
*/







|
<
|
<
<



|
|








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







1654
1655
1656
1657
1658
1659
1660
1661

1662


1663
1664
1665
1666
1667
1668
1669
1670
1671
1672
1673
1674
1675
1676
1677
1678
1679
1680
1681
1682
1683
1684
1685
1686
1687
1688
1689
1690
1691
1692
1693
1694
1695
1696
1697
1698
1699
1700
}

/*
** Return the value of a status counter for a prepared statement
*/
int sqlite3_stmt_status(sqlite3_stmt *pStmt, int op, int resetFlag){
  Vdbe *pVdbe = (Vdbe*)pStmt;
  u32 v = 0;

  if( !pStmt ){


    (void)SQLITE_MISUSE_BKPT;
    return 0;
  }
  switch( op ){
    case SQLITE_STMTSTATUS_MEMUSED: {
      sqlite3 *db = pVdbe->db;
      sqlite3_mutex_enter(db->mutex);
      v = 0;
      db->pnBytesFreed = (int*)&v;
      sqlite3VdbeClearObject(db, pVdbe);
      sqlite3DbFree(db, pVdbe);
      db->pnBytesFreed = 0;
      sqlite3_mutex_leave(db->mutex);
      break;
    }
    case SQLITE_STMTSTATUS_EST_ROWS: {
      v = pVdbe->nRowEst;
      break;
    }
    case SQLITE_STMTSTATUS_EST_COST: {
      v = pVdbe->iCostEst;
      break;
    }
    default: {
      if( op>=0 && op<ArraySize(pVdbe->aCounter) ){
        v = pVdbe->aCounter[op];
        if( resetFlag ) pVdbe->aCounter[op] = 0;
      }else{
        (void)SQLITE_MISUSE_BKPT;
      }
    }
  }
  return (int)v;
}

/*
** Return the SQL associated with a prepared statement
*/

Changes to src/vdbeaux.c.

4843
4844
4845
4846
4847
4848
4849














4850
4851
4852
4853
4854
4855
4856
  assert( (v->db->flags & SQLITE_EnableQPSG)==0 );
  if( iVar>=32 ){
    v->expmask |= 0x80000000;
  }else{
    v->expmask |= ((u32)1 << (iVar-1));
  }
}















/*
** Cause a function to throw an error if it was call from OP_PureFunc
** rather than OP_Function.
**
** OP_PureFunc means that the function must be deterministic, and should
** throw an error if it is given inputs that would make it non-deterministic.







>
>
>
>
>
>
>
>
>
>
>
>
>
>







4843
4844
4845
4846
4847
4848
4849
4850
4851
4852
4853
4854
4855
4856
4857
4858
4859
4860
4861
4862
4863
4864
4865
4866
4867
4868
4869
4870
  assert( (v->db->flags & SQLITE_EnableQPSG)==0 );
  if( iVar>=32 ){
    v->expmask |= 0x80000000;
  }else{
    v->expmask |= ((u32)1 << (iVar-1));
  }
}

/*
** Update the estimated cost fields
*/
void sqlite3VdbeUpdateCostEstimates(Parse *pParse, LogEst iCost, LogEst nRow){
  Vdbe *v = pParse->pVdbe;
  if( v->iCostEst ){
    v->iCostEst = sqlite3LogEstAdd(v->iCostEst, iCost+pParse->nQueryLoop) + 1;
    if( nRow > v->nRowEst ) v->nRowEst = nRow;
  }else{
    v->nRowEst = nRow;
    v->iCostEst = iCost + 1;
  }
}

/*
** Cause a function to throw an error if it was call from OP_PureFunc
** rather than OP_Function.
**
** OP_PureFunc means that the function must be deterministic, and should
** throw an error if it is given inputs that would make it non-deterministic.

Changes to src/where.c.

4359
4360
4361
4362
4363
4364
4365

4366
4367
4368
4369
4370
4371
4372
....
5141
5142
5143
5144
5145
5146
5147

5148
5149
5150
5151
5152
5153
5154
        pWInfo->revMask = revMask;
      }
    }
  }


  pWInfo->nRowOut = pFrom->nRow;


  /* Free temporary memory and return success */
  sqlite3DbFreeNN(db, pSpace);
  return SQLITE_OK;
}

/*
................................................................................
  WhereLevel *pLevel;
  WhereLoop *pLoop;
  SrcList *pTabList = pWInfo->pTabList;
  sqlite3 *db = pParse->db;

  /* Generate loop termination code.
  */

  VdbeModuleComment((v, "End WHERE-core"));
  for(i=pWInfo->nLevel-1; i>=0; i--){
    int addr;
    pLevel = &pWInfo->a[i];
    pLoop = pLevel->pWLoop;
    if( pLevel->op!=OP_Noop ){
#ifndef SQLITE_DISABLE_SKIPAHEAD_DISTINCT







>







 







>







4359
4360
4361
4362
4363
4364
4365
4366
4367
4368
4369
4370
4371
4372
4373
....
5142
5143
5144
5145
5146
5147
5148
5149
5150
5151
5152
5153
5154
5155
5156
        pWInfo->revMask = revMask;
      }
    }
  }


  pWInfo->nRowOut = pFrom->nRow;
  pWInfo->iTotalCost = pFrom->rCost;

  /* Free temporary memory and return success */
  sqlite3DbFreeNN(db, pSpace);
  return SQLITE_OK;
}

/*
................................................................................
  WhereLevel *pLevel;
  WhereLoop *pLoop;
  SrcList *pTabList = pWInfo->pTabList;
  sqlite3 *db = pParse->db;

  /* Generate loop termination code.
  */
  sqlite3VdbeUpdateCostEstimates(pParse, pWInfo->iTotalCost, pWInfo->nRowOut);
  VdbeModuleComment((v, "End WHERE-core"));
  for(i=pWInfo->nLevel-1; i>=0; i--){
    int addr;
    pLevel = &pWInfo->a[i];
    pLoop = pLevel->pWLoop;
    if( pLevel->op!=OP_Noop ){
#ifndef SQLITE_DISABLE_SKIPAHEAD_DISTINCT

Changes to src/whereInt.h.

458
459
460
461
462
463
464

465
466
467
468
469
470
471
  u8 untestedTerms;         /* Not all WHERE terms resolved by outer loop */
  u8 eDistinct;             /* One of the WHERE_DISTINCT_* values */
  u8 bOrderedInnerLoop;     /* True if only the inner-most loop is ordered */
  int iTop;                 /* The very beginning of the WHERE loop */
  WhereLoop *pLoops;        /* List of all WhereLoop objects */
  Bitmask revMask;          /* Mask of ORDER BY terms that need reversing */
  LogEst nRowOut;           /* Estimated number of output rows */

  WhereClause sWC;          /* Decomposition of the WHERE clause */
  WhereMaskSet sMaskSet;    /* Map cursor numbers to bitmasks */
  WhereLevel a[1];          /* Information about each nest loop in WHERE */
};

/*
** Private interfaces - callable only by other where.c routines.







>







458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
  u8 untestedTerms;         /* Not all WHERE terms resolved by outer loop */
  u8 eDistinct;             /* One of the WHERE_DISTINCT_* values */
  u8 bOrderedInnerLoop;     /* True if only the inner-most loop is ordered */
  int iTop;                 /* The very beginning of the WHERE loop */
  WhereLoop *pLoops;        /* List of all WhereLoop objects */
  Bitmask revMask;          /* Mask of ORDER BY terms that need reversing */
  LogEst nRowOut;           /* Estimated number of output rows */
  LogEst iTotalCost;        /* Cost estimate for the whole plan */
  WhereClause sWC;          /* Decomposition of the WHERE clause */
  WhereMaskSet sMaskSet;    /* Map cursor numbers to bitmasks */
  WhereLevel a[1];          /* Information about each nest loop in WHERE */
};

/*
** Private interfaces - callable only by other where.c routines.