Indexes
Partial indexes
In the indexes you created earlier, PostgreSQL created an index entry for every row in the table.
A partial index works differently. It contains entries only for rows that satisfy a condition.
To see why this can be useful, suppose the application frequently needs to find a customer's orders that are still being processed:
1234SELECT *FROM ordersWHERE customer_id = 9189 AND status = 'processing';Before creating an index, inspect how PostgreSQL executes the query:
12345EXPLAIN ANALYZESELECT *FROM ordersWHERE customer_id = 9189 AND status = 'processing';You should see a sequential scan of the orders table.
PostgreSQL therefore examines each row in the table and checks whether both customer_id = 9189 and status = 'processing' are true for that row.
Creating a partial index
For this query, we can create an index on customer_id that contains entries only for orders whose status is processing:
123CREATE INDEX idx_orders_processing_customerON orders (customer_id)WHERE status = 'processing';The condition WHERE status = 'processing' is called the predicate of the partial index.
PostgreSQL creates an index entry only for rows that satisfy this predicate. Orders with any other status are not included in the index.
A simplified view looks like this:
Notice that customer_id is the indexed column, while status = 'processing' determines which rows are included in the index.
The column used in the predicate doesn't have to be one of the indexed columns.
Using the partial index
Now run the query again:
12345EXPLAIN ANALYZESELECT *FROM ordersWHERE customer_id = 9189 AND status = 'processing';The execution plan should now show an index scan using idx_orders_processing_customer:
12Index Scan using idx_orders_processing_customer on orders Index Cond: (customer_id = 9189)This means PostgreSQL used the partial index instead of scanning every row in the orders table.
Because the index contains only rows where status = 'processing', PostgreSQL only needs to search it for customer_id = 9189 to find the matching orders.
The query must match the indexed subset
A partial index can be used only when PostgreSQL can determine that the rows requested by the query are included in the subset represented by the index.
Consider this query:
123SELECT *FROM ordersWHERE customer_id = 9189;This query asks for all orders belonging to customer 9189, regardless of their status.
But idx_orders_processing_customer contains entries only for orders where status = 'processing'. Orders with other statuses are not present in the index.
PostgreSQL therefore cannot use this partial index to retrieve all the rows required by the query.
Now consider this query:
1234SELECT *FROM ordersWHERE customer_id = 9189 AND status = 'delivered';The partial index cannot be used here either. It contains only processing orders, while this query asks for delivered orders.
The important rule is that the query condition must imply the predicate of the partial index. In other words, PostgreSQL must be able to determine that every row required by the query belongs to the subset represented by the index.
Why use a partial index?
A regular index on customer_id would contain an entry for every order:
12CREATE INDEX idx_orders_customerON orders (customer_id);Our partial index contains entries only for processing orders:
123CREATE INDEX idx_orders_processing_customerON orders (customer_id)WHERE status = 'processing';Because fewer rows are included, a partial index can require less storage than an equivalent index that covers the entire table.
It can also reduce index-maintenance work because rows that don't satisfy the predicate don't need entries in this index.
Partial indexes are therefore useful when an application frequently queries a particular subset of a table and there is little benefit in indexing the remaining rows.
As with any index, PostgreSQL's planner still decides whether using the partial index is cheaper than the other available execution plans.
Before continuing, remove the partial index:
1DROP INDEX idx_orders_processing_customer;