On 5/28/15, Florian Weimer <fw at 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.
>
Thanks for the detailed bug report and for supplying a sample
database. That made the problem much easier to analyze.
The problem is in the second subquery of the result set. Here is a
simplification of the query:
SELECT
st.bug_name,
/* Problem in subquery below */
(SELECT debian_cve.bug FROM debian_cve
WHERE debian_cve.bug_name = st.bug_name
ORDER BY debian_cve.bug),
/* Problem in subquery above */
sp.release
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;
Note that debian_cve is a VIEW not a table, and so we cannot do a
CREATE INDEX on debian_cve to fix this problem.
The reason that 3.7.17 is faster is that it is manifesting the view
into a transient table (one that exists for the duration of the query
only) and then automatically creating the appropriate index. Version
3.8.x is just rerunning the subquery each time. Bummer. So we have a
query planner case that we need to work on....
In the meantime, you can work around the problem by manifesting the
view yourself. Before running your query, do:
CREATE TEMP TABLE dx(
bug_name,
bug,
PRIMARY KEY(bug_name,bug)
) WITHOUT ROWID;
INSERT INTO dx(bug_name,dx) SELECT bug_name, dx FROM debian_cve;
Then use the "dx" table in place of "debian_cve" in your query. Then
"DROP TABLE dx" after the query.
Version 3.7.17 was basically doing the above for you automatically.
Version 3.8.x is not, unfortunately. Until we can get 3.8.x fixed and
get the fix into circulation, I suggest that you deal with this by
manifesting the view manually as shown above.
--
D. Richard Hipp
drh at sqlite.org
