Partial indexes: index the column you sort by

A background worker regularly fetched the oldest flagged rows from a table with millions of records:

SELECT * FROM posts WHERE needs_sync = true ORDER BY updated_at LIMIT 200;

Only a small fraction of the records were flagged, so I started with a partial index on the flag:

CREATE INDEX ON posts (needs_sync) WHERE needs_sync = true;

This turned out to be less useful than I expected. Inside such an index every key is true, because the value is taken from needs_sync. The index can be used to find the rows, but cannot be used for sorting. So to get the oldest 200 rows, Postgres still has to fetch all flagged rows and sort them before it can apply the LIMIT.

Put the column you ORDER BY in the key and the flag in the WHERE:

CREATE INDEX ON posts (updated_at) WHERE needs_sync = true;

The index scan can now stop after 200 entries. It can also be used for a fast index-only scan when counting the flagged rows:

SELECT COUNT(*) FROM posts WHERE needs_sync = true;