/ 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 Unified Diffs Ignore Whitespace Patch

Added test/autoindex2.test.































































































































































































































































































































































































































































































































































>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
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
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
# 2014-06-17
#
# The author disclaims copyright to this source code.  In place of
# a legal notice, here is a blessing:
#
#    May you do good and not evil.
#    May you find forgiveness for yourself and forgive others.
#    May you share freely, never taking more than you give.
#
#*************************************************************************
#
# This file implements regression tests for SQLite library.  The
# focus of this script is testing automatic index creation logic.
#
# This file contains a single real-world test case that was giving
# suboptimal performance because of over-use of automatic indexes.
#

set testdir [file dirname $argv0]
source $testdir/tester.tcl


do_execsql_test autoindex2-100 {
  CREATE TABLE t1(
    t1_id largeint,
    did char(9),
    ptime largeint,
    exbyte char(4),
    pe_id int,
    field_id int,
    mass float,
    param10 float,
    param11 float,
    exmass float,
    deviation float,
    trange float,
    vstatus int,
    commit_status int,
    formula char(329),
    tier int DEFAULT 2,
    ssid int DEFAULT 0,
    last_operation largeint DEFAULT 0,
    admin_uuid int DEFAULT 0,
    previous_value float,
    job_id largeint,
    last_t1 largeint DEFAULT 0,
    data_t1 int,
    previous_date largeint DEFAULT 0,
    flg8 int DEFAULT 1,
    failed_fields char(100)
  );
  CREATE INDEX t1x0 on t1 (t1_id);
  CREATE INDEX t1x1 on t1 (ptime, vstatus);
  CREATE INDEX t1x2 on t1 (did, ssid, ptime, vstatus, exbyte, t1_id);
  CREATE INDEX t1x3 on t1 (job_id);
  
  CREATE TABLE t2(
    did char(9),
    client_did char(30),
    description char(49),
    uid int,
    tzid int,
    privilege int,
    param2 int,
    type char(30),
    subtype char(32),
    dparam1 char(7) DEFAULT '',
    param5 char(3) DEFAULT '',
    notional float DEFAULT 0.000000,
    create_time largeint,
    sample_time largeint DEFAULT 0,
    param6 largeint,
    frequency int,
    expiration largeint,
    uw_status int,
    next_sample largeint,
    last_sample largeint,
    reserve1 char(29) DEFAULT '',
    reserve2 char(29) DEFAULT '',
    reserve3 char(29) DEFAULT '',
    bxcdr char(19) DEFAULT 'XY',
    ssid int DEFAULT 1,
    last_t1_id largeint,
    reserve4 char(29) DEFAULT '',
    reserve5 char(29) DEFAULT '',
    param12 int DEFAULT 0,
    long_did char(100) DEFAULT '',
    gr_code int DEFAULT 0,
    drx char(100) DEFAULT '',
    parent_id char(9) DEFAULT '',
    param13 int DEFAULT 0,
    position float DEFAULT 1.000000,
    client_did3 char(100) DEFAULT '',
    client_did4 char(100) DEFAULT '',
    dlib_id char(9) DEFAULT ''
  );
  CREATE INDEX t2x0 on t2 (did);
  CREATE INDEX t2x1 on t2 (client_did);
  CREATE INDEX t2x2 on t2 (long_did);
  CREATE INDEX t2x3 on t2 (uid);
  CREATE INDEX t2x4 on t2 (param2);
  CREATE INDEX t2x5 on t2 (type);
  CREATE INDEX t2x6 on t2 (subtype);
  CREATE INDEX t2x7 on t2 (last_sample);
  CREATE INDEX t2x8 on t2 (param6);
  CREATE INDEX t2x9 on t2 (frequency);
  CREATE INDEX t2x10 on t2 (privilege);
  CREATE INDEX t2x11 on t2 (sample_time);
  CREATE INDEX t2x12 on t2 (notional);
  CREATE INDEX t2x13 on t2 (tzid);
  CREATE INDEX t2x14 on t2 (gr_code);
  CREATE INDEX t2x15 on t2 (parent_id);
  
  CREATE TABLE t3(
    uid int,
    param3 int,
    uuid int,
    acc_id int,
    cust_num int,
    numerix_id int,
    pfy char(29),
    param4 char(29),
    param15 int DEFAULT 0,
    flg7 int DEFAULT 0,
    param21 int DEFAULT 0,
    bxcdr char(2) DEFAULT 'PC',
    c31 int DEFAULT 0,
    c33 int DEFAULT 0,
    c35 int DEFAULT 0,
    c37 int,
    mgr_uuid int,
    back_up_uuid int,
    priv_mars int DEFAULT 0,
    is_qc int DEFAULT 0,
    c41 int DEFAULT 0,
    deleted int DEFAULT 0,
    c47 int DEFAULT 1
  );
  CREATE INDEX t3x0 on t3 (uid);
  CREATE INDEX t3x1 on t3 (param3);
  CREATE INDEX t3x2 on t3 (uuid);
  CREATE INDEX t3x3 on t3 (acc_id);
  CREATE INDEX t3x4 on t3 (param4);
  CREATE INDEX t3x5 on t3 (pfy);
  CREATE INDEX t3x6 on t3 (is_qc);
  SELECT count(*) FROM sqlite_master;
} {30}
do_execsql_test autoindex2-110 {
  ANALYZE sqlite_master;
  INSERT INTO sqlite_stat1 VALUES('t1','t1x3','10747267 260');
  INSERT INTO sqlite_stat1 VALUES('t1','t1x2','10747267 121 113 2 2 2 1');
  INSERT INTO sqlite_stat1 VALUES('t1','t1x1','10747267 50 40');
  INSERT INTO sqlite_stat1 VALUES('t1','t1x0','10747267 1');
  INSERT INTO sqlite_stat1 VALUES('t2','t2x15','39667 253');
  INSERT INTO sqlite_stat1 VALUES('t2','t2x14','39667 19834');
  INSERT INTO sqlite_stat1 VALUES('t2','t2x13','39667 13223');
  INSERT INTO sqlite_stat1 VALUES('t2','t2x12','39667 7');
  INSERT INTO sqlite_stat1 VALUES('t2','t2x11','39667 17');
  INSERT INTO sqlite_stat1 VALUES('t2','t2x10','39667 19834');
  INSERT INTO sqlite_stat1 VALUES('t2','t2x9','39667 7934');
  INSERT INTO sqlite_stat1 VALUES('t2','t2x8','39667 11');
  INSERT INTO sqlite_stat1 VALUES('t2','t2x7','39667 5');
  INSERT INTO sqlite_stat1 VALUES('t2','t2x6','39667 242');
  INSERT INTO sqlite_stat1 VALUES('t2','t2x5','39667 1984');
  INSERT INTO sqlite_stat1 VALUES('t2','t2x4','39667 4408');
  INSERT INTO sqlite_stat1 VALUES('t2','t2x3','39667 81');
  INSERT INTO sqlite_stat1 VALUES('t2','t2x2','39667 551');
  INSERT INTO sqlite_stat1 VALUES('t2','t2x1','39667 2');
  INSERT INTO sqlite_stat1 VALUES('t2','t2x0','39667 1');
  INSERT INTO sqlite_stat1 VALUES('t3','t3x6','569 285');
  INSERT INTO sqlite_stat1 VALUES('t3','t3x5','569 2');
  INSERT INTO sqlite_stat1 VALUES('t3','t3x4','569 2');
  INSERT INTO sqlite_stat1 VALUES('t3','t3x3','569 5');
  INSERT INTO sqlite_stat1 VALUES('t3','t3x2','569 3');
  INSERT INTO sqlite_stat1 VALUES('t3','t3x1','569 6');
  INSERT INTO sqlite_stat1 VALUES('t3','t3x0','569 1');
  ANALYZE sqlite_master;
} {}
do_execsql_test autoindex2-120 {
  EXPLAIN QUERY PLAN
  SELECT
     t1_id,
     t1.did,
     param2,
     param3,
     t1.ptime,
     t1.trange,
     t1.exmass,
     t1.mass,
     t1.vstatus,
     type,
     subtype,
     t1.deviation,
     t1.formula,
     dparam1,
     reserve1,
     reserve2,
     param4,
     t1.last_operation,
     t1.admin_uuid,
     t1.previous_value,
     t1.job_id,
     client_did, 
     t1.last_t1,
     t1.data_t1,
     t1.previous_date,
     param5,
     param6,
     mgr_uuid
  FROM
     t1,
     t2,
     t3
  WHERE
     t1.ptime > 1393520400
     AND param3<>9001
     AND t3.flg7 = 1
     AND t1.did = t2.did
     AND t2.uid = t3.uid
  ORDER BY t1.ptime desc LIMIT 500;
} {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=?)}}
#
# ^^^--- Before being fixed, the above was using an automatic covering
# on t3 and reordering the tables so that t3 was in the outer loop and
# implementing the ORDER BY clause using a B-Tree.

do_execsql_test autoindex2-120 {
  EXPLAIN QUERY PLAN
  SELECT
     t1_id,
     t1.did,
     param2,
     param3,
     t1.ptime,
     t1.trange,
     t1.exmass,
     t1.mass,
     t1.vstatus,
     type,
     subtype,
     t1.deviation,
     t1.formula,
     dparam1,
     reserve1,
     reserve2,
     param4,
     t1.last_operation,
     t1.admin_uuid,
     t1.previous_value,
     t1.job_id,
     client_did, 
     t1.last_t1,
     t1.data_t1,
     t1.previous_date,
     param5,
     param6,
     mgr_uuid
  FROM
     t3,
     t2,
     t1
  WHERE
     t1.ptime > 1393520400
     AND param3<>9001
     AND t3.flg7 = 1
     AND t1.did = t2.did
     AND t2.uid = t3.uid
  ORDER BY t1.ptime desc LIMIT 500;
} {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=?)}}

finish_test