/ Check-in [ffe3fea4]
Login

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

Overview
Comment:Add the autoindex2.test testing module.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | autoindex-improvements
Files: files | file ages | folders
SHA1: ffe3fea47b8ba8ec4557f68243ef3ef5aac00dfd
User & Date: drh 2014-06-17 13:23:36
Context
2014-06-17
20:16
Prevent an automatic index from taking the place of a declared index. Closed-Leaf check-in: 4ece839d user: drh tags: autoindex-improvements
15:53
Improvements to query planning, especially in regards to estimating the cost and benefit of automatic indexes. check-in: 1272fb89 user: drh tags: trunk
13:23
Add the autoindex2.test testing module. check-in: ffe3fea4 user: drh tags: autoindex-improvements
12:33
A different approach to the autoindex problem that deletes query-planner code rather than adding it. check-in: f270fb6e user: drh tags: autoindex-improvements
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Added test/autoindex2.test.

            1  +# 2014-06-17
            2  +#
            3  +# The author disclaims copyright to this source code.  In place of
            4  +# a legal notice, here is a blessing:
            5  +#
            6  +#    May you do good and not evil.
            7  +#    May you find forgiveness for yourself and forgive others.
            8  +#    May you share freely, never taking more than you give.
            9  +#
           10  +#*************************************************************************
           11  +#
           12  +# This file implements regression tests for SQLite library.  The
           13  +# focus of this script is testing automatic index creation logic.
           14  +#
           15  +# This file contains a single real-world test case that was giving
           16  +# suboptimal performance because of over-use of automatic indexes.
           17  +#
           18  +
           19  +set testdir [file dirname $argv0]
           20  +source $testdir/tester.tcl
           21  +
           22  +
           23  +do_execsql_test autoindex2-100 {
           24  +  CREATE TABLE t1(
           25  +    t1_id largeint,
           26  +    did char(9),
           27  +    ptime largeint,
           28  +    exbyte char(4),
           29  +    pe_id int,
           30  +    field_id int,
           31  +    mass float,
           32  +    param10 float,
           33  +    param11 float,
           34  +    exmass float,
           35  +    deviation float,
           36  +    trange float,
           37  +    vstatus int,
           38  +    commit_status int,
           39  +    formula char(329),
           40  +    tier int DEFAULT 2,
           41  +    ssid int DEFAULT 0,
           42  +    last_operation largeint DEFAULT 0,
           43  +    admin_uuid int DEFAULT 0,
           44  +    previous_value float,
           45  +    job_id largeint,
           46  +    last_t1 largeint DEFAULT 0,
           47  +    data_t1 int,
           48  +    previous_date largeint DEFAULT 0,
           49  +    flg8 int DEFAULT 1,
           50  +    failed_fields char(100)
           51  +  );
           52  +  CREATE INDEX t1x0 on t1 (t1_id);
           53  +  CREATE INDEX t1x1 on t1 (ptime, vstatus);
           54  +  CREATE INDEX t1x2 on t1 (did, ssid, ptime, vstatus, exbyte, t1_id);
           55  +  CREATE INDEX t1x3 on t1 (job_id);
           56  +  
           57  +  CREATE TABLE t2(
           58  +    did char(9),
           59  +    client_did char(30),
           60  +    description char(49),
           61  +    uid int,
           62  +    tzid int,
           63  +    privilege int,
           64  +    param2 int,
           65  +    type char(30),
           66  +    subtype char(32),
           67  +    dparam1 char(7) DEFAULT '',
           68  +    param5 char(3) DEFAULT '',
           69  +    notional float DEFAULT 0.000000,
           70  +    create_time largeint,
           71  +    sample_time largeint DEFAULT 0,
           72  +    param6 largeint,
           73  +    frequency int,
           74  +    expiration largeint,
           75  +    uw_status int,
           76  +    next_sample largeint,
           77  +    last_sample largeint,
           78  +    reserve1 char(29) DEFAULT '',
           79  +    reserve2 char(29) DEFAULT '',
           80  +    reserve3 char(29) DEFAULT '',
           81  +    bxcdr char(19) DEFAULT 'XY',
           82  +    ssid int DEFAULT 1,
           83  +    last_t1_id largeint,
           84  +    reserve4 char(29) DEFAULT '',
           85  +    reserve5 char(29) DEFAULT '',
           86  +    param12 int DEFAULT 0,
           87  +    long_did char(100) DEFAULT '',
           88  +    gr_code int DEFAULT 0,
           89  +    drx char(100) DEFAULT '',
           90  +    parent_id char(9) DEFAULT '',
           91  +    param13 int DEFAULT 0,
           92  +    position float DEFAULT 1.000000,
           93  +    client_did3 char(100) DEFAULT '',
           94  +    client_did4 char(100) DEFAULT '',
           95  +    dlib_id char(9) DEFAULT ''
           96  +  );
           97  +  CREATE INDEX t2x0 on t2 (did);
           98  +  CREATE INDEX t2x1 on t2 (client_did);
           99  +  CREATE INDEX t2x2 on t2 (long_did);
          100  +  CREATE INDEX t2x3 on t2 (uid);
          101  +  CREATE INDEX t2x4 on t2 (param2);
          102  +  CREATE INDEX t2x5 on t2 (type);
          103  +  CREATE INDEX t2x6 on t2 (subtype);
          104  +  CREATE INDEX t2x7 on t2 (last_sample);
          105  +  CREATE INDEX t2x8 on t2 (param6);
          106  +  CREATE INDEX t2x9 on t2 (frequency);
          107  +  CREATE INDEX t2x10 on t2 (privilege);
          108  +  CREATE INDEX t2x11 on t2 (sample_time);
          109  +  CREATE INDEX t2x12 on t2 (notional);
          110  +  CREATE INDEX t2x13 on t2 (tzid);
          111  +  CREATE INDEX t2x14 on t2 (gr_code);
          112  +  CREATE INDEX t2x15 on t2 (parent_id);
          113  +  
          114  +  CREATE TABLE t3(
          115  +    uid int,
          116  +    param3 int,
          117  +    uuid int,
          118  +    acc_id int,
          119  +    cust_num int,
          120  +    numerix_id int,
          121  +    pfy char(29),
          122  +    param4 char(29),
          123  +    param15 int DEFAULT 0,
          124  +    flg7 int DEFAULT 0,
          125  +    param21 int DEFAULT 0,
          126  +    bxcdr char(2) DEFAULT 'PC',
          127  +    c31 int DEFAULT 0,
          128  +    c33 int DEFAULT 0,
          129  +    c35 int DEFAULT 0,
          130  +    c37 int,
          131  +    mgr_uuid int,
          132  +    back_up_uuid int,
          133  +    priv_mars int DEFAULT 0,
          134  +    is_qc int DEFAULT 0,
          135  +    c41 int DEFAULT 0,
          136  +    deleted int DEFAULT 0,
          137  +    c47 int DEFAULT 1
          138  +  );
          139  +  CREATE INDEX t3x0 on t3 (uid);
          140  +  CREATE INDEX t3x1 on t3 (param3);
          141  +  CREATE INDEX t3x2 on t3 (uuid);
          142  +  CREATE INDEX t3x3 on t3 (acc_id);
          143  +  CREATE INDEX t3x4 on t3 (param4);
          144  +  CREATE INDEX t3x5 on t3 (pfy);
          145  +  CREATE INDEX t3x6 on t3 (is_qc);
          146  +  SELECT count(*) FROM sqlite_master;
          147  +} {30}
          148  +do_execsql_test autoindex2-110 {
          149  +  ANALYZE sqlite_master;
          150  +  INSERT INTO sqlite_stat1 VALUES('t1','t1x3','10747267 260');
          151  +  INSERT INTO sqlite_stat1 VALUES('t1','t1x2','10747267 121 113 2 2 2 1');
          152  +  INSERT INTO sqlite_stat1 VALUES('t1','t1x1','10747267 50 40');
          153  +  INSERT INTO sqlite_stat1 VALUES('t1','t1x0','10747267 1');
          154  +  INSERT INTO sqlite_stat1 VALUES('t2','t2x15','39667 253');
          155  +  INSERT INTO sqlite_stat1 VALUES('t2','t2x14','39667 19834');
          156  +  INSERT INTO sqlite_stat1 VALUES('t2','t2x13','39667 13223');
          157  +  INSERT INTO sqlite_stat1 VALUES('t2','t2x12','39667 7');
          158  +  INSERT INTO sqlite_stat1 VALUES('t2','t2x11','39667 17');
          159  +  INSERT INTO sqlite_stat1 VALUES('t2','t2x10','39667 19834');
          160  +  INSERT INTO sqlite_stat1 VALUES('t2','t2x9','39667 7934');
          161  +  INSERT INTO sqlite_stat1 VALUES('t2','t2x8','39667 11');
          162  +  INSERT INTO sqlite_stat1 VALUES('t2','t2x7','39667 5');
          163  +  INSERT INTO sqlite_stat1 VALUES('t2','t2x6','39667 242');
          164  +  INSERT INTO sqlite_stat1 VALUES('t2','t2x5','39667 1984');
          165  +  INSERT INTO sqlite_stat1 VALUES('t2','t2x4','39667 4408');
          166  +  INSERT INTO sqlite_stat1 VALUES('t2','t2x3','39667 81');
          167  +  INSERT INTO sqlite_stat1 VALUES('t2','t2x2','39667 551');
          168  +  INSERT INTO sqlite_stat1 VALUES('t2','t2x1','39667 2');
          169  +  INSERT INTO sqlite_stat1 VALUES('t2','t2x0','39667 1');
          170  +  INSERT INTO sqlite_stat1 VALUES('t3','t3x6','569 285');
          171  +  INSERT INTO sqlite_stat1 VALUES('t3','t3x5','569 2');
          172  +  INSERT INTO sqlite_stat1 VALUES('t3','t3x4','569 2');
          173  +  INSERT INTO sqlite_stat1 VALUES('t3','t3x3','569 5');
          174  +  INSERT INTO sqlite_stat1 VALUES('t3','t3x2','569 3');
          175  +  INSERT INTO sqlite_stat1 VALUES('t3','t3x1','569 6');
          176  +  INSERT INTO sqlite_stat1 VALUES('t3','t3x0','569 1');
          177  +  ANALYZE sqlite_master;
          178  +} {}
          179  +do_execsql_test autoindex2-120 {
          180  +  EXPLAIN QUERY PLAN
          181  +  SELECT
          182  +     t1_id,
          183  +     t1.did,
          184  +     param2,
          185  +     param3,
          186  +     t1.ptime,
          187  +     t1.trange,
          188  +     t1.exmass,
          189  +     t1.mass,
          190  +     t1.vstatus,
          191  +     type,
          192  +     subtype,
          193  +     t1.deviation,
          194  +     t1.formula,
          195  +     dparam1,
          196  +     reserve1,
          197  +     reserve2,
          198  +     param4,
          199  +     t1.last_operation,
          200  +     t1.admin_uuid,
          201  +     t1.previous_value,
          202  +     t1.job_id,
          203  +     client_did, 
          204  +     t1.last_t1,
          205  +     t1.data_t1,
          206  +     t1.previous_date,
          207  +     param5,
          208  +     param6,
          209  +     mgr_uuid
          210  +  FROM
          211  +     t1,
          212  +     t2,
          213  +     t3
          214  +  WHERE
          215  +     t1.ptime > 1393520400
          216  +     AND param3<>9001
          217  +     AND t3.flg7 = 1
          218  +     AND t1.did = t2.did
          219  +     AND t2.uid = t3.uid
          220  +  ORDER BY t1.ptime desc LIMIT 500;
          221  +} {0 0 0 {SEARCH TABLE t1 USING INDEX t1x1 (ptime>?)} 0 1 1 {SEARCH TABLE t2 USING INDEX t2x0 (did=?)} 0 2 2 {SEARCH TABLE t3 USING INDEX t3x0 (uid=?)}}
          222  +#
          223  +# ^^^--- Before being fixed, the above was using an automatic covering
          224  +# on t3 and reordering the tables so that t3 was in the outer loop and
          225  +# implementing the ORDER BY clause using a B-Tree.
          226  +
          227  +do_execsql_test autoindex2-120 {
          228  +  EXPLAIN QUERY PLAN
          229  +  SELECT
          230  +     t1_id,
          231  +     t1.did,
          232  +     param2,
          233  +     param3,
          234  +     t1.ptime,
          235  +     t1.trange,
          236  +     t1.exmass,
          237  +     t1.mass,
          238  +     t1.vstatus,
          239  +     type,
          240  +     subtype,
          241  +     t1.deviation,
          242  +     t1.formula,
          243  +     dparam1,
          244  +     reserve1,
          245  +     reserve2,
          246  +     param4,
          247  +     t1.last_operation,
          248  +     t1.admin_uuid,
          249  +     t1.previous_value,
          250  +     t1.job_id,
          251  +     client_did, 
          252  +     t1.last_t1,
          253  +     t1.data_t1,
          254  +     t1.previous_date,
          255  +     param5,
          256  +     param6,
          257  +     mgr_uuid
          258  +  FROM
          259  +     t3,
          260  +     t2,
          261  +     t1
          262  +  WHERE
          263  +     t1.ptime > 1393520400
          264  +     AND param3<>9001
          265  +     AND t3.flg7 = 1
          266  +     AND t1.did = t2.did
          267  +     AND t2.uid = t3.uid
          268  +  ORDER BY t1.ptime desc LIMIT 500;
          269  +} {0 0 2 {SEARCH TABLE t1 USING INDEX t1x1 (ptime>?)} 0 1 1 {SEARCH TABLE t2 USING INDEX t2x0 (did=?)} 0 2 0 {SEARCH TABLE t3 USING INDEX t3x0 (uid=?)}}
          270  +
          271  +finish_test