| 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 |