/ Check-in [71ffa195]
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
Context
2016-08-24
18:49
Merge updates from trunk. check-in: 428fc38d user: mistachkin tags: callbackConv
2016-07-29
01:55
Merge additional linkage macro changes from the branch. check-in: 8b0eb22e user: drh tags: trunk
01:47
Merge updates from trunk. check-in: 71ffa195 user: mistachkin tags: callbackConv
01:46
Update the autoconf makefile for MSVC. check-in: 2daf2098 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: c7d51934 user: drh tags: trunk
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to src/where.c.

  2474   2474      && (rc = whereLoopResize(db, pNew, pNew->nLTerm+1))==SQLITE_OK
  2475   2475     ){
  2476   2476       LogEst nIter;
  2477   2477       pNew->u.btree.nEq++;
  2478   2478       pNew->nSkip++;
  2479   2479       pNew->aLTerm[pNew->nLTerm++] = 0;
  2480   2480       pNew->wsFlags |= WHERE_SKIPSCAN;
  2481         -    nIter = pProbe->aiRowLogEst[saved_nEq]+1 - pProbe->aiRowLogEst[saved_nEq+1];
         2481  +    nIter = pProbe->aiRowLogEst[saved_nEq] - pProbe->aiRowLogEst[saved_nEq+1];
  2482   2482       pNew->nOut -= nIter;
  2483   2483       /* TUNING:  Because uncertainties in the estimates for skip-scan queries,
  2484   2484       ** add a 1.375 fudge factor to make skip-scan slightly less likely. */
  2485         -    nIter += 4;
         2485  +    nIter += 5;
  2486   2486       whereLoopAddBtreeIndex(pBuilder, pSrc, pProbe, nIter + nInMul);
  2487   2487       pNew->nOut = saved_nOut;
  2488   2488       pNew->u.btree.nEq = saved_nEq;
  2489   2489       pNew->nSkip = saved_nSkip;
  2490   2490       pNew->wsFlags = saved_wsFlags;
  2491   2491     }
  2492   2492   

