Indexes: B-tree, GIN, and partial

The right index, not just an index

B-tree serves equality and range comparisons and is the default for good reason. GIN indexes containment queries over arrays, JSONB, and text search vectors. Using the wrong type means the index simply is not consulted.

Column order in a composite index matters: it can serve a query filtering on a leading prefix, but not one filtering only on a later column. Put the equality columns first and the range column last.

A partial index covers only the rows matching a condition. On a table where one percent of rows are pending, an index on just those is a fraction of the size and far more effective.