SQLite

Check-in [71ffa195f5]
Login

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

Overview
Comment:Merge updates from trunk.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | callbackConv
Files: files | file ages | folders
SHA1: 71ffa195f5a48280ee522d80ab82e8cdea636961
User & Date: mistachkin 2016-07-29 01:47:18.951
Context
2016-08-24
18:49
Merge updates from trunk. (check-in: 428fc38d95 user: mistachkin tags: callbackConv)
2016-07-29
01:55
Merge additional linkage macro changes from the branch. (check-in: 8b0eb22ee1 user: drh tags: trunk)
01:47
Merge updates from trunk. (check-in: 71ffa195f5 user: mistachkin tags: callbackConv)
01:46
Update the autoconf makefile for MSVC. (check-in: 2daf20980d user: mistachkin tags: callbackConv)
01:32
Undo some unhelpful changes to skip-scan scoring that were added by the check-in [9e2b2681] that improved scoring of index scan. (check-in: c7d51934a4 user: drh tags: trunk)
Changes
Unified Diff Ignore Whitespace Patch
Changes to src/where.c.
︙︙
2474
2475
2476
2477
2478
2479
2480
2481
2482
2483
2484
2485
2486
2487
2488
2489
2490
2491
2492

   && (rc = whereLoopResize(db, pNew, pNew->nLTerm+1))==SQLITE_OK
  ){
    LogEst nIter;
    pNew->u.btree.nEq++;
    pNew->nSkip++;
    pNew->aLTerm[pNew->nLTerm++] = 0;
    pNew->wsFlags |= WHERE_SKIPSCAN;
    nIter = pProbe->aiRowLogEst[saved_nEq]+1 - pProbe->aiRowLogEst[saved_nEq+1];
    pNew->nOut -= nIter;
    /* TUNING:  Because uncertainties in the estimates for skip-scan queries,
    ** add a 1.375 fudge factor to make skip-scan slightly less likely. */
    nIter += 4;
    whereLoopAddBtreeIndex(pBuilder, pSrc, pProbe, nIter + nInMul);
    pNew->nOut = saved_nOut;
    pNew->u.btree.nEq = saved_nEq;
    pNew->nSkip = saved_nSkip;
    pNew->wsFlags = saved_wsFlags;
  }









|



|








2474
2475
2476
2477
2478
2479
2480
2481
2482
2483
2484
2485
2486
2487
2488
2489
2490
2491
2492

   && (rc = whereLoopResize(db, pNew, pNew->nLTerm+1))==SQLITE_OK
  ){
    LogEst nIter;
    pNew->u.btree.nEq++;
    pNew->nSkip++;
    pNew->aLTerm[pNew->nLTerm++] = 0;
    pNew->wsFlags |= WHERE_SKIPSCAN;
    nIter = pProbe->aiRowLogEst[saved_nEq] - pProbe->aiRowLogEst[saved_nEq+1];
    pNew->nOut -= nIter;
    /* TUNING:  Because uncertainties in the estimates for skip-scan queries,
    ** add a 1.375 fudge factor to make skip-scan slightly less likely. */
    nIter += 5;
    whereLoopAddBtreeIndex(pBuilder, pSrc, pProbe, nIter + nInMul);
    pNew->nOut = saved_nOut;
    pNew->u.btree.nEq = saved_nEq;
    pNew->nSkip = saved_nSkip;
    pNew->wsFlags = saved_wsFlags;
  }
︙︙
Changes to test/scanstatus.test.
︙︙
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359

do_eqp_test 5.3.1 {
  SELECT count(*) FROM t2 WHERE y = 'j';
} {0 0 0 {SEARCH TABLE t2 USING COVERING INDEX t2xy (ANY(x) AND y=?)}}
do_execsql_test 5.3.2 {
  SELECT count(*) FROM t2 WHERE y = 'j';
} {19}
do_scanstatus_test 5.3.3 { 
  nLoop 1 nVisit 19 nEst 52.0 zName t2xy zExplain
  {SEARCH TABLE t2 USING COVERING INDEX t2xy (ANY(x) AND y=?)}
}

