PostgreSQL Performance Optimization

Slow SQL shows up in the plan first, not in postgresql.conf. Follow PostgreSQL: run EXPLAIN (ANALYZE, BUFFERS), see Seq Scan versus Index Scan, then choose an index, a rewrite, or a knob.

Missing indexes, a function wrapped around a column, stale statistics, autovacuum falling behind, cost constants left on the spinning-disk default — any of those send the same query through a full table. Read the plan before editing config.

Choose / skip

Read the plan

EXPLAIN estimates. EXPLAIN ANALYZE executes the query and prints actual rows and time per node. Current docs enable BUFFERS whenever ANALYZE is on; spell it out anyway:

EXPLAIN (ANALYZE, BUFFERS)
SELECT *
FROM orders
WHERE status = 'open'
  AND created_at >= DATE '2026-01-01';

Swap in real relation names. ANALYZE has side effects: UPDATE / DELETE change rows. Wrap those in BEGIN … ROLLBACK when the goal is the plan, not the write.

Ignore millisecond figures copied from other machines. Three fields matter:

1. Scan type: Seq Scan, Index Scan, Bitmap Index Scan + Bitmap Heap Scan, Index Only Scan.
2. Estimated rows versus actual. An order-of-magnitude miss is an ANALYZE problem (or a statistics target problem), not an index problem.
3. Buffers: shared hit versus shared read. Heavy read means the working set missed cache, or the scan is simply too wide.

Seq Scan is allowed. Small tables, predicates that match most rows, or a planner that believes random index I/O is more expensive than a sequential heap read will pick it. Costs are relative: default seq_page_cost = 1.0, random_page_cost = 4.0. On a Seq Scan, ask whether the row estimate is junk, then whether the predicate even landed in Index Cond.

Filter is not Index Cond. Filter means the node already fetched the row (or scanned the heap) and then rejected it. Index Cond is the index actually narrowing the range.

Index types, column order, INCLUDE, partial

Default CREATE INDEX is btree. It covers =, <, >, BETWEEN, IN, and ordered scans. Do not switch types for a plain equality column.

• btree: equality, range, sort. email = $1 and created_at >= $1 belong here.
• GIN: inverted. Array containment, jsonb containment, full-text. Writes cost more. Ordinary scalar equality does not want GIN.
• GiST: geometry, overlapping ranges, nearest-neighbor ORDER BY loc <-> point. Not a “stronger btree”.

A multicolumn btree is most efficient with equality on the leading columns and the range on the first column that is not equality. (status, created_at) matches WHERE status = 'open' AND created_at >= $1. A query that only has created_at >= $1 may scan most of the index. PostgreSQL 18 btree skip scan can probe later columns when the leading column has few distinct values. High cardinality on the leader, skip scan does not save the plan, and a Seq Scan can still win. Put columns in query order, not “importance” order.

Covering indexes use INCLUDE. Payload does not belong in the search key. The official pattern is in Index-Only Scans:

CREATE INDEX orders_status_created_cover
  ON orders (status, created_at)
  INCLUDE (user_id, total_cents);

INCLUDE columns are not searchable and do not participate in UNIQUE. Index Only Scan needs every referenced column in the index and a visibility map that is mostly all-visible. A hot heap still visits the table; INCLUDE then only bloats the index. Skip wide columns. GIN cannot do index-only scans.

Partial indexes put the common predicate in the index:

CREATE INDEX orders_open_created_idx
  ON orders (created_at)
  WHERE status = 'open';

The query WHERE must imply that predicate. status = 'open' AND created_at >= $1 can use it. Drop status, and the index is ineligible.

The index exists and the plan ignores it

This is the usual failure, not “no index”. \d lists the index; EXPLAIN still shows Seq Scan, or pg_stat_user_indexes.idx_scan stays 0.

-- failure: a function on the column, so btree cannot match
CREATE INDEX users_email_idx ON users (email);

EXPLAIN (ANALYZE, BUFFERS)
SELECT id, email
FROM users
WHERE lower(email) = '[email protected]';

Expect Seq Scan, condition in Filter, not Index Cond. Confirm with that EXPLAIN, then with usage counts:

SELECT
  schemaname,
  relname,
  indexrelname,
  idx_scan,
  pg_size_pretty(pg_relation_size(indexrelid)) AS index_size
FROM pg_stat_user_indexes
WHERE idx_scan = 0
ORDER BY pg_relation_size(indexrelid) DESC;

idx_scan = 0 only means unused since stats were reset. Brand-new indexes are 0. After that, a large unused index is the question.

Same shape, different causes:

• Type mismatch: WHERE id::text = $1 or a CAST on the column. Cast the constant, not the column.
• Leading wildcard: email LIKE '%example.com' cannot use btree from the left. LIKE 'ada%' can.
• High selectivity: the predicate matches most of the table; random heap fetches lose to a sequential read. The planner is saving I/O.
• Stale stats: estimated rows far from actual. ANALYZE the table; raise per-column statistics with ALTER TABLE … SET STATISTICS if the histogram is too coarse.
• Composite used backwards: index on (status, created_at), query only on created_at, and status is high cardinality.

