/ Check-in [36373b85]
Login

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

Overview
Comment:Fixes to EXPLAIN QUERY PLAN output. Change weights back to something closer to what they are in legacy. More test case fixes.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | nextgen-query-plan-logcost
Files: files | file ages | folders
SHA1: 36373b85f9a97840aa06e24ae31c12fcfbae084e
User & Date: drh 2013-06-11 02:32:50
Context
2013-06-11
02:36
Use a logarithmic rather than linear cost and row-count measures. Do not report row count estimates in EQP output. check-in: b777b109 user: drh tags: nextgen-query-plan-exp
02:32
Fixes to EXPLAIN QUERY PLAN output. Change weights back to something closer to what they are in legacy. More test case fixes. Closed-Leaf check-in: 36373b85 user: drh tags: nextgen-query-plan-logcost
01:50
Handle virtual tables correctly when using logarithmic costs. Fixes to test cases. check-in: e612664a user: drh tags: nextgen-query-plan-logcost
Changes
Hide Diffs Unified Diffs Show Whitespace Changes Patch

Changes to src/select.c.

3880
3881
3882
3883
3884
3885
3886
3887
3888
3889
3890
3891
3892
3893
3894
3895
3896
3897
3898
#ifndef SQLITE_OMIT_EXPLAIN
static void explainSimpleCount(
  Parse *pParse,                  /* Parse context */
  Table *pTab,                    /* Table being queried */
  Index *pIdx                     /* Index used to optimize scan, or NULL */
){
  if( pParse->explain==2 ){
    char *zEqp = sqlite3MPrintf(pParse->db, "SCAN TABLE %s %s%s(~%d rows)",
        pTab->zName, 
        pIdx ? "USING COVERING INDEX " : "",
        pIdx ? pIdx->zName : "",
        pTab->nRowEst
    );
    sqlite3VdbeAddOp4(
        pParse->pVdbe, OP_Explain, pParse->iSelectId, 0, 0, zEqp, P4_DYNAMIC
    );
  }
}
#else







|

|
|
<







3880
3881
3882
3883
3884
3885
3886
3887
3888
3889
3890

3891
3892
3893
3894
3895
3896
3897
#ifndef SQLITE_OMIT_EXPLAIN
static void explainSimpleCount(
  Parse *pParse,                  /* Parse context */
  Table *pTab,                    /* Table being queried */
  Index *pIdx                     /* Index used to optimize scan, or NULL */
){
  if( pParse->explain==2 ){
    char *zEqp = sqlite3MPrintf(pParse->db, "SCAN TABLE %s%s%s",
        pTab->zName, 
        pIdx ? " USING COVERING INDEX " : "",
        pIdx ? pIdx->zName : ""

    );
    sqlite3VdbeAddOp4(
        pParse->pVdbe, OP_Explain, pParse->iSelectId, 0, 0, zEqp, P4_DYNAMIC
    );
  }
}
#else

Changes to src/where.c.

