| blob | f4da416745ce33b04dcebd47416bfb19ec3bd4c3 |
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 #
19 set testdir [file dirname $argv0]
20 source $testdir/tester.tcl
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);
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);
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.
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=?)}}
271 finish_test