do_eqp_test 5.4.1 {
  SELECT count(*) FROM t1, t2 WHERE y = c;
} {
  0 0 0 {SCAN TABLE t1 USING COVERING INDEX t1bc}
  0 1 1 {SEARCH TABLE t2 USING COVERING INDEX t2xy (ANY(x) AND y=?)}
}
do_execsql_test 5.4.2 {
  SELECT count(*) FROM t1, t2 WHERE y = c;
} {200}
do_scanstatus_test 5.4.3 { 
  nLoop 1 nVisit 10 nEst 10.0 zName t1bc 
  zExplain {SCAN TABLE t1 USING COVERING INDEX t1bc}
  nLoop 10 nVisit 200 nEst 52.0 zName t2xy 
  zExplain {SEARCH TABLE t2 USING COVERING INDEX t2xy (ANY(x) AND y=?)}
}

do_eqp_test 5.5.1 {
  SELECT count(*) FROM t1, t3 WHERE y = c;
} {
  0 0 1 {SCAN TABLE t3} 








|















|








329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359

do_eqp_test 5.3.1 {
  SELECT count(*) FROM t2 WHERE y = 'j';
} {0 0 0 {SEARCH TABLE t2 USING COVERING INDEX t2xy (ANY(x) AND y=?)}}
do_execsql_test 5.3.2 {
  SELECT count(*) FROM t2 WHERE y = 'j';
} {19}
do_scanstatus_test 5.3.3 { 
  nLoop 1 nVisit 19 nEst 56.0 zName t2xy zExplain
  {SEARCH TABLE t2 USING COVERING INDEX t2xy (ANY(x) AND y=?)}
}

do_eqp_test 5.4.1 {
  SELECT count(*) FROM t1, t2 WHERE y = c;
} {
  0 0 0 {SCAN TABLE t1 USING COVERING INDEX t1bc}
  0 1 1 {SEARCH TABLE t2 USING COVERING INDEX t2xy (ANY(x) AND y=?)}
}
do_execsql_test 5.4.2 {
  SELECT count(*) FROM t1, t2 WHERE y = c;
} {200}
do_scanstatus_test 5.4.3 { 
  nLoop 1 nVisit 10 nEst 10.0 zName t1bc 
  zExplain {SCAN TABLE t1 USING COVERING INDEX t1bc}
  nLoop 10 nVisit 200 nEst 56.0 zName t2xy 
  zExplain {SEARCH TABLE t2 USING COVERING INDEX t2xy (ANY(x) AND y=?)}
}

do_eqp_test 5.5.1 {
  SELECT count(*) FROM t1, t3 WHERE y = c;
} {
  0 0 1 {SCAN TABLE t3}
︙︙
Changes to test/skipscan1.test.
︙︙
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69

# index unconstrainted.
#
do_execsql_test skipscan1-1.2 {
  SELECT a,b,c,d,'|' FROM t1 WHERE b=345 ORDER BY a;
} {abc 345 7 8 | def 345 9 10 |}
do_execsql_test skipscan1-1.2eqp {
  EXPLAIN QUERY PLAN
  SELECT a,b,c,d,'|' FROM t1 WHERE b=345 ORDER BY a;
} {/* USING INDEX t1abc (ANY(a) AND b=?)*/}
do_execsql_test skipscan1-1.2sort {
  EXPLAIN QUERY PLAN
  SELECT a,b,c,d,'|' FROM t1 WHERE b=345 ORDER BY a;
} {~/*ORDER BY*/}

do_execsql_test skipscan1-1.3 {
  SELECT a,b,c,d,'|' FROM t1 WHERE b=345 ORDER BY a DESC;
} {def 345 9 10 | abc 345 7 8 |}
do_execsql_test skipscan1-1.3eqp {
  EXPLAIN QUERY PLAN
  SELECT a,b,c,d,'|' FROM t1 WHERE b=345 ORDER BY a;
} {/* USING INDEX t1abc (ANY(a) AND b=?)*/}
do_execsql_test skipscan1-1.3sort {
  EXPLAIN QUERY PLAN
  SELECT a,b,c,d,'|' FROM t1 WHERE b=345 ORDER BY a;
} {~/*ORDER BY*/}

do_execsql_test skipscan1-1.4 {
  SELECT a,b,c,d,'|' FROM t1 WHERE c=6 ORDER BY a, b, c;
} {abc 234 6 7 | bcd 100 6 11 |}
do_execsql_test skipscan1-1.4eqp {
  EXPLAIN QUERY PLAN








|



|



|



|



|








39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69

# index unconstrainted.
#
do_execsql_test skipscan1-1.2 {
  SELECT a,b,c,d,'|' FROM t1 WHERE b=345 ORDER BY a;
} {abc 345 7 8 | def 345 9 10 |}
do_execsql_test skipscan1-1.2eqp {
  EXPLAIN QUERY PLAN
  SELECT a,b,c,d,'|' FROM t1 WHERE d<>99 AND b=345 ORDER BY a;
} {/* USING INDEX t1abc (ANY(a) AND b=?)*/}
do_execsql_test skipscan1-1.2sort {
  EXPLAIN QUERY PLAN
  SELECT a,b,c,d,'|' FROM t1 WHERE d<>99 AND b=345 ORDER BY a;
} {~/*ORDER BY*/}

do_execsql_test skipscan1-1.3 {
  SELECT a,b,c,d,'|' FROM t1 WHERE d<>99 AND b=345 ORDER BY a DESC;
} {def 345 9 10 | abc 345 7 8 |}
do_execsql_test skipscan1-1.3eqp {
  EXPLAIN QUERY PLAN
  SELECT a,b,c,d,'|' FROM t1 WHERE d<>99 AND b=345 ORDER BY a DESC;
} {/* USING INDEX t1abc (ANY(a) AND b=?)*/}
do_execsql_test skipscan1-1.3sort {
  EXPLAIN QUERY PLAN
  SELECT a,b,c,d,'|' FROM t1 WHERE d<>99 AND b=345 ORDER BY a DESC;
} {~/*ORDER BY*/}

do_execsql_test skipscan1-1.4 {
  SELECT a,b,c,d,'|' FROM t1 WHERE c=6 ORDER BY a, b, c;
} {abc 234 6 7 | bcd 100 6 11 |}
do_execsql_test skipscan1-1.4eqp {
  EXPLAIN QUERY PLAN
︙︙
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168

  ** columns are not selective. */
  ANALYZE;
  UPDATE sqlite_stat1 SET stat='10000 5000 2000 10' WHERE idx NOT NULL;
  ANALYZE sqlite_master;
} {}

do_execsql_test skipscan1-2.2 {
  SELECT a,b,c,d,'|' FROM t2 WHERE b=345 ORDER BY a;
} {abc 345 7 8 | def 345 9 10 |}
do_execsql_test skipscan1-2.2eqp {
  EXPLAIN QUERY PLAN
  SELECT a,b,c,d,'|' FROM t2 WHERE b=345 ORDER BY a;
} {/* USING INDEX sqlite_autoindex_t2_1 (ANY(a) AND b=?)*/}
do_execsql_test skipscan1-2.2sort {
  EXPLAIN QUERY PLAN
  SELECT a,b,c,d,'|' FROM t2 WHERE b=345 ORDER BY a;
} {~/*ORDER BY*/}


do_execsql_test skipscan1-3.1 {
  CREATE TABLE t3(a TEXT, b INT, c INT, d INT,
                  PRIMARY KEY(a,b,c)) WITHOUT ROWID;
  INSERT INTO t3 SELECT * FROM t1;








|



|



|








146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168

  ** columns are not selective. */
  ANALYZE;
  UPDATE sqlite_stat1 SET stat='10000 5000 2000 10' WHERE idx NOT NULL;
  ANALYZE sqlite_master;
} {}

do_execsql_test skipscan1-2.2 {
  SELECT a,b,c,d,'|' FROM t2 WHERE d<>99 AND b=345 ORDER BY a;
} {abc 345 7 8 | def 345 9 10 |}
do_execsql_test skipscan1-2.2eqp {
  EXPLAIN QUERY PLAN
  SELECT a,b,c,d,'|' FROM t2 WHERE d<>99 AND b=345 ORDER BY a;
} {/* USING INDEX sqlite_autoindex_t2_1 (ANY(a) AND b=?)*/}
do_execsql_test skipscan1-2.2sort {
  EXPLAIN QUERY PLAN
  SELECT a,b,c,d,'|' FROM t2 WHERE d<>99 AND b=345 ORDER BY a;
} {~/*ORDER BY*/}


do_execsql_test skipscan1-3.1 {
  CREATE TABLE t3(a TEXT, b INT, c INT, d INT,
                  PRIMARY KEY(a,b,c)) WITHOUT ROWID;
  INSERT INTO t3 SELECT * FROM t1;
︙︙