Re: [sqlite] Crippling query plan change between 3.7.13 and 3.8.10.2

[Richard Hipp <drh@sqlite.org>]

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

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@sqlite.org