Re: [sqlite] Crippling query plan change between 3.7.13 and 3.8.10.2
On 5/28/15, Florian Weimer <fw@deneb.enyo.de> wrote:
> The Debian security tracker <https://security-tracker.debian.org/>
> uses an SQLite database to keep track of vulnerabilites and
> generate reports.
>
> We recently upgraded SQLite from 3.7.13 to 3.8.7.1 as part of an
> operating system upgrade and experienced a crippling query planner
> change. I verified that the issue is present in 3.8.10.2 as well.
We believe that this performance issue has now been addressed by the
latest check-in to the SQLite trunk. Please download the latest
SQLite pre-release snapshot from https://www.sqlite.org/download.html
and try it out in your application. No application changes should be
necessary. It should just work.
Please let us know one way or the other. Thanks.
>
> Here are the details. A sample database file is available at
> <https://people.debian.org/~fw/security.db.gz>.
>
> CREATE TABLE source_packages
> (name TEXT NOT NULL,
> release TEXT NOT NULL,
> subrelease TEXT NOT NULL,
> archive TEXT NOT NULL,
> version TEXT NOT NULL,
> version_id INTEGER NOT NULL DEFAULT 0,
> PRIMARY KEY (name, release, subrelease, archive));
> CREATE TABLE package_notes
> (id INTEGER NOT NULL PRIMARY KEY,
> bug_name TEXT NOT NULL,
> package TEXT NOT NULL,
> fixed_version TEXT
> CHECK (fixed_version IS NULL OR fixed_version <> ''),
> fixed_version_id INTEGER NOT NULL DEFAULT 0,
> release TEXT NOT NULL,
> package_kind TEXT NOT NULL DEFAULT 'unknown',
> urgency TEXT NOT NULL,
> bug_origin TEXT NOT NULL DEFAULT '');
> CREATE TABLE debian_bugs
> (bug INTEGER NOT NULL,
> note INTEGER NOT NULL,
> PRIMARY KEY (bug, note));
> CREATE TABLE bugs
> (name TEXT NOT NULL PRIMARY KEY,
> cve_status TEXT NOT NULL
> CHECK (cve_status IN
> ('', 'CANDIDATE', 'ASSIGNED', 'RESERVED',
> 'REJECTED')),
> not_for_us INTEGER NOT NULL CHECK (not_for_us IN (0, 1)),
> description TEXT NOT NULL,
> release_date TEXT NOT NULL,
> source_file TEXT NOT NULL,
> source_line INTEGER NOT NULL);
> CREATE TABLE bugs_notes
> (bug_name TEXT NOT NULL CHECK (typ <> ''),
> typ TEXT NOT NULL CHECK (typ IN ('TODO', 'NOTE')),
> release TEXT NOT NULL DEFAULT '',
> comment TEXT NOT NULL CHECK (comment <> ''));
> CREATE TABLE bugs_xref
> (source TEXT NOT NULL,
> target TEXT NOT NULL,
> PRIMARY KEY (source, target));
> CREATE TABLE bug_status
> (bug_name TEXT NOT NULL,
> release TEXT NOT NULL,
> status TEXT NOT NULL
> CHECK (status IN ('vulnerable', 'fixed', 'unknown',
> 'undetermined',
> 'partially-fixed', 'todo')),
> reason TEXT NOT NULL,
> PRIMARY KEY (bug_name, release));
> CREATE TABLE source_package_status
> (bug_name TEXT NOT NULL,
> package INTEGER NOT NULL,
> vulnerable INTEGER NOT NULL,
> urgency TEXT NOT NULL,
> PRIMARY KEY (bug_name, package));
> CREATE TABLE removed_packages (name TEXT NOT NULL PRIMARY KEY);
> CREATE TABLE nvd_data
> (cve_name TEXT NOT NULL PRIMARY KEY,
> cve_desc TEXT NOT NULL,
> discovered TEXT NOT NULL,
> published TEXT NOT NULL,
> severity TEXT NOT NULL,
> range_local INTEGER,
> range_remote INTEGER,
> range_user_init INTEGER,
> loss_avail INTEGER NOT NULL,
> loss_conf INTEGER NOT NULL,
> loss_int INTEGER NOT NULL,
> loss_sec_prot_user INTEGER NOT NULL,
> loss_sec_prot_admin INTEGER NOT NULL,
> loss_sec_prot_other INTEGER NOT NULL);
> CREATE TABLE debsecan_data
> (name TEXT NOT NULL PRIMARY KEY,
> data TEXT NOT NULL);
> CREATE TABLE package_notes_nodsa
> (bug_name TEXT NOT NULL,
> package TEXT NOT NULL,
> release TEXT NOT NULL,
> reason TEXT NOT NULL,
> comment TEXT NOT NULL,
> PRIMARY KEY (bug_name, package, release));
> CREATE INDEX package_notes_package
> ON package_notes(package);
> CREATE INDEX bugs_xref_target ON bugs_xref(target);
> CREATE INDEX source_package_status_package
> ON source_package_status(package);
> CREATE UNIQUE INDEX package_notes_bug
> ON package_notes(bug_name, package, release);
> CREATE VIEW debian_cve AS
> SELECT DISTINCT debian_bugs.bug, st.bug_name
> FROM package_notes, debian_bugs, source_package_status AS st
> WHERE package_notes.bug_name = st.bug_name
> AND debian_bugs.note = package_notes.id
> ORDER BY debian_bugs.bug;
>
> The offending query is:
>
> SELECT sp.name, st.bug_name,
> (SELECT cve_desc FROM nvd_data
> WHERE cve_name = st.bug_name),
> (SELECT debian_cve.bug FROM debian_cve
> WHERE debian_cve.bug_name = st.bug_name
> ORDER BY debian_cve.bug),
> sp.release, sp.subrelease,
> sp.version,
> (SELECT pn.fixed_version FROM package_notes AS pn
> WHERE pn.bug_name = st.bug_name
> AND pn.package = sp.name AND
> (pn.release = sp.release OR (pn.release = '' AND fixed_version !=
> ''))),
> st.vulnerable, st.urgency,
> (SELECT range_remote FROM nvd_data
> WHERE cve_name = st.bug_name),
> (SELECT comment FROM package_notes_nodsa AS nd
> WHERE nd.package = sp.name AND nd.release = sp.release
> AND nd.bug_name = st.bug_name) AS nodsa
> FROM source_package_status AS st, source_packages AS sp, bugs
> WHERE sp.rowid = st.package AND st.bug_name = bugs.name
> AND ( st.bug_name LIKE 'CVE-%' OR st.bug_name LIKE 'TEMP-%' )
> AND ( sp.release = 'sid' OR sp.release = 'stretch' OR sp.release =
> 'jessie'
> OR sp.release = 'wheezy' OR sp.release = 'squeeze' )
> ORDER BY sp.name, st.bug_name, sp.release, sp.subrelease;
>
> The *old* explain output:
>
> addr opcode p1 p2 p3 p4 p5 comment
> ---- ------------- ---- ---- ---- ------------- -- -------------
> 0 Trace 0 0 0 00
> 1 SorterOpen 11 6 0 keyinfo(4,BINARY,BINARY) 00
>
> 2 String8 0 1 0 sid 00
> 3 String8 0 2 0 stretch 00
> 4 String8 0 3 0 jessie 00
> 5 String8 0 4 0 wheezy 00
> 6 String8 0 5 0 squeeze 00
> 7 Goto 0 255 0 00
> 8 OpenRead 0 14 0 4 00
> 9 OpenRead 1 2 0 5 00
> 10 OpenRead 12 8 0 keyinfo(1,BINARY) 00
> 11 Null 0 7 0 00
> 12 Integer 31 6 0 00
> 13 Rewind 0 22 0 00
> 14 String8 0 10 0 CVE-% 00
> 15 Column 0 0 11 00
> 16 Function 1 10 9 like(2) 02
> 17 IfNot 9 21 1 00
> 18 Rowid 0 8 0 00
> 19 RowSetTest 7 21 8 0 00
> 20 Gosub 6 32 0 00
> 21 Next 0 14 0 01
> 22 Rewind 0 31 0 00
> 23 String8 0 10 0 TEMP-% 00
> 24 Column 0 0 11 00
> 25 Function 1 10 9 like(2) 02
> 26 IfNot 9 30 1 00
> 27 Rowid 0 8 0 00
> 28 RowSetTest 7 30 8 -1 00
> 29 Gosub 6 32 0 00
> 30 Next 0 23 0 01
> 31 Goto 0 231 0 00
> 32 String8 0 10 0 CVE-% 00
> 33 Column 0 0 11 00
> 34 Function 1 10 9 like(2) 02
> 35 If 9 40 0 00
> 36 String8 0 10 0 TEMP-% 00
> 37 Column 0 0 11 00
> 38 Function 1 10 9 like(2) 02
> 39 IfNot 9 230 1 00
> 40 Column 0 1 9 00
> 41 MustBeInt 9 230 0 00
> 42 NotExists 1 230 9 00
> 43 Column 1 1 12 00
> 44 Eq 1 49 12 collseq(BINARY) 61
> 45 Eq 2 49 12 collseq(BINARY) 61
> 46 Eq 3 49 12 collseq(BINARY) 61
> 47 Eq 4 49 12 collseq(BINARY) 61
> 48 Ne 5 230 12 collseq(BINARY) 69
> 49 Column 0 0 14 00
> 50 IsNull 14 230 0 00
> 51 SeekGe 12 230 14 1 00
> 52 IdxGE 12 230 14 1 01
> 53 Column 1 0 15 00
> 54 Column 0 0 16 00
> 55 Null 0 27 0 00
> 56 Integer 1 28 0 00
> 57 OpenRead 3 18 0 2 00
> 58 OpenRead 13 19 0 keyinfo(1,BINARY) 00
> 59 Column 0 0 29 00
> 60 IsNull 29 68 0 00
> 61 SeekGe 13 68 29 1 00
> 62 IdxGE 13 68 29 1 01
> 63 IdxRowid 13 9 0 00
> 64 Seek 3 9 0 00
> 65 Column 3 1 30 00
> 66 Move 30 27 1 00
> 67 IfZero 28 68 -1 00
> 68 Close 3 0 0 00
> 69 Close 13 0 0 00
> 70 Copy 27 17 0 00
> 71 Null 0 31 0 00
> 72 Integer 101 32 0 00
> 73 Once 0 101 0 00
> 74 Noop 0 0 0 00
> 75 OpenEphemeral 4 2 0 00
> 76 OpenEphemeral 15 0 0 keyinfo(2,BINARY,BINARY) 08
>
> 77 OpenRead 16 6 0 keyinfo(2,BINARY,BINARY) 00
>
> 78 OpenRead 5 4 0 2 00
> 79 OpenRead 17 15 0 keyinfo(2,BINARY,BINARY) 00
>
> 80 Rewind 16 98 33 0 00
> 81 Column 16 1 9 00
> 82 MustBeInt 9 97 0 00
> 83 NotExists 5 97 9 00
> 84 Column 5 1 33 00
> 85 IsNull 33 97 0 00
> 86 SeekGe 17 97 33 1 00
> 87 IdxGE 17 97 33 1 01
> 88 Column 16 0 34 00
> 89 Column 17 0 35 00
> 90 Found 15 96 34 2 00
> 91 MakeRecord 34 2 9 00
> 92 IdxInsert 15 9 0 00
> 93 MakeRecord 34 2 9 00
> 94 NewRowid 4 13 0 00
> 95 Insert 4 9 13 08
> 96 Next 17 87 0 00
> 97 Next 16 81 0 00
> 98 Close 16 0 0 00
> 99 Close 5 0 0 00
> 100 Close 17 0 0 00
> 101 Return 32 0 0 00
> 102 OpenEphemeral 18 3 0 keyinfo(1,BINARY) 00
> 103 Integer 1 36 0 00
> 104 Once 1 113 0 00
> 105 OpenAutoindex 19 3 0 keyinfo(2,BINARY,BINARY) 00
>
> 106 Rewind 4 113 0 00
> 107 Rowid 4 40 0 00
> 108 Column 4 1 38 00
> 109 Column 4 0 39 00
> 110 MakeRecord 38 3 37 00
> 111 IdxInsert 19 37 0 10
> 112 Next 4 107 0 03
> 113 Column 0 0 41 00
> 114 IsNull 41 129 0 00
> 115 SeekGe 19 129 41 1 00
> 116 IdxGE 19 129 41 1 01
> 117 Column 19 1 42 00
> 118 Column 19 1 38 00
> 119 Sequence 18 39 0 00
> 120 Move 42 40 1 00
> 121 MakeRecord 38 3 37 00
> 122 IdxInsert 18 37 0 00
> 123 IfZero 36 126 0 00
> 124 AddImm 36 -1 0 00
> 125 Goto 0 128 0 00
> 126 Last 18 0 0 00
> 127 Delete 18 0 0 00
> 128 Next 19 116 0 00
> 129 Sort 18 133 0 00
> 130 Column 18 2 37 00
> 131 Move 37 31 1 00
> 132 Next 18 130 0 00
> 133 Copy 31 18 0 00
> 134 Column 1 1 19 00
> 135 Column 1 2 20 00
> 136 Column 1 4 21 00
> 137 Null 0 44 0 00
> 138 Integer 1 45 0 00
> 139 OpenRead 8 4 0 6 00
> 140 Null 0 47 0 00
> 141 Integer 170 46 0 00
> 142 OpenRead 20 29 0 keyinfo(3,BINARY,BINARY) 00
>
> 143 Column 0 0 49 00
> 144 IsNull 49 155 0 00
> 145 Column 1 0 50 00
> 146 IsNull 50 155 0 00
> 147 Column 1 1 51 00
> 148 IsNull 51 155 0 00
> 149 SeekGe 20 155 49 3 00
> 150 IdxGE 20 155 49 3 01
> 151 IdxRowid 20 43 0 00
> 152 Seek 8 43 0 00
> 153 RowSetTest 47 155 43 0 00
> 154 Gosub 46 171 0 00
> 155 OpenRead 21 29 0 keyinfo(3,BINARY,BINARY) 00
>
> 156 Column 0 0 52 00
> 157 IsNull 52 170 0 00
> 158 Column 1 0 53 00
> 159 IsNull 53 170 0 00
> 160 String8 0 54 0 00
> 161 SeekGe 21 170 52 3 00
> 162 IdxGE 21 170 52 3 01
> 163 IdxRowid 21 37 0 00
> 164 Seek 8 37 0 00
> 165 Column 8 3 55 00
> 166 String8 0 56 0 00
> 167 Eq 56 170 55 collseq(BINARY) 69
> 168 RowSetTest 47 170 37 1 00
> 169 Gosub 46 171 0 00
> 170 Goto 0 181 0 00
> 171 Column 8 1 55 00
> 172 Column 0 0 56 00
> 173 Ne 56 180 55 collseq(BINARY) 6a
> 174 Column 8 2 57 00
> 175 Column 1 0 58 00
> 176 Ne 58 180 57 collseq(BINARY) 6a
> 177 Column 8 3 59 00
> 178 Move 59 44 1 00
> 179 IfZero 45 181 -1 00
> 180 Return 46 0 0 00
> 181 Close 8 0 0 00
> 182 Copy 44 22 0 00
> 183 Column 0 2 23 00
> 184 Column 0 3 24 00
> 185 Null 0 60 0 00
> 186 Integer 1 61 0 00
> 187 OpenRead 9 18 0 7 00
> 188 OpenRead 22 19 0 keyinfo(1,BINARY) 00
> 189 Column 0 0 62 00
> 190 IsNull 62 198 0 00
> 191 SeekGe 22 198 62 1 00
> 192 IdxGE 22 198 62 1 01
> 193 IdxRowid 22 58 0 00
> 194 Seek 9 58 0 00
> 195 Column 9 6 63 00
> 196 Move 63 60 1 00
> 197 IfZero 61 198 -1 00
> 198 Close 9 0 0 00
> 199 Close 22 0 0 00
> 200 Copy 60 25 0 00
> 201 Null 0 64 0 00
> 202 Integer 1 65 0 00
> 203 OpenRead 10 22 0 5 00
> 204 OpenRead 23 23 0 keyinfo(3,BINARY,BINARY) 00
>
> 205 Column 0 0 66 00
> 206 IsNull 66 218 0 00
> 207 Column 1 0 67 00
> 208 IsNull 67 218 0 00
> 209 Column 1 1 68 00
> 210 IsNull 68 218 0 00
> 211 SeekGe 23 218 66 3 00
> 212 IdxGE 23 218 66 3 01
> 213 IdxRowid 23 58 0 00
> 214 Seek 10 58 0 00
> 215 Column 10 4 69 00
> 216 Move 69 64 1 00
> 217 IfZero 65 218 -1 00
> 218 Close 10 0 0 00
> 219 Close 23 0 0 00
> 220 Copy 64 26 0 00
> 221 MakeRecord 15 12 58 00
> 222 Column 1 0 70 00
> 223 Column 0 0 71 00
> 224 Column 1 1 72 00
> 225 Column 1 2 73 00
> 226 Sequence 11 74 0 00
> 227 Move 58 75 1 00
> 228 MakeRecord 70 6 57 00
> 229 SorterInsert 11 57 0 00
> 230 Return 6 0 0 00
> 231 Close 0 0 0 00
> 232 Close 1 0 0 00
> 233 Close 12 0 0 00
> 234 OpenPseudo 24 58 12 00
> 235 OpenPseudo 25 76 6 00
> 236 SorterSort 11 253 0 00
> 237 SorterData 11 76 0 00
> 238 Column 25 5 58 20
> 239 Column 24 0 15 20
> 240 Column 24 1 16 00
> 241 Column 24 2 17 00
> 242 Column 24 3 18 00
> 243 Column 24 4 19 00
> 244 Column 24 5 20 00
> 245 Column 24 6 21 00
> 246 Column 24 7 22 00
> 247 Column 24 8 23 00
> 248 Column 24 9 24 00
> 249 Column 24 10 25 00
> 250 Column 24 11 26 00
> 251 ResultRow 15 12 0 00
> 252 SorterNext 11 237 0 00
> 253 Close 24 0 0 00
> 254 Halt 0 0 0 00
> 255 Transaction 0 0 0 00
> 256 VerifyCookie 0 27 0 00
> 257 TableLock 0 14 0 source_package_status 00
>
> 258 TableLock 0 2 0 source_packages 00
> 259 TableLock 0 7 0 bugs 00
> 260 TableLock 0 18 0 nvd_data 00
> 261 TableLock 0 5 0 debian_bugs 00
> 262 TableLock 0 4 0 package_notes 00
> 263 TableLock 0 22 0 package_notes_nodsa 00
>
> 264 Goto 0 8 0 00
>
> The *new* explain output:
>
> addr opcode p1 p2 p3 p4 p5 comment
> ---- ------------- ---- ---- ---- ------------- -- -------------
> 0 Init 0 190 0 00
> 1 SorterOpen 11 17 0 k(4,B,B,B,B) 00
> 2 OpenRead 0 14 0 4 00
> 3 OpenRead 1 2 0 5 00
> 4 OpenRead 12 8 0 k(1,nil) 02
> 5 Rewind 0 169 0 00
> 6 Column 0 0 3 00
> 7 Function 1 2 1 like(2) 02
> 8 If 1 12 0 00
> 9 Column 0 0 5 00
> 10 Function 1 4 1 like(2) 02
> 11 IfNot 1 168 1 00
> 12 Column 0 1 6 00
> 13 MustBeInt 6 168 0 00
> 14 NotExists 1 168 6 00
> 15 Column 1 1 1 00
> 16 Eq 7 21 1 (BINARY) 42
> 17 Eq 9 21 1 (BINARY) 42
> 18 Eq 10 21 1 (BINARY) 42
> 19 Eq 11 21 1 (BINARY) 42
> 20 Ne 12 168 1 (BINARY) 52
> 21 Column 0 0 13 00
> 22 SeekGE 12 168 13 1 00
> 23 IdxGT 12 168 13 1 00
> 24 Column 1 0 18 00
> 25 Copy 13 19 0 00
> 26 Null 0 30 0 00
> 27 Integer 1 31 0 00
> 28 OpenRead 3 18 0 2 00
> 29 OpenRead 13 19 0 k(1,nil) 02
> 30 Column 0 0 32 00
> 31 SeekGE 13 37 32 1 00
> 32 IdxGT 13 37 32 1 00
> 33 IdxRowid 13 33 0 00
> 34 Seek 3 33 0 00
> 35 Column 3 1 30 00
> 36 DecrJumpZero 31 37 0 00
> 37 Close 3 0 0 00
> 38 Close 13 0 0 00
> 39 Copy 30 20 0 00
> 40 Null 0 34 0 00
> 41 InitCoroutine 35 66 42 00
> 42 Noop 0 0 0 00
> 43 OpenEphemeral 15 0 0 k(2,B,B) 08
> 44 OpenRead 16 6 0 k(2,nil,nil) 00
> 45 OpenRead 5 4 0 2 00
> 46 OpenRead 17 15 0 k(2,nil,nil) 02
> 47 Rewind 16 62 36 0 00
> 48 Column 16 1 36 00
> 49 MustBeInt 36 61 0 00
> 50 NotExists 5 61 36 00
> 51 Column 5 1 37 00
> 52 SeekGE 17 61 37 1 00
> 53 IdxGT 17 61 37 1 00
>
> 54 Column 16 0 38 00
>
> 55 Column 17 0 39 00
>
> 56 Found 15 60 38 2 00
>
> 57 MakeRecord 38 2 8 00
>
> 58 IdxInsert 15 8 0 00
>
> 59 Yield 35 0 0 00
>
> 60 Next 17 53 0 00
> 61 Next 16 48 0 01
> 62 Close 16 0 0 00
> 63 Close 5 0 0 00
> 64 Close 17 0 0 00
> 65 EndCoroutine 35 0 0 00
> 66 OpenEphemeral 18 3 0 k(1,B) 00
> 67 Integer 1 40 0 00
> 68 InitCoroutine 35 0 42 00
> 69 Yield 35 83 0 00
> 70 Copy 39 41 0 00
> 71 Column 0 0 42 00
> 72 Ne 42 82 41 (BINARY) 51
> 73 Copy 38 34 0 00
> 74 Copy 34 44 0 00
> 75 Sequence 18 45 0 00
> 76 Move 34 46 1 00
> 77 MakeRecord 44 3 43 00
> 78 IdxInsert 18 43 0 00
> 79 IfNotZero 40 82 -1 00
> 80 Last 18 0 0 00
> 81 Delete 18 0 0 00
> 82 Goto 0 69 0 00
> 83 Sort 18 87 0 00
> 84 Column 18 2 41 00
> 85 Move 41 34 1 00
> 86 Next 18 84 0 00
> 87 Copy 34 21 0 00
> 88 Column 1 1 22 00
> 89 Column 1 2 23 00
> 90 Column 1 4 24 00
> 91 Null 0 47 0 00
> 92 Integer 1 48 0 00
> 93 OpenRead 8 4 0 6 00
> 94 Null 0 50 0 00
> 95 Integer 118 49 0 00
> 96 OpenRead 19 29 0 k(3,nil,nil,nil) 02
>
> 97 Column 0 0 52 00
> 98 Column 1 0 53 00
> 99 Column 1 1 54 00
> 100 SeekGE 19 106 52 3 00
> 101 IdxGT 19 106 52 3 00
> 102 IdxRowid 19 55 0 00
> 103 Seek 8 55 0 00
> 104 RowSetTest 50 106 55 0 00
> 105 Gosub 49 119 0 00
> 106 ReopenIdx 19 29 0 k(3,nil,nil,nil) 02
>
> 107 Column 0 0 56 00
> 108 Column 1 0 57 00
> 109 String8 0 58 0 00
> 110 SeekGE 19 118 56 3 00
> 111 IdxGT 19 118 56 3 00
> 112 IdxRowid 19 59 0 00
> 113 Seek 8 59 0 00
> 114 Column 8 3 42 00
> 115 Eq 60 118 42 (BINARY) 52
> 116 RowSetTest 50 118 59 1 00
> 117 Gosub 49 119 0 00
> 118 Goto 0 128 0 00
> 119 Column 19 0 42 00
> 120 Column 0 0 41 00
> 121 Ne 41 127 42 (BINARY) 51
> 122 Column 19 1 61 00
> 123 Column 1 0 62 00
> 124 Ne 62 127 61 (BINARY) 51
> 125 Column 8 3 47 00
> 126 DecrJumpZero 48 128 0 00
> 127 Return 49 0 0 00
> 128 Close 8 0 0 00
> 129 Copy 47 25 0 00
> 130 Column 0 2 26 00
> 131 Column 0 3 27 00
> 132 Null 0 63 0 00
> 133 Integer 1 64 0 00
> 134 OpenRead 9 18 0 7 00
> 135 OpenRead 20 19 0 k(1,nil) 02
> 136 Column 0 0 65 00
> 137 SeekGE 20 143 65 1 00
> 138 IdxGT 20 143 65 1 00
> 139 IdxRowid 20 66 0 00
> 140 Seek 9 66 0 00
> 141 Column 9 6 63 00
> 142 DecrJumpZero 64 143 0 00
> 143 Close 9 0 0 00
> 144 Close 20 0 0 00
> 145 Copy 63 28 0 00
> 146 Null 0 67 0 00
> 147 Integer 1 68 0 00
> 148 OpenRead 10 22 0 5 00
> 149 OpenRead 21 23 0 k(3,nil,nil,nil) 02
>
> 150 Column 0 0 69 00
> 151 Column 1 0 70 00
> 152 Column 1 1 71 00
> 153 SeekGE 21 159 69 3 00
> 154 IdxGT 21 159 69 3 00
> 155 IdxRowid 21 72 0 00
> 156 Seek 10 72 0 00
> 157 Column 10 4 67 00
> 158 DecrJumpZero 68 159 0 00
> 159 Close 10 0 0 00
> 160 Close 21 0 0 00
> 161 Copy 67 29 0 00
> 162 Column 1 0 14 00
> 163 Column 0 0 15 00
> 164 Column 1 1 16 00
> 165 Column 1 2 17 00
> 166 MakeRecord 14 16 73 00
> 167 SorterInsert 11 73 0 00
> 168 Next 0 6 0 01
> 169 Close 0 0 0 00
> 170 Close 1 0 0 00
> 171 Close 12 0 0 00
> 172 OpenPseudo 22 74 17 00
> 173 SorterSort 11 189 0 00
> 174 SorterData 11 74 22 00
> 175 Column 22 4 18 00
> 176 Column 22 5 19 00
> 177 Column 22 6 20 00
> 178 Column 22 7 21 00
> 179 Column 22 8 22 00
> 180 Column 22 9 23 00
> 181 Column 22 10 24 00
> 182 Column 22 11 25 00
> 183 Column 22 12 26 00
> 184 Column 22 13 27 00
> 185 Column 22 14 28 00
> 186 Column 22 15 29 00
> 187 ResultRow 18 12 0 00
> 188 SorterNext 11 174 0 00
> 189 Halt 0 0 0 00
> 190 Transaction 0 0 27 0 01
> 191 TableLock 0 14 0 source_package_status 00
>
> 192 TableLock 0 2 0 source_packages 00
> 193 TableLock 0 7 0 bugs 00
> 194 TableLock 0 18 0 nvd_data 00
> 195 TableLock 0 5 0 debian_bugs 00
> 196 TableLock 0 4 0 package_notes 00
> 197 TableLock 0 22 0 package_notes_nodsa 00
>
> 198 String8 0 2 0 CVE-% 00
> 199 String8 0 4 0 TEMP-% 00
> 200 String8 0 7 0 sid 00
> 201 String8 0 9 0 stretch 00
> 202 String8 0 10 0 jessie 00
> 203 String8 0 11 0 wheezy 00
> 204 String8 0 12 0 squeeze 00
> 205 String8 0 60 0 00
> 206 Goto 0 1 0 00
>
>
> I have not run the query to completion using the new SQLite version.
>
> I will figure out a way to rewrite the query so that it runs
> reasonably fast again (which will address our immediate needs), but
> maybe there is something that can be fixed in the planner as well.
> _______________________________________________
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
--
D. Richard Hipp
drh@sqlite.org
Reply to: