Partial index
A partial index stores entries only for table rows that satisfy a specified predicate.
SQLite includes a row only when the index predicate is true; false and NULL exclude it. The predicate may reference a column absent from the index key. [1]
UNIQUE constrains the selected subset, not the whole table. NULL-key handling is a separate rule: SQLite treats NULL keys as distinct. Updating a row into the subset must also satisfy uniqueness. [2]
Query eligibility requires the planner to recognize that the query implies the predicate; logical equivalence alone need not suffice. SQLite forbids parameters in the index definition, not every parameterized query using it. [1]
SQLite permits only deterministic functions in the predicate. Labeling a changing application function deterministic does not make its results stable or preserve valid index contents. [3]
PostgreSQL can use custom plans with supplied parameter values or generic plans that retain parameters. A parameter-dependent implication cannot be assumed valid for every value. [4] [5]
A 1989 proposal also used partial predicates to track incremental index construction and rebuilding; those ideas require optimizer support, not just several small indexes. [6]
Relations
- related
- SQLite expression index, B-tree
Citations
- SQLite: Partial Indexes, membership and planner rules
- SQLite: CREATE INDEX, unique and partial indexes
- SQLite: Deterministic SQL Functions, index restrictions
- PostgreSQL 17: Partial Indexes, implication at planning time
- PostgreSQL 17: PREPARE, generic and custom plans
- Stonebraker: The Case for Partial Indexes, UCB/ERL M89/17 (1989), sections 3.1-3.4
Further reading
Sources & caveats
- Primary documentation synthesis. PostgreSQL plan qualification combines its partial-index and PREPARE documentation; no PostgreSQL execution or performance claim. Linked SQLite demonstration uses constructed rows, not field data.
- Historical design proposal read in full; cost-model examples are not modern engine benchmarks. [6]