#!/bin/bash
# =============================================================================
# 单库容量实测：真实多租户分布下的性能基线
#
# 【重要教训】本脚本的前置条件是先 VACUUM ANALYZE。
#   实测发现：统计信息滞后会让优化器选择错误执行计划，
#   同一查询 1.4ms 与 396ms 的差别（280 倍）完全来自统计信息是否新鲜。
#   => 生产环境必须配置 autovacuum 调优与监控，这比索引设计更关键。
#
# 数据分布：100 万房源 / 300 万租客 / 500 万账单 / 100 个租户
# 环境：本机 PG 17.11 默认配置（shared_buffers=128MB）
#      生产调优后（阿里云 8C32G，shared_buffers=8GB）预计快 2-5 倍
# =============================================================================
set -u
DB=rental_bench

echo "=============================================="
echo " 先 VACUUM ANALYZE（关键前置步骤）"
echo "=============================================="
psql -d "$DB" -q -c "VACUUM ANALYZE room; VACUUM ANALYZE tenant_person; VACUUM ANALYZE bill;"
echo "✅ 统计信息已刷新"
echo ""

bench() {
  local title="$1"; local sql="$2"
  local result
  result=$(psql -d "$DB" -tA -c "EXPLAIN (ANALYZE, BUFFERS) $sql" 2>&1)
  local exec_time=$(echo "$result" | grep "Execution Time" | grep -oE "[0-9]+\.[0-9]+")
  local scan_type=$(echo "$result" | grep -oE "Index Only Scan|Bitmap Index Scan|Index Scan|Parallel Seq Scan|Seq Scan" | head -1)
  local heap=$(echo "$result" | grep -oE "Heap Fetches: [0-9]+" | head -1)
  printf "  %-42s %8s ms   %-18s %s\n" "$title" "$exec_time" "$scan_type" "$heap"
}

echo "=============================================="
echo " 查询性能基线（真实多租户分布）"
echo "=============================================="
echo ""
printf "  %-42s %11s   %-18s %s\n" "查询场景" "耗时" "扫描方式" "回表"
echo "  -------------------------------------------------------------------------------"
bench "① 主键点查房源" "SELECT * FROM room WHERE id=500000"
bench "② 房源列表页(租户+状态,LIMIT20)" "SELECT * FROM room WHERE tenant_id=1 AND status='vacant' LIMIT 20"
bench "③ 房源详情(按房号)" "SELECT * FROM room WHERE tenant_id=1 AND room_no='R123'"
bench "④ 出租率统计(单租户)" "SELECT status,count(*) FROM room WHERE tenant_id=1 GROUP BY status"
bench "⑤ 账单号精确查" "SELECT * FROM bill WHERE tenant_id=1 AND bill_no='B1234567'"
bench "⑥ 逾期未缴扫描(部分索引)" "SELECT count(*) FROM bill WHERE tenant_id=1 AND status IN ('pending','partial','overdue')"
bench "⑦ 月度账期范围查" "SELECT count(*) FROM bill WHERE tenant_id=1 AND period_start>='2025-01-01' AND period_start<'2025-02-01'"
bench "⑧ 账龄汇总(单租户聚合)" "SELECT status,sum(amount_due),count(*) FROM bill WHERE tenant_id=1 GROUP BY status"
bench "⑨ 证件哈希查重(风控)" "SELECT id FROM tenant_person WHERE id_type='cn_id' AND id_no_hash=md5('bench123456')"
bench "⑩ 手机号查租客" "SELECT id FROM tenant_person WHERE phone='13900123456'"
echo ""
echo "########## 最坏情况：跨租户全局聚合 ##########"
bench "⑪ 全平台账单汇总(应改用OLAP)" "SELECT status,count(*) FROM bill GROUP BY status"
bench "⑫ 全表 count" "SELECT count(*) FROM bill"