How does adding an index change a query plan even though the new query plan doesn't use the newly added index?

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
6 messages Options
Reply | Threaded
Open this post in threaded view
|

How does adding an index change a query plan even though the new query plan doesn't use the newly added index?

David Pitchford
I've almost finished debugging an issue in which a certain query was taking
drastically longer for some versions of a database (a music library file)
than for other, similarly sized versions. I have been using SQLite 3.8.2
for this since I don't feel up to try replacing the version that came with
my OS. The basic form of the query (with names shortened) is:

SELECT ...
FROM a,b,c
INNER JOIN d
    ON a.tID = d.tID
INNER JOIN e
    ON d.eID = e.iID
WHERE
    e.mID = 188 AND b.aID = a.aID AND
    c.lID = a.lID
AND err = 0
AND (a.lp < 1518483204 OR a.lp IS NULL)
AND (a.ls < 1518483204 OR a.ls IS NULL)
ORDER BY RANDOM () LIMIT 1;

This query was running nearly instantly for some versions of my database,
and took at least 15 seconds for other versions, even if all the tables
involved were the same size. The "slow" query plan (from EXPLAIN QUERY
PLAN) is:

0|0|0|SEARCH TABLE CoreTracks USING AUTOMATIC COVERING INDEX
(LastStreamError=?)
0|1|1|SEARCH TABLE CoreArtists USING INTEGER PRIMARY KEY (rowid=?)
0|2|2|SEARCH TABLE CoreAlbums USING INTEGER PRIMARY KEY (rowid=?)
0|3|4|SEARCH TABLE CoreCache USING AUTOMATIC COVERING INDEX (ModelID=?)
0|4|3|SEARCH TABLE CorePlaylistEntries USING INTEGER PRIMARY KEY (rowid=?)
0|0|0|USE TEMP B-TREE FOR ORDER BY

And the "fast" plan is:

0|0|4|SCAN TABLE CoreCache
0|1|3|SEARCH TABLE CorePlaylistEntries USING INTEGER PRIMARY KEY (rowid=?)
0|2|0|SEARCH TABLE CoreTracks USING INTEGER PRIMARY KEY (rowid=?)
0|3|1|SEARCH TABLE CoreArtists USING INTEGER PRIMARY KEY (rowid=?)
0|4|2|SEARCH TABLE CoreAlbums USING INTEGER PRIMARY KEY (rowid=?)
0|0|0|USE TEMP B-TREE FOR ORDER BY

So my question became why SQLite was sometimes using the (much) slower
query plan. I found that a reliable way to get it to use the faster query
plan was to create a new index on d(eID, pID)--the same as an
already-existing index, but with the two indexed columns reversed. This
causes SQLite to use the faster query plan where it previously used the
slower one. But I'm not sure why this is the case, given that the faster
query plan uses no indexes at all.

So I'm wondering not just for this query, but in general, how can adding an
index cause a query plan to change even if the new query plan doesn't make
use of the new index?

-David Pitchford
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: How does adding an index change a query plan even though the new query plan doesn't use the newly added index?

David Raymond
A little hard to debug without the actual schema. You're giving us the query with a, b, c etc but the query plan with the real names.

The slow plan is using an automatic index on CoreCache (ModelID), ie it's making a new index when it runs and then dumping the index at the end of the query. That's probably the slowest part and it's pushing it off as long as it can when it can use other indexes to potentially filter things out first. Is that the field d(eID) that you mention you're creating an index on?

"the same as an already-existing index, but with the two indexed columns reversed"

Remember that the usefulness of an index depends on the ordering of the fields. An index on (b, a) isn't useful if you're looking for a, it's only useful if you're looking for b.

Consider ye olde phone book. It's an index on lastname, firstname. If I ask you to go through the New York City phonebook and find everyone with the lastname Pitchford you flip to the P's and life is good. If I ask you to find everyone with the firstname David then it doesn't matter that firstname is the second field of the index, you still have to flip through the entire thing to find all the David's.


