PostgreSQL In-Depth
Module P-8·20 min read

EXPLAIN ANALYZE for practitioners, key config parameters, N+1 queries, PgBouncer basics, and slow query logging.

Written by Jatin Jain Saraf · Architect of SupraScan

P-8 — Performance Tuning for Application Engineers

There is a version of performance tuning that belongs to DBAs: they adjust shared_buffers, tune checkpoint_completion_target, read kernel parameters, and operate at the infrastructure level. That work matters at scale.

There is another version that belongs to application engineers — and it produces far larger wins in far less time. This module covers the second kind: reading EXPLAIN ANALYZE output confidently, eliminating N+1 queries, understanding the configuration knobs that actually matter for applications, and using PgBouncer correctly. These are the skills that turn a slow application into a fast one without needing infrastructure access.

Reading EXPLAIN ANALYZE

EXPLAIN shows the query plan PostgreSQL chose. EXPLAIN ANALYZE executes the query and shows both the plan and the actual runtime measurements. Always use ANALYZE when debugging real performance — the plan alone is often misleading.

EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT o.id, o.total, u.email
FROM orders o
JOIN users u ON u.id = o.user_id
WHERE o.status = 'pending'
ORDER BY o.created_at DESC
LIMIT 20;

Anatomy of an EXPLAIN Output

Limit  (cost=1234.56..1234.61 rows=20 width=48) (actual time=45.123..45.130 rows=20 loops=1)
  ->  Sort  (cost=1234.56..1259.56 rows=10000 width=48) (actual time=45.120..45.125 rows=20 loops=1)
        Sort Key: o.created_at DESC
        Sort Method: top-N heapsort  Memory: 27kB
        ->  Hash Join  (cost=450.00..900.00 rows=10000 width=48) (actual time=12.345..40.234 rows=9876 loops=1)
              Hash Cond: (o.user_id = u.id)
              ->  Seq Scan on orders o  (cost=0.00..350.00 rows=10000 width=32) (actual time=0.012..15.234 rows=9876 loops=1)
                    Filter: ((status)::text = 'pending'::text)
                    Rows Removed by Filter: 90124
              ->  Hash  (cost=200.00..200.00 rows=20000 width=24) (actual time=8.456..8.456 rows=20000 loops=1)
                    Buckets: 32768  Batches: 1  Memory Usage: 1234kB
                    ->  Seq Scan on users u  (cost=0.00..200.00 rows=20000 width=24) (actual time=0.008..5.678 rows=20000 loops=1)
Planning Time: 0.456 ms
Execution Time: 45.234 ms

Key things to read:

cost=X..Y — estimated cost units. The first number is startup cost (cost before first row). The second is total cost. These are planner estimates, not milliseconds.

actual time=X..Y — real milliseconds. First number: time to first row. Second: total time. This is what you actually care about.

rows=N (estimated) vs rows=N (actual) — if these diverge significantly (10x or more), the planner has bad statistics. Run ANALYZE table_name to refresh them.

loops=N — how many times this node executed. If a nested loop runs 10,000 times, multiply the actual time by loops to get true total time.

Rows Removed by Filter — rows that were scanned and discarded. High numbers here mean a sequential scan is doing unnecessary work that an index could avoid.

The Most Important Nodes to Recognise

Seq Scan — full table scan. Not always bad (small tables, high selectivity filters), but suspicious on large tables with a filter.

Index Scan — uses a B-tree index to find rows. Good.

Index Only Scan — all needed data is in the index itself, no heap fetch. Best case.

Bitmap Index Scan + Bitmap Heap Scan — fetches a set of matching row locations via index, then reads those pages. Good for queries returning many rows from an index.

Nested Loop — for each row in the outer relation, look up matching rows in the inner relation. Fine when the outer set is small. Catastrophic when the outer set is large (N+1 pattern).

Hash Join — build a hash table from the smaller relation, probe it with the larger. Good for large joins on equality conditions.

Merge Join — sort both sides and merge. Good when both inputs are already sorted.

The BUFFERS Option

EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM orders WHERE user_id = 42;

Adding BUFFERS shows cache hits and misses:

Buffers: shared hit=12 read=3
  • hit — pages served from PostgreSQL's shared buffer cache (fast, no disk I/O)
  • read — pages read from disk (slow, or from OS page cache)

A query with many read pages on a hot table suggests shared_buffers is too small or the working set doesn't fit in memory.

Identifying Missing Indexes

The most common fix for slow queries is a missing index. Signs to look for in EXPLAIN ANALYZE:

  1. Seq Scan on a large table with Rows Removed by Filter >> actual rows returned
  2. actual time on a Seq Scan node is large
  3. The filter column isn't indexed
-- This query is slow: status is unindexed, table has 500k rows
EXPLAIN ANALYZE
SELECT * FROM orders WHERE status = 'pending';
 
-- Seq Scan on orders (cost=0.00..12500.00 rows=1000 width=128)
--                    (actual time=0.012..234.567 rows=987 loops=1)
--   Filter: (status = 'pending')
--   Rows Removed by Filter: 499013
 
-- Fix: add an index
CREATE INDEX CONCURRENTLY idx_orders_status ON orders (status);
 
-- Now: Index Scan using idx_orders_status on orders
--      (actual time=0.034..1.234 rows=987 loops=1)

CREATE INDEX CONCURRENTLY builds the index without locking the table — always use it in production.

The N+1 Query Problem

The N+1 problem is the most common performance killer in production applications. It happens when you execute one query to fetch N parent records, then execute one query per record to fetch related data — producing N+1 total queries.

// Bad: N+1 in application code
const orders = await db.query('SELECT id, user_id FROM orders LIMIT 100');
// 100 queries follow:
for (const order of orders) {
  const user = await db.query('SELECT email FROM users WHERE id = $1', [order.user_id]);
  order.user = user;
}

At 100 orders this is noticeable. At 1000 orders this is a page timeout.

Fix 1: JOIN in SQL

SELECT o.id, o.total, u.email
FROM orders o
JOIN users u ON u.id = o.user_id
LIMIT 100;

One query. The database does the join efficiently using indexes.

Fix 2: IN clause batch fetch

// Better: two queries total
const orders = await db.query('SELECT id, user_id FROM orders LIMIT 100');
const userIds = orders.map(o => o.user_id);
const users = await db.query('SELECT id, email FROM users WHERE id = ANY($1)', [userIds]);
// Join in application memory

Fix 3: ORM eager loading

In ORMs, N+1 manifests through lazy loading. Most ORMs have an "eager load" or "include" option:

// Prisma
const orders = await prisma.order.findMany({
  take: 100,
  include: { user: true }  // single JOIN query, not N queries
});

How to Detect N+1 in Production

Enable log_min_duration_statement to catch slow queries. But for N+1, individual queries may each be fast — the problem is volume. Use a query counter middleware or APM tool (Datadog, New Relic) that shows query count per request. Any request making 50+ identical queries is almost certainly N+1.

-- In development: log ALL queries to spot N+1
ALTER SYSTEM SET log_min_duration_statement = 0;
SELECT pg_reload_conf();
 
-- In production: log queries slower than 100ms
ALTER SYSTEM SET log_min_duration_statement = 100;
SELECT pg_reload_conf();

Sign in to keep reading

The rest of this module is free — sign in with Google to unlock it and track your progress.

Sign in & Register
PreviousModule P-7: Full-Text SearchNext Module P-9: External Services, Caching Layers, and Deployment

Discussion

0

Join the discussion

Loading comments...
PortfolioBlog

© 2026 Jatin Jain Saraf (JJS). All rights reserved.