PostgreSQL Query Performance Tuning

Slow queries are the most common performance bottleneck in PostgreSQL applications. Learning to read execution plans and apply targeted optimizations can turn a 10-second query into a 10-millisecond one.

Reading EXPLAIN ANALYZE

EXPLAIN ANALYZE
SELECT c.first_name, COUNT(o.id)
FROM customers c
JOIN orders o ON c.id = o.customer_id
WHERE o.created_at >= '2025-01-01'
GROUP BY c.id, c.first_name;

-- Key output fields:
-- Seq Scan vs Index Scan   (how rows are found)
-- actual time              (real execution time in ms)
-- rows                     (rows processed)
-- loops                    (number of iterations)
-- Planning Time / Execution Time

EXPLAIN shows the planner's strategy. Adding ANALYZE actually runs the query and shows real execution times. Look for Seq Scan on large tables -- it usually means a missing index.

Common Bottlenecks and Fixes

-- Problem: Sequential scan on a large table
-- Fix: Create an index on the filtered column
CREATE INDEX idx_orders_created_at ON orders (created_at);

-- Problem: Sorting millions of rows
-- Fix: Create an index that matches the ORDER BY
CREATE INDEX idx_products_price ON products (category, price DESC);

-- Problem: Hash Join on a huge table
-- Fix: Add an index on the join column
CREATE INDEX idx_orders_customer_id ON orders (customer_id);

Query Optimization Patterns

-- SLOW: Function on indexed column prevents index use
SELECT * FROM orders WHERE DATE(created_at) = '2025-03-15';

-- FAST: Use a range instead
SELECT * FROM orders
WHERE created_at >= '2025-03-15'
  AND created_at < '2025-03-16';

-- SLOW: SELECT * fetches unnecessary columns
SELECT * FROM customers WHERE id = 42;

-- FAST: Select only what you need
SELECT first_name, email FROM customers WHERE id = 42;

-- SLOW: OR conditions can prevent index use
SELECT * FROM products WHERE category = 'A' OR category = 'B';

-- FAST: Use IN instead
SELECT * FROM products WHERE category IN ('A', 'B');

Monitoring Slow Queries

-- Enable pg_stat_statements extension
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

-- Find the slowest queries
SELECT
  ROUND(mean_exec_time::numeric, 2) AS avg_ms,
  calls,
  ROUND(total_exec_time::numeric, 2) AS total_ms,
  LEFT(query, 80) AS query_preview
FROM pg_stat_statements
ORDER BY mean_exec_time DESC
LIMIT 10;

Performance Checklist

• Run EXPLAIN ANALYZE on any query taking more than 100ms
• Index columns used in WHERE, JOIN ON, and ORDER BY clauses
• Avoid wrapping indexed columns in functions
• Select only the columns you need, not SELECT *
• Use pg_stat_statements to identify the most impactful slow queries
• Run VACUUM ANALYZE regularly to keep statistics up to date