智能索引推荐（tencentdb_index_advisor）

-- 安装依赖扩展 hypopg（如未安装）
CREATE EXTENSION IF NOT EXISTS hypopg;

-- 安装索引推荐扩展
CREATE EXTENSION IF NOT EXISTS tencentdb_index_advisor;

SELECT extname, extversion FROM pg_extension 
WHERE extname IN ('hypopg', 'tencentdb_index_advisor')
ORDER BY extname;

tencentdb_index_advise(query_string text)
RETURNS TABLE (
    schema_name text,
    table_name  text,
    am_name     text,
    index_size  bigint,
    index_def   text
)

-- 关闭覆盖索引推荐（减少索引大小）
SET tencentdb_index_advisor.enable_covering_index = off;

-- 限制最多推荐 3 个索引
SET tencentdb_index_advisor.max_index_recommend = 3;

-- 跳过变异优化阶段（加快推荐速度）
SET tencentdb_index_advisor.try_variation_timeout = 0;

-- 批量分析时启用工作负载压缩
SET tencentdb_index_advisor.enable_workload_compression = on;

-- 对慢查询进行索引推荐
SELECT schema_name, table_name, am_name,
       pg_size_pretty(index_size) AS est_size,
       index_def
FROM tencentdb_index_advise(
    'SELECT * FROM orders WHERE customer_id = 123 AND status = ''shipped'' AND order_date > ''2025-01-01'''
);

SELECT schema_name, table_name, am_name,
       pg_size_pretty(index_size) AS est_size,
       index_def
FROM tencentdb_index_advise(
    'SELECT o.id, c.name, o.amount
     FROM orders o JOIN customers c ON o.customer_id = c.id
     WHERE c.city = ''Beijing'' AND o.status = ''delivered'''
);

-- 同时分析多条业务 SQL
SELECT schema_name, table_name,
       pg_size_pretty(index_size) AS est_size,
       index_def
FROM tencentdb_index_advise('
    SELECT * FROM orders WHERE customer_id = 100;
    SELECT * FROM orders WHERE status = ''pending'' AND region = ''east'';
    SELECT * FROM products WHERE category = ''electronics'' AND price < 200;
    SELECT o.id, c.name FROM orders o JOIN customers c ON o.customer_id = c.id WHERE c.city = ''Beijing'';
');

-- 开启覆盖索引推荐
SET tencentdb_index_advisor.enable_covering_index = on;
SELECT index_def FROM tencentdb_index_advise(
    'SELECT customer_id, amount FROM orders WHERE status = ''shipped'' AND region = ''east'''
);
-- 结果: CREATE INDEX ON public.orders USING btree (region, status, customer_id, amount)

-- 关闭覆盖索引推荐
SET tencentdb_index_advisor.enable_covering_index = off;
SELECT index_def FROM tencentdb_index_advise(
    'SELECT customer_id, amount FROM orders WHERE status = ''shipped'' AND region = ''east'''
);
-- 结果: CREATE INDEX ON public.orders USING btree (region, status)

-- UPDATE 语句索引推荐
SELECT index_def FROM tencentdb_index_advise(
    'UPDATE orders SET amount = 0 WHERE customer_id = 123 AND status = ''cancelled'''
);
-- 结果: CREATE INDEX ON public.orders USING btree (customer_id, status)

-- DELETE 语句索引推荐
SELECT index_def FROM tencentdb_index_advise(
    'DELETE FROM orders WHERE customer_id = 999 AND order_date < ''2025-01-01'''
);
-- 结果: CREATE INDEX ON public.orders USING btree (customer_id, order_date)

输入 SQL 语句
    │
    ▼
步骤1: 查询解析
    └── 解析 WHERE/JOIN/GROUP BY/ORDER BY 等子句
         │
步骤2: 候选索引列生成
    └── 按优先级识别候选列：
         EQUALITY(等值) > RANGE(范围) > JOIN(连接)
         > GROUP_ORDER(排序/分组) > COVERING(覆盖)
         │
步骤3: 假设索引创建
    └── 通过 hypopg 创建假设索引（不占磁盘空间）
         │
步骤4: 代价评估
    └── 利用 PostgreSQL 优化器评估有无索引的执行代价差异
         │
步骤5: 贪心选择 + 变异优化
    └── 贪心算法选择收益最大的索引组合
         变异阶段尝试不同组合寻找更优方案
         │
步骤6: 输出推荐
    └── 返回 CREATE INDEX 语句和预估索引大小

步骤1: 从 pg_stat_statements 提取 TOP 慢查询
    └── SELECT query FROM pg_stat_statements ORDER BY mean_exec_time DESC LIMIT 10;
         │
步骤2: 批量输入到索引推荐
    └── SELECT * FROM tencentdb_index_advise('慢查询1; 慢查询2; ...');
         │
步骤3: 评估推荐索引
    ├── 查看预估索引大小，评估磁盘空间影响
    ├── 用 EXPLAIN 验证查询计划变化
    └── 确认无重复/冗余索引
         │
步骤4: 低峰期创建索引
    └── CREATE INDEX CONCURRENTLY ... （在线创建，不锁表）
         │
步骤5: 验证效果
    └── 对比创建前后的查询耗时和执行计划

-- 第一步：查看当前执行计划和代价
EXPLAIN SELECT * FROM orders WHERE customer_id = 123 AND status = 'shipped';
-- 结果: Seq Scan, cost=1218.0

-- 第二步：获取推荐索引
SELECT index_def FROM tencentdb_index_advise(
    'SELECT * FROM orders WHERE customer_id = 123 AND status = ''shipped'''
);
-- 结果: CREATE INDEX ON public.orders USING btree (customer_id, status)

-- 第三步：创建索引（生产环境建议用 CONCURRENTLY）
CREATE INDEX CONCURRENTLY idx_orders_cust_status 
    ON orders USING btree (customer_id, status);

-- 第四步：验证优化效果
EXPLAIN SELECT * FROM orders WHERE customer_id = 123 AND status = 'shipped';
-- 结果: Index Scan, cost=8.31  (性能提升 99.3%)