/ Check-in [2404304c]
Login

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

Overview
Comment:Add the new "explain" virtual table in ext/misc. Use this virtual table for additional test cases for the optimization that avoids updating indexes on expressions when none of the columns changed by the UPDATE are in the expression.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA3-256: 2404304cc15eaeee2744cf3c8f9cac0a544631c4f1060c5a17a78b43ca86edf0
User & Date: drh 2018-09-16 16:18:01
Context
2018-09-16
16:34
Fix a memory leak in the explain extension. check-in: d0c92b04 user: drh tags: trunk
16:18
Add the new "explain" virtual table in ext/misc. Use this virtual table for additional test cases for the optimization that avoids updating indexes on expressions when none of the columns changed by the UPDATE are in the expression. check-in: 2404304c user: drh tags: trunk
15:01
Improved presentation on the new code that prevents unnecessary writes to expressions on indexes during an UPDATE when the expression does not reference any of the columns that are changing. check-in: c9f04529 user: drh tags: trunk
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to Makefile.in.

   433    433     $(TOP)/ext/expert/sqlite3expert.c \
   434    434     $(TOP)/ext/expert/test_expert.c \
   435    435     $(TOP)/ext/misc/amatch.c \
   436    436     $(TOP)/ext/misc/carray.c \
   437    437     $(TOP)/ext/misc/closure.c \
   438    438     $(TOP)/ext/misc/csv.c \
   439    439     $(TOP)/ext/misc/eval.c \
          440  +  $(TOP)/ext/misc/explain.c \
   440    441     $(TOP)/ext/misc/fileio.c \
   441    442     $(TOP)/ext/misc/fuzzer.c \
   442    443     $(TOP)/ext/fts5/fts5_tcl.c \
   443    444     $(TOP)/ext/fts5/fts5_test_mi.c \
   444    445     $(TOP)/ext/fts5/fts5_test_tok.c \
   445    446     $(TOP)/ext/misc/ieee754.c \
   446    447     $(TOP)/ext/misc/mmapwarm.c \

Changes to Makefile.msc.

  1511   1511     $(TOP)\ext\expert\sqlite3expert.c \
  1512   1512     $(TOP)\ext\expert\test_expert.c \
  1513   1513     $(TOP)\ext\misc\amatch.c \
  1514   1514     $(TOP)\ext\misc\carray.c \
  1515   1515     $(TOP)\ext\misc\closure.c \
  1516   1516     $(TOP)\ext\misc\csv.c \
  1517   1517     $(TOP)\ext\misc\eval.c \
         1518  +  $(TOP)\ext\misc\explain.c \
  1518   1519     $(TOP)\ext\misc\fileio.c \
  1519   1520     $(TOP)\ext\misc\fuzzer.c \
  1520   1521     $(TOP)\ext\fts5\fts5_tcl.c \
  1521   1522     $(TOP)\ext\fts5\fts5_test_mi.c \
  1522   1523     $(TOP)\ext\fts5\fts5_test_tok.c \
  1523   1524     $(TOP)\ext\misc\ieee754.c \
  1524   1525     $(TOP)\ext\misc\mmapwarm.c \

