A query returning 12 rows scanned two million of them. The SQL reads fine and the planner chose Seq Scan. Before you blame the ORM or the hardware, read the plan: EXPLAIN (ANALYZE, BUFFERS) prints the path Postgres took, with time per node and pages read. The three index types below cover most read problems in PostgreSQL.

Read the plan before creating an index

EXPLAIN shows the estimated plan. EXPLAIN ANALYZE runs the query and shows real time per node. The gap between estimate and reality exposes a filter that discards most rows after the scan, or a cold buffer cache at test time. A Seq Scan on a 50k-row table is the right call: reading everything in sequence costs less than tree jumps. A Seq Scan on a 20-million-row table with a filter that returns 10 rows points to a missing index or a column the existing index does not cover.

The official PostgreSQL documentation (2025) describes the index method family: B-tree, Hash, GiST, SP-GiST, GIN and BRIN. Each method serves one shape of search, and B-tree dominates equality and range queries.

B-tree, GIN, and column order

A B-tree index serves =, range operators, BETWEEN and ORDER BY in the indexed direction. A pattern match in the middle of the text, like ILIKE '%value%', leaves B-tree behind and needs GIN with the pg_trgm extension. JSONB columns and arrays use GIN: the index inverts the document and points at rows containing each key or value.

Column order in a composite index follows the planner's rule: equality columns first, range columns after. An index on (tenant_id, created_at) serves a tenant filter sorted by date; the reverse order serves a date filter across any tenant and forces a wider scan. Markus Winand documents this hierarchy on use-the-index-luke.com, a reference database teams have relied on for over a decade.

Partial and covering indexes

A partial index covers a subset of the table: WHERE status = 'active' shrinks the index and cuts write cost. Tables whose queries land on the active subset benefit the most. A covering index, with the INCLUDE clause, keeps extra columns in the index leaf: the planner reads the index without going back to the heap table. The leaf-by-leaf math behind this appears in Database Internals by Alex Petrov (O'Reilly, 2019), with the details of how Postgres walks pages.

Write cost and forgotten indexes

Every INSERT, UPDATE and DELETE pays to maintain the table's indexes. An event table with 12 indexes doubles write work. pg_stat_user_indexes lists indexes the planner never used; you measure write throughput before and after the drop and keep the ones that serve a rare audit query, with the rationale checked into the repository. On heavy-write tables, autovacuum needs headroom; without it, the B-tree fragments and a leaf occupies more space than the data.