Indexes & Query Performance

Partial, Functional, and Specialized Indexes

Specialized indexes encode a narrower population, a computed search key, uniqueness, or type-specific operator semantics. They are powerful when query text and invariants match their contract, but deliberately serve fewer shapes.

Partial indexes store only qualifying rows.
Expression indexes store computed keys.
Unique indexes enforce an invariant.
Partial uniqueness expresses scoped identity.
Specialized operator classes define meaning.
Prepared parameters can obstruct implication.

PostgreSQL specialized contracts

-- Small pending-order queue: the query implies the index predicate.
CREATE INDEX orders_pending_idx
  ON orders (customer_id, placed_at) WHERE status = 'PENDING';

-- Case-folded identity on the canonical customer relation.
CREATE UNIQUE INDEX customers_email_ci_uq
  ON customers (lower(email));

-- Transaction-line uniqueness beyond the canonical primary key.
CREATE UNIQUE INDEX order_items_product_once_uq
  ON order_items (order_id, product_id);

SELECT order_id FROM orders
WHERE customer_id = 7 AND status = 'PENDING'
ORDER BY placed_at;

Contract and failure mode
Kind	Contract	Common mismatch
Partial / filtered	Query implies stored-row predicate	Parameterized or logically different predicate
Expression / function-based	Query expression matches indexed expression	Implicit cast, collation, different function
Unique	No duplicate index keys under engine NULL rules	Assuming business normalization not encoded
GIN/GiST/trigram/etc.	Operator belongs to configured class/family	Using unsupported operator or function

The partial queue index omits shipped orders, so their writes avoid this structure and queue scans touch fewer entries. It cannot answer historical shipped-order queries. The unique cart index enforces the scoped invariant atomically under concurrency, which an application “check then insert” cannot.