PostgreSQL Performance Cheat Sheet

EXPLAIN SELECT * FROM orders WHERE customer_id = 42;

# Show the planner's chosen execution plan.

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

# Run the query and show actual timing and buffer usage.

EXPLAIN (ANALYZE, VERBOSE, BUFFERS, SETTINGS) SELECT * FROM orders WHERE customer_id = 42;

# Include more detail about output columns and active settings.

EXPLAIN (ANALYZE, FORMAT JSON) SELECT * FROM orders;

# Return the plan as machine-readable JSON.

LOAD 'auto_explain';
SET auto_explain.log_min_duration = '200ms';
SET auto_explain.log_analyze = on;

# Log slow query plans automatically in the server logs.

VACUUM public.users;

# Reclaim dead tuples and maintain visibility data.

VACUUM (ANALYZE) public.users;

# Vacuum and refresh planner stats in one command.

VACUUM (FULL, ANALYZE) public.users;

# Rewrite a bloated table more aggressively.

REINDEX TABLE public.users;

# Rebuild all indexes on a table.

ANALYZE VERBOSE public.users;

# Show extra detail while updating planner statistics.

SHOW autovacuum;

# Check whether autovacuum is enabled.

SHOW work_mem;

# Inspect a setting that affects sort and hash memory usage.

SET LOCAL work_mem = '256MB';

# Increase work_mem for the current transaction only.

SHOW effective_cache_size;

# Inspect a planner estimate of OS cache availability.

SHOW random_page_cost;

# Inspect a cost parameter that influences index-vs-seqscan decisions.

SET enable_seqscan = off;

# Temporarily discourage sequential scans while investigating plans.

SELECT schemaname, tablename, attname, n_distinct, most_common_vals
FROM pg_stats
WHERE tablename = 'orders';

# Inspect statistics the planner uses.

SELECT *
FROM orders
WHERE (created_at, order_id) < ('2026-03-01', 1000)
ORDER BY created_at DESC, order_id DESC
LIMIT 50;

# Prefer keyset pagination over deep OFFSET scans for large result sets.

SELECT order_id, customer_id, total_amount FROM orders WHERE status = 'open';

# Avoid fetching unnecessary columns, especially wide rows.

CREATE INDEX idx_orders_customer_created_inc
ON orders (customer_id, created_at DESC)
INCLUDE (status, total_amount);

# Use included columns to support index-only reads for certain queries.

UPDATE orders
SET archived_at = now()
WHERE order_id IN (
  SELECT order_id FROM orders WHERE created_at < now() - interval '1 year' LIMIT 10000
);

# Chunk large writes into batches to reduce contention and WAL spikes.

DELETE FROM audit_log
WHERE ctid IN (
  SELECT ctid FROM audit_log WHERE created_at < now() - interval '180 days' LIMIT 10000
);

# Delete in chunks to avoid huge transactions.

CREATE TABLE metrics (
  ts timestamptz NOT NULL,
  device_id bigint NOT NULL,
  value numeric NOT NULL
) PARTITION BY RANGE (ts);

# Partition large time-series tables by range.

CREATE TABLE metrics_2026_03 PARTITION OF metrics
FOR VALUES FROM ('2026-03-01') TO ('2026-04-01');

# Add a partition for a date range.