PostgreSQL Performance Cheat Sheet

EXPLAIN SELECT * FROM orders WHERE customer_id = 42;

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

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

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

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

VACUUM public.users;

VACUUM (ANALYZE) public.users;

VACUUM (FULL, ANALYZE) public.users;

REINDEX TABLE public.users;

ANALYZE VERBOSE public.users;

SHOW autovacuum;

SHOW work_mem;

SET LOCAL work_mem = '256MB';

SHOW effective_cache_size;

SHOW random_page_cost;

SET enable_seqscan = off;

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

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

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

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

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

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

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

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