#!/usr/bin/env bash
# surinch.com · PostgreSQL work_mem 外部排序演示 v1.0
# 在测试库对比 work_mem=64kB（external merge Disk）vs 4MB/64MB（内存 Hash/quicksort）
# 仅 SET 会话级 work_mem / max_parallel_workers_per_gather，不改 postgresql.conf。
# 请勿对生产只读从库以外的业务库直接跑重查询。默认库名 workmem_lab。
# 用法：./pg-work-mem-external-merge-demo-v1.0.sh [库名]
# 连接：PGHOST/PGPORT/PGUSER/PGPASSWORD 或本机 peer（sudo -u postgres）
set -euo pipefail
DB="${1:-workmem_lab}"
PSQL=(psql -v ON_ERROR_STOP=1 -d "$DB")

echo "pg_version=$(${PSQL[@]} -Atc 'SELECT version();')"
echo "events=$(${PSQL[@]} -Atc 'SELECT COUNT(*) FROM events;')"

run_one() {
  local wmem=$1
  echo "=== work_mem=${wmem}, max_parallel_workers_per_gather=0 ==="
  ${PSQL[@]} <<SQL
SET max_parallel_workers_per_gather = 0;
SET work_mem = '${wmem}';
SHOW work_mem;
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT user_id, event_type, COUNT(*) AS cnt, SUM(payload_score) AS total_score
FROM events
WHERE created_at >= '2025-01-01' AND created_at < '2025-07-01'
GROUP BY user_id, event_type
ORDER BY total_score DESC
LIMIT 50;
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT DISTINCT user_id FROM events ORDER BY user_id;
SQL
}

run_one "64kB"
run_one "4MB"
run_one "64MB"
echo "done. 64kB 应出现 Sort Method: external merge Disk / HashAggregate Disk Usage；4MB+ 多为 Memory + Batches: 1。"