Query Performance

Use Partial Indexes to Index the Rows You Actually Query

A normal database index contains an entry for every table row that qualifies for the indexed columns. That is often appropriate, but some applications repeatedly query only a small, stable subset of a table. Consider a task table where most tasks eventually become completed or archived, while the application dashboard mainly reads current open tasks. A full index on project_id keeps index entries for historical rows even though those rows are rarely part of the hot query path.