I has been a while without response, so I just bumping this message.
19 July 2019, 14:21:27, by "Paul" <de...@ukr.net>: > I have a test case when the regression can be observed in queries that > use JOINs with FTS4 tables, somewhere in between 3.22.0 and 3.23.0. > For some reason the planner decides to search non-FTS table first then > scan the whole FTS table. Version 3.22.0 is the last unaffected, while > issue is still present in HEAD. > > Probably it has something to do with a fact that, according to EXPLAIN, > new version of planner ignores LEFT join and considers it just a JOIN. > At least it feels that way, anyway. > > Test case: > > > CREATE VIRTUAL TABLE search USING FTS4(text); > > WITH RECURSIVE > cnt(x) AS ( > SELECT 1 > UNION ALL > SELECT x+1 FROM cnt > LIMIT 2000 > ) > INSERT INTO search(docid, text) SELECT x, 'test' || x FROM cnt; > > CREATE TABLE foo(s_docid integer primary key, bar integer); > > WITH RECURSIVE > cnt(x) AS ( > SELECT 1 > UNION ALL > SELECT x+1 FROM cnt > LIMIT 2000 > ) > INSERT INTO foo(s_docid, bar) SELECT x, 1 FROM cnt; > > .timer on > > -- Fast > SELECT COUNT() FROM search LEFT JOIN foo > ON s_docid = docid > WHERE bar = 1 AND search MATCH 'test*'; > > -- Fast > SELECT COUNT() FROM foo > WHERE bar = 1 > AND s_docid IN ( > SELECT docid FROM search WHERE search MATCH 'test*' > ); > > -- Create index, as some real-life queries use searches by `bar` > CREATE INDEX foo_bar_idx ON foo (bar); > > -- Slow > SELECT COUNT() FROM search LEFT JOIN foo > ON s_docid = docid > WHERE bar = 1 AND search MATCH 'test*'; > > -- As fast as before (current workaround) > SELECT COUNT() FROM foo > WHERE bar = 1 > AND s_docid IN ( > SELECT docid FROM search WHERE search MATCH 'test*' > ); > _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
