Indexes and Query PerformanceLesson 4.4

How to optimize slow PostgreSQL queries in production

pg_stat_statements, slow query log, auto_explain, vacuum and analyze, table statistics, parallel query, enable_seqscan, rewriting inefficient queries

Query Optimization in Production

Optimization starts with measurement. Never guess which queries are slow — let the database tell you.

Find slow queries

-- Enable pg_stat_statements in postgresql.conf:
shared_preload_libraries = 'pg_stat_statements'

-- Top 10 slowest by total time
SELECT query, calls, total_exec_time, mean_exec_time
FROM   pg_stat_statements
ORDER  BY total_exec_time DESC
LIMIT  10;

Keep statistics fresh

ANALYZE employees;           -- update statistics for one table
VACUUM ANALYZE employees;    -- reclaim dead rows + update stats

Autovacuum handles this automatically in most cases. If a table has heavy churn or was recently bulk-loaded, run ANALYZE manually.

Common rewrites

-- Avoid function wrapping indexed column:
-- SLOW:
WHERE UPPER(email) = 'ALICE@EXAMPLE.COM'
-- FAST: use expression index or store normalized:
WHERE email = LOWER('ALICE@EXAMPLE.COM')

-- Avoid SELECT * on wide tables
SELECT id, name FROM products  -- not SELECT *

Check pg_stat_user_indexes for idx_scan = 0 — those indexes are unused and should be dropped. Use EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON) and paste into explain.dalibo.com for a visual breakdown.