SQLite Forum

Query planner fails to use obvious index

> A table is (at least in some storage engines, like SQLite) itself simply a covering Index with its INDEXED key being its rowid ...

Well, with all due respect, that is not quite correct either. And the cases where it is not true show when using an index could be faster than a b-tree guided table scan.

In fact, as documented for the [SQLite3 file format](https://sqlite.org/fileformat2.html), at section "1.6. B-tree Pages", so-called "payload" (data other than the rowid) can be stored on the same pages that contain the rowid b-tree structure. (See 11th paragraph, among others.) So such pages **may** be more than simply a rowid covering index. And for that reason, when the only information to be retrieved is a rowid that can be discerned from the b-tree alone, a b-tree only index traversal *could* require fewer pages to be read from disk (or other slower forms of memory) than a traversal that also requires useless payload to be pulled into main memory.

I acknowledge that this only differs from nit-picking by a smidgen. But since we're getting all technical here, I figured the correction is apropos.

It should also be noted that the use case addressed by this and related posts fits the definition of "edge case" to a T. The "missed" optimization opportunity is likely to be no such thing when we consider that optimization takes time too.