sqlite> .s object_record
CREATE TABLE object_record
(
record_id INTEGER PRIMARY KEY,
ts INTEGER NOT NULL DEFAULT (strftime('%s','now')),
object_id INTEGER NOT NULL
);
CREATE INDEX object_id_ts_idx on object_record(object_id,ts);
CREATE INDEX object_ts_idx on object_record(ts);
The planner bug with "distinct":
sqlite> explain query plan select distinct object_id from (select
ts,object_id from object_record where rowid>1581369-1) as x;
0|0|TABLE object_record WITH INDEX object_id_ts_idx ORDER BY
CPU Time: user 0.000000 sys 0.000000
sqlite> select distinct object_id from (select ts,object_id from
object_record where rowid>1581369-1) as x;
31596
CPU Time: user 0.224014 sys 0.000000
The plan without "distinct" is correct:
sqlite> explain query plan select object_id from (select ts,object_id from
object_record where rowid>1581369-1) as x;
0|0|TABLE object_record USING PRIMARY KEY
CPU Time: user 0.000000 sys 0.000000
sqlite> select object_id from (select ts,object_id from object_record where
rowid>1581369-1) as x;
31596
CPU Time: user 0.000000 sys 0.000000
The correct plan may be:
sqlite> drop INDEX object_id_ts_idx;
CPU Time: user 0.024001 sys 0.000000
sqlite> explain query plan select distinct object_id from (select
ts,object_id from object_record where rowid>1581369-1) as x;
0|0|TABLE object_record USING PRIMARY KEY
CPU Time: user 0.000000 sys 0.000000
sqlite> select distinct object_id from (select ts,object_id from
object_record where rowid>1581369-1) as x;
31596
CPU Time: user 0.000000 sys 0.000000
And bug again:
sqlite> CREATE INDEX object_id_idx on object_record(object_id);
CPU Time: user 4.540283 sys 0.016001
sqlite> explain query plan select distinct object_id from (select
ts,object_id from object_record where rowid>1581369-1) as x;
0|0|TABLE object_record WITH INDEX object_id_idx ORDER BY
CPU Time: user 0.000000 sys 0.000000
sqlite> select distinct object_id from (select ts,object_id from
object_record where rowid>1581369-1) as x;
31596
CPU Time: user 0.236015 sys 0.000000
--
Best regards, Alexey Pechnikov.
http://pechnikov.tel/
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
