#!/usr/bin/env bash
# surinch.com · PostgreSQL Index Only Scan / heap fetches 演示 v1.0
# 对比：仅 (uid) 索引的 Bitmap Heap Scan vs (uid) INCLUDE (...) 的 Index Only Scan
# 以及可见性地图脏了之后 Heap Fetches > 0，VACUUM 后回到 0。
# 会对指定库做 DROP/CREATE INDEX、UPDATE、VACUUM；请勿对生产库直接跑。默认库名 shop。
# 用法：PGPASSWORD=... ./pg-index-only-scan-explain-demo-v1.0.sh [库名]
# 连接：PGHOST / PGPORT / PGUSER / PGPASSWORD
set -euo pipefail
DB="${1:-shop}"
export PGHOST="${PGHOST:-127.0.0.1}" PGPORT="${PGPORT:-5432}" PGUSER="${PGUSER:-shop}" PGDATABASE="$DB"

# 每条 -c 单独提交；VACUUM 不能放进隐式事务块
psql_q() { psql -v ON_ERROR_STOP=1 -X -At -c "$1"; }
psql_f() { psql -v ON_ERROR_STOP=1 -X -c "$1"; }

echo "pg_version=$(psql_q 'SHOW server_version;')"
echo "users=$(psql_q 'SELECT COUNT(*) FROM users;') orders=$(psql_q 'SELECT COUNT(*) FROM orders;')"
UID_PICK="$(psql_q 'SELECT uid FROM orders GROUP BY uid ORDER BY COUNT(*) DESC LIMIT 1;')"
echo "demo_uid=${UID_PICK} rows=$(psql_q "SELECT COUNT(*) FROM orders WHERE uid=${UID_PICK};")"

psql_f "DROP INDEX IF EXISTS idx_orders_uid;"
psql_f "DROP INDEX IF EXISTS idx_orders_uid_covering;"
psql_f "CREATE INDEX idx_orders_uid ON orders (uid);"
psql_f "ANALYZE orders;"

echo "=== NON-COVERING (idx_orders_uid) ==="
psql_f "EXPLAIN (ANALYZE, BUFFERS) SELECT id, amount, status FROM orders WHERE uid = ${UID_PICK};"

psql_f "DROP INDEX idx_orders_uid;"
psql_f "CREATE INDEX idx_orders_uid_covering ON orders (uid) INCLUDE (id, amount, status);"
psql_f "ANALYZE orders;"
psql_f "VACUUM orders;"

echo "=== COVERING clean (Heap Fetches expect 0) ==="
psql_f "EXPLAIN (ANALYZE, BUFFERS) SELECT id, amount, status FROM orders WHERE uid = ${UID_PICK};"

echo "=== DIRTY visibility map (UPDATE then force Index Only Scan) ==="
psql_f "UPDATE orders SET amount = amount WHERE uid = ${UID_PICK};"
psql_f "ANALYZE orders;"
psql_f "SET enable_bitmapscan = off; SET enable_seqscan = off; EXPLAIN (ANALYZE, BUFFERS) SELECT id, amount, status FROM orders WHERE uid = ${UID_PICK};"

echo "=== AFTER VACUUM (Heap Fetches expect 0) ==="
psql_f "VACUUM orders;"
psql_f "ANALYZE orders;"
psql_f "EXPLAIN (ANALYZE, BUFFERS) SELECT id, amount, status FROM orders WHERE uid = ${UID_PICK};"
echo "done. Heap Fetches>0 说明可见性地图未覆盖，仍要回堆确认；VACUUM 后再看是否降到 0。"