PostgreSQL Indexing Cheat Sheet: Speeding Up Slow Queries

Slow PostgreSQL queries are often caused by a missing or poorly chosen index, according to this compact guide. It recommends using EXPLAIN (ANALYZE, BUFFERS) to spot sequential scans on large tables and large gaps between estimated and actual row counts. For equality, range, and sorting queries, it suggests composite indexes with the equality-filtered column placed first, built with CONCURRENTLY to avoid blocking writes. The guide also covers partial indexes for frequently queried subsets, covering indexes that include extra columns, and GIN indexes for payload data. Finally, it warns that every index adds write overhead and disk usage, advising developers to review pg_stat_user_indexes and drop indexes that are never scanned.