[sqlite] [BUG] 1200x slow-down running query in 3.7.3
I have a query which takes 17 minutes to run with 3.7.3 against 800ms with 3.7.2 The query is: SELECT x.sheep_no, x.registering_flock, x.date_of_registration FROM sheep x LEFT JOIN (SELECT s.sheep_no, prev.flock_no, prev.owner_person_id, s.date_of_registration, prev.owner_change_date FROM sheep s JOIN flock_owner prev ON s.registering_flock = prev.flock_no AND (prev.owner_change_date <= s.date_of_registration || ' 00:00:00') WHERE NOT EXISTS (SELECT 'x' FROM flock_owner later WHERE prev.flock_no = later.flock_no AND later.owner_change_date > prev.owner_change_date AND later.owner_change_date <= s.date_of_registration || ' 00:00:00') ) y ON x.sheep_no = y.sheep_no WHERE y.sheep_no IS NULL ORDER BY x.registering_flock explain query plan with 3.7.3: 0 0 TABLE sheep AS s 1 1 TABLE flock_owner AS prev WITH INDEX sqlite_autoindex_flock_owner_1 0 0 TABLE flock_owner AS later WITH INDEX sqlite_autoindex_flock_owner_1 0 0 TABLE sheep AS x 1 1 TABLE AS y explain query plan with 3.7.2: 0 0 TABLE sheep AS s 1 1 TABLE flock_owner AS prev WITH INDEX sqlite_autoindex_flock_owner_1 0 0 TABLE flock_owner AS later WITH INDEX sqlite_autoindex_flock_owner_1 0 0 TABLE sheep AS x 1 1 TABLE AS y WITH AUTOMATIC INDEX Seems the planner has missed creating an index for the second nested SELECT. The flock_owner table has an index on each of flock_no, owner_person_id and owner_change_date. Pete -- Peter Hardman Breeder of Shetland sheep and cattle ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] [BUG] 1200x slow-down running query in 3.7.3
On Mon, Oct 18, 2010 at 9:18 AM, Peter wrote: > I have a query which takes 17 minutes to run with 3.7.3 against 800ms > with 3.7.2 > > Thank you for the report. Can you please send your complete schema. The query is useful in combination with the schema but is pretty much useless without it. Have you tried running ANALYZE? Does it help? Can you also send (in addition to the schema) the content of the sqlite_stat1 table after you have run ANALYZE. Your easiest work-around for the time being is to do PRAGMA automatic_index=OFF; > The query is: > > SELECT x.sheep_no, x.registering_flock, x.date_of_registration > FROM sheep x LEFT JOIN > (SELECT s.sheep_no, prev.flock_no, prev.owner_person_id, > s.date_of_registration, prev.owner_change_date > FROM sheep s JOIN flock_owner prev ON s.registering_flock = > prev.flock_no > AND (prev.owner_change_date <= s.date_of_registration || ' 00:00:00') > WHERE NOT EXISTS > (SELECT 'x' FROM flock_owner later > WHERE prev.flock_no = later.flock_no > AND later.owner_change_date > prev.owner_change_date > AND later.owner_change_date <= s.date_of_registration || ' > 00:00:00') > ) y ON x.sheep_no = y.sheep_no > WHERE y.sheep_no IS NULL > ORDER BY x.registering_flock > > explain query plan with 3.7.3: > 0 0 TABLE sheep AS s > 1 1 TABLE flock_owner AS prev WITH INDEX > sqlite_autoindex_flock_owner_1 > 0 0 TABLE flock_owner AS later WITH INDEX > sqlite_autoindex_flock_owner_1 > 0 0 TABLE sheep AS x > 1 1 TABLE AS y > > explain query plan with 3.7.2: > 0 0 TABLE sheep AS s > 1 1 TABLE flock_owner AS prev WITH INDEX > sqlite_autoindex_flock_owner_1 > 0 0 TABLE flock_owner AS later WITH INDEX > sqlite_autoindex_flock_owner_1 > 0 0 TABLE sheep AS x > 1 1 TABLE AS y WITH AUTOMATIC INDEX > > > Seems the planner has missed creating an index for the second nested > SELECT. > > The flock_owner table has an index on each of flock_no, owner_person_id > and owner_change_date. > > Pete > -- > Peter Hardman > Breeder of Shetland sheep and cattle > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] [BUG] 1200x slow-down running query in 3.7.3
>> On Mon, Oct 18, 2010 at 9:18 AM, Peter wrote: >> > I have a query which takes 17 minutes to run with 3.7.3 against 800ms >> > with 3.7.2 >> > >> > > Thank you for the report. > > Can you please send your complete schema. The query is useful in > combination with the schema but is pretty much useless without it. > > Have you tried running ANALYZE? Does it help? Can you also send (in > addition to the schema) the content of the sqlite_stat1 table after you have > run ANALYZE. > > Your easiest work-around for the time being is to do > > PRAGMA automatic_index=OFF; > Sorry, temporary softening of the brain. The relevant tables (and the query) are at http://www.somborneshetlands.co.uk/things/slow_query.zip. Turning automatic_index off doesn't restore the original performance (I got bored waiting after a couple of minutes though). Ditto for ANALYZE (the database in the .zip has had ANALYZE run on it). I have another query in the same style (nested selects) that also runs like a dog on 3.7.3. I decided the best work-around was to extract the third level select (the one that makes table y) and run it separately to create a temporary table. If I substitute that table instead of the select then the combination of the two runs about as fast as the original - fast enough anyway. Pete -- Peter Hardman Breeder of Shetland sheep and cattle ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] [BUG] 1200x slow-down running query in 3.7.3
On Mon, Oct 18, 2010 at 9:18 AM, Peter wrote: > I have a query which takes 17 minutes to run with 3.7.3 against 800ms > with 3.7.2 > > The query is: > > SELECT x.sheep_no, x.registering_flock, x.date_of_registration > FROM sheep x LEFT JOIN > Regarding: "Turning automatic_index off doesn't restore the original performance (I got bored waiting after a couple of minutes though)." The planner code, as it incorporates ever more human-like intelligence, is clearly going to sleep because you are instructing it to count sheep. [just a joke; forgive me] Even though the development team may want to tweak the planner algorithms, wouldn't you still want to add an explicit index for owner_person_id > (SELECT s.sheep_no, prev.flock_no, prev.owner_person_id, > s.date_of_registration, prev.owner_change_date > FROM sheep s JOIN flock_owner prev ON s.registering_flock = > prev.flock_no > AND (prev.owner_change_date <= s.date_of_registration || ' 00:00:00') > WHERE NOT EXISTS > (SELECT 'x' FROM flock_owner later > WHERE prev.flock_no = later.flock_no > AND later.owner_change_date > prev.owner_change_date > AND later.owner_change_date <= s.date_of_registration || ' > 00:00:00') > ) y ON x.sheep_no = y.sheep_no > WHERE y.sheep_no IS NULL > ORDER BY x.registering_flock > > explain query plan with 3.7.3: > 0 0 TABLE sheep AS s > 1 1 TABLE flock_owner AS prev WITH INDEX > sqlite_autoindex_flock_owner_1 > 0 0 TABLE flock_owner AS later WITH INDEX > sqlite_autoindex_flock_owner_1 > 0 0 TABLE sheep AS x > 1 1 TABLE AS y > > explain query plan with 3.7.2: > 0 0 TABLE sheep AS s > 1 1 TABLE flock_owner AS prev WITH INDEX > sqlite_autoindex_flock_owner_1 > 0 0 TABLE flock_owner AS later WITH INDEX > sqlite_autoindex_flock_owner_1 > 0 0 TABLE sheep AS x > 1 1 TABLE AS y WITH AUTOMATIC INDEX > > > Seems the planner has missed creating an index for the second nested > SELECT. > > The flock_owner table has an index on each of flock_no, owner_person_id > and owner_change_date. > > Pete > -- > Peter Hardman > Breeder of Shetland sheep and cattle > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] [BUG] 1200x slow-down running query in 3.7.3
On Mon, Oct 18, 2010 at 9:18 AM, Peter wrote: > I have a query which takes 17 minutes to run with 3.7.3 against 800ms > with 3.7.2 > > explain query plan with 3.7.3: > 0 0 TABLE sheep AS s > 1 1 TABLE flock_owner AS prev WITH INDEX > sqlite_autoindex_flock_owner_1 > 0 0 TABLE flock_owner AS later WITH INDEX > sqlite_autoindex_flock_owner_1 > 0 0 TABLE sheep AS x > 1 1 TABLE AS y > > explain query plan with 3.7.2: > 0 0 TABLE sheep AS s > 1 1 TABLE flock_owner AS prev WITH INDEX > sqlite_autoindex_flock_owner_1 > 0 0 TABLE flock_owner AS later WITH INDEX > sqlite_autoindex_flock_owner_1 > 0 0 TABLE sheep AS x > 1 1 TABLE AS y WITH AUTOMATIC INDEX > > > Seems the planner has missed creating an index for the second nested > SELECT. > Should be fixed in http://www.sqlite.org/src/info/d30f7b2def -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
