Your PostgreSQL table has 50 million orders.
But your operational query may care about only the small number still PENDING.
Why maintain an index entry for every completed order?
That's where a Partial Index becomes interesting.
CREATE INDEX idx_orders_pending
ON orders (created_at)
WHERE status = 'PENDING';
The table still contains everything.
The index doesn't have to.
The same idea can be useful for:
🛒 E-commerce → orders still requiring processing
📦 Parcel delivery → parcels not yet delivered
💳 Payments → transactions not yet settled
But there's an important catch:
PostgreSQL can use the partial index only when the planner can establish that the query's conditions imply the index predicate.
So don't add one because it sounds efficient.
Measure the data distribution. Examine the query plan. Test it against the real workload.
Sometimes better indexing isn't about indexing more.
It's about deliberately indexing less.
