Postgres index cheatsheet

Index types

• B-tree — the default. Equality and range queries, sorting, LIKE 'prefix%'.
• GIN — arrays, jsonb containment, and full-text search.
• BRIN — very large, naturally ordered tables such as append-only logs.
• Partial — add WHERE to index only the rows you query, like unarchived records.

Check the plan

EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM entries
WHERE topic = 'git' AND published_at > now() - interval '30 days';

Look for Index Scan or Bitmap Index Scan. A Seq Scan on a large table usually means the index does not match the filter or the planner estimates it would not help.

Find unused indexes

SELECT relname, indexrelname, idx_scan
FROM pg_stat_user_indexes
WHERE idx_scan = 0
ORDER BY relname;