Changes to test/scanstatus.test.

   329    329   do_eqp_test 5.3.1 {
   330    330     SELECT count(*) FROM t2 WHERE y = 'j';
   331    331   } {0 0 0 {SEARCH TABLE t2 USING COVERING INDEX t2xy (ANY(x) AND y=?)}}
   332    332   do_execsql_test 5.3.2 {
   333    333     SELECT count(*) FROM t2 WHERE y = 'j';
   334    334   } {19}
   335    335   do_scanstatus_test 5.3.3 { 
   336         -  nLoop 1 nVisit 19 nEst 52.0 zName t2xy zExplain
          336  +  nLoop 1 nVisit 19 nEst 56.0 zName t2xy zExplain
   337    337     {SEARCH TABLE t2 USING COVERING INDEX t2xy (ANY(x) AND y=?)}
   338    338   }
   339    339   
   340    340   do_eqp_test 5.4.1 {
   341    341     SELECT count(*) FROM t1, t2 WHERE y = c;
   342    342   } {
   343    343     0 0 0 {SCAN TABLE t1 USING COVERING INDEX t1bc}
................................................................................
   345    345   }
   346    346   do_execsql_test 5.4.2 {
   347    347     SELECT count(*) FROM t1, t2 WHERE y = c;
   348    348   } {200}
   349    349   do_scanstatus_test 5.4.3 { 
   350    350     nLoop 1 nVisit 10 nEst 10.0 zName t1bc 
   351    351     zExplain {SCAN TABLE t1 USING COVERING INDEX t1bc}
   352         -  nLoop 10 nVisit 200 nEst 52.0 zName t2xy 
          352  +  nLoop 10 nVisit 200 nEst 56.0 zName t2xy 
   353    353     zExplain {SEARCH TABLE t2 USING COVERING INDEX t2xy (ANY(x) AND y=?)}
   354    354   }
   355    355   
   356    356   do_eqp_test 5.5.1 {
   357    357     SELECT count(*) FROM t1, t3 WHERE y = c;
   358    358   } {
   359    359     0 0 1 {SCAN TABLE t3} 

Changes to test/skipscan1.test.

    39     39   # index unconstrainted.
    40     40   #
    41     41   do_execsql_test skipscan1-1.2 {
    42     42     SELECT a,b,c,d,'|' FROM t1 WHERE b=345 ORDER BY a;
    43     43   } {abc 345 7 8 | def 345 9 10 |}
    44     44   do_execsql_test skipscan1-1.2eqp {
    45     45     EXPLAIN QUERY PLAN
    46         -  SELECT a,b,c,d,'|' FROM t1 WHERE b=345 ORDER BY a;
           46  +  SELECT a,b,c,d,'|' FROM t1 WHERE d<>99 AND b=345 ORDER BY a;
    47     47   } {/* USING INDEX t1abc (ANY(a) AND b=?)*/}
    48     48   do_execsql_test skipscan1-1.2sort {
    49     49     EXPLAIN QUERY PLAN
    50         -  SELECT a,b,c,d,'|' FROM t1 WHERE b=345 ORDER BY a;
           50  +  SELECT a,b,c,d,'|' FROM t1 WHERE d<>99 AND b=345 ORDER BY a;
    51     51   } {~/*ORDER BY*/}
    52     52   
    53     53   do_execsql_test skipscan1-1.3 {
    54         -  SELECT a,b,c,d,'|' FROM t1 WHERE b=345 ORDER BY a DESC;
           54  +  SELECT a,b,c,d,'|' FROM t1 WHERE d<>99 AND b=345 ORDER BY a DESC;
    55     55   } {def 345 9 10 | abc 345 7 8 |}
    56     56   do_execsql_test skipscan1-1.3eqp {
    57     57     EXPLAIN QUERY PLAN
    58         -  SELECT a,b,c,d,'|' FROM t1 WHERE b=345 ORDER BY a;
           58  +  SELECT a,b,c,d,'|' FROM t1 WHERE d<>99 AND b=345 ORDER BY a DESC;
    59     59   } {/* USING INDEX t1abc (ANY(a) AND b=?)*/}
    60     60   do_execsql_test skipscan1-1.3sort {
    61     61     EXPLAIN QUERY PLAN
    62         -  SELECT a,b,c,d,'|' FROM t1 WHERE b=345 ORDER BY a;
           62  +  SELECT a,b,c,d,'|' FROM t1 WHERE d<>99 AND b=345 ORDER BY a DESC;
    63     63   } {~/*ORDER BY*/}
    64     64   
    65     65   do_execsql_test skipscan1-1.4 {
    66     66     SELECT a,b,c,d,'|' FROM t1 WHERE c=6 ORDER BY a, b, c;
    67     67   } {abc 234 6 7 | bcd 100 6 11 |}
    68     68   do_execsql_test skipscan1-1.4eqp {
    69     69     EXPLAIN QUERY PLAN
................................................................................
   146    146     ** columns are not selective. */
   147    147     ANALYZE;
   148    148     UPDATE sqlite_stat1 SET stat='10000 5000 2000 10' WHERE idx NOT NULL;
   149    149     ANALYZE sqlite_master;
   150    150   } {}
   151    151   
   152    152   do_execsql_test skipscan1-2.2 {
   153         -  SELECT a,b,c,d,'|' FROM t2 WHERE b=345 ORDER BY a;
          153  +  SELECT a,b,c,d,'|' FROM t2 WHERE d<>99 AND b=345 ORDER BY a;
   154    154   } {abc 345 7 8 | def 345 9 10 |}
   155    155   do_execsql_test skipscan1-2.2eqp {
   156    156     EXPLAIN QUERY PLAN
   157         -  SELECT a,b,c,d,'|' FROM t2 WHERE b=345 ORDER BY a;
          157  +  SELECT a,b,c,d,'|' FROM t2 WHERE d<>99 AND b=345 ORDER BY a;
   158    158   } {/* USING INDEX sqlite_autoindex_t2_1 (ANY(a) AND b=?)*/}
   159    159   do_execsql_test skipscan1-2.2sort {
   160    160     EXPLAIN QUERY PLAN
   161         -  SELECT a,b,c,d,'|' FROM t2 WHERE b=345 ORDER BY a;
          161  +  SELECT a,b,c,d,'|' FROM t2 WHERE d<>99 AND b=345 ORDER BY a;
   162    162   } {~/*ORDER BY*/}
   163    163   
   164    164   
   165    165   do_execsql_test skipscan1-3.1 {
   166    166     CREATE TABLE t3(a TEXT, b INT, c INT, d INT,
   167    167                     PRIMARY KEY(a,b,c)) WITHOUT ROWID;
   168    168     INSERT INTO t3 SELECT * FROM t1;