On 1/24/19, Gerlando Falauto <gerlando.fala...@gmail.com> wrote:
> Hi,
>
> I read about the skip-scan optimization:
>
> https://www.sqlite.org/optoverview.html#skipscan
>
> is there a way to check whether it is being used for a given query, or not?
>
> Explain query plan does not seem to give any insight...
In the output from the script below, the "ANY(a)" in the EXPLAIN QUERY
PLAN output indicates that column "a" of the index is being skipped by
the skip-scan.
CREATE TABLE t1(a TEXT, b INT, c INT, d INT);
CREATE INDEX t1abc ON t1(a,b,c);
INSERT INTO t1 VALUES('abc',123,4,5);
INSERT INTO t1 VALUES('abc',234,5,6);
INSERT INTO t1 VALUES('abc',234,6,7);
INSERT INTO t1 VALUES('abc',345,7,8);
INSERT INTO t1 VALUES('def',567,8,9);
INSERT INTO t1 VALUES('def',345,9,10);
INSERT INTO t1 VALUES('bcd',100,6,11);
/* Fake the sqlite_stat1 table so that the query planner believes
** the table contains thousands of rows and that the first few
** columns are not selective. */
ANALYZE;
DELETE FROM sqlite_stat1;
INSERT INTO sqlite_stat1 VALUES('t1','t1abc','10000 5000 2000 10');
ANALYZE sqlite_master;
EXPLAIN QUERY PLAN
SELECT a,b,c,d,'|' FROM t1 WHERE d<>99 AND b=345 ORDER BY a;
> I tried both before and after running ANALYZE; / DROP TABLE sqlite_stat1;
> but it doesn't seem to make that much of a difference.
>
> Thank you!
> Gerlando
> _______________________________________________
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
--
D. Richard Hipp
d...@sqlite.org
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
