Intermediate LevelSQL & MySQL
What are Partial (Filtered) Indexes and how do they reduce index storage space?
❓ Interview Prompt: Explain Partial Indexes (`CREATE INDEX ... WHERE condition`), reducing index tree size, and speeding up queries on specific row subsets.
A Partial (Filtered) Index (supported in PostgreSQL/SQLite) includes only rows that satisfy a specific 'WHERE' predicate:
Benefits:
•
Storage Savings: Ignores 99% of un-needed rows (e.g. indexing ONLY active un-processed jobs 'WHERE status = 'PENDING'').
•
Fast Maintenance: Re-indexing and updates consume minimal CPU and disk I/O.
Code Implementation Example
sql
-- Creating a Partial Index on pending orders only
CREATE INDEX idx_orders_pending ON orders(created_at)
WHERE status = 'PENDING';💡Creating a Partial Index on pending orders in PostgreSQL.
🎯 Interview Key Takeaway
Use Partial Indexes to index specific row subsets, reducing index storage space and maintenance overhead.
#SQL#Partial Index#Filtered Index#PostgreSQL#Intermediate
Back to all SQL & MySQL Questions→