Query Optimization

PostgreSQL’s query planner decides how to execute every query — which indexes to use, in which order to join tables, and how to aggregate results. For most queries it makes good decisions automatically, but when a query is slow, the planner’s choices are the first thing to investigate. Reading execution plans and understanding what influences the planner’s decisions is one of the most practical skills for any developer working with Postgres at scale.

EXPLAIN and EXPLAIN ANALYZE

EXPLAIN shows the execution plan the planner would use without actually running the query. EXPLAIN ANALYZE runs the query and adds real timing and row counts to each plan node. The combination of ANALYZE, BUFFERS gives the most complete picture of where time and I/O are being spent.

-- Plan only, no execution — safe to run on production without side effects
EXPLAIN SELECT * FROM orders WHERE customer_id = 42;

-- Plan + real execution data — runs the query, so avoid on expensive writes in production
EXPLAIN ANALYZE SELECT * FROM orders WHERE customer_id = 42;

-- Most useful combination: real timing plus buffer (cache) hit information
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT o.id, o.total, c.name
FROM orders o
JOIN customers c ON c.id = o.customer_id
WHERE o.created_at >= '2024-01-01';

Each node in the output shows:

• cost=startup..total — estimated cost units (not milliseconds) to return the first row and all rows
• rows — estimated row count
• width — estimated average row width in bytes
• actual time — real milliseconds (first row..last row) when using ANALYZE
• actual rows — real row count returned

Seq Scan on orders  (cost=0.00..4821.00 rows=95000 width=48)
                    (actual time=0.012..18.432 rows=94817 loops=1)

A large gap between estimated rows and actual rows is a red flag — it means the planner is working with stale statistics.

Scan Types

Understanding scan types tells you immediately whether a query is touching the right amount of data.

Sequential Scan (Seq Scan) reads every page of the table. It is the right choice for small tables or queries returning a large fraction of rows. On a large table returning a small fraction of rows, it’s a sign a useful index is missing.

Index Scan follows the index to find matching rows, then fetches each row from the heap (table). Random I/O makes this expensive when returning many rows.

Index Only Scan satisfies the query entirely from the index without touching the heap. Requires a covering index and a clean visibility map. This is the best case.

Bitmap Heap Scan first collects all matching index entries into a bitmap, then reads heap pages once in physical order. It is the middle ground — better than a full index scan for moderate result sets.

-- Create a covering index to enable Index Only Scan
-- The query needs customer_id and total — both are in the index
CREATE INDEX idx_orders_customer_total
  ON orders (customer_id, total);

EXPLAIN SELECT customer_id, total FROM orders WHERE customer_id = 42;
-- Should now show: Index Only Scan

Join Strategies

The planner chooses a join strategy based on table sizes and available indexes. You’ll see these in EXPLAIN output.

Nested Loop — for each row in the outer relation, scan the inner relation. Best when the inner side is small or driven by an index. O(n*m) worst case.

Hash Join — build a hash table from the smaller relation, then probe it with the larger. Efficient for large unsorted inputs but uses memory.

Merge Join — both inputs must be sorted on the join key. Very fast when the data is already sorted (e.g., joining on a sequential PK).

-- Force a specific join type to test alternatives (diagnostic use only)
SET enable_hashjoin = off;
EXPLAIN ANALYZE
SELECT o.id, c.name FROM orders o JOIN customers c ON c.id = o.customer_id;
SET enable_hashjoin = on;  -- always reset after testing

Keeping Statistics Fresh

The planner uses pg_statistic to estimate how many rows each condition will match. These statistics become stale after heavy writes. When estimated row counts diverge significantly from actual counts, the planner makes bad decisions. ANALYZE refreshes them.

-- Update statistics for one table after a large data load
ANALYZE orders;

-- Check when a table was last analyzed
SELECT relname, last_analyze, last_autoanalyze
FROM pg_stat_user_tables
WHERE relname = 'orders';

Autovacuum runs ANALYZE automatically, but after a bulk load you should run it manually before executing critical queries.

Common Anti-Patterns

These are the most frequently encountered query patterns that silently disable index use.

Functions on indexed columns defeat indexes — wrapping a column in a function prevents PostgreSQL from using the index on that column:

-- Bad: index on created_at is ignored because DATE() is applied first
WHERE DATE(created_at) = '2024-06-01'

-- Good: express as a range so the index on created_at can be used
WHERE created_at >= '2024-06-01' AND created_at < '2024-06-02'

Leading wildcard kills index use — a B-tree index can only match from the left side of a string:

-- Bad: full table scan required — the leading % means "anything before @gmail.com"
WHERE email LIKE '%@gmail.com'

-- Good: prefix match uses the B-tree index directly
WHERE email LIKE 'john%'

Implicit type cast prevents index use — when the column type and the literal type don’t match, PostgreSQL applies a cast that prevents index use:

-- Bad: customer_id is integer, '42' is text — the implicit cast disables the index
WHERE customer_id = '42'

-- Good: matching types allow index use
WHERE customer_id = 42

pg_stat_statements

This extension tracks execution statistics across all calls. It’s the fastest way to identify your slowest and most frequently executed queries — the ones worth tuning first.

-- Enable in postgresql.conf (requires restart):
-- shared_preload_libraries = 'pg_stat_statements'

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

-- Top 10 queries by total execution time — your highest-impact optimization targets
SELECT
  round(total_exec_time::numeric, 2) AS total_ms,
  calls,
  round(mean_exec_time::numeric, 2) AS avg_ms,
  left(query, 80) AS query_snippet
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;

Table Bloat and Autovacuum

PostgreSQL’s MVCC model never overwrites rows — it writes new versions and marks old ones dead. Dead tuples accumulate as bloat until VACUUM reclaims them. Bloat inflates table size, slows sequential scans, and degrades index performance. For high-write tables, autovacuum’s default settings are often too conservative.

-- Check bloat per table — tables with high dead_pct need attention
SELECT
  relname,
  n_dead_tup,
  n_live_tup,
  round(100.0 * n_dead_tup / nullif(n_live_tup + n_dead_tup, 0), 1) AS dead_pct,
  last_vacuum,
  last_autovacuum
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC;

-- Manually reclaim space and update statistics
VACUUM ANALYZE orders;

-- Reclaim space and return pages to OS (locks table during operation)
VACUUM FULL orders;

Tune autovacuum aggressively for high-write tables by lowering autovacuum_vacuum_scale_factor at the table level:

-- Trigger autovacuum when 1% of rows are dead instead of the default 20%
ALTER TABLE orders SET (autovacuum_vacuum_scale_factor = 0.01);

Quick Optimization Checklist

1. Run EXPLAIN (ANALYZE, BUFFERS) and look for Seq Scans on large tables.
2. Check estimated vs. actual rows — a large mismatch means stale statistics; run ANALYZE.
3. Look for nested loops on large outer relations without supporting indexes.
4. Add indexes to foreign key columns and frequently filtered columns.
5. Use pg_stat_statements to find the highest-impact queries to tune first.
6. Check for functions or type mismatches in WHERE clauses.
7. Monitor dead tuple counts and ensure autovacuum is keeping up.