A partial index, also called a filtered index, stores entries only for table rows satisfying a condition in the index’s WHERE clause. It targets frequently queried subsets while ignoring unrelated rows. section 4.8
Because it contains fewer rows, a partial index is smaller, uses less memory and disk, and can make updates faster by avoiding index maintenance for excluded rows. PostgreSQL chooses B-tree by default unless another access method is specified. section 4.2 section 4.8
CREATE INDEX partial_index_name ON table_name(columnA) WHERE condition;
Use the partial-index template by naming the indexed column and expressing the qualifying row condition after WHERE. section 4.8
Indexing occasional players
A game tracks 10,000 players, but only 74 have played 50 hours or fewer. The target query filters those players and orders them by play_time. Before indexing, EXPLAIN shows a sequential scan that filters out 9,926 rows and sorts the 74 matches. Creating CREATE INDEX idx_occasional_players ON game.player_stats (play_time) WHERE play_time <= '50 hours'; changes the plan to an index scan, reducing execution time in the displayed run from 2.168 ms to 0.168 ms. The predicate must match the indexed condition: a query using play_time <= '50 hours 1 second' falls back to a sequential scan in the example. section 4.8 section 4.8
Indexing active queue messages
A message queue can create CREATE INDEX mq_partial_index_btree ON mq.queue (created_at, status) WHERE status = 'new';. This keeps processing candidates compact and lets PostgreSQL skip messages marked processing or completed. section 11.5.2
