TDSQL-PG Analyze Skill

技能概述

本技能专门用于分析 TDSQL-PG（PostgreSQL 兼容）数据库的 SQL 执行计划，识别性能瓶颈，给出针对性优化建议，并预测优化效果。

知识来源：腾讯云 TDSQL-PG 执行计划与性能优化官方培训资料（V1.0，2020）

激活条件

当用户提出以下任一需求时，立即加载本技能：

• 分析 SQL 执行计划（EXPLAIN / EXPLAIN ANALYZE 输出）
• 排查 SQL 性能瓶颈
• 优化慢查询
• 分析多表连接效率
• 检查索引使用情况
• 分析 TDSQL-PG 数据倾斜
• 解读执行计划算子含义

工作流程（SOP）

收到用户的执行计划文本后，必须严格按以下 5 步执行：

Step 1 — 解析执行计划结构

1. 识别执行计划的 阅读规则：
   - 嵌套层次最深的节点最先执行
   - 同等嵌套深度的节点从上到下先执行
   - 每步 cost 累加包含子步骤开销
2. 提取关键指标：
   - cost=启动..总计（估算代价，虚拟单位）
   - rows=N（估算返回行数）
   - width=N（估算行宽度，字节）
   - actual time=启动..总计（真实毫秒，仅 EXPLAIN ANALYZE 有）
   - loops=N（执行循环次数）
   - buffers（缓冲命中情况）
   - planning time / execution time（仅 EXPLAIN ANALYZE 有）
3. 构建执行树，标注每个节点的算子类型

Step 2 — 识别算子并标注风险等级

对照下表逐一标注计划中出现的算子：

Step 3 — 瓶颈分析（输出瓶颈列表）

按严重程度（🔴高 / 🟡中 / 🟢低）输出结构化瓶颈列表：

## 瓶颈列表

| # | 严重程度 | 算子/节点 | 指标 | 瓶颈描述 |
|---|---------|-----------|------|---------|
| 1 | 🔴 高 | Seq Scan on orders | rows=2,500,000 | 全表扫描，无索引过滤，扫描 250 万行 |
| 2 | 🟡 中 | Hash Join | batches=4 | Hash 表溢出磁盘，需增大 work_mem |
| 3 | 🟢 低 | Sort | actual time=1200ms | 排序耗时较长，建议在排序列加索引 |

按严重程度（🔴高 / 🟡中 / 🟢低）输出结构化瓶颈列表：

Step 4 — 优化建议

针对每个瓶颈，给出具体、可执行的优化方案，格式如下：

## 优化建议

### 建议 1：为 orders 表的查询列创建索引（对应瓶颈 #1）

**问题**：Seq Scan 全表扫描 250 万行，缺少索引。

**方案**：
```sql
-- 创建索引
CREATE INDEX CONCURRENTLY idx_orders_status_created
  ON orders(status, created_at DESC);

-- 验证索引生效
EXPLAIN (ANALYZE, BUFFERS) SELECT ...;

预期效果：Seq Scan → Index Scan，扫描行数从 250 万降至估算 5000 行，耗时预计从 1200ms 降至 30ms 以内。

建议 2：增大 work_mem 以消除 Hash 溢出（对应瓶颈 #2）

...

**分场景优化策略速查**：

#### 索引优化
- 等值查询列 → 创建 B-tree 索引
- 排序/范围列 → 在排序列上创建索引（注意 NULLS FIRST/LAST 与查询一致）
- 多列组合查询 → 创建联合索引，最高选择性列放前面
- `SELECT` 列全在索引内 → 改造为 covering index 实现 Index Only Scan
- `IS NULL` 查询 → B-tree 索引原生支持，确保索引建立时指定 NULLS LAST

#### 连接优化
- **小表 JOIN 大表（rows < 10000）** → Nested Loop + 内表索引
- **大表 JOIN 大表** → Hash Join（小表建 hash，大表探测）
- **已排序数据 JOIN** → Merge Join 可能更优
- **TDSQL-PG 多表 JOIN** → 连接列应与分布键一致，避免跨节点传输

#### 统计信息
```sql
-- 更新表统计信息
VACUUM ANALYZE table_name;
-- 或指定列
ANALYZE table_name (column1, column2);

work_mem 调整

-- 会话级别调整（Hash Join / Sort 溢出时）
SET work_mem = '256MB';
-- 再次执行查询验证效果
EXPLAIN (ANALYZE, BUFFERS) SELECT ...;

TDSQL-PG 分布键优化

-- 查看表的分布键
SELECT relname, pclocatortype, pcattnum
FROM pgxc_class
WHERE relname = 'your_table';

-- 重建表（修改分布键，需停服或用在线迁移工具）
CREATE TABLE new_table (LIKE old_table INCLUDING ALL)
  DISTRIBUTE BY HASH(new_dist_key);
INSERT INTO new_table SELECT * FROM old_table;

数据倾斜检测

-- 各节点表大小对比（连接不同 DN 执行）
SELECT pg_size_pretty(pg_table_size('table_name'));
-- 如果各节点差距 > 2x，存在数据倾斜

Step 5 — 预测优化结果

根据资料中的 cost 计算公式和算子特性，对每项建议预测优化效果：

## 优化效果预测

| 优化项 | 当前代价 | 预测代价 | 预期提升 | 置信度 |
|--------|---------|---------|---------|-------|
| 添加索引（建议1） | cost=25000 / 1200ms | cost=500 / 30ms | ~97% | 高 |
| 增大 work_mem（建议2） | batches=4 溢出磁盘 | batches=1 纯内存 | ~60% | 中 |
| ANALYZE 更新统计（建议3） | 估算偏差 50x | 估算误差 <2x | 规划器更优 | 中 |

预测依据公式（来自 TDSQL-PG 官方资料）：

• Seq Scan cost = (磁盘页 × seq_page_cost) + (扫描行 × cpu_tuple_cost)
• 默认：seq_page_cost=1.0，cpu_tuple_cost=0.01
• Index Scan 增加随机 IO：random_page_cost=4.0（默认），SSD 建议设为 1.1
• Nested Loop cost = 外表cost + (内表cost × 外表cardinality)
• Hash Join cost = (外表cost × hash分区数) + 内表cost

输出格式规范

最终输出结构（Markdown）：

# TDSQL-PG 执行计划分析报告

## 一、执行计划解读
[执行树结构 + 关键指标提取]

## 二、瓶颈列表
[表格形式，按严重程度排序]

## 三、优化建议
[每条建议含：问题描述 + SQL方案 + 验证方法]

## 四、优化效果预测
[表格形式，量化预测]

## 五、执行计划最优形态（目标）
[描述优化后期望的执行计划样态]

参考资料

• 详细算子说明：见 REFERENCE.md
• 分析脚本（命令行解析 EXPLAIN 输出）：见 scripts/analyze.py

注意事项

1. EXPLAIN vs EXPLAIN ANALYZE：EXPLAIN 仅显示估算值（cost/rows/width），EXPLAIN ANALYZE 才有真实执行数据（actual time/loops）。建议始终使用 EXPLAIN (ANALYZE, BUFFERS, VERBOSE) 获取完整信息。
2. cost 是虚拟单位，不是毫秒，不同 SQL 间的 cost 不可直接横向比较。
3. TDSQL-PG 分布式架构：先在 CN 节点查看执行计划（EXPLAIN VERBOSE），确认数据分布的 DN 节点后，再分别到对应 DN 上查看详细执行计划并综合分析。
4. 估算行数偏差：若 rows 估算 与 actual rows 偏差超过 10 倍，优先执行 VACUUM ANALYZE，再重新审视执行计划。