What is a Partial Index and When Should You Use One?
Learn what a partial index is, how it filters rows with a WHERE predicate, and when it beats a full index in SQL.
Expected Interview Answer
A partial index is an index built over only the subset of rows that satisfy a WHERE condition, rather than every row in the table, which keeps it smaller, faster to scan, and cheaper to maintain when queries consistently filter on that same condition.
Instead of indexing every row, you declare a predicate at index-creation time — for example, indexing only orders where status equals "pending" — and the database only stores entries for rows matching that predicate. This is especially useful when a column has a highly skewed distribution, such as a boolean flag where only a small fraction of rows are true, or when queries almost always filter for one specific subset (active users, unprocessed jobs, non-deleted records). Because the index omits the majority of rows that queries never care about, it stays small enough to fit comfortably in memory and imposes less write overhead, since inserts and updates to non-matching rows never touch the index at all.
- Smaller index size for skewed or filtered data
- Faster scans since irrelevant rows are never indexed
- Lower write overhead for rows outside the predicate
- Keeps hot, frequently queried subsets in memory
AI Mentor Explanation
Think of a scout who only keeps a fast-reference card file for currently active national-squad players, ignoring the thousands of retired or domestic-only players entirely — the file stays thin and quick to flip through because it was never built to cover everyone. A partial index works the same way: it only stores entries for rows matching a predicate like "status = active", so a query that only ever asks about active players never has to wade through irrelevant retired-player entries the index does not even contain.
Step-by-Step Explanation
Step 1
Identify a skewed or filtered query pattern
Find a column where queries consistently filter on one small subset, like status = pending.
Step 2
Define the predicate
Write the WHERE clause that captures exactly the rows queries actually need.
Step 3
Create the partial index
Build the index with that predicate so only matching rows are stored in it.
Step 4
Match query predicates to the index
Ensure queries use a WHERE clause that the planner can match to the partial index’s predicate.
What Interviewer Expects
- Clear definition: an index over a filtered subset of rows, not the whole table
- A concrete use case like indexing only active or pending rows
- Understanding of size and write-overhead savings versus a full index
- Awareness that the query’s WHERE clause must align with the index predicate to be used
Common Mistakes
- Confusing a partial index with a composite (multi-column) index
- Forgetting that the query’s filter must match or imply the index predicate
- Using a partial index on a column with low skew, gaining little benefit
- Not mentioning the reduced write overhead for non-matching rows
Best Answer (HR Friendly)
“A partial index only indexes the rows that match a specific condition, like status equals pending, instead of every row in the table. This keeps the index small and fast when queries repeatedly ask about that same subset, and it avoids the overhead of updating the index for rows outside that condition.”
Code Example
-- Index only the rows that matter for a hot query pattern
CREATE INDEX idx_orders_pending
ON Orders (created_at)
WHERE status = 'pending';
-- This query can use the small partial index directly,
-- since its WHERE clause matches the index predicate
SELECT order_id, created_at
FROM Orders
WHERE status = 'pending'
ORDER BY created_at;
-- A query for status = 'shipped' cannot use this index at allFollow-up Questions
- How does a partial index differ from a filtered composite index?
- What happens if a query’s WHERE clause does not match the partial index predicate?
- How does a partial index reduce write overhead compared to a full index?
- When would a partial index provide little to no benefit?
MCQ Practice
1. What defines a partial index?
A partial index applies a WHERE condition at creation time so it only contains entries for rows satisfying that predicate.
2. A partial index is most beneficial when?
Partial indexes shine when a small, predictable subset of rows (like pending orders) is queried far more often than the rest.
3. Why does a partial index reduce write overhead?
Since only matching rows are indexed, inserts and updates to non-matching rows never need to touch this index at all.
Flash Cards
What is a partial index? — An index built over only the rows that satisfy a specified WHERE predicate.
When is a partial index most useful? — When queries consistently filter on a small, skewed subset of rows, like active or pending records.
Why is a partial index smaller? — It never stores entries for rows that do not match its predicate.
What must align for a partial index to be used? — The query’s WHERE clause must match or imply the index’s predicate.