tencentdb_spm

# postgresql.conf
shared_preload_libraries = 'pg_hint_plan,tencentdb_spm'
compute_query_id = on
# 可选：调整共享内存计数器容量（默认 4096）
# tencentdb_spm.max_shmem_entries = 4096

-- 超级用户执行
CREATE EXTENSION tencentdb_spm;

-- ① 总开关（默认 off，PGC_SUSET，管理员设置）
SET tencentdb_spm.enable_plan_management = on;          -- 会话级
-- 或 ALTER SYSTEM SET tencentdb_spm.enable_plan_management = on; SELECT pg_reload_conf();

-- ② 捕获开关（默认 off，用户级可设）
SET tencentdb_spm.capture_plan_baselines = 'manual';    -- 或 'automatic'

-- ③ 应用开关（默认 off，用户级可设）
SET tencentdb_spm.use_plan_baselines = on;

DROP EXTENSION tencentdb_spm;   -- 删除 schema、表、函数
-- 再从 shared_preload_libraries 移除 tencentdb_spm 并重启

-- 1) 准备数据
CREATE TABLE spm_demo(id int primary key, v int, pad text);
INSERT INTO spm_demo SELECT g, g % 100, 'x' FROM generate_series(1, 100000) g;
ANALYZE spm_demo;

-- 2) 管理员会话开启总开关
SET tencentdb_spm.enable_plan_management = on;

-- 3) 开启手动捕获
SET tencentdb_spm.capture_plan_baselines = 'manual';
-- 也可以用便捷函数（对本会话生效）：
-- SELECT tencentdb_spm.capture_plan();

-- 4) 执行目标 SQL（首捕获计划自动成为 Approved 激活基线）
SELECT count(*) FROM spm_demo WHERE id = 42;

-- 5) 查看捕获结果
SELECT sql_id, plan_id, status, enabled, origin, sql_text
FROM tencentdb_spm.dba_plans;
--  status  | enabled | origin |              sql_text
-- ----------+---------+--------+-------------------------------------
--  Approved | t       | AUTO   | SELECT count(*) FROM spm_demo WHERE id = $1

-- 6) 开启应用并验证（EXPLAIN 文本格式末尾会追加 SPM 决策行）
SET tencentdb_spm.use_plan_baselines = on;
EXPLAIN (COSTS OFF) SELECT count(*) FROM spm_demo WHERE id = 42;
--                      QUERY PLAN
-- ------------------------------------------------------
--  Aggregate
--    ->  Index Only Scan using spm_demo_pkey on spm_demo
--          Index Cond: (id = 42)
--  SPM: sql_id=617283... plan_id=-521096... (baseline forced)
-- (4 rows)

-- 切换示例：将候选计划切换为激活计划
SELECT tencentdb_spm.activate_plan(617283..., -521096..., 'switch to seq scan plan');
-- 回滚到上一版激活计划
SELECT tencentdb_spm.rollback_plan(617283...);
-- 查看历史
SELECT * FROM tencentdb_spm.show_plan_history(617283...);

-- sql_id 可从 EXPLAIN 的 SPM 行或 dba_plans 中获取
SELECT tencentdb_spm.create_patch(
    617283...,
    'IndexScan(spm_demo spm_demo_pkey)',
    'fix regression after upgrade');

-- 管理员开启（PGC_SUSET）
SET tencentdb_spm.evolve_enabled = on;

-- 预览/执行过期候选清理：仅删除 Unapproved 且超过保留期的行
-- Approved / Rejected 永不删除；dry_run 默认 true
SELECT tencentdb_spm.gc_plans();                 -- 预览（按 plan_retention_days，默认 32 天）
SELECT tencentdb_spm.gc_plans(dry_run => false); -- 实删

-- 校验基线引用的表/索引是否仍存在
SELECT tencentdb_spm.validate_plans(sql_id, plan_id);                -- 仅报告
SELECT tencentdb_spm.validate_plans(sql_id, plan_id, 'disable');     -- 失效行置 enabled=false
SELECT tencentdb_spm.validate_plans(sql_id, plan_id, 'delete');      -- 删除失效行

-- "为什么基线没生效"排查示例
SELECT tencentdb_spm.get_stats()->>'apply_misses'        AS misses,
       tencentdb_spm.get_stats()->>'apply_fingerprint_refused' AS fp_refused,
       tencentdb_spm.get_stats()->>'apply_render_failures'     AS render_fail;
SELECT tencentdb_spm.diagnose_plan_usage(617283...);

GRANT spm_role TO analyst_role;