PostgreSQL Query Optimization: pg_stat_statements, EXPLAIN ANALYZE, Indexes and VACUUM

Systematic Performance Approach

1. Find slow queries (pg_stat_statements, logs)
2. Understand execution plan (EXPLAIN ANALYZE)
3. Fix: missing index, bad query, table bloat, config
4. Verify (re-run EXPLAIN ANALYZE, monitor)
5. Repeat

I’ve watched teams skip straight to step 3 — guessing at a fix, usually “let’s add an index” — without ever confirming step 1 or 2, and it’s a genuinely wasteful habit: an index added to a column that was never actually the bottleneck just adds write overhead (every INSERT/UPDATE now has to maintain that index too) with zero read benefit, and the real slow query is still slow. The order in this list is load-bearing, not just tidy structure — pg_stat_statements and EXPLAIN ANALYZE exist specifically to replace guessing with measurement, and the entire rest of this guide is organized around “here’s what a specific EXPLAIN red flag means and what fixes it,” which only works if you’ve actually looked at the plan first.

---

Finding Slow Queries

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

-- Top 10 slowest queries by total time
SELECT
  round(total_exec_time::numeric, 2) AS total_ms,
  calls,
  round(mean_exec_time::numeric, 2) AS avg_ms,
  left(query, 200) AS query
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;

-- High average time (optimization candidates)
SELECT
  round(mean_exec_time::numeric, 2) AS avg_ms,
  calls,
  left(query, 200) AS query
FROM pg_stat_statements
WHERE calls > 100
ORDER BY mean_exec_time DESC
LIMIT 20;

# postgresql.conf: log queries over 1 second
log_min_duration_statement = 1000
log_checkpoints = on
track_io_timing = on

total_exec_time versus mean_exec_time are asking genuinely different questions, and mixing them up sends you chasing the wrong query — total time (the first query) surfaces whatever’s consuming the most aggregate database time, which is often a fast query that just runs an enormous number of times, while mean time (the second query, filtered to calls > 100 specifically to exclude one-off queries whose average is meaningless on a tiny sample) surfaces queries that are individually slow every time they run. A query with a 2ms average called a million times can dominate total time despite never looking “slow” in isolation; a report generator that runs once a day at 8 seconds shows up prominently in mean time but barely registers in total time. Both are real problems worth fixing, but they’re different problems with different fixes — total-time offenders often need caching or reducing call volume, mean-time offenders usually need the query itself rewritten or indexed.

---

EXPLAIN ANALYZE

EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT u.id, u.email, COUNT(o.id) AS order_count
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
WHERE u.created_at > '2026-01-01'
GROUP BY u.id, u.email
ORDER BY order_count DESC
LIMIT 20;

Red flags in output:

• Seq Scan on large tables → needs an index
• actual rows >> estimated rows → stale statistics, run ANALYZE
• Sort Method: external merge → increase work_mem
• Buffers: hit=0, read=N → I/O bound, increase shared_buffers

The ANALYZE flag (distinct from the ANALYZE statement used elsewhere in this guide to refresh table statistics — an unfortunately overloaded name worth not confusing) is what turns a plan from a prediction into a measurement: without it, EXPLAIN alone only shows the planner’s estimated costs and row counts, based purely on statistics, without actually running the query at all. EXPLAIN ANALYZE genuinely executes the query and reports real elapsed time and real row counts alongside the estimates — which is exactly what makes the “actual rows >> estimated rows” red flag detectable in the first place, since there’d be no “actual” to compare against otherwise. Worth a caution that’s easy to forget: EXPLAIN ANALYZE on a query with side effects (an UPDATE, DELETE, or INSERT) actually performs those writes — wrapping it in a transaction you roll back (BEGIN; EXPLAIN ANALYZE ...; ROLLBACK;) is the safe way to profile a mutating query without actually committing its effects.

---

Index Strategies

B-tree indexes

-- Single column
CREATE INDEX CONCURRENTLY idx_orders_user_id ON orders(user_id);
CREATE INDEX CONCURRENTLY idx_users_email ON users(email);

-- Composite (most selective column first)
CREATE INDEX CONCURRENTLY idx_users_status_created
  ON users(status, created_at);

-- Covering index (index-only scan — no table access)
CREATE INDEX CONCURRENTLY idx_users_status_cover
  ON users(status) INCLUDE (id, email);

