#!/usr/bin/env bash
# PostgreSQL 多列相关 → 行数低估 → Nested Loop 演示 v1.0（surinch.com · 盈尺）
# 只在测试实例上跑！会新建并重建库 stats_lab（自造数据：orders 100 万行，约 10 秒）。
# 测试版本：PostgreSQL 17.11。用法：PSQL="psql -X -U postgres -h 127.0.0.1" ./pg-extended-statistics-demo-v1.0.sh
set -euo pipefail
PSQL=${PSQL:-psql -X}
$PSQL -d postgres -c "DROP DATABASE IF EXISTS stats_lab" -c "CREATE DATABASE stats_lab"
P="$PSQL -d stats_lab"
$P -q <<'SQL'
CREATE TABLE orders (
  id bigint PRIMARY KEY, province_id int NOT NULL, city_id int NOT NULL, district_id int NOT NULL,
  region_id int NOT NULL, warehouse_id int NOT NULL, customer_id int NOT NULL, amount numeric(10,2) NOT NULL);
-- 区县决定城市、城市决定省；region_id = 省、warehouse_id = 城市（五列强相关）
INSERT INTO orders
SELECT g, ((d - 1) / 100) + 1, ((d - 1) / 10) + 1, d, ((d - 1) / 100) + 1, ((d - 1) / 10) + 1,
       (1 + (g::bigint * 7919) % 200000)::int, (g % 1000) + 0.99
FROM (SELECT g, 1 + (g * 31) % 1000 AS d FROM generate_series(1, 1000000) g) s;
CREATE INDEX idx_orders_area ON orders (province_id, city_id, district_id);
-- 1000 行的小表，没有主键也没有索引
CREATE TABLE district_targets AS SELECT d AS district_id, (d % 7) * 1000 AS monthly_target FROM generate_series(1, 1000) d;
VACUUM ANALYZE orders; ANALYZE district_targets;
SQL
Q="SELECT count(*), sum(o.amount), sum(t.monthly_target) FROM orders o JOIN district_targets t ON t.district_id = o.district_id WHERE o.region_id = 4 AND o.province_id = 4 AND o.city_id = 37 AND o.warehouse_id = 37"
echo "== 1 单表估算：看 rows= 和 actual rows="
$P -c "EXPLAIN (ANALYZE, TIMING OFF) SELECT * FROM orders o WHERE o.region_id = 4 AND o.province_id = 4 AND o.city_id = 37 AND o.warehouse_id = 37"
echo "== 2 JOIN：看 Nested Loop 和 Rows Removed by Join Filter"
$P -c "EXPLAIN (ANALYZE) $Q"
echo "== 3 建扩展统计（dependencies）后重新 ANALYZE"
$P -c "CREATE STATISTICS st_orders_area_dep (dependencies) ON region_id, province_id, city_id, warehouse_id FROM orders" -c "ANALYZE orders"
$P -c "EXPLAIN (ANALYZE, TIMING OFF) SELECT * FROM orders o WHERE o.region_id = 4 AND o.province_id = 4 AND o.city_id = 37 AND o.warehouse_id = 37"
$P -c "EXPLAIN (ANALYZE) $Q"
echo "完成。清理：$PSQL -d postgres -c 'DROP DATABASE stats_lab'"