A partial index stores only rows satisfying its predicate. It can reduce index size for a frequently queried subset.
Before you start
You should understand tables, keys, joins and SELECT queries. Write down a tiny dataset before reasoning about a query. SQL examples use PostgreSQL-style syntax where relevant; planner behavior, locking and available features must be checked for the database and version used in an application.
The practical goal is to reason through this situation: Index unresolved jobs when most historical jobs are complete. Read the walkthrough first, then try the interview exercise before opening its answer. The important part is explaining the decision and its consequences, rather than remembering a definition alone.
Step-by-step walkthrough
Step 1: Identify the important subset
Unresolved jobs may be a small fraction of historical rows.
Step 2: Align query predicates
The planner must establish that the query fits the partial index’s condition.
Step 3: Test changing distributions
The subset can grow and reduce the original advantage.
Worked scenario
Index unresolved jobs when most historical jobs are complete.
CREATE INDEX jobs_pending ON jobs (created_at, id)
WHERE status = 'pending';A query explicitly selecting pending jobs can be a candidate for this index. Different parameterized forms or predicates may lead to different plan choices; verify the application’s actual prepared query rather than an isolated rewritten example.
Common mistake
The planner must be able to relate the query predicate to the index predicate.
Verify the behavior
Compare plans with realistic pending proportions and the application’s parameterization.
Interview exercise
Evaluate a partial index.
Answer and reasoning
Test the actual query form and data distribution, including parameterization and the subset’s changing size.
Continue learning
Compare the scenario with the SQL and PostgreSQL interview questions and test your understanding with the SQL and PostgreSQL MCQs. For terminology and implementation details, consult the reference material.