1---
2title: Use Partial Indexes for Filtered Queries
3impact: HIGH
4impactDescription: 5-20x smaller indexes, faster writes and queries
5tags: indexes, partial-index, query-optimization, storage
6---
7 
8## Use Partial Indexes for Filtered Queries
9 
10Partial indexes only include rows matching a WHERE condition, making them smaller and faster when queries consistently filter on the same condition.
11 
12**Incorrect (full index includes irrelevant rows):**
13 
14```sql
15-- Index includes all rows, even soft-deleted ones
16create index users_email_idx on users (email);
17 
18-- Query always filters active users
19select * from users where email = '[email protected]' and deleted_at is null;
20```
21 
22**Correct (partial index matches query filter):**
23 
24```sql
25-- Index only includes active users
26create index users_active_email_idx on users (email)
27where deleted_at is null;
28 
29-- Query uses the smaller, faster index
30select * from users where email = '[email protected]' and deleted_at is null;
31```
32 
33Common use cases for partial indexes:
34 
35```sql
36-- Only pending orders (status rarely changes once completed)
37create index orders_pending_idx on orders (created_at)
38where status = 'pending';
39 
40-- Only non-null values
41create index products_sku_idx on products (sku)
42where sku is not null;
43```
44 
45Reference: [Partial Indexes](https://www.postgresql.org/docs/current/indexes-partial.html)
46