Chapter 10 · Indexes and the SQLite Query Planner
Partial Indexes and Indexes on Expressions
Use partial and expression indexes for selective or computed access patterns, understand deterministic-expression restrictions, and prove why mathematically or semantically similar expressions may not match an index.
Learning outcomes
Full indexes include an entry for every table row. That is sometimes wasteful: a queue query may care only about pending rows, or an application may search a normalized expression rather than the stored spelling. SQLite supports both patterns with partial indexes and indexes on expressions. Their power comes with a strict rule: the index definition must remain stable and the query must logically/syntactically match what the planner can prove.
Create partial indexes whose WHERE predicate selects only rows relevant to a stable access pattern.
Explain why partial-index predicates cannot use subqueries, bound parameters, other tables, or non-deterministic functions.
Create deterministic expression indexes and match them with corresponding WHERE/ORDER BY expressions.
Prove a failed expression-index match where an equivalent-looking expression is written differently.
Compare full versus partial index storage on fresh disposable database copies without relying on optional dbstat support.
Use partial UNIQUE indexes as a design tool only when their conditional uniqueness matches a business rule.
Partial index: store only rows the query cares about
In the seeded data, only about one tenth of maintenance notes are status='open'. A dashboard that repeatedly lists newest open work does not necessarily need a time index entry for every closed note.
-- Full: every row contributes an entry.CREATE INDEX idx_note_status_time_fullON maintenance_note(status, occurred_at DESC);DROP INDEX idx_note_status_time_full;-- Partial: only rows for which the WHERE predicate is true.CREATE INDEX idx_note_open_timeON maintenance_note(occurred_at DESC)WHERE status = 'open';EXPLAIN QUERY PLANSELECT note_id, occurred_at, device_id, summaryFROM maintenance_noteWHERE status='open'ORDER BY occurred_at DESCLIMIT 25;If the planner can prove the query’s WHERE clause implies the partial-index predicate, it can use the smaller index. A query for status='closed' cannot use this index to represent closed rows because those rows simply are not in it.
Partial predicates are stored design logic
The partial-index WHERE clause decides which rows physically get index entries. SQLite therefore restricts it to expressions whose meaning is stable for the stored database.
| Not allowed in a partial-index WHERE clause | Why |
|---|---|
| Subqueries | Membership must not depend on another query result. |
| References to another table | The index must be maintained from the indexed row itself. |
| Bound parameters | Schema cannot depend on a runtime parameter value. |
Non-deterministic functions such as random() | An index cannot remain valid if the same row’s predicate changes unpredictably. |
-- Both should fail:CREATE INDEX bad_partial_1 ON maintenance_note(note_id)WHERE random() > 0;CREATE INDEX bad_partial_2 ON maintenance_note(note_id)WHERE device_id IN (SELECT device_id FROM device WHERE active=1);Partial indexes are useful beyond status flags
| Pattern | Example predicate | Why it may fit |
|---|---|---|
| Pending work | WHERE completed_at IS NULL | Completed historical rows do not need the queue index. |
| Active entities | WHERE active=1 | Hot operational subset is small relative to archived rows. |
| Non-NULL optional lookup | WHERE external_ref IS NOT NULL | Avoid entries for rows that cannot match lookups. |
| Conditional uniqueness | UNIQUE ... WHERE retired_at IS NULL | Enforce uniqueness only among currently active rows when that is the real rule. |
The predicate must match stable domain semantics. Avoid using a partial index to encode a transient condition you cannot describe cleanly and test reliably.
Expression indexes: index the computed key you actually search
Suppose reports bucket the ISO-8601 timestamp by UTC date using SQLite’s date(occurred_at). An expression index can store the result of that deterministic expression.
CREATE INDEX idx_note_dayON maintenance_note(date(occurred_at));EXPLAIN QUERY PLANSELECT count(*)FROM maintenance_noteWHERE date(occurred_at) = '2026-06-15';The planner can search the stored expression key instead of computing the date function for every row, assuming the expression matches and the cost model chooses it.
The planner does not do algebraic or semantic equivalence for expression indexes
Expression-index matching is intentionally literal enough that two mathematically equivalent or semantically similar spellings can behave differently. SQLite’s documentation gives the classic x+y versus y+x example: the planner does not algebraically rewrite one into the other.
-- Index stores date(occurred_at):CREATE INDEX IF NOT EXISTS idx_note_dayON maintenance_note(date(occurred_at));-- Matches the indexed expression:EXPLAIN QUERY PLANSELECT count(*) FROM maintenance_noteWHERE date(occurred_at)='2026-06-15';-- Semantically similar for this ISO text, but a different expression:EXPLAIN QUERY PLANSELECT count(*) FROM maintenance_noteWHERE substr(occurred_at,1,10)='2026-06-15';The second query should not use idx_note_day as an expression lookup just because the current data format makes the results equivalent. If an expression-index contract matters, centralize the query expression so schema and application do not drift apart.
Deterministic functions are a file-integrity requirement
An expression index stores computed keys in the database file. SQLite forbids functions whose result might change for the same input—such as random() or sqlite_version()—because that could make the stored index disagree with future evaluations. Application-defined functions must be explicitly registered as deterministic before SQLite may accept them in these schema contexts.
CREATE INDEX bad_random_indexON maintenance_note(random());-- Expected: an error about non-deterministic functions in index expressions.Compare full versus partial storage without optional extensions
dbstat can provide detailed page accounting when compiled in, but the course does not require optional compile features. Instead, compare two fresh database copies populated identically: one gets a full index, the other gets the partial open-work index. Check file bytes/page count after a clean close.
import sqlite3, shutilfrom pathlib import Pathsource = Path("fieldnotes-planner.db")for name, ddl in { "full-index.db": "CREATE INDEX x ON maintenance_note(status, occurred_at DESC)", "partial-index.db": "CREATE INDEX x ON maintenance_note(occurred_at DESC) WHERE status='open'",}.items(): dst = Path(name) dst.unlink(missing_ok=True) shutil.copyfile(source, dst) con = sqlite3.connect(dst) con.execute(ddl) con.commit() pages = con.execute("PRAGMA page_count").fetchone()[0] page_size = con.execute("PRAGMA page_size").fetchone()[0] con.close() print(name, "pages=", pages, "logical-bytes=", pages*page_size, "file-bytes=", dst.stat().st_size)Because the partial index contains roughly only the open subset, it should usually consume fewer additional pages than the full index. Record your actual numbers; do not hard-code a percentage as a universal storage promise.
Failure cases and diagnosis
| Symptom | Likely cause | Correction |
|---|---|---|
| Partial index exists but closed-row query does not use it. | Those rows are intentionally absent. | Use an appropriate full/different partial index only if the query matters. |
| Partial index rejected at CREATE time. | Predicate contains unsupported subquery/parameter/non-deterministic logic. | Move volatile/business logic elsewhere; keep index predicate stable and row-local. |
| Expression index not used. | Query expression is not the same expression or has different collation/structure. | Match the indexed expression and verify EQP. |
| Expression index accepted but query still scans. | Cost estimate says the index is not beneficial for this data/query. | Measure cardinality and statistics; existence is not a mandate. |
| Application-defined function rejected as non-deterministic. | Registration did not mark it deterministic—or it truly is not. | Only flag genuinely deterministic functions and test version/driver behavior. |
Reproducible lab
Keep both indexes temporarily and answer four questions: does the open queue use the partial index, does a closed queue avoid it, does date(occurred_at) use the expression index, and does substr(...) fail to match it? Then remove the lab indexes so Lesson 4 starts with a controlled schema.
DROP INDEX IF EXISTS idx_note_open_time;DROP INDEX IF EXISTS idx_note_day;DROP INDEX IF EXISTS idx_note_device_time;PRAGMA index_list('maintenance_note');Specialized-index checkpoint
Use planner evidence and schema semantics.
- Which rows get entries in a partial index?
- Why are random() and subqueries prohibited in its predicate?
- Why can date(occurred_at) and substr(occurred_at,1,10) produce similar answers but not share one expression index?
- What makes a function safe for an expression index?
- Why can a partial index reduce write/storage cost?
- Why should you still use EQP after creating a specialized index?
Review the answers
Only rows whose partial predicate evaluates true are indexed. Stored index membership/keys must be stable and row-local, so volatile functions and subqueries are forbidden. Expression indexes require the query expression to match what was indexed rather than relying on semantic/algebraic equivalence. Deterministic functions return stable results for the same inputs. Omitting irrelevant rows reduces entries to maintain, but the planner remains cost-based, so EQP is still required.
Production judgment and bridge
Partial and expression indexes are precision tools. Use them when the predicate/expression is a stable part of the application contract and test query/schema spelling together. Lesson 4 now treats the query planner itself as an observable subsystem—SCAN, SEARCH, covering access, temporary sorting B-trees, automatic indexes, join loop order, and low-level virtual-machine bytecode.