Indexes: B-tree, GIN, GiST, partial and expression

Choosing the right index type, composite column order, covering indexes with INCLUDE, partial and expression indexes, and when to reindex.

Which index type

-- composite: the order of columns is the access rule
-- supports (customer_id), (customer_id, issued_at) but NOT (issued_at) alone
create index invoice_customer_issued on invoice (customer_id, issued_at desc);

-- covering: all columns the query needs, so no heap fetch
create index invoice_customer_issued_cover on invoice (customer_id, issued_at desc)
  include (total_cents, status);

-- partial: index only the rows the hot query reads
create index invoice_unpaid on invoice (due_on)
  where status = 'issued';

-- expression: index the computed value
create index invoice_month on invoice (date_trunc('month', issued_at));
create index customer_email_lower on customer (lower(email));

Only an index whose leading columns appear in the query can be used. A composite index on (customer_id, issued_at) is useless to a query filtering only on issued_at, which is why column order is the single decision that matters most.

Indexes in practice

-- build without blocking writes
create index concurrently invoice_status_idx on invoice (status);

-- an index on an expression must match the expression literally
-- this query cannot use the index above:
select * from invoice where issued_at::date = current_date;
-- this one can:
select * from invoice where issued_at >= current_date
                         and issued_at < current_date + 1;

-- find unused indexes (they cost writes and space for nothing)
select relname as table_name, indexrelname as index_name, idx_scan,
       pg_size_pretty(pg_relation_size(indexrelid)) as size
from pg_stat_user_indexes
where idx_scan = 0 and indexrelid not in (select conindid from pg_constraint)
order by pg_relation_size(indexrelid) desc;

-- find missing indexes: sequential scans on large tables
select relname, seq_scan, seq_tup_read, idx_scan
from pg_stat_user_tables where seq_scan > 100 order by seq_tup_read desc;

• create index concurrently takes longer, does two table scans and can fail leaving an invalid index. Check pg_index.indisvalid and drop and retry if it did.
• Every index adds write cost and bloat. Adding twenty indexes to make one report fast is the classic own goal.
• The planner needs current statistics. If a query suddenly picks a bad plan, analyze the table before rewriting the SQL.
• reindex concurrently rebuilds bloat; plain reindex takes an exclusive lock on the table.

FAQ

Related

Query planning and performance tuning Data types, tables and constraints

Last refreshed 2026-09-18.