openpolitics.com

Efficient Use of PostgreSQL Indexes

For example, suppose you allow users to flag comments on your site, which in turn sets the flagged boolean to true. You then process flagged comments in batches. You may want to create an index like so:

CREATE INDEX articles_flagged_created_at_index ON articles(created_at) WHERE flagged IS TRUE;

This index will remain fairly small, and can also be used along other indexes on the more complex queries that may require it.