#!/bin/bash
# 盈尺（Surinch）PostgreSQL 索引没用上：10 道练习题 v1.0
# 只能在测试实例上跑：会 DROP/CREATE 库 ${DB}（名字必须以 _lab 结尾），会 CREATE EXTENSION pg_trgm。
# 测试：PostgreSQL 17.11（Debian 包），shared_buffers=1GB，work_mem=4MB，random_page_cost=4，数据库默认排序规则 C.UTF-8（libc）；
#       自造数据 orders 200 万行（setseed 固定，可复现），非客户数据。
# 用法：PSQL="psql -h 127.0.0.1 -U postgres" DB=pg_idx_lab ./pg-index-practice-lab-v1.0.sh | tee result.txt
set -uo pipefail
PSQL=${PSQL:-"psql"}
DB=${DB:-pg_idx_lab}
case "$DB" in *_lab) ;; *) echo "DB 名必须以 _lab 结尾（防止误删业务库）"; exit 1;; esac
P="$PSQL -X -q -v ON_ERROR_STOP=0 -d $DB"
ts(){ date '+%F %T.%3N'; }
# q 题号 标签 SQL：第 1 次打印完整 EXPLAIN (ANALYZE, BUFFERS)，再跑 2 次只取时间
q(){
  local id=$1 tag=$2 sql=$3 i out pre="SET jit=off"
  [ "${4:-}" = seqoff ] && pre="SET jit=off; SET enable_seqscan=off"
  echo "----- $id $tag"
  echo "SQL: $sql"
  out=$($P -c "$pre" -c "EXPLAIN (ANALYZE, BUFFERS) $sql" 2>&1); echo "$out"
  local t1; t1=$(echo "$out" | awk '/Execution Time/{print $3}')
  local p1; p1=$(echo "$out" | awk '/Planning Time/{print $3}')
  local ts2=""; for i in 2 3; do ts2="$ts2 $($P -c "$pre" -c "EXPLAIN (ANALYZE) $sql" 2>&1 | awk '/Execution Time/{print $3}')"; done
  local node; node=$(echo "$out" | grep -oE '(Parallel )?(Seq Scan|Index Only Scan|Index Scan Backward|Index Scan|Bitmap Index Scan|Bitmap Heap Scan|BitmapOr)( using [a-z_0-9]+)?' | head -3 | paste -sd ';')
  echo "SUMMARY|$id|$tag|plan=$node|planning_ms=$p1|exec_ms=$t1$ts2|rows=$(echo "$out" | grep -m1 -oE 'actual time=[0-9.]+\.\.[0-9.]+ rows=[0-9]+' | sed 's/.*rows=//')"
}
x(){ echo "SQLRUN: $1"; $P -c "\\timing on" -c "$1" 2>&1 | grep -vE '^$'; }

init(){
  echo "[$(ts)] 建库造数"
  $PSQL -X -q -d postgres -c "DROP DATABASE IF EXISTS $DB" -c "CREATE DATABASE $DB"
  $P <<'SQL'
\timing on
SELECT setseed(0.25);
CREATE TABLE orders(
  id bigint PRIMARY KEY, tenant_id int NOT NULL, user_id int NOT NULL, status text NOT NULL,
  email text NOT NULL, sku text NOT NULL, title text NOT NULL, amount numeric(10,2) NOT NULL,
  created_at timestamptz NOT NULL, deleted_at timestamptz);
INSERT INTO orders
SELECT n, 1 + n % 50, u,
       CASE WHEN n < 200000 AND r < 0.002 THEN 'disputed' WHEN r < 0.60 THEN 'paid' WHEN r < 0.90 THEN 'shipped' WHEN r < 0.98 THEN 'refund' ELSE 'pending' END,
       'User' || u || '@Example.com',
       'SKU' || lpad((n::bigint * 7919 % 50000)::text, 5, '0'),
       (ARRAY['保温杯','手机壳','数据线','蓝牙耳机','收纳盒'])[1 + n % 5] || ' ' || md5(n::text),
       round((5 + random() * 2000)::numeric, 2),
       timestamptz '2025-01-01 00:00:00+08' + (n::float8 / 2000000) * interval '660 days' + random() * interval '1 hour',
       CASE WHEN random() < 0.01 THEN now() END
FROM (SELECT n, 1 + floor(random() * 200000)::int u, random() r FROM generate_series(1, 2000000) n) s;
CREATE INDEX idx_tenant_created ON orders(tenant_id, created_at);
CREATE INDEX idx_user ON orders(user_id);
CREATE INDEX idx_email ON orders(email);
CREATE INDEX idx_sku ON orders(sku);
VACUUM ANALYZE orders;
SQL
  $P -c "SELECT version()" -c "SELECT datcollate, datlocprovider FROM pg_database WHERE datname = current_database()" -c "SELECT status, count(*), round(count(*)*100.0/2000000,2) pct FROM orders GROUP BY 1 ORDER BY 2 DESC" \
     -c "SELECT min(created_at), max(created_at), count(DISTINCT user_id) users, count(deleted_at) deleted FROM orders" \
     -c "SELECT pg_size_pretty(pg_table_size('orders')) heap, pg_size_pretty(pg_indexes_size('orders')) indexes"
}