-----Original Message-----
From: sqlite-users [mailto:[hidden email]] On Behalf Of David Pitchford
Sent: Thursday, February 15, 2018 6:32 PM
To: [hidden email]
Subject: [sqlite] How does adding an index change a query plan even though the new query plan doesn't use the newly added index?

I've almost finished debugging an issue in which a certain query was taking
drastically longer for some versions of a database (a music library file)
than for other, similarly sized versions. I have been using SQLite 3.8.2
for this since I don't feel up to try replacing the version that came with
my OS. The basic form of the query (with names shortened) is:

SELECT ...
FROM a,b,c
INNER JOIN d
    ON a.tID = d.tID
INNER JOIN e
    ON d.eID = e.iID
WHERE
    e.mID = 188 AND b.aID = a.aID AND
    c.lID = a.lID
AND err = 0
AND (a.lp < 1518483204 OR a.lp IS NULL)
AND (a.ls < 1518483204 OR a.ls IS NULL)
ORDER BY RANDOM () LIMIT 1;

This query was running nearly instantly for some versions of my database,
and took at least 15 seconds for other versions, even if all the tables
involved were the same size. The "slow" query plan (from EXPLAIN QUERY
PLAN) is:

0|0|0|SEARCH TABLE CoreTracks USING AUTOMATIC COVERING INDEX
(LastStreamError=?)
0|1|1|SEARCH TABLE CoreArtists USING INTEGER PRIMARY KEY (rowid=?)
0|2|2|SEARCH TABLE CoreAlbums USING INTEGER PRIMARY KEY (rowid=?)
0|3|4|SEARCH TABLE CoreCache USING AUTOMATIC COVERING INDEX (ModelID=?)
0|4|3|SEARCH TABLE CorePlaylistEntries USING INTEGER PRIMARY KEY (rowid=?)
0|0|0|USE TEMP B-TREE FOR ORDER BY

And the "fast" plan is:

0|0|4|SCAN TABLE CoreCache
0|1|3|SEARCH TABLE CorePlaylistEntries USING INTEGER PRIMARY KEY (rowid=?)
0|2|0|SEARCH TABLE CoreTracks USING INTEGER PRIMARY KEY (rowid=?)
0|3|1|SEARCH TABLE CoreArtists USING INTEGER PRIMARY KEY (rowid=?)
0|4|2|SEARCH TABLE CoreAlbums USING INTEGER PRIMARY KEY (rowid=?)
0|0|0|USE TEMP B-TREE FOR ORDER BY

So my question became why SQLite was sometimes using the (much) slower
query plan. I found that a reliable way to get it to use the faster query
plan was to create a new index on d(eID, pID)--the same as an
already-existing index, but with the two indexed columns reversed. This
causes SQLite to use the faster query plan where it previously used the
slower one. But I'm not sure why this is the case, given that the faster
query plan uses no indexes at all.

So I'm wondering not just for this query, but in general, how can adding an
index cause a query plan to change even if the new query plan doesn't make
use of the new index?

-David Pitchford
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: How does adding an index change a query plan even though the new query plan doesn't use the newly added index?

Dominique Devienne
On Fri, Feb 16, 2018 at 6:07 PM, David Raymond <[hidden email]>
wrote:

> Remember that the usefulness of an index depends on the ordering of the
> fields.

An index on (b, a) isn't useful if you're looking for a, it's only useful
> if you're looking for b.
>

Sometimes it is. See https://sqlite.org/optoverview.html#skipscan --DD
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: How does adding an index change a query plan even though the new query plan doesn't use the newly added index?

Simon Slavin-3
In reply to this post by David Pitchford


On 15 Feb 2018, at 11:31pm, David Pitchford <[hidden email]> wrote:

> So I'm wondering not just for this query, but in general, how can adding an
> index cause a query plan to change even if the new query plan doesn't make
> use of the new index?

After creating indexes and entering plausible data (in any order) use the command "ANALYZE".  This allows SQLite to pick the best query plans.

Having done "ANALYZE" once you do not have to do it again unless you've changed your schema.  The results are stored in the database file.

