"SQL performance and database operations" cheat sheet

SELECT calls, round(mean_exec_time) AS mean_ms, round(total_exec_time) AS total_ms, left(query, 80)
FROM pg_stat_statements ORDER BY total_exec_time DESC LIMIT 10;

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

CREATE INDEX CONCURRENTLY orders_cust_status ON orders (customer_id, status);
SELECT relname, indexrelname, idx_scan FROM pg_stat_user_indexes ORDER BY idx_scan LIMIT 10;
ANALYZE orders;

SELECT pid, state, now()-xact_start AS tx_age, wait_event_type, left(query, 60)
FROM pg_stat_activity WHERE state <> 'idle' ORDER BY tx_age DESC NULLS LAST;

SELECT pid, pg_blocking_pids(pid) AS blocked_by FROM pg_stat_activity WHERE cardinality(pg_blocking_pids(pid)) > 0;
SELECT pg_cancel_backend(2214);      -- cancel query
SELECT pg_terminate_backend(2214);   -- end session

SELECT state, count(*) FROM pg_stat_activity GROUP BY state;
SHOW max_connections;

SELECT relname, n_live_tup, n_dead_tup, last_autovacuum FROM pg_stat_user_tables ORDER BY n_dead_tup DESC LIMIT 10;
VACUUM (VERBOSE, ANALYZE) orders;

SELECT now() - pg_last_xact_replay_timestamp() AS replica_lag;   -- on the replica
SELECT client_addr, state, sent_lsn, replay_lsn FROM pg_stat_replication;  -- on the primary