-- CreateIndex —— 画像圈人(personaTags)筛选索引,每个可筛维度一条偏索引
--
-- 【症状】生产 2026-07-28 实测:召回池按「禁忌:手术禁忌」筛人
--     GET /pac/v1/plans?view=pool&personaTags=contraindication:surgery
--   前端等 20+ 秒。服务端复现:count 23,379ms + findMany 22,208ms(两句并发,故墙钟 ≈23s)。
--   **不是禁忌特有** —— 测试服同口径按「价值分群:重要价值」(rfm)更慢,57s。
--   14 个圈人维度全都踩同一个坑,禁忌只是用户先点到的那个。
--
-- 【根因】Prisma 的 relation filter 编译成两层 EXISTS,最内层是:
--     EXISTS (SELECT persona_id FROM persona_features t2
--             WHERE t2.key = 'contraindication'
--               AND (t2.data #> '{domains}') @> '"surgery"'
--               AND t1.id = t2.persona_id)
--   现有索引只有 persona_features_key_idx(单列 key),于是 planner 只能:
--     按 key 取出该维度**全部**特征行 → 逐行回堆读 data(jsonb) → 在堆上过滤 JSON 值。
--
--   生产 EXPLAIN (ANALYZE, BUFFERS) 实测(persona_features 7,657,944 行 / 5.4GB,堆 3.4GB):
--     Index Scan using persona_features_key_idx  actual time=3567..3954ms
--       Index Cond: (key = 'contraindication')
--       Filter:     ((data #> '{domains}') @> '"surgery"')
--       rows=2,156,**Rows Removed by Filter: 188,054**      ← 命中率 1.1%,98.9% 是白读
--       Buffers: shared read=162,830(约 1.3GB 随机堆读)   ← 冷缓存下就是这 20 秒
--   即:为了捞 2,156 行,把 190,210 行的 jsonb 全从磁盘搬了一遍。
--   维度越大越惨:rfm / gender / lifecycle_stage 各 869,043 行,一次筛人搬 869k 行 data。
--
-- 【修法】给每个可筛维度建一条**偏索引**(WHERE key = '<维度>'),表达式与 Prisma 生成的
--   完全一致(data #> '{<dataPath>}'),让 JSON 条件下推到索引:
--     · 数组维度(isArray,array_contains → @>)→ GIN jsonb_path_ops,只支持也只需要 @>
--     · 标量维度(equals → =)             → btree (表达式, persona_id) 复合
--       第二列 persona_id 不是为了排序,是为了 **index-only scan**:内层 EXISTS 只 SELECT
--       persona_id,索引自带就不用回堆。rfm 这种低选择性维度(164,442 行命中)光靠索引定位
--       没用 —— 测试服实测走上 btree 后 Bitmap Index Scan 只花 217ms,但回堆 143,187 个块
--       花了 103s(还因 work_mem 不够退化成 lossy,Rows Removed by Index Recheck 1,564,553)。
--       盖住 persona_id 才是这类维度真正的解。
--
-- 【为什么不写进 schema.prisma】Prisma schema 表达不了表达式索引 / 偏索引,只能落在迁移 SQL 里。
--   副作用:本地 `prisma migrate dev` 会把这些索引报成 drift(它不认识),**别按提示 reset**;
--   `migrate deploy` 不受影响。同类先例见 20260727070000 / 20260727110000。
--
-- 【维度增删纪律】可筛维度的单一真理源是 packages/types/src/persona-tag-filters.ts 的
--   PERSONA_TAG_FILTER_DIMS。往那里加维度时,必须在这里补一条对应索引(数组走 GIN、标量走 btree),
--   否则新维度一上线就是 20 秒起步 —— 这正是禁忌维度这次踩的坑。
--
-- 【体积与写入代价】偏索引只覆盖本 key 的行,14 条加起来 ≈ 全表一遍(每行至多落进一条)。
--   生产实测 14 条合计 **220MB**(表 5.4GB,占 4%)。写入侧同理:插一行特征至多多维护 1 条索引,
--   画像重算不会被拖垮。
--
-- 【CONCURRENTLY 与上线方式】同 20260727110000:普通 CREATE INDEX 会 ACCESS EXCLUSIVE 锁全表,
--   画像重算与摄入全挂,故用 CONCURRENTLY。**推荐部署前手动先建**(deploy 时 pac-service 要等
--   pac-migrate 退出,14 条索引串行构建 = 白等的停机);手动建好后本文件全部 IF NOT EXISTS 空跑。
--   生产实测单条 4-17s,14 条共 **88 秒**(不锁表,期间业务照常);测试服磁盘慢,单条约 42s。
--
-- 【上线后实测】生产 2026-07-28 已手动建完,同口径复测(count + findMany 并发,墙钟):
--     contraindication:surgery      23,379ms → **84ms**    (278×,命中 1,347 单)
--     contraindication:implant                    842ms    (25,900 单)
--     rfm:important_value                       1,558ms    (61,300 单)
--     treatment_history:implant_history           854ms    (17,179 单)
--     gender:female                             2-4s       (105,110 单)
--   剩下的耗时不再是 JSON 过滤,而是**结果集本身大**:count(*) 要数完十万级 plan。
--   若哪天 gender 这类宽维度也嫌慢,方向是给 count 做近似/缓存,不是继续加索引。
--   测试服同日也已手动建完(14 条 / 238s / 195MB):rfm 57,299ms → **8,576ms**,
--   禁忌 4,210ms → **344ms**。那台盘慢,绝对值别拿去跟生产比,看倍数即可。

-- ── 标量维度(equals):btree(表达式, persona_id),第二列盖住 EXISTS 的输出列 ──────────────
CREATE INDEX CONCURRENTLY IF NOT EXISTS "persona_features_rfm_segment_idx"
  ON "persona_features" (((data #> '{segment}'::text[])), "persona_id") WHERE "key" = 'rfm';
CREATE INDEX CONCURRENTLY IF NOT EXISTS "persona_features_lifecycle_stage_stage_idx"
  ON "persona_features" (((data #> '{stage}'::text[])), "persona_id") WHERE "key" = 'lifecycle_stage';
CREATE INDEX CONCURRENTLY IF NOT EXISTS "persona_features_urgency_level_level_idx"
  ON "persona_features" (((data #> '{level}'::text[])), "persona_id") WHERE "key" = 'urgency_level';
CREATE INDEX CONCURRENTLY IF NOT EXISTS "persona_features_age_bracket_bracket_idx"
  ON "persona_features" (((data #> '{bracket}'::text[])), "persona_id") WHERE "key" = 'age_bracket';
CREATE INDEX CONCURRENTLY IF NOT EXISTS "persona_features_gender_gender_idx"
  ON "persona_features" (((data #> '{gender}'::text[])), "persona_id") WHERE "key" = 'gender';
CREATE INDEX CONCURRENTLY IF NOT EXISTS "persona_features_family_structure_structure_idx"
  ON "persona_features" (((data #> '{structure}'::text[])), "persona_id") WHERE "key" = 'family_structure';
CREATE INDEX CONCURRENTLY IF NOT EXISTS "persona_features_referral_champion_type_idx"
  ON "persona_features" (((data #> '{type}'::text[])), "persona_id") WHERE "key" = 'referral_champion';

-- ── 数组维度(array_contains → @>):GIN jsonb_path_ops ────────────────────────────────────
CREATE INDEX CONCURRENTLY IF NOT EXISTS "persona_features_contraindication_domains_idx"
  ON "persona_features" USING gin (((data #> '{domains}'::text[])) jsonb_path_ops) WHERE "key" = 'contraindication';
CREATE INDEX CONCURRENTLY IF NOT EXISTS "persona_features_entitlement_status_types_idx"
  ON "persona_features" USING gin (((data #> '{types}'::text[])) jsonb_path_ops) WHERE "key" = 'entitlement_status';
CREATE INDEX CONCURRENTLY IF NOT EXISTS "persona_features_treatment_history_types_idx"
  ON "persona_features" USING gin (((data #> '{types}'::text[])) jsonb_path_ops) WHERE "key" = 'treatment_history';
CREATE INDEX CONCURRENTLY IF NOT EXISTS "persona_features_treatment_sensitivity_types_idx"
  ON "persona_features" USING gin (((data #> '{types}'::text[])) jsonb_path_ops) WHERE "key" = 'treatment_sensitivity';
CREATE INDEX CONCURRENTLY IF NOT EXISTS "persona_features_special_attention_types_idx"
  ON "persona_features" USING gin (((data #> '{types}'::text[])) jsonb_path_ops) WHERE "key" = 'special_attention';
CREATE INDEX CONCURRENTLY IF NOT EXISTS "persona_features_time_preference_types_idx"
  ON "persona_features" USING gin (((data #> '{types}'::text[])) jsonb_path_ops) WHERE "key" = 'time_preference';
CREATE INDEX CONCURRENTLY IF NOT EXISTS "persona_features_potential_treatment_types_idx"
  ON "persona_features" USING gin (((data #> '{types}'::text[])) jsonb_path_ops) WHERE "key" = 'potential_treatment';
