Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
SQLite version 3.24.0 2018-06-04 14:10:15
Enter ".help" for usage hints.
sqlite> .schema
CREATE TABLE stats (
  date        TEXT,
  group_id    TEXT,
  artifact_id TEXT,
  version     TEXT,
  downloads   INT,
  CONSTRAINT prim UNIQUE (date, group_id, artifact_id, version)
);
sqlite> .timer on
sqlite> select count(downloads) from stats where artifact_id = 're-frame' and group_id = 're-frame' and date like '2018-%';
4106
Run Time: real 0.966 user 0.789593 sys 0.174458
sqlite> explain query plan select count(downloads) from stats where artifact_id = 're-frame' and group_id = 're-frame' and date like '2018-%';
QUERY PLAN
`--SCAN TABLE stats
Run Time: real 0.000 user 0.000086 sys 0.000019

A full table scan is necessary for the query above — despite the UNIQUE constraint.

sqlite> create index stats_keys on stats (date, group_id, artifact_id, version);
Run Time: real 14.217 user 10.871043 sys 3.053484
sqlite> explain query plan select count(downloads) from stats where artifact_id = 're-frame' and group_id = 're-frame' and date like '2018-%';
QUERY PLAN
`--SCAN TABLE stats
Run Time: real 0.000 user 0.000100 sys 0.000015

Adding another index doesn't immediately solve the issue.

But running ANALYZE stats does:

sqlite> ANALYZE stats;
Run Time: real 4.970 user 4.575637 sys 0.385702
sqlite> explain query plan select count(downloads) from stats where artifact_id = 're-frame' and group_id = 're-frame' and date like '2018-%';
QUERY PLAN
`--SEARCH TABLE stats USING INDEX stats_keys (ANY(date) AND group_id=? AND artifact_id=?)
Run Time: real 0.000 user 0.000105 sys 0.000021

Why is that? Is it expected that users run ANALYZE on their own?

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.