CONCURRENTLY builds the index without locking the table — always use in production. I’ve seen a plain CREATE INDEX (without it) genuinely take a production table offline for the duration of the build, because the default behavior takes a lock that blocks writes — and on a table with millions of rows, that build can run for minutes, during which every INSERT/UPDATE/DELETE against that table simply queues up and waits. CONCURRENTLY avoids that by building the index in multiple passes that tolerate concurrent writes, at the cost of taking noticeably longer to complete and — worth knowing before relying on it — it can fail partway through and leave behind an invalid, unusable index that has to be manually dropped and retried, which is a real failure mode worth checking for (\d tablename shows an INVALID marker) rather than assuming success.

The composite-index comment (“most selective column first”) is a real rule of thumb worth understanding rather than memorizing — a composite index’s usefulness for filtering depends on how much each leading column narrows down the result set before the next column even comes into play, so leading with a column that has few distinct values (like a boolean status) wastes the index’s ability to narrow the search compared to leading with a high-cardinality column. It’s not an absolute rule (the actual query patterns the index needs to serve matter more than cardinality alone), but it’s the right starting heuristic when the choice isn’t otherwise obvious. The covering index example is worth understanding by contrast with a normal index: INCLUDE (id, email) doesn’t make those columns searchable the way the indexed status column is, it just tags along extra data so a query selecting only status, id, email can be answered entirely from the index itself — an “index-only scan” — without a second trip to the actual table’s heap to fetch those columns, which is a real, measurable speedup for read-heavy queries that only need a handful of columns.

Partial indexes

-- Only index active users
CREATE INDEX CONCURRENTLY idx_users_email_active
  ON users(email) WHERE is_active = true;

-- Only index pending jobs
CREATE INDEX CONCURRENTLY idx_jobs_pending
  ON jobs(created_at) WHERE status = 'pending';

Partial indexes are worth reaching for specifically when a query’s WHERE clause always filters to a small, predictable slice of a much larger table — the is_active = true example only indexes the active-user subset, which stays small and fast to maintain even as the overall users table (including every inactive/deleted account ever created) grows large, and PostgreSQL only considers the index for queries whose WHERE clause is provably a subset of the partial index’s own condition. This is a genuinely underused technique in practice — a full index on a column where 95% of queries only care about a small subset of rows wastes space and write overhead maintaining entries that essentially never get used by that common query pattern, and a well-chosen partial index fixes exactly that waste.

JSONB and full-text

-- GIN index for JSONB containment queries
CREATE INDEX CONCURRENTLY idx_products_meta ON products USING GIN(metadata);
SELECT * FROM products WHERE metadata @> '{"category": "electronics"}';

-- Full-text search
ALTER TABLE articles ADD COLUMN search_vector tsvector
  GENERATED ALWAYS AS (to_tsvector('english', title || ' ' || body)) STORED;
CREATE INDEX idx_articles_fts ON articles USING GIN(search_vector);
SELECT * FROM articles WHERE search_vector @@ plainto_tsquery('english', 'postgres index');

GIN (Generalized Inverted Index) is a fundamentally different index structure from the B-trees covered above, worth understanding why it’s needed at all: a B-tree indexes a single, comparable scalar value per row efficiently, but JSONB containment (@>) and full-text search both need to match against multiple values extracted from within a single row’s data — every key in a JSONB document, or every lexeme (normalized word) in a text document — which a B-tree structurally can’t represent. GIN inverts that relationship, indexing each individual extracted value pointing back to the rows containing it, which is exactly the shape both containment queries and text search need. The generated search_vector column (GENERATED ALWAYS AS (...) STORED) is worth knowing as a genuinely nice piece of ergonomics here — it automatically stays in sync with title/body on every write, so there’s no separate trigger or application-code step needed to keep the search index’s source data current, unlike older PostgreSQL full-text setups that required maintaining that sync manually.

---

Find Unused and Missing Indexes

-- Unused indexes (candidates for removal)
SELECT
  indexname,
  pg_size_pretty(pg_relation_size(indexrelid)) AS index_size,
  idx_scan AS scans
FROM pg_stat_user_indexes
WHERE idx_scan = 0
  AND indexrelname NOT LIKE '%_pkey'
ORDER BY pg_relation_size(indexrelid) DESC;

-- Tables with too many sequential scans (missing index)
SELECT
  relname AS table,
  seq_scan,
  idx_scan,
  pg_size_pretty(pg_total_relation_size(relid)) AS size
FROM pg_stat_user_tables
WHERE seq_scan > 100
  AND pg_total_relation_size(relid) > 10 * 1024 * 1024
ORDER BY seq_scan DESC
LIMIT 20;