4304
4305
4306
4307
4308
4309
4310
4311
4312
4313
4314
4315
4316
4317
4318
#endif
    if( pNew->wsFlags & (WHERE_IDX_ONLY|WHERE_IPK) ){
      /* Step cost for each output row */
      pNew->rRun = whereCostAdd(pNew->rRun, pNew->nOut);
    }else{
      /* Each row involves a step of the index, then a binary search of
      ** the main table */
      WhereCost rStepAndSearch = rLogSize>80 ? rLogSize-80 : 1;
      pNew->rRun =  whereCostAdd(pNew->rRun, rStepAndSearch);
    }
    /* TBD: Adjust nOut for additional constraints */
    rc = whereLoopInsert(pBuilder, pNew);
    if( (pNew->wsFlags & WHERE_TOP_LIMIT)==0
     && pNew->u.btree.nEq<=pProbe->nColumn
     && pProbe->zName!=0







|







4304
4305
4306
4307
4308
4309
4310
4311
4312
4313
4314
4315
4316
4317
4318
#endif
    if( pNew->wsFlags & (WHERE_IDX_ONLY|WHERE_IPK) ){
      /* Step cost for each output row */
      pNew->rRun = whereCostAdd(pNew->rRun, pNew->nOut);
    }else{
      /* Each row involves a step of the index, then a binary search of
      ** the main table */
      WhereCost rStepAndSearch = whereCostAdd(10, rLogSize>17 ? rLogSize-17 : 1);
      pNew->rRun =  whereCostAdd(pNew->rRun, rStepAndSearch);
    }
    /* TBD: Adjust nOut for additional constraints */
    rc = whereLoopInsert(pBuilder, pNew);
    if( (pNew->wsFlags & WHERE_TOP_LIMIT)==0
     && pNew->u.btree.nEq<=pProbe->nColumn
     && pProbe->zName!=0

Changes to test/like.test.

162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
  set ::sqlite_sort_count 0
  set data [execsql $sql]
  if {$::sqlite_sort_count} {set x sort} {set x nosort}
  lappend data $x
  set eqp [execsql "EXPLAIN QUERY PLAN $sql"]
  # puts eqp=$eqp
  foreach {a b c x} $eqp {
    if {[regexp { TABLE (\w+ AS )?(\w+) USING COVERING INDEX (\w+)\W} \
        $x all as tab idx]} {
      lappend data {} $idx
    } elseif {[regexp { TABLE (\w+ AS )?(\w+) USING.* INDEX (\w+)\W} \
        $x all as tab idx]} {
      lappend data $tab $idx
    } elseif {[regexp { TABLE (\w+ AS )?(\w+)\W} $x all as tab]} {
      lappend data $tab *
    }
  }
  return $data   
}

# Perform tests on the like optimization.







|


|


|







162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
  set ::sqlite_sort_count 0
  set data [execsql $sql]
  if {$::sqlite_sort_count} {set x sort} {set x nosort}
  lappend data $x
  set eqp [execsql "EXPLAIN QUERY PLAN $sql"]
  # puts eqp=$eqp
  foreach {a b c x} $eqp {
    if {[regexp { TABLE (\w+ AS )?(\w+) USING COVERING INDEX (\w+)\y} \
        $x all as tab idx]} {
      lappend data {} $idx
    } elseif {[regexp { TABLE (\w+ AS )?(\w+) USING.* INDEX (\w+)\y} \
        $x all as tab idx]} {
      lappend data $tab $idx
    } elseif {[regexp { TABLE (\w+ AS )?(\w+)\y} $x all as tab]} {
      lappend data $tab *
    }
  }
  return $data   
}

# Perform tests on the like optimization.

Changes to test/where.test.

whitespace changes only

Changes to test/where2.test.

72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
  set ::sqlite_sort_count 0
  set data [execsql $sql]
  if {$::sqlite_sort_count} {set x sort} {set x nosort}
  lappend data $x
  set eqp [execsql "EXPLAIN QUERY PLAN $sql"]
  # puts eqp=$eqp
  foreach {a b c x} $eqp {
    if {[regexp { TABLE (\w+ AS )?(\w+) USING.* INDEX (\w+)\W} \
        $x all as tab idx]} {
      lappend data $tab $idx
    } elseif {[regexp { TABLE (\w+ AS )?(\w+)\W} $x all as tab]} {
      lappend data $tab *
    }
  }
  return $data   
}









|


|







72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
  set ::sqlite_sort_count 0
  set data [execsql $sql]
  if {$::sqlite_sort_count} {set x sort} {set x nosort}
  lappend data $x
  set eqp [execsql "EXPLAIN QUERY PLAN $sql"]
  # puts eqp=$eqp
  foreach {a b c x} $eqp {
    if {[regexp { TABLE (\w+ AS )?(\w+) USING.* INDEX (\w+)\y} \
        $x all as tab idx]} {
      lappend data $tab $idx
    } elseif {[regexp { TABLE (\w+ AS )?(\w+)\y} $x all as tab]} {
      lappend data $tab *
    }
  }
  return $data   
}


Changes to test/where3.test.

107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
#
proc queryplan {sql} {
  set ::sqlite_sort_count 0
  set data [execsql $sql]
  set eqp [execsql "EXPLAIN QUERY PLAN $sql"]
  # puts eqp=$eqp
  foreach {a b c x} $eqp {
    if {[regexp { TABLE (\w+ AS )?(\w+) USING.* INDEX (\w+)\W} \
        $x all as tab idx]} {
      lappend data $tab $idx
    } elseif {[regexp { TABLE (\w+ AS )?(\w+)\W} $x all as tab]} {
      lappend data $tab *
    }
  }
  return $data   
}









|


|







107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
#
proc queryplan {sql} {
  set ::sqlite_sort_count 0
  set data [execsql $sql]
  set eqp [execsql "EXPLAIN QUERY PLAN $sql"]
  # puts eqp=$eqp
  foreach {a b c x} $eqp {
    if {[regexp { TABLE (\w+ AS )?(\w+) USING.* INDEX (\w+)\y} \
        $x all as tab idx]} {
      lappend data $tab $idx
    } elseif {[regexp { TABLE (\w+ AS )?(\w+)\y} $x all as tab]} {
      lappend data $tab *
    }
  }
  return $data   
}


Changes to test/whereE.test.

whitespace changes only