Simon.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: How does adding an index change a query plan even though the new query plan doesn't use the newly added index?

Dan Kennedy-4
In reply to this post by David Pitchford
On 02/16/2018 06:31 AM, David Pitchford wrote:
> I've almost finished debugging an issue in which a certain query was taking
> drastically longer for some versions of a database (a music library file)
> than for other, similarly sized versions. I have been using SQLite 3.8.2
> for this since I don't feel up to try replacing the version that came with
> my OS.

Released in 2013. Upgrading would fix the problem, as newer versions of
SQLite never use an AUTOMATIC index for the outer loop.

Anyway, do you have an sqlite_stat1 table in the database? If so, does
deleting its contents also cause SQLite to choose the faster plan?

Dan.





>   The basic form of the query (with names shortened) is:
>
> SELECT ...
> FROM a,b,c
> INNER JOIN d
>      ON a.tID = d.tID
> INNER JOIN e
>      ON d.eID = e.iID
> WHERE
>      e.mID = 188 AND b.aID = a.aID AND
>      c.lID = a.lID
> AND err = 0
> AND (a.lp < 1518483204 OR a.lp IS NULL)
> AND (a.ls < 1518483204 OR a.ls IS NULL)
> ORDER BY RANDOM () LIMIT 1;
>
> This query was running nearly instantly for some versions of my database,
> and took at least 15 seconds for other versions, even if all the tables
> involved were the same size. The "slow" query plan (from EXPLAIN QUERY
> PLAN) is:
>
> 0|0|0|SEARCH TABLE CoreTracks USING AUTOMATIC COVERING INDEX
> (LastStreamError=?)
> 0|1|1|SEARCH TABLE CoreArtists USING INTEGER PRIMARY KEY (rowid=?)
> 0|2|2|SEARCH TABLE CoreAlbums USING INTEGER PRIMARY KEY (rowid=?)
> 0|3|4|SEARCH TABLE CoreCache USING AUTOMATIC COVERING INDEX (ModelID=?)
> 0|4|3|SEARCH TABLE CorePlaylistEntries USING INTEGER PRIMARY KEY (rowid=?)
> 0|0|0|USE TEMP B-TREE FOR ORDER BY
>
> And the "fast" plan is:
>
> 0|0|4|SCAN TABLE CoreCache
> 0|1|3|SEARCH TABLE CorePlaylistEntries USING INTEGER PRIMARY KEY (rowid=?)
> 0|2|0|SEARCH TABLE CoreTracks USING INTEGER PRIMARY KEY (rowid=?)
> 0|3|1|SEARCH TABLE CoreArtists USING INTEGER PRIMARY KEY (rowid=?)
> 0|4|2|SEARCH TABLE CoreAlbums USING INTEGER PRIMARY KEY (rowid=?)
> 0|0|0|USE TEMP B-TREE FOR ORDER BY
>
> So my question became why SQLite was sometimes using the (much) slower
> query plan. I found that a reliable way to get it to use the faster query
> plan was to create a new index on d(eID, pID)--the same as an
> already-existing index, but with the two indexed columns reversed. This
> causes SQLite to use the faster query plan where it previously used the
> slower one. But I'm not sure why this is the case, given that the faster
> query plan uses no indexes at all.
>
> So I'm wondering not just for this query, but in general, how can adding an
> index cause a query plan to change even if the new query plan doesn't make
> use of the new index?
>
> -David Pitchford
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: How does adding an index change a query plan even though the new query plan doesn't use the newly added index?

Jens Alfke-2
In reply to this post by David Pitchford


> On Feb 15, 2018, at 3:31 PM, David Pitchford <[hidden email]> wrote:
>
> I have been using SQLite 3.8.2
> for this since I don't feel up to try replacing the version that came with
> my OS.

If you're coding in some dialect of C, simply download the 'amalgamation' (the big sqlite3.c and sqlite3.h files) from sqlite3.org, and compile/link them into your program instead of using the library (and header) built into your OS. There have been a LOT of fixes, optimizations and improvements over the past four years.

—Jens
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users