Added ext/misc/explain.c.

            1  +/*
            2  +** 2018-09-16
            3  +**
            4  +** The author disclaims copyright to this source code.  In place of
            5  +** a legal notice, here is a blessing:
            6  +**
            7  +**    May you do good and not evil.
            8  +**    May you find forgiveness for yourself and forgive others.
            9  +**    May you share freely, never taking more than you give.
           10  +**
           11  +*************************************************************************
           12  +**
           13  +** This file demonstrates an eponymous virtual table that returns the
           14  +** EXPLAIN output from an SQL statement.
           15  +**
           16  +** Usage example:
           17  +**
           18  +**     .load ./explain
           19  +**     SELECT p2 FROM explain('SELECT * FROM sqlite_master')
           20  +**      WHERE opcode='OpenRead';
           21  +*/
           22  +#if !defined(SQLITEINT_H)
           23  +#include "sqlite3ext.h"
           24  +#endif
           25  +SQLITE_EXTENSION_INIT1
           26  +#include <assert.h>
           27  +#include <string.h>
           28  +
           29  +#ifndef SQLITE_OMIT_VIRTUALTABLE
           30  +
           31  +/* explain_vtab is a subclass of sqlite3_vtab which will
           32  +** serve as the underlying representation of a explain virtual table
           33  +*/
           34  +typedef struct explain_vtab explain_vtab;
           35  +struct explain_vtab {
           36  +  sqlite3_vtab base;  /* Base class - must be first */
           37  +  sqlite3 *db;        /* Database connection for this explain vtab */
           38  +};
           39  +
           40  +/* explain_cursor is a subclass of sqlite3_vtab_cursor which will
           41  +** serve as the underlying representation of a cursor that scans
           42  +** over rows of the result from an EXPLAIN operation.
           43  +*/
           44  +typedef struct explain_cursor explain_cursor;
           45  +struct explain_cursor {
           46  +  sqlite3_vtab_cursor base;  /* Base class - must be first */
           47  +  sqlite3 *db;               /* Database connection for this cursor */
           48  +  char *zSql;                /* Value for the EXPLN_COLUMN_SQL column */
           49  +  sqlite3_stmt *pExplain;    /* Statement being explained */
           50  +  int rc;                    /* Result of last sqlite3_step() on pExplain */
           51  +};
           52  +
           53  +/*
           54  +** The explainConnect() method is invoked to create a new
           55  +** explain_vtab that describes the explain virtual table.
           56  +**
           57  +** Think of this routine as the constructor for explain_vtab objects.
           58  +**
           59  +** All this routine needs to do is:
           60  +**
           61  +**    (1) Allocate the explain_vtab object and initialize all fields.
           62  +**
           63  +**    (2) Tell SQLite (via the sqlite3_declare_vtab() interface) what the
           64  +**        result set of queries against explain will look like.
           65  +*/
           66  +static int explainConnect(
           67  +  sqlite3 *db,
           68  +  void *pAux,
           69  +  int argc, const char *const*argv,
           70  +  sqlite3_vtab **ppVtab,
           71  +  char **pzErr
           72  +){
           73  +  explain_vtab *pNew;
           74  +  int rc;
           75  +
           76  +/* Column numbers */
           77  +#define EXPLN_COLUMN_ADDR     0   /* Instruction address */
           78  +#define EXPLN_COLUMN_OPCODE   1   /* Opcode */
           79  +#define EXPLN_COLUMN_P1       2   /* Operand 1 */
           80  +#define EXPLN_COLUMN_P2       3   /* Operand 2 */
           81  +#define EXPLN_COLUMN_P3       4   /* Operand 3 */
           82  +#define EXPLN_COLUMN_P4       5   /* Operand 4 */
           83  +#define EXPLN_COLUMN_P5       6   /* Operand 5 */
           84  +#define EXPLN_COLUMN_COMMENT  7   /* Comment */
           85  +#define EXPLN_COLUMN_SQL      8   /* SQL that is being explained */
           86  +
           87  +
           88  +  rc = sqlite3_declare_vtab(db,
           89  +     "CREATE TABLE x(addr,opcode,p1,p2,p3,p4,p5,comment,sql HIDDEN)");
           90  +  if( rc==SQLITE_OK ){
           91  +    pNew = sqlite3_malloc( sizeof(*pNew) );
           92  +    *ppVtab = (sqlite3_vtab*)pNew;
           93  +    if( pNew==0 ) return SQLITE_NOMEM;
           94  +    memset(pNew, 0, sizeof(*pNew));
           95  +    pNew->db = db;
           96  +  }
           97  +  return rc;
           98  +}
           99  +
          100  +/*
          101  +** This method is the destructor for explain_cursor objects.
          102  +*/
          103  +static int explainDisconnect(sqlite3_vtab *pVtab){
          104  +  sqlite3_free(pVtab);
          105  +  return SQLITE_OK;
          106  +}
          107  +
          108  +/*
          109  +** Constructor for a new explain_cursor object.
          110  +*/
          111  +static int explainOpen(sqlite3_vtab *p, sqlite3_vtab_cursor **ppCursor){
          112  +  explain_cursor *pCur;
          113  +  pCur = sqlite3_malloc( sizeof(*pCur) );
          114  +  if( pCur==0 ) return SQLITE_NOMEM;
          115  +  memset(pCur, 0, sizeof(*pCur));
          116  +  pCur->db = ((explain_vtab*)p)->db;
          117  +  *ppCursor = &pCur->base;
          118  +  return SQLITE_OK;
          119  +}
          120  +
          121  +/*
          122  +** Destructor for a explain_cursor.
          123  +*/
          124  +static int explainClose(sqlite3_vtab_cursor *cur){
          125  +  explain_cursor *pCur = (explain_cursor*)cur;
          126  +  sqlite3_finalize(pCur->pExplain);
          127  +  sqlite3_free(pCur->zSql);
          128  +  sqlite3_free(pCur);
          129  +  return SQLITE_OK;
          130  +}
          131  +
          132  +
          133  +/*
          134  +** Advance a explain_cursor to its next row of output.
          135  +*/
          136  +static int explainNext(sqlite3_vtab_cursor *cur){
          137  +  explain_cursor *pCur = (explain_cursor*)cur;
          138  +  pCur->rc = sqlite3_step(pCur->pExplain);
          139  +  if( pCur->rc!=SQLITE_DONE && pCur->rc!=SQLITE_ROW ) return pCur->rc;
          140  +  return SQLITE_OK;
          141  +}
          142  +
          143  +/*
          144  +** Return values of columns for the row at which the explain_cursor
          145  +** is currently pointing.
          146  +*/
          147  +static int explainColumn(
          148  +  sqlite3_vtab_cursor *cur,   /* The cursor */
          149  +  sqlite3_context *ctx,       /* First argument to sqlite3_result_...() */
          150  +  int i                       /* Which column to return */
          151  +){
          152  +  explain_cursor *pCur = (explain_cursor*)cur;
          153  +  if( i==EXPLN_COLUMN_SQL ){
          154  +    sqlite3_result_text(ctx, pCur->zSql, -1, SQLITE_TRANSIENT);
          155  +  }else{
          156  +    sqlite3_result_value(ctx, sqlite3_column_value(pCur->pExplain, i));
          157  +  }
          158  +  return SQLITE_OK;
          159  +}
          160  +
          161  +/*
          162  +** Return the rowid for the current row.  In this implementation, the
          163  +** rowid is the same as the output value.
          164  +*/
          165  +static int explainRowid(sqlite3_vtab_cursor *cur, sqlite_int64 *pRowid){
          166  +  explain_cursor *pCur = (explain_cursor*)cur;
          167  +  *pRowid = sqlite3_column_int64(pCur->pExplain, 0);
          168  +  return SQLITE_OK;
          169  +}
          170  +
          171  +/*
          172  +** Return TRUE if the cursor has been moved off of the last
          173  +** row of output.
          174  +*/
          175  +static int explainEof(sqlite3_vtab_cursor *cur){
          176  +  explain_cursor *pCur = (explain_cursor*)cur;
          177  +  return pCur->rc!=SQLITE_ROW;
          178  +}
          179  +
          180  +/*
          181  +** This method is called to "rewind" the explain_cursor object back
          182  +** to the first row of output.  This method is always called at least
          183  +** once prior to any call to explainColumn() or explainRowid() or 
          184  +** explainEof().
          185  +**
          186  +** The argv[0] is the SQL statement that is to be explained.
          187  +*/
          188  +static int explainFilter(
          189  +  sqlite3_vtab_cursor *pVtabCursor, 
          190  +  int idxNum, const char *idxStr,
          191  +  int argc, sqlite3_value **argv
          192  +){
          193  +  explain_cursor *pCur = (explain_cursor *)pVtabCursor;
          194  +  char *zSql = 0;
          195  +  int rc;
          196  +  sqlite3_finalize(pCur->pExplain);
          197  +  pCur->pExplain = 0;
          198  +  if( sqlite3_value_type(argv[0])!=SQLITE_TEXT ){
          199  +    pCur->rc = SQLITE_DONE;
          200  +    return SQLITE_OK;
          201  +  }
          202  +  sqlite3_free(pCur->zSql);
          203  +  pCur->zSql = sqlite3_mprintf("%s", sqlite3_value_text(argv[0]));
          204  +  if( pCur->zSql ){
          205  +    zSql = sqlite3_mprintf("EXPLAIN %s", pCur->zSql);
          206  +  }
          207  +  if( zSql==0 ){
          208  +    rc = SQLITE_NOMEM;
          209  +  }else{
          210  +    rc = sqlite3_prepare_v2(pCur->db, zSql, -1, &pCur->pExplain, 0);
          211  +  }
          212  +  if( rc ){
          213  +    sqlite3_finalize(pCur->pExplain);
          214  +    pCur->pExplain = 0;
          215  +    sqlite3_free(pCur->zSql);
          216  +    pCur->zSql = 0;
          217  +  }else{
          218  +    pCur->rc = sqlite3_step(pCur->pExplain);
          219  +    rc = (pCur->rc==SQLITE_DONE || pCur->rc==SQLITE_ROW) ? SQLITE_OK : pCur->rc;
          220  +  }
          221  +  return rc;
          222  +}
          223  +
          224  +/*
          225  +** SQLite will invoke this method one or more times while planning a query
          226  +** that uses the explain virtual table.  This routine needs to create
          227  +** a query plan for each invocation and compute an estimated cost for that
          228  +** plan.
          229  +*/
          230  +static int explainBestIndex(
          231  +  sqlite3_vtab *tab,
          232  +  sqlite3_index_info *pIdxInfo
          233  +){
          234  +  int i;
          235  +
          236  +  pIdxInfo->estimatedCost = (double)1000000;
          237  +  pIdxInfo->estimatedRows = 500;
          238  +  for(i=0; i<pIdxInfo->nConstraint; i++){
          239  +    struct sqlite3_index_constraint *p = &pIdxInfo->aConstraint[i];
          240  +    if( p->usable
          241  +     && p->iColumn==EXPLN_COLUMN_SQL
          242  +     && p->op==SQLITE_INDEX_CONSTRAINT_EQ
          243  +    ){
          244  +      pIdxInfo->estimatedCost = 10.0;
          245  +      pIdxInfo->idxNum = 1;
          246  +      pIdxInfo->aConstraintUsage[i].argvIndex = 1;
          247  +      pIdxInfo->aConstraintUsage[i].omit = 1;
          248  +      break;
          249  +    }
          250  +  }
          251  +  return SQLITE_OK;
          252  +}
          253  +
          254  +/*
          255  +** This following structure defines all the methods for the 
          256  +** explain virtual table.
          257  +*/
          258  +static sqlite3_module explainModule = {
          259  +  0,                         /* iVersion */
          260  +  0,                         /* xCreate */
          261  +  explainConnect,            /* xConnect */
          262  +  explainBestIndex,          /* xBestIndex */
          263  +  explainDisconnect,         /* xDisconnect */
          264  +  0,                         /* xDestroy */
          265  +  explainOpen,               /* xOpen - open a cursor */
          266  +  explainClose,              /* xClose - close a cursor */
          267  +  explainFilter,             /* xFilter - configure scan constraints */
          268  +  explainNext,               /* xNext - advance a cursor */
          269  +  explainEof,                /* xEof - check for end of scan */
          270  +  explainColumn,             /* xColumn - read data */
          271  +  explainRowid,              /* xRowid - read data */
          272  +  0,                         /* xUpdate */
          273  +  0,                         /* xBegin */
          274  +  0,                         /* xSync */
          275  +  0,                         /* xCommit */
          276  +  0,                         /* xRollback */
          277  +  0,                         /* xFindMethod */
          278  +  0,                         /* xRename */
          279  +  0,                         /* xSavepoint */
          280  +  0,                         /* xRelease */
          281  +  0,                         /* xRollbackTo */
          282  +};
          283  +
          284  +#endif /* SQLITE_OMIT_VIRTUALTABLE */
          285  +
          286  +int sqlite3ExplainVtabInit(sqlite3 *db){
          287  +  int rc = SQLITE_OK;
          288  +#ifndef SQLITE_OMIT_VIRTUALTABLE
          289  +  rc = sqlite3_create_module(db, "explain", &explainModule, 0);
          290  +#endif
          291  +  return rc;
          292  +}
          293  +
          294  +#ifdef _WIN32
          295  +__declspec(dllexport)
          296  +#endif
          297  +int sqlite3_explain_init(
          298  +  sqlite3 *db, 
          299  +  char **pzErrMsg, 
          300  +  const sqlite3_api_routines *pApi
          301  +){
          302  +  int rc = SQLITE_OK;
          303  +  SQLITE_EXTENSION_INIT2(pApi);
          304  +#ifndef SQLITE_OMIT_VIRTUALTABLE
          305  +  rc = sqlite3ExplainVtabInit(db);
          306  +#endif
          307  +  return rc;
          308  +}