Fixes stay narrow: an expression index ON users (lower(email)) for the function case; constants coerced on the right-hand side; equality columns leading the index. SET enable_seqscan = off is a debug switch to see whether a forced index is actually slower. It is not a production setting.

Bloat and autovacuum

UPDATE / DELETE do not return space to the OS. Dead tuples occupy heap and indexes until VACUUM. Plain VACUUM recycles space for later writes. VACUUM FULL rewrites the table under ACCESS EXCLUSIVE. Autovacuum never issues FULL.

The default trigger is roughly autovacuum_vacuum_threshold + autovacuum_vacuum_scale_factor * reltuples. scale_factor defaults to 0.2. A million-row table waits for on the order of 20% dead rows. Large tables bloat first, clean later. Knobs that actually move the needle:

• Per-table autovacuum_vacuum_scale_factor (0.02 on a hot table beats a cluster-wide “aggressive” copy).
• autovacuum_vacuum_cost_delay / autovacuum_vacuum_cost_limit: a large delay starves vacuum so it never catches writes. Throttling “to protect production” is how bloat then slows every query.
• autovacuum_max_workers: several large tables become eligible together and the default workers serialize. Raising workers means multiplying autovacuum_work_mem.
• log_autovacuum_min_duration: see how long vacuum ran and which pages it skipped before changing numbers.

Do not set autovacuum = off. That trades I/O today for wraparound protection later, and even insert-only large tables still need freeze vacuums.

Catalog views are enough to start; no extra extension:

SELECT
  relname,
  n_live_tup,
  n_dead_tup,
  last_autovacuum,
  last_autoanalyze,
  seq_scan,
  idx_scan
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC
LIMIT 20;

If n_dead_tup stays large versus n_live_tup and last_autovacuum is old, inspect that table’s storage parameters before raising shared_buffers for the whole cluster.

Three misleading memory / cost knobs

Blog posts ship a kit: shared_buffers = 25% of RAM, work_mem = 64MB, random_page_cost = 1.1. Upstream gives starting points and relative costs, not paste-ready production values. All three depend on RAM, how much of the working set sits in cache, and whether storage is local SSD, networked disk, or spinning rust.

shared_buffers. Resource consumption: on a dedicated box with ≥ 1GB RAM, 25% is a reasonable start; more than 40% is rarely better than leaving pages to the OS cache. This is the server’s own page cache, set at startup. Too small: hot pages reread from disk. Too large: the OS cache and work_mem shrink, and checkpoints need a larger max_wal_size to spread writes. The same percentage on a laptop and on a 128GB database host is not the same setting.

work_mem. Default 4MB. The cap is per sort or hash node, not per connection. One query with several sorts/hashes takes several allocations; hash nodes also multiply by hash_mem_multiplier (default 2.0). Concurrent sessions multiply again. 64MB with max_connections = 200 and overlapping complex queries is a recipe for paging. Raise it after EXPLAIN shows Sort Method: external merge Disk, or after pg_stat_statements.temp_blks_written is high — and prefer SET work_mem on that session before changing the cluster.

random_page_cost. Planner cost constants default to 4.0 relative to seq_page_cost = 1.0. Lowering it makes index scans look cheaper; raising it does the opposite. SSD shrinks the random-versus-sequential gap, so trying a 1.x value is common. A fully cached working set can justify setting them nearly equal. Magnetic disks, poor cache hit rates, or network-attached storage do not get the SSD number. Upstream is blunt: there is no well-defined method for ideal cost constants, and tuning them from a handful of experiments is risky. After a change, rerun the same EXPLAIN and see whether the plan actually moved from Seq to Index. Do not memorize 1.1.

effective_cache_size is a planner assumption about OS cache (default 4GB). It allocates nothing. Setting it to 75% of RAM does not reserve 75% of RAM.

Config fixes resource starvation. It does not fix a query that walks ten million rows to return twelve.

Cumulative time and lock waits

One EXPLAIN is one execution. Production time hides in statements that are cheap each call and expensive in aggregate. pg_stat_statements needs shared_preload_libraries, a restart, and CREATE EXTENSION pg_stat_statements in the database. Keep compute_query_id at auto or on.

SELECT
  left(query, 80) AS query,
  calls,
  round(total_exec_time::numeric, 1) AS total_ms,
  round(mean_exec_time::numeric, 1) AS mean_ms,
  rows,
  shared_blks_read,
  temp_blks_written
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 20;

Sort by total_exec_time, not only mean_exec_time. A query with modest mean time and thousands of calls per second outruns the rare monster. temp_blks_written feeds back into work_mem. High shared_blks_read with high calls is often a repeated Seq Scan.

Lock waits live in pg_stat_activity.wait_event_type / wait_event. Join blockers with pg_blocking_pids:

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

Long Lock / LWLock waits: look for idle-in-transaction sessions and explicit LOCK TABLE before adding indexes. Anti-wraparound autovacuum is not cancelled by ordinary conflicting locks; regular vacuum is. An idle-in-transaction session parks both vacuum and queries.

Fix the plan, then touch config. After config, rerun the same EXPLAIN (ANALYZE, BUFFERS). Do not swap in a new set of blog numbers.