An index is not a general “make this table faster” switch. It is an additional data structure that can provide a cheaper access path for particular query shapes. PostgreSQL must maintain it during writes, store it, and decide whether using it is cheaper than alternatives. The right starting point is therefore a real query and its execution plan—not a list of columns that look searchable.

The example below is synthetic. It illustrates the reasoning process, not measured production performance. Assume an invoices table with enough rows for access-path choice to matter, uneven activity across institutions, and a UI that requests the newest invoices for one institution. Actual row counts, data distribution, cache state, hardware, concurrency, and PostgreSQL configuration are deliberately unspecified and must be measured in the target system.

Start with the exact query

SELECT id, total, status
FROM invoices
WHERE institution_id = 42
  AND issued_at >= DATE '2026-10-01'
ORDER BY issued_at DESC
LIMIT 50;

The access pattern has four relevant properties:

  • equality on institution_id;
  • a range boundary on issued_at;
  • ordering by issued_at DESC;
  • an early stop after 50 rows.

Those details matter more than the fact that the table contains an institution_id column. A query that aggregates all invoices by month, searches by external reference, or returns most rows needs different analysis.

Capture the query with representative parameter values. One institution may own a large share of the table while another has very little data, so a plan observed for one value may not generalize. Include query frequency and concurrency: an expensive query run once a day presents a different tradeoff from one issued repeatedly by an interactive screen.

Read the plan before proposing an index

In a safe environment, use:

EXPLAIN (ANALYZE, BUFFERS)
SELECT id, total, status
FROM invoices
WHERE institution_id = 42
  AND issued_at >= DATE '2026-10-01'
ORDER BY issued_at DESC
LIMIT 50;

ANALYZE executes the statement, so use it cautiously for writes and on systems where execution is acceptable. BUFFERS adds information about buffer activity. For sensitive workloads, begin with plain EXPLAIN, inspect an existing captured plan, or reproduce the workload with representative non-sensitive data.

An illustrative pre-index plan shape might be:

Limit
  -> Sort (issued_at DESC)
       -> Seq Scan on invoices
            Filter: institution_id = ... AND issued_at >= ...

This is a shape, not a claim about timings or row counts. A sequential scan is not automatically a defect. It can be the cheapest choice when the table is small, a large fraction of rows qualifies, or the required pages are already cheap to read. The separate sort may also be acceptable for a small result set.

Compare estimated and actual rows at each node. Large differences can indicate stale statistics, skew, or correlation that the planner does not model well. Before adding an index, confirm that autovacuum and analyze behavior are healthy and that the test parameters resemble the real workload.

Match the index to the access path

For the stated assumptions, a reasonable candidate is:

CREATE INDEX CONCURRENTLY invoices_institution_issued_at_idx
ON invoices (institution_id, issued_at DESC);

The leading equality column allows PostgreSQL to find entries for one institution. The next column supports the date range and requested order, potentially allowing the executor to stop after finding 50 qualifying entries. An illustrative post-index shape is:

Limit
  -> Index Scan using invoices_institution_issued_at_idx
       Index Cond: institution_id = ... AND issued_at >= ...

Again, this shape is illustrative. PostgreSQL may choose a bitmap scan, a sequential scan, or another path based on its cost estimates. The objective is not to force an index scan; it is to verify that the chosen plan performs acceptably for the workload.

Column order follows the query, not a universal “most selective first” rule. Here, equality followed by range and order is useful. Reversing the columns could make it harder to narrow by institution while preserving the desired ordering. A separate single-column index on each predicate usually cannot provide the same ordered path.

Consider coverage without over-indexing

PostgreSQL supports included columns:

CREATE INDEX CONCURRENTLY invoices_institution_issued_at_cover_idx
ON invoices (institution_id, issued_at DESC)
INCLUDE (id, total, status);

This may permit index-only scans when visibility-map conditions are favorable, but it creates a larger index and adds write work. Included columns are payload, not search keys. Do not add them merely because they appear in SELECT; verify whether heap access is actually costly and whether index-only scans occur under representative conditions.

Partial indexes can be valuable for stable predicates, such as rows with a particular lifecycle state, but only when the query predicate reliably implies the index predicate. They require careful review as application queries evolve.

Account for the write cost

Every insert must add relevant index entries. Updates to indexed columns require index maintenance; updates to other columns may lose the benefit of heap-only tuple updates when indexes include those columns. Deletes leave entries that vacuum later cleans up. Larger indexes consume storage, cache, backup, and replication resources.

That cost changes the decision. A read-heavy lookup may justify a targeted composite index. A write-heavy ingestion table with many overlapping indexes may not. Check for indexes that duplicate the same leading key sequence, but do not remove one based only on visual similarity: uniqueness, sort direction, predicates, operator classes, and included columns can change its role.

CREATE INDEX CONCURRENTLY reduces blocking of normal writes, but it takes longer, performs additional work, and still has operational implications. It cannot run inside a transaction block. Plan capacity, monitor progress, and understand the failure cleanup procedure for the PostgreSQL version in use.

Verify the tradeoff

Re-run the plan using representative parameters and the same query shape. Compare:

  • estimated and actual rows;
  • scan and sort nodes;
  • buffer activity;
  • behavior for common and skewed parameter values;
  • latency distribution under realistic concurrency;
  • insert and update impact;
  • index size and usage over an appropriate observation period.

Warm-cache tests alone can hide I/O behavior, while a cold synthetic run can exaggerate it. State the test conditions. Do not copy timing from a laptop into a production capacity claim.

If query performance is part of a broader capacity or reliability problem, performance and reliability consulting can help connect plans, workload characteristics, application behavior, and operating constraints.

The durable method is straightforward: define the query, inspect the plan, form an index hypothesis, measure both read benefit and write cost, and retain the index only when the evidence supports it.