Guide/Advanced/Performance Tuning

Performance Tuning

A systematic approach to finding and fixing slow queries.

SQL performance problems usually feel mysterious until you look at the query plan. Once you do, they almost always fall into a small number of categories. The process is more diagnostic than creative: measure, identify the bottleneck, apply a targeted fix, and verify.

Step one: measure before you guess

The most common mistake in performance tuning is optimizing before measuring. Adding indexes to queries that are not actually slow, or rewriting queries that run in 2ms, is wasted effort. Start by identifying the slow query, then understanding why it is slow.

EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT * FROM orders WHERE customer_id = 42;

Read the output before doing anything else. The plan tells you exactly where the time is being spent.

Missing or unused indexes

This is the most common cause of slow queries. Look for Seq Scan on a large table in the plan. If the table has millions of rows and the query returns a small fraction of them, a sequential scan means no index is helping.

-- Slow: scanning the entire orders table to find one customer
Seq Scan on orders (cost=0.00..48500.00 rows=85 ...)

-- Add an index:
CREATE INDEX idx_orders_customer_id ON orders (customer_id);

-- Fast: using the index
Index Scan using idx_orders_customer_id on orders (cost=0.43..120.00 rows=85 ...)

Sometimes an index exists but is not used. The most common reason: the query filters on a function of the column rather than the column directly.

-- Will NOT use an index on created_at (because of DATE_TRUNC)
WHERE DATE_TRUNC('day', created_at) = '2024-01-15'

-- Will use it (direct range comparison on the indexed column)
WHERE created_at >= '2024-01-15' AND created_at < '2024-01-16'

Other index-defeating patterns: wrapping a column in a function, implicit type casting between the filter value and the column type, and using LIKE with a leading wildcard (LIKE '%term').

Stale planner statistics

The query planner estimates how many rows each step will return using statistics collected about the table's data distribution. If those statistics are out of date, the planner may choose a bad execution strategy.

Look for a large gap between estimated and actual rows in EXPLAIN ANALYZE output:

Seq Scan on orders (cost=... rows=100 ...) (actual rows=1500000 ...)

The planner expected 100 rows but found 1.5 million. That discrepancy will cause it to make bad decisions about join strategies and index use. Refresh the statistics:

ANALYZE orders;

PostgreSQL's autovacuum runs ANALYZE automatically, but on rapidly growing tables it may not keep up.

The N+1 pattern

N+1 is a class of problem where application code runs one query to get a list of N things, then runs one more query per item to get related data. The result is N+1 database round trips when one query with a join would suffice.

At the query level, the same pattern appears as a nested loop with a high loop count:

Nested Loop  (cost=... actual rows=... loops=50000)
  -> Seq Scan on orders

If the inner scan (orders) runs 50,000 times, either the outer query is returning 50,000 rows or a correlated subquery is running per row. The fix is usually to restructure as a join, or to use a window function to compute the per-row value in one pass.

Selecting too many columns or too much data

SELECT * fetches every column, including wide columns like TEXT, JSONB, and BYTEA. If a table has a description column storing kilobytes of text and you are only displaying a name and date, you are transferring far more data than necessary.

Select only the columns you actually need:

-- Fetches all columns including large ones
SELECT * FROM products WHERE category = 'electronics';

-- Fetches only what the caller needs
SELECT id, name, price FROM products WHERE category = 'electronics';

This reduces I/O and network transfer, and can allow the database to use a covering index (an index that contains all the requested columns, avoiding the need to touch the table at all).

Inefficient joins

A poorly structured join can force the database into an expensive strategy. The most common problem is a join column that lacks an index on one side:

-- If orders.customer_id is not indexed, this is a nested loop with a seq scan per row
SELECT c.name, o.total
FROM customers c
JOIN orders o ON o.customer_id = c.id;

Adding an index on the join column lets the planner use a more efficient strategy. Check EXPLAIN ANALYZE for join node types: a Hash Join or Merge Join on large tables is usually fine; a Nested Loop with high loop counts and inner seq scans is not.

A repeatable diagnostic process

  1. Find the slow query (slow query log, APM tool, or pg_stat_statements)
  2. Run EXPLAIN (ANALYZE, BUFFERS) and read the output
  3. Look for: seq scans on large tables, large estimate-to-actual discrepancies, high loop counts
  4. Apply a targeted fix: add an index, rewrite the filter, run ANALYZE, restructure a join
  5. Rerun EXPLAIN ANALYZE and verify the plan improved

Performance tuning is detective work. The plan is your evidence. Work from evidence, not guesswork.

PreviousStored Procedures and Functions