Worth treating the “unused indexes” query’s results as candidates for investigation, not automatic deletion — idx_scan = 0 genuinely means an index has never been used since statistics were last reset (server restart, or an explicit reset), which can just as easily mean “this index backs a rarely-triggered but important query, like a monthly report, that simply hasn’t run recently” as it can mean “this index is genuinely dead weight.” I’ve seen a well-intentioned index cleanup drop something that turned out to matter for an infrequent but real workload — checking pg_stat_database.stats_reset for how long the current stats window actually covers, and understanding what a candidate index was originally added for, is worth the extra step before dropping anything a “0 scans” query flags. The missing-index query’s threshold (seq_scan > 100 and a table over 10MB) exists to filter out small tables where a sequential scan genuinely is faster than an index lookup — for a table with only a few hundred rows, PostgreSQL’s planner correctly prefers a seq scan over the overhead of an index traversal, and adding one there would be pure waste.

---

Table Bloat and VACUUM

PostgreSQL leaves dead rows after UPDATE/DELETE. VACUUM reclaims them. This is worth understanding at the mechanism level, not just as a maintenance chore: PostgreSQL’s MVCC (multi-version concurrency control) model means an UPDATE doesn’t modify a row in place, it writes a brand-new row version and marks the old one as dead — which is what lets concurrent transactions each see a consistent snapshot of the data without blocking each other, but it also means every UPDATE/DELETE leaves behind a dead row version nobody needs anymore. Without VACUUM ever reclaiming that space, a table with heavy update traffic can bloat to many times its logically-necessary size — I’ve seen a table that “should” have been a few hundred MB balloon to several GB purely from unvacuumed bloat, which slows every sequential scan and every index scan touching it (more pages to read, even though most of the query’s actual live data hasn’t grown).

-- Check bloat
SELECT
  relname AS table_name,
  n_dead_tup AS dead_rows,
  n_live_tup AS live_rows,
  round(n_dead_tup::numeric / NULLIF(n_live_tup + n_dead_tup, 0) * 100, 1) AS dead_pct,
  last_autovacuum
FROM pg_stat_user_tables
WHERE n_dead_tup > 10000
ORDER BY n_dead_tup DESC;

-- Manual vacuum (safe, non-locking)
VACUUM ANALYZE orders;

-- VACUUM FULL (rewrites entire table — locks it)
-- Only during maintenance windows
VACUUM FULL orders;

The distinction between these two forms of VACUUM matters enough to get genuinely wrong in production: plain VACUUM marks dead space as reusable for future writes but doesn’t actually shrink the table’s on-disk file size or return space to the OS — it’s non-locking and safe to run anytime, including automatically via autovacuum. VACUUM FULL physically rewrites the entire table into a new, compact file and does return space to the OS, but it takes an ACCESS EXCLUSIVE lock for the whole operation — every read and write against that table blocks until it finishes, which on a large table can mean real, visible downtime. VACUUM FULL is genuinely the wrong tool reached for reflexively “to be thorough” — regular VACUUM (ideally via well-tuned autovacuum settings, covered next) is what should be running continuously; VACUUM FULL belongs in an explicit, scheduled maintenance window after a case where bloat has already gotten severe, not as routine maintenance.

# postgresql.conf: tune autovacuum for high-write tables
autovacuum_vacuum_scale_factor = 0.01
autovacuum_analyze_scale_factor = 0.005

The default autovacuum_vacuum_scale_factor (0.2, meaning autovacuum waits until 20% of a table’s rows are dead before triggering) is calibrated for a generic, moderate-write workload — on a genuinely high-write table, waiting for 20% dead rows on a multi-million-row table means autovacuum only kicks in after real, substantial bloat has already accumulated, and the resulting vacuum run itself becomes a heavier, more disruptive operation than if it ran more frequently on a smaller amount of accumulated dead space. Lowering the scale factor specifically for high-churn tables (as shown here, down to 1%) trades more frequent, smaller vacuum runs for less peak bloat and less disruptive individual vacuum operations — a tuning change worth making per-table (ALTER TABLE ... SET (autovacuum_vacuum_scale_factor = 0.01)) rather than globally, since a low-write table doesn’t need or benefit from the more aggressive schedule.

---

Connection Pooling (PgBouncer)

PostgreSQL creates one OS process per connection. At 500+ connections, this overloads the server.

# pgbouncer.ini
[databases]
myapp = host=db.example.com dbname=myapp

[pgbouncer]
pool_mode = transaction     # recommended
max_client_conn = 1000      # app-facing connections
default_pool_size = 20      # actual PostgreSQL connections
server_idle_timeout = 600

