A Benchmark You Can Trust Twice

-- ── The three layers that warm up under you ─────────────────────
--  1. shared_buffers   — Postgres's own cache. Visible in BUFFERS.
--  2. OS page cache    — invisible from inside Postgres. A "shared read"
--                        may still be a RAM read at the OS level.
--  3. Plan cache       — prepared statements reuse a plan after 5 runs.
--
-- You cannot clear layer 2 from SQL. Restarting the container clears
-- 1 and 3; clearing 2 needs the host. So the practical approach is not
-- to measure cold, but to measure CONSISTENTLY WARM.

-- ── Warm, then measure. Discard the first N runs. ────────────────
\timing on

-- Warm-up: run 5 times, ignore every result.
SELECT count(*) FROM orders WHERE status = 'refunded';  -- x5

-- Measure: 20 runs, record all durations, report the median and p95.
-- pgbench does this properly; a hand loop is for one-off checks.

-- ── pgbench: the right tool ──────────────────────────────────────
/*
  # 1. Put the query in a file with a randomised parameter, so you are
  #    not measuring the same cached row every time.
  cat > q.sql <<'EOF'
  set uid random(1, 200000)
  SELECT * FROM orders WHERE user_id = :uid AND status = 'refunded';
  EOF

  # 2. Warm the cache: throwaway run.
  pgbench -n -f q.sql -c 4 -T 30 qp > /dev/null

  # 3. Measure, with per-transaction latencies for real percentiles.
  pgbench -n -f q.sql -c 4 -T 60 -P 5 --latency-limit=5000           --log --log-prefix=before qp

  # 4. Make the change (add the index), then repeat steps 2 and 3
  #    with --log-prefix=after.

  # 5. Compare percentiles from the log files, not the summary line:
  awk '{print $3}' before.* | sort -n |     awk '{a[NR]=$1} END {print "p50", a[int(NR*0.50)], "p95", a[int(NR*0.95)]}'
*/

-- ── Reading buffers to prove the cache is warm ───────────────────
EXPLAIN (ANALYZE, BUFFERS, COSTS OFF)
SELECT count(*) FROM orders WHERE status = 'refunded';

--  Buffers: shared hit=31210 read=0        <- fully warm, comparable
--  Buffers: shared hit=1024  read=30186    <- cold, NOT comparable
--
-- If read is non-zero in one arm and zero in the other, you measured
-- cache state, not your change. Re-run until both are warm.

-- ── The parameter trap ───────────────────────────────────────────
-- user_id = 8812 has 4,000 orders. user_id = 199,404 has 1.
-- Benchmarking with one hard-coded id measures that id's slice of the
-- distribution, not your workload. Always randomise the parameter over
-- the real range, as the pgbench set above does.

-- ── Exclude or include background work consistently ─────────────
SELECT relname, last_autovacuum, last_autoanalyze, n_dead_tup
FROM pg_stat_user_tables WHERE relname IN ('orders','events','users');
-- An autovacuum that fires during one arm and not the other will move
-- your numbers more than most index changes. Check before and after.