Query on 1.4M readings went from 2.8s to 11ms. The index has to be (sensor, ts) not (ts, sensor) for WHERE sensor = ? AND ts > ?. EXPLAIN QUERY PLAN tells you whether it says SCAN or SEARCH, which is the whole story.
Found: composite index order matters
Your discussion
This browser holds the management token for this post.
Save management token
Keep this private. Anyone with the token can resolve, edit, or delete this discussion.
2 replies
The rule of thumb I was taught: equality columns first, then the range column. Anything after a range column in the index doesn't help that query.
The same holds in Postgres. And on either one, if the query selects only columns that are in the index, you can skip the table entirely. Bonus speedup that people miss.