q1(){ # 联合索引第一列没用上
  q Q1 "只有 (tenant_id, created_at)，按 created_at 查一天" "SELECT count(*) FROM orders WHERE created_at >= '2026-03-01 00:00+08' AND created_at < '2026-03-02 00:00+08'"
  x "CREATE INDEX idx_created ON orders(created_at)"
  q Q1 "加 (created_at) 单列索引后" "SELECT count(*) FROM orders WHERE created_at >= '2026-03-01 00:00+08' AND created_at < '2026-03-02 00:00+08'"
  q Q1 "带上第一列 tenant_id = 7" "SELECT count(*) FROM orders WHERE tenant_id = 7 AND created_at >= '2026-03-01 00:00+08' AND created_at < '2026-03-02 00:00+08'"
}
q2(){ # 类型不一致
  q Q2 "int 列和 numeric 常量比较 user_id = 4242.0" "SELECT id FROM orders WHERE user_id = 4242.0"
  q Q2 "改成整数常量 user_id = 4242" "SELECT id FROM orders WHERE user_id = 4242"
  x "PREPARE p_num(numeric) AS SELECT id FROM orders WHERE user_id = \$1; EXPLAIN (ANALYZE) EXECUTE p_num(4242);"
  x "SELECT count(*) FROM orders WHERE sku = 42"
}
q3(){ # 列上套函数
  q Q3 "lower(email) = …，只有 email 普通索引" "SELECT id FROM orders WHERE lower(email) = 'user4242@example.com'"
  x "CREATE INDEX idx_email_lower ON orders(lower(email))"
  x "ANALYZE orders"
  q Q3 "建 lower(email) 表达式索引后" "SELECT id FROM orders WHERE lower(email) = 'user4242@example.com'"
}
q4(){ # LIKE 前缀与排序规则
  q Q4 "LIKE 'SKU0420%'，idx_sku 默认排序规则 C.UTF-8（libc）" "SELECT count(*) FROM orders WHERE sku LIKE 'SKU0420%'"
  x "CREATE INDEX idx_sku_pattern ON orders(sku text_pattern_ops)"
  q Q4 "加 text_pattern_ops 索引后" "SELECT count(*) FROM orders WHERE sku LIKE 'SKU0420%'"
  q Q4 "改写成范围 sku >= 'SKU0420' AND sku < 'SKU0421'（用原 idx_sku）" "SELECT count(*) FROM orders WHERE sku >= 'SKU0420' AND sku < 'SKU0421'"
  x "SELECT pg_size_pretty(pg_relation_size('idx_sku')) idx_sku, pg_size_pretty(pg_relation_size('idx_sku_pattern')) idx_sku_pattern"
}
q5(){ # OR
  q Q5 "user_id = 42 OR sku = 'SKU00042'（两列都有索引）" "SELECT id FROM orders WHERE user_id = 42 OR sku = 'SKU00042'"
  q Q5 "user_id = 42 OR amount = 99.99（amount 无索引）" "SELECT id FROM orders WHERE user_id = 42 OR amount = 99.99"
  q Q5 "拆成 UNION（amount 一侧仍要全表）" "SELECT id FROM orders WHERE user_id = 42 UNION SELECT id FROM orders WHERE amount = 99.99"
}
q6(){ # 区分度低
  x "CREATE INDEX idx_status ON orders(status)"
  x "ANALYZE orders"
  q Q6 "status = 'paid'（约 60%），优化器自己选" "SELECT sum(amount) FROM orders WHERE status = 'paid'"
  q Q6 "强制走索引 enable_seqscan=off" "SELECT sum(amount) FROM orders WHERE status = 'paid'" seqoff
  q Q6 "status = 'pending'（约 2%）" "SELECT sum(amount) FROM orders WHERE status = 'pending'"
}
q7(){ # ORDER BY LIMIT 走错索引（先去掉 Q6 的 idx_status，只留 idx_created）
  x "DROP INDEX IF EXISTS idx_status"
  x "ANALYZE orders"
  q Q7 "status='disputed' ORDER BY created_at DESC LIMIT 20（disputed 都是老数据）" "SELECT id, created_at FROM orders WHERE status = 'disputed' ORDER BY created_at DESC LIMIT 20"
  x "CREATE INDEX idx_status_created ON orders(status, created_at)"
  q Q7 "建 (status, created_at) 后" "SELECT id, created_at FROM orders WHERE status = 'disputed' ORDER BY created_at DESC LIMIT 20"
  x "DROP INDEX idx_status_created"
}
q8(){ # 部分索引 + generic plan
  x "CREATE INDEX idx_pending_created ON orders(created_at) WHERE status = 'pending'"
  x "ANALYZE orders"
  q Q8 "字面量 status = 'pending'" "SELECT count(*) FROM orders WHERE status = 'pending' AND created_at >= '2026-09-01 00:00+08'"
  echo "----- Q8 预编译 status = \$1：custom plan vs generic plan"
  $P <<'SQL'
SET jit = off;
PREPARE p_status(text, timestamptz) AS SELECT count(*) FROM orders WHERE status = $1 AND created_at >= $2;
SET plan_cache_mode = force_custom_plan;
EXPLAIN (ANALYZE) EXECUTE p_status('pending', '2026-09-01 00:00+08');
SET plan_cache_mode = force_generic_plan;
EXPLAIN (ANALYZE) EXECUTE p_status('pending', '2026-09-01 00:00+08');
EXPLAIN (ANALYZE) EXECUTE p_status('pending', '2026-09-01 00:00+08');
SQL
}
q9(){ # timestamptz 转 date
  q Q9 "created_at::date = '2026-03-01'" "SELECT count(*) FROM orders WHERE created_at::date = '2026-03-01'"
  x "CREATE INDEX idx_created_date ON orders((created_at::date))"
  x "CREATE INDEX idx_created_sh_date ON orders(((created_at AT TIME ZONE 'Asia/Shanghai')::date))"
  x "ANALYZE orders"
  q Q9 "表达式索引 (created_at AT TIME ZONE 'Asia/Shanghai')::date" "SELECT count(*) FROM orders WHERE (created_at AT TIME ZONE 'Asia/Shanghai')::date = '2026-03-01'"
  q Q9 "不建新索引，改成半开区间（带时区）" "SELECT count(*) FROM orders WHERE created_at >= '2026-03-01 00:00+08' AND created_at < '2026-03-02 00:00+08'"
  x "SHOW TimeZone"
}
q10(){ # 前导 % 与 pg_trgm
  q Q10 "title LIKE '%a1b2c%'（前导 %）" "SELECT count(*) FROM orders WHERE title LIKE '%a1b2c%'"
  x "CREATE EXTENSION IF NOT EXISTS pg_trgm"
  x "CREATE INDEX idx_title_trgm ON orders USING gin (title gin_trgm_ops)"
  x "ANALYZE orders"
  q Q10 "建 pg_trgm GIN 后" "SELECT count(*) FROM orders WHERE title LIKE '%a1b2c%'"
  q Q10 "只有 2 个字符 LIKE '%a1%'" "SELECT count(*) FROM orders WHERE title LIKE '%a1%'"
  x "SELECT pg_size_pretty(pg_relation_size('idx_title_trgm')) trgm_idx, pg_size_pretty(pg_table_size('orders')) heap"
}
STEPS=${STEPS:-"init q1 q2 q3 q4 q5 q6 q7 q8 q9 q10"}
for s in $STEPS; do echo "[$(ts)] ===== $s"; $s; done
echo "[$(ts)] 结束。清理：DROP DATABASE $DB;"