A PostgreSQL index does not have to represent every row in its table. A partial index adds a predicate to the index definition, and only rows satisfying that predicate receive index entries. The result is an index whose physical contents encode a condition about the table.
That narrower scope changes both storage and planning behavior. Rows outside the predicate do not occupy entries in the index, but a query can use the index only when PostgreSQL can establish that the query condition implies the index predicate.
The predicate defines index membership
A conventional B-tree index on order_nr covers every table row with a non-null or null key as supported by the index method. A partial B-tree can restrict that population:
CREATE INDEX orders_open_order_nr_idx
ON orders (order_nr)
WHERE closed_at IS NULL;The index contains entries for rows whose closed_at value is null. A row with a non-null closed_at remains in the table but has no entry in this index.
This distinction is maintained as data changes. An update that moves a row from outside the predicate to inside it can require an index entry. An update that moves a row in the opposite direction removes its membership from the index. The predicate is therefore part of write behavior, not merely a hint supplied to the planner.
The indexed columns and predicate columns can also differ. In the example above, order_nr supplies the index key while closed_at determines membership. This permits an index to target a table subset without forcing the subset condition into the key layout.
Query conditions must imply the predicate
A partial index is safe only when every row relevant to an index scan is represented in that index. PostgreSQL checks this through predicate implication during query planning.
For the index above, this query states the predicate directly:
SELECT order_nr, customer_id
FROM orders
WHERE closed_at IS NULL
AND order_nr = 42017;The planner can recognize that rows matching the query also satisfy closed_at IS NULL, so the partial index is eligible for consideration. Eligibility does not guarantee selection; PostgreSQL still compares candidate plans by estimated cost.
Contrast that with a query containing only the indexed key:
SELECT order_nr, customer_id
FROM orders
WHERE order_nr = 42017;The matching order might have a non-null closed_at, and such a row would be absent from the partial index. Using that index as the sole access path could miss a valid result, so it cannot serve this query on the basis of order_nr alone.
PostgreSQL can recognize some simple implications, including certain inequalities. It does not attempt unrestricted symbolic proof of equivalent expressions. Predicate syntax and query shape can therefore affect whether a valid logical relation is visible to the planner.
Planning time creates a parameter boundary
Predicate implication is established while PostgreSQL plans the statement. A parameter whose value is not fixed for that planning decision can prevent the planner from proving a relation that would hold for a particular execution value.
Consider an index restricted to smaller values:
CREATE INDEX measurements_low_value_idx
ON measurements (value)
WHERE value < 100;A query with a literal condition such as value < 50 can establish a relation to the index predicate: every value below 50 is also below 100. A generic parameterized condition such as value < $1 does not provide that guarantee for every possible parameter value.
This boundary matters when partial-index design is paired with prepared statements. A predicate that looks compatible with a common runtime value can still be unusable when the plan must remain valid across parameter values. The relevant question is what the planner can establish from the planned expression, not what happens to be true for one execution.
Partial unique indexes constrain a subset
The same predicate mechanism can narrow uniqueness enforcement. A unique partial index applies its uniqueness rule only to rows represented in the index.
For example, a table can permit many historical records for an account while allowing only one active record:
CREATE UNIQUE INDEX account_state_one_active_idx
ON account_state (account_id)
WHERE ended_at IS NULL;Rows with a non-null ended_at are outside the index and do not participate in this uniqueness check. Among rows with ended_at IS NULL, duplicate account_id values conflict.
This is different from a full unique index whose key includes a status flag. The partial index expresses both the constrained subset and the key that must be unique within that subset. Its behavior follows index membership rather than treating the predicate column as another ordinary key component.
Smaller scope changes maintenance cost
Excluding rows reduces the number of entries stored in the index when the predicate selects a proper subset of the table. That can reduce index size and avoid index maintenance for writes that remain outside the indexed subset.
The benefit depends on the actual predicate and workload. A predicate matching nearly every row produces an index close to full-table scope. A selective predicate can produce a much smaller structure, but it is useful for access only when query conditions expose the required implication.
Data distribution can also shift. A predicate chosen around a once-small subset may cover a much larger share of the table later. PostgreSQL keeps the index correct as rows enter and leave the subset, but the original size and cost assumptions can become stale as application data changes.
Predicate design is part of query design
A partial index ties three things together: the rows stored in the index, the writes that maintain those entries, and the conditions the planner must prove before using the structure. That coupling is more specific than ordinary key selection.
The most stable uses tend to have a predicate that represents a durable data state, such as rows that remain open, active, or otherwise part of a clearly bounded subset. The index then reflects a property already present in query conditions rather than depending on incidental phrasing.
A partial index can remove large portions of a table from an index structure, but that omission is safe only because its predicate remains an explicit correctness boundary. Index size is the visible effect; planner implication and row membership are the mechanisms that make the design valid.