Start with EXPLAIN ANALYZE
Never guess. Always measure:
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT p.*, u.name AS author_name
FROM posts p
JOIN users u ON u.id = p.author_id
WHERE p.status = 'published'
AND p.category_id = 5
ORDER BY p.created_at DESC
LIMIT 20;
ANALYZE executes the query and shows actual times. BUFFERS shows how many pages were read from disk vs cache. Without these flags, you are looking at estimates that can be wildly wrong.
Reading the output
Sort (cost=45123.45..45123.50 rows=20 width=256) (actual time=8234.123..8234.130 rows=20 loops=1)
Sort Key: p.created_at DESC
-> Hash Join (cost=... rows=45000 ...)
-> Seq Scan on posts p (cost=... rows=45000 ...)
Filter: ((status = 'published') AND (category_id = 5))
Rows Removed by Filter: 1955000
The problem is Seq Scan on posts — a sequential scan of 2M rows, removing 1.95M of them. PostgreSQL is reading the entire table to find 45K matching rows.
The fix: a composite index
CREATE INDEX idx_posts_status_category_created
ON posts (status, category_id, created_at DESC);
This composite index covers all three parts of the query: the equality filters (status, category_id) and the sort (created_at DESC). The order matters — equality columns first, then sort columns.
After the index:
Limit (cost=0.56..12.34 rows=20 width=256) (actual time=0.089..0.156 rows=20 loops=1)
-> Index Scan using idx_posts_status_category_created on posts p
Index Cond: ((status = 'published') AND (category_id = 5))
8 seconds to 0.15ms. The index scan reads exactly the 20 rows it needs, already sorted.
Common performance killers
1. Missing indexes on foreign keys
PostgreSQL does not auto-index foreign keys. Every JOIN on an unindexed foreign key is a sequential scan:
-- Check for missing FK indexes
SELECT
tc.table_name, kcu.column_name
FROM information_schema.table_constraints tc
JOIN information_schema.key_column_usage kcu
ON tc.constraint_name = kcu.constraint_name
WHERE tc.constraint_type = 'FOREIGN KEY'
AND NOT EXISTS (
SELECT 1 FROM pg_indexes
WHERE tablename = tc.table_name
AND indexdef LIKE '%' || kcu.column_name || '%'
);
2. SELECT * when you need three columns
-- Bad: fetches all columns, more I/O
SELECT * FROM posts WHERE status = 'published';
-- Good: only what you need
SELECT id, title, slug FROM posts WHERE status = 'published';
With a covering index, PostgreSQL can serve the query entirely from the index without touching the table:
CREATE INDEX idx_posts_published_covering
ON posts (status) INCLUDE (id, title, slug);
3. OR conditions that prevent index use
-- Bad: cannot use a single index efficiently
SELECT * FROM posts WHERE author_id = 5 OR category_id = 10;
-- Better: UNION of two indexed queries
SELECT * FROM posts WHERE author_id = 5
UNION
SELECT * FROM posts WHERE category_id = 10;
4. Functions on indexed columns
-- Bad: index on created_at is useless
SELECT * FROM posts WHERE DATE(created_at) = '2024-03-15';
-- Good: range query uses the index
SELECT * FROM posts
WHERE created_at >= '2024-03-15'
AND created_at < '2024-03-16';
pg_stat_statements: find your worst queries
Enable the pg_stat_statements extension and query it periodically:
SELECT query, calls, mean_exec_time, total_exec_time
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;
This shows the queries consuming the most total time. Optimizing the top 3 often gives you 80 percent of the performance improvement.