PostgreSQL 10 High Performance - Third Edition

Partial indexes

Indexes do not have to cover the entirety of a table. You can create smaller, targeted indexes that satisfy a particular WHERE clause, and the planner will use those when appropriate. For example, consider the case where you are tracking a set of customer accounts, and want to flag a small number of them for special processing; let's just say they are flagged as interesting with a Boolean of that name. You could then create a partial index:

CREATE INDEX accounts_interesting_index ON accounts WHERE interesting IS true;  

(That's intentionally more verbose than necessary just to demonstrate the syntax.) You'd expect this index to be very small, relative to one that included every account. And it would return the list of interesting ...