App (1000 connections) → PgBouncer → PostgreSQL (20 connections)

pool_mode = transaction is the setting worth understanding deeply before adopting, since it has a real behavioral consequence beyond just “pooling mode” — in transaction mode, a PostgreSQL connection is only bound to a client for the duration of a single transaction, then immediately returned to the pool for another client to use, which is what enables the dramatic connection multiplexing this diagram shows (1000 app-facing connections served by just 20 real ones). The tradeoff: anything relying on session-level state persisting between transactions on the same connection — session-scoped temp tables, SET variables meant to persist, advisory locks held across transactions, LISTEN/NOTIFY — breaks under transaction pooling, because the next statement might land on a completely different underlying connection than the previous one. This is a genuinely common gotcha migrating an existing app to PgBouncer: code that worked fine against a direct PostgreSQL connection can behave incorrectly under transaction-mode pooling if it assumed session continuity, which is worth auditing for before flipping this on in production, not after.

---

postgresql.conf Key Settings

# Memory
shared_buffers = 4GB           # 25% of RAM
effective_cache_size = 12GB    # 75% of RAM
work_mem = 64MB                # per sort/hash (multiply by max connections)
maintenance_work_mem = 512MB   # for VACUUM, CREATE INDEX

# WAL
checkpoint_completion_target = 0.9
max_wal_size = 4GB

# Parallelism
max_parallel_workers_per_gather = 4

# Logging
log_min_duration_statement = 1000
log_lock_waits = on
log_temp_files = 0             # log all temp files (work_mem tuning signal)

work_mem’s “multiply by max connections” comment is the single most consequential caveat on this whole list, and it’s worth spelling out concretely why: work_mem isn’t a global memory pool, it’s the amount each individual sort/hash operation is allowed to use, and a complex query can use several of these simultaneously (one per sort, one per hash join), and every concurrent connection can be running such a query at the same time. Setting work_mem = 64MB with max_connections = 200 doesn’t reserve 64MB total — under worst-case concurrent load with multiple operations per query, actual memory usage can reach many times work_mem × max_connections, which is exactly how an aggressively-tuned work_mem setting that looked fine in isolated testing ends up triggering out-of-memory kills in production once real concurrent load hits. This is precisely the kind of setting worth tuning conservatively and validating under genuine concurrent load, not just a single slow-query test in isolation.

---

Common Patterns and Fixes

-- ❌ OFFSET pagination: scans 100,000 rows then discards them
SELECT * FROM posts ORDER BY id DESC LIMIT 20 OFFSET 100000;

-- ✅ Keyset pagination: O(log n) via index
SELECT * FROM posts
WHERE id < :last_seen_id
ORDER BY id DESC LIMIT 20;
-- OFFSET pagination has to walk through and discard every one of the
-- first 100,000 rows before it can return the 20 you actually asked for
-- — the deeper the page, the more wasted work, and that cost grows
-- linearly with offset regardless of how well-indexed the table is.
-- Keyset pagination instead uses the last-seen id as a starting point
-- for an index lookup that jumps directly to the right position — O(log
-- n) via the index's B-tree structure rather than a linear scan-and-
-- discard, which is why it stays fast on page 5,000 the same way it's
-- fast on page 5. The real-world cost: OFFSET pagination doesn't jump to
-- a problem number, it degrades quietly as users page deeper, which
-- means it's easy to ship, pass every test against a small dataset, and
-- only become visibly slow once a table has grown large and someone
-- actually pages deep into it.

-- ❌ Exact COUNT(*) on large table: full scan
SELECT COUNT(*) FROM events;