Changes to main.mk.

   357    357   #
   358    358   TESTSRC += \
   359    359     $(TOP)/ext/misc/amatch.c \
   360    360     $(TOP)/ext/misc/carray.c \
   361    361     $(TOP)/ext/misc/closure.c \
   362    362     $(TOP)/ext/misc/csv.c \
   363    363     $(TOP)/ext/misc/eval.c \
          364  +  $(TOP)/ext/misc/explain.c \
   364    365     $(TOP)/ext/misc/fileio.c \
   365    366     $(TOP)/ext/misc/fuzzer.c \
   366    367     $(TOP)/ext/misc/ieee754.c \
   367    368     $(TOP)/ext/misc/mmapwarm.c \
   368    369     $(TOP)/ext/misc/nextchar.c \
   369    370     $(TOP)/ext/misc/normalize.c \
   370    371     $(TOP)/ext/misc/percentile.c \

Changes to src/test1.c.

  7034   7034     Tcl_Obj *CONST objv[]
  7035   7035   ){
  7036   7036     extern int sqlite3_amatch_init(sqlite3*,char**,const sqlite3_api_routines*);
  7037   7037     extern int sqlite3_carray_init(sqlite3*,char**,const sqlite3_api_routines*);
  7038   7038     extern int sqlite3_closure_init(sqlite3*,char**,const sqlite3_api_routines*);
  7039   7039     extern int sqlite3_csv_init(sqlite3*,char**,const sqlite3_api_routines*);
  7040   7040     extern int sqlite3_eval_init(sqlite3*,char**,const sqlite3_api_routines*);
         7041  +  extern int sqlite3_explain_init(sqlite3*,char**,const sqlite3_api_routines*);
  7041   7042     extern int sqlite3_fileio_init(sqlite3*,char**,const sqlite3_api_routines*);
  7042   7043     extern int sqlite3_fuzzer_init(sqlite3*,char**,const sqlite3_api_routines*);
  7043   7044     extern int sqlite3_ieee_init(sqlite3*,char**,const sqlite3_api_routines*);
  7044   7045     extern int sqlite3_nextchar_init(sqlite3*,char**,const sqlite3_api_routines*);
  7045   7046     extern int sqlite3_percentile_init(sqlite3*,char**,const sqlite3_api_routines*);
  7046   7047     extern int sqlite3_regexp_init(sqlite3*,char**,const sqlite3_api_routines*);
  7047   7048     extern int sqlite3_remember_init(sqlite3*,char**,const sqlite3_api_routines*);
................................................................................
  7058   7059       int (*pInit)(sqlite3*,char**,const sqlite3_api_routines*);
  7059   7060     } aExtension[] = {
  7060   7061       { "amatch",                sqlite3_amatch_init               },
  7061   7062       { "carray",                sqlite3_carray_init               },
  7062   7063       { "closure",               sqlite3_closure_init              },
  7063   7064       { "csv",                   sqlite3_csv_init                  },
  7064   7065       { "eval",                  sqlite3_eval_init                 },
         7066  +    { "explain",               sqlite3_explain_init              },
  7065   7067       { "fileio",                sqlite3_fileio_init               },
  7066   7068       { "fuzzer",                sqlite3_fuzzer_init               },
  7067   7069       { "ieee754",               sqlite3_ieee_init                 },
  7068   7070       { "nextchar",              sqlite3_nextchar_init             },
  7069   7071       { "percentile",            sqlite3_percentile_init           },
  7070   7072       { "regexp",                sqlite3_regexp_init               },
  7071   7073       { "remember",              sqlite3_remember_init             },

Changes to test/indexexpr2.test.

   193    193   } {2}
   194    194   do_test 4.130 {
   195    195     set ::cnt 0
   196    196     db eval {UPDATE t1 SET d=d+1;}
   197    197     set ::cnt
   198    198     # Refcnt() should not be invoked because that index does not change.
   199    199   } {0}
          200  +
          201  +# Additional test cases to show that UPDATE does not modify indexes that
          202  +# do not involve unchanged columns.
          203  +#
          204  +load_static_extension db explain
          205  +do_execsql_test 4.200 {
          206  +  CREATE TABLE t2(a,b,c,d,e,f);
          207  +  INSERT INTO t2 VALUES(2,3,4,5,6,7);
          208  +  CREATE INDEX t2abc ON t2(a+b+c);
          209  +  CREATE INDEX t2cd ON t2(c*d);
          210  +  CREATE INDEX t2def ON t2(d,e+25*f);
          211  +  SELECT sqlite_master.name 
          212  +    FROM sqlite_master, explain('UPDATE t2 SET b=b+1')
          213  +   WHERE explain.opcode LIKE 'Open%'
          214  +     AND sqlite_master.rootpage=explain.p2
          215  +   ORDER BY 1;
          216  +} {t2 t2abc}
          217  +do_execsql_test 4.210 {
          218  +  SELECT sqlite_master.name 
          219  +    FROM sqlite_master, explain('UPDATE t2 SET c=c+1')
          220  +   WHERE explain.opcode LIKE 'Open%'
          221  +     AND sqlite_master.rootpage=explain.p2
          222  +   ORDER BY 1;
          223  +} {t2 t2abc t2cd}
          224  +do_execsql_test 4.220 {
          225  +  SELECT sqlite_master.name 
          226  +    FROM sqlite_master, explain('UPDATE t2 SET c=c+1, f=NULL')
          227  +   WHERE explain.opcode LIKE 'Open%'
          228  +     AND sqlite_master.rootpage=explain.p2
          229  +   ORDER BY 1;
          230  +} {t2 t2abc t2cd t2def}
          231  +
   200    232   
   201    233   finish_test