On Nov 8, 2010, at 5:12 AM, Alexey Pechnikov wrote:
> 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 concept of "correct" is tricky here. When planning a query,
SQLite normally assumes that the (rowid>?) clause identifies
1/3 of the rows in table object_record. Using this assumption,
it goes on to assume that scanning via the index (so that
duplicate object_id values can excluded without using any
additional data structure) is going to be faster than using the
rowid index and a temporary b-tree to filter out duplicate
entries.
If the (rowid>?) clause identifies a small number of rows,
then the last assumption will be incorrect - using the rowid
index would be much faster. But SQLite doesn't know this when
formulating a query plan.
Regards,
Dan.
> 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
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