-- ✅ Approximate count (instant)
SELECT reltuples::bigint FROM pg_class WHERE relname = 'events';
-- Exact COUNT(*) is slow for the same MVCC reason VACUUM exists: because
-- different transactions can see different, valid snapshots of the same
-- table simultaneously, PostgreSQL can't just keep a running total
-- somewhere — it has to actually check visibility for every single row
-- against the current transaction's snapshot, which means a full scan
-- regardless of indexes. reltuples is instead a statistical estimate
-- ANALYZE maintains (the same statistics EXPLAIN's planner relies on),
-- accurate enough for "about how many rows" UI displays (an infinite
-- scroll's rough total, a dashboard metric) but not for anything needing
-- an exact, authoritative count — a billing calculation or an audit
-- report still needs the real, slower COUNT(*).

-- ❌ Function on indexed column (can't use index)
SELECT * FROM users WHERE lower(email) = '[email protected]';

-- ✅ Expression index
CREATE INDEX idx_users_email_lower ON users(lower(email));
SELECT * FROM users WHERE lower(email) = '[email protected]';

The “function on indexed column” trap is worth understanding at a mechanical level, since it explains why a seemingly-indexed lookup can still be slow: a plain CREATE INDEX ... ON users(email) stores the raw email values in sorted order, but WHERE lower(email) = ... is asking the planner to match against a transformed value that doesn’t appear anywhere in that index — the planner can’t algebraically know that lower(email) for a given row would match without computing it, so it falls back to a sequential scan, computing lower() on every row to check. An expression index sidesteps this by storing the already-transformed values (lower(email), computed once at index-build/insert time) as what actually gets indexed and searched — the query’s WHERE lower(email) = ... then matches the index’s own stored expression exactly, letting the planner use it normally. This same principle generalizes: any function wrapped around an indexed column in a WHERE clause needs a matching expression index, or it silently defeats the index that already exists — a subtle, easy-to-miss gap between “I have an index on this column” and “this specific query can actually use it.”

---

Monitoring Queries

-- Long-running queries
SELECT pid, now() - query_start AS duration, state, left(query, 100) AS query
FROM pg_stat_activity
WHERE state != 'idle' AND query_start < now() - interval '5 seconds'
ORDER BY duration DESC;

-- Kill a query
SELECT pg_terminate_backend(pid);
-- pg_terminate_backend is a last resort worth treating with real
-- caution, not a routine cleanup command — it forcibly kills the
-- connection, which aborts whatever transaction was in progress
-- (rolling it back) and can leave application code that was waiting on
-- that connection with a confusing connection-reset error rather than a
-- clean failure. pg_cancel_backend is the gentler alternative worth
-- reaching for first — it cancels the currently-running query specifically
-- but leaves the connection and any already-open transaction intact,
-- which is the right first move for "this one query is stuck" versus
-- terminate_backend's "kill this connection entirely," reserved for a
-- connection that's genuinely unresponsive to cancellation.

-- Lock waits
SELECT
  blocked.pid AS blocked_pid,
  blocking.pid AS blocking_pid,
  left(blocked.query, 80) AS blocked_query,
  left(blocking.query, 80) AS blocking_query
FROM pg_stat_activity blocked
JOIN pg_stat_activity blocking ON blocking.pid = ANY(pg_blocking_pids(blocked.pid));

-- Buffer cache hit ratio (should be > 99%)
SELECT
  round(sum(heap_blks_hit)::numeric /
    (sum(heap_blks_hit) + sum(heap_blks_read)) * 100, 2) AS cache_hit_pct
FROM pg_statio_user_tables;

A cache hit ratio consistently below 99% is one of the more reliable single signals that shared_buffers genuinely needs increasing (referenced in the Key Takeaways table below), and it’s worth understanding what it’s actually measuring: every heap block PostgreSQL needs either comes from its own shared memory cache (heap_blks_hit) or has to be read from disk/the OS’s own page cache (heap_blks_read) — a low ratio means a meaningful fraction of your working data set genuinely doesn’t fit in shared_buffers, so routine queries are paying real disk I/O latency on data that should ideally be sitting in memory already. It’s worth checking this ratio after addressing indexing and query issues, not before — a badly-indexed query scanning far more data than necessary will show up as excess I/O and drag this number down regardless of how much memory you throw at shared_buffers, which is exactly why this guide’s opening systematic-approach list puts indexing and query fixes ahead of configuration tuning.

---

Symptom-to-fix lookup

Problem	Fix
Slow SELECT	Index on WHERE/JOIN/ORDER BY columns
High write latency	Remove unused indexes
Too many connections	PgBouncer connection pooling
Table bloat	Tune autovacuum or VACUUM ANALYZE
Slow pagination	Keyset instead of OFFSET
Low cache hit ratio	Increase shared_buffers
Sort spills to disk	Increase work_mem
Planner makes bad plans	Run ANALYZE, update statistics

Work down this table from the top, not the bottom. The configuration rows are tempting because they are one-line changes, but a missing index or a query that fetches far more rows than it returns will not be fixed by a larger shared_buffers, and raising work_mem globally multiplies across every sort in every connection. Start from pg_stat_statements to find the queries that cost the most in total, and confirm each change with EXPLAIN (ANALYZE, BUFFERS).

---

Related Articles

• PostgreSQL vs MySQL: Data Types, JSON, Locking, Replication and When to Use Each
• MySQL EXPLAIN Query Optimization

---