/ Check-in [b2d41ff7]
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 | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA3-256:b2d41ff7027b44ccb5dffc303c47d42f1f6fd66ce22e90450d3a666c73fe8b8e
User & Date: drh 2018-11-16 19:19:58
Context
2018-11-16
20:18
Add SQLITE_CONSTRAINT support to the xBestIndex methods on dbstat, sqlite_dbpage, and zipfile. check-in: 5cd24e55 user: drh tags: trunk
19:19
Update the explain virtual table to make use of SQLITE_CONSTRAINT. check-in: b2d41ff7 user: drh tags: trunk
16:04
Fix the JSON table-valued functions to make use of SQLITE_CONSTRAINT. check-in: 3f34f4f5 user: drh tags: trunk
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to ext/misc/explain.c.

14
15
16
17
18
19
20




21
22
23
24
25
26
27
...
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
258
** 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>
................................................................................
** 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.
*/







>
>
>
>







 







|
>
>

<



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







14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
...
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
270
** 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>
................................................................................
** 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.
*/