partial-index · shipping

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

  1. SQLite: Partial Indexes, membership and planner rules (accessed 2026-11-18)
  2. SQLite: CREATE INDEX, unique and partial indexes (accessed 2026-11-18)
  3. SQLite: Deterministic SQL Functions, index restrictions (accessed 2026-11-18)
  4. PostgreSQL 17: Partial Indexes, implication at planning time (accessed 2026-11-18)
  5. PostgreSQL 17: PREPARE, generic and custom plans (accessed 2026-11-18)
  6. Stonebraker: The Case for Partial Indexes, UCB/ERL M89/17 (1989), sections 3.1-3.4 (accessed 2026-11-18)

Further reading

  1. Hesela: partial-index membership and uniqueness controls (accessed 2026-11-18)
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]

JSON: /data/terms/partial-index.json

Published by Hesela team · Updated

Report a correction · Editorial standards · Reuse guidance

Cite this page
Hesela team. Partial index. Hesela. 2026-11-18. https://hesela.dev/terms/partial-index/