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 } {~/AUTO/}
 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 # This test is sanitized data received from a user.  The original unsanitized
 228 # data and STAT4 data is found in the th3private test repository.  See one of
 229 # the th3private check-ins on 2016-02-25.  The test is much more accurate when
 230 # STAT4 data is used.
 231
 232 finish_test