SQLite

Check-in [b2d41ff702]
Login

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

Overview
Comment:Update the explain virtual table to make use of SQLITE_CONSTRAINT.
Downloads: Tarball | ZIP archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA3-256: b2d41ff7027b44ccb5dffc303c47d42f1f6fd66ce22e90450d3a666c73fe8b8e
User & Date: drh 2018-11-16 19:19:58.887
Context
2018-11-16
20:18
Add SQLITE_CONSTRAINT support to the xBestIndex methods on dbstat, sqlite_dbpage, and zipfile. (check-in: 5cd24e552b user: drh tags: trunk)
19:19
Update the explain virtual table to make use of SQLITE_CONSTRAINT. (check-in: b2d41ff702 user: drh tags: trunk)
16:04
Fix the JSON table-valued functions to make use of SQLITE_CONSTRAINT. (check-in: 3f34f4f561 user: drh tags: trunk)
Changes
Unified Diff Ignore Whitespace Patch
Changes to ext/misc/explain.c.
︙︙
14
15
16
17
18
19
20




21
22
23
24
25
26
27

** EXPLAIN output from an SQL statement.
**
** Usage example:
**
**     .load ./explain
**     SELECT p2 FROM explain('SELECT * FROM sqlite_master')
**      WHERE opcode='OpenRead';




*/
#if !defined(SQLITEINT_H)
#include "sqlite3ext.h"
#endif
SQLITE_EXTENSION_INIT1
#include <assert.h>
#include <string.h>








>
>
>
>








14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31

** EXPLAIN output from an SQL statement.
**
** Usage example:
**
**     .load ./explain
**     SELECT p2 FROM explain('SELECT * FROM sqlite_master')
**      WHERE opcode='OpenRead';
**
** This module was originally written to help simplify SQLite testing,
** by providing an easier means of verifying certain patterns in the
** generated bytecode.
*/
#if !defined(SQLITEINT_H)
#include "sqlite3ext.h"
#endif
SQLITE_EXTENSION_INIT1
#include <assert.h>
#include <string.h>
︙︙
228
229
230
231
232
233
234
235


236
237
238
239
240

241
242

243



244

245
246
247
248



249
250
251
252
253
254
255
256
257

** a query plan for each invocation and compute an estimated cost for that
** plan.
*/
static int explainBestIndex(
  sqlite3_vtab *tab,
  sqlite3_index_info *pIdxInfo
){
  int i;



  pIdxInfo->estimatedCost = (double)1000000;
  pIdxInfo->estimatedRows = 500;
  for(i=0; i<pIdxInfo->nConstraint; i++){
    struct sqlite3_index_constraint *p = &pIdxInfo->aConstraint[i];

    if( p->usable
     && p->iColumn==EXPLN_COLUMN_SQL

     && p->op==SQLITE_INDEX_CONSTRAINT_EQ



    ){

      pIdxInfo->estimatedCost = 10.0;
      pIdxInfo->idxNum = 1;
      pIdxInfo->aConstraintUsage[i].argvIndex = 1;
      pIdxInfo->aConstraintUsage[i].omit = 1;



      break;
    }
  }
  return SQLITE_OK;
}

/*
** This following structure defines all the methods for the 
** explain virtual table.








|
>
>

<



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








232
233
234
235
236
237
238
239
240
241
242

243
244
245
246
247

248
249
250
251
252
253
254
255
256
257
258
259
260
261
262

263
264
265
266
267
268
269

** a query plan for each invocation and compute an estimated cost for that
** plan.
*/
static int explainBestIndex(
  sqlite3_vtab *tab,
  sqlite3_index_info *pIdxInfo
){
  int i;                  /* Loop counter */
  int idx = -1;           /* Index of a usable == constraint against SQL */
  int unusable = 0;       /* True if there are unusable constraints on SQL */


  pIdxInfo->estimatedRows = 500;
  for(i=0; i<pIdxInfo->nConstraint; i++){
    struct sqlite3_index_constraint *p = &pIdxInfo->aConstraint[i];
    if( p->iColumn!=EXPLN_COLUMN_SQL ) continue;
    if( !p->usable ){

      unusable = 1;
    }else if( p->op==SQLITE_INDEX_CONSTRAINT_EQ ){
      idx = i;
    }
  }
  if( idx>=0 ){
    /* There exists a usable == constraint against the SQL column */
    pIdxInfo->estimatedCost = 10.0;
    pIdxInfo->idxNum = 1;
    pIdxInfo->aConstraintUsage[idx].argvIndex = 1;
    pIdxInfo->aConstraintUsage[idx].omit = 1;
  }else if( unusable ){
    /* There are unusable constraints against the SQL column.  Do not allow
    ** this plan to continue forward. */
    return SQLITE_CONSTRAINT;

  }
  return SQLITE_OK;
}

/*
** This following structure defines all the methods for the 
** explain virtual table.
︙︙