Postgresql optimization

何时使用

• PostgreSQL 查询变慢、CPU/IO 飙高、连接堆积，需定位瓶颈并出优化方案。
• 设计索引策略（B-tree / 复合 / 部分 / 覆盖 / GIN-GiST）、改写低效 SQL。
• 调优实例参数（shared_buffers、work_mem、effective_cache_size、checkpoint、autovacuum）。
• 治理 VACUUM/膨胀、过期统计信息，或搭建 PG 性能监控。

不该用的边界：

• 仅写一条普通查询、不涉及性能 → 用 sql-query-builder。
• 数据库选型、分库分表架构、应用层缓存/连接池设计 → 超出本技能。
• 非 PostgreSQL（MySQL/Oracle/SQL Server 等）→ 执行计划、参数、系统视图均不同，勿照搬。
• 无法连库、拿不到 EXPLAIN/系统视图权限 → 先取访问权限，不要凭空猜瓶颈。

步骤 / 指令

按序推进，每步先观测再动手，单次只改一处并复测，避免叠加变更无法归因。

1. 评估现状（只读）
   - 版本与配置：SELECT version();  SHOW shared_buffers; SHOW work_mem;
   - 找慢查询：启用并查 pg_stat_statements，按 total_exec_time / mean_exec_time 排序
   - 资源面：连接数、cache 命中率、锁等待、autovacuum 是否在跑
2. 分析单条慢查询
   - EXPLAIN (ANALYZE, BUFFERS) <sql>; 看实际耗时与扫描行数
   - 警惕：Seq Scan 大表、估算行数与实际严重偏差（统计信息过期）、Nested Loop 放大、外部 Sort（work_mem 不足落盘）
3. 索引策略
   - 高频过滤/连接列建 B-tree；多列等值+范围用复合索引（等值列在前）
   - 只查部分行用部分索引 WHERE；只取索引内列用覆盖索引 INCLUDE
   - 全文/JSONB/数组用 GIN，地理/范围用 GiST
   - 建完查 pg_stat_user_indexes 确认被用；删掉从不命中的冗余索引
4. 改写查询
   - 消除函数包裹索引列（DATE(col) 致失效）、SELECT * → 只取所需列
   - 低效 IN 子查询 → EXISTS；嵌套逻辑 → CTE 拆分；大 OFFSET 分页 → keyset（WHERE id > :last）
5. 配置调优（改后需 reload 或 restart，逐项验证）
   - shared_buffers ≈ 内存 25%；effective_cache_size ≈ 内存 50%~75%
   - work_mem 按「单会话排序/哈希内存 × 并发」估算，勿设过大致 OOM
   - 调 checkpoint_timeout / max_wal_size 平滑写入；按写入量调 autovacuum 触发阈值
6. 维护
   - 定期 ANALYZE 刷新统计信息；VACUUM 回收死元组
   - 查表膨胀，必要时 VACUUM (FULL) 或 pg_repack（FULL 持锁，避开高峰）
7. 监控
   - 持续采集 pg_stat_statements、命中率、膨胀、复制延迟，配告警看趋势

示例

定位最耗时的查询：

-- 需预先 CREATE EXTENSION pg_stat_statements; 并在 shared_preload_libraries 加载
SELECT query, calls, round(mean_exec_time::numeric, 2) AS avg_ms,
       round(total_exec_time::numeric, 2) AS total_ms, rows
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;

看真实执行计划（带缓冲与实际行数）：

EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT o.id, o.amount
FROM orders o
JOIN customers c ON c.id = o.customer_id
WHERE c.region = 'CN' AND o.created_at >= DATE '2025-01-01';
-- 关注：是否 Seq Scan 大表 / estimated 与 actual rows 偏差 / Sort 是否 disk

针对性建索引（复合 + 部分）并核对命中：

CREATE INDEX CONCURRENTLY idx_orders_cust_created
  ON orders (customer_id, created_at)
  WHERE created_at >= DATE '2025-01-01';   -- 部分索引，缩小体积
-- CONCURRENTLY 不锁写，建库高峰也可执行

SELECT relname, idx_scan FROM pg_stat_user_indexes
WHERE relname = 'orders';

查表膨胀与维护：

SELECT relname, n_live_tup, n_dead_tup,
       round(100 * n_dead_tup / nullif(n_live_tup + n_dead_tup, 0), 1) AS dead_pct
FROM pg_stat_user_tables ORDER BY dead_pct DESC NULLS LAST LIMIT 10;

ANALYZE orders;        -- 刷新统计信息（计划变差时优先）
VACUUM (VERBOSE) orders;

给 Agent 的提示词模板：

PG 版本=16。表 orders(id,customer_id,amount,created_at,status)。
现象：<慢查询/高 IO>。已附 EXPLAIN (ANALYZE, BUFFERS) 输出：<粘贴>。
要求：定位瓶颈→给索引/改写/配置方案，每项注明权衡与验证方式；DDL 用 CONCURRENTLY；改配置只给一处并说明 reload/restart。

注意事项

• 用 EXPLAIN (ANALYZE) 看真实耗时而非裸 EXPLAIN 的估算；ANALYZE 会真实执行，写操作慎用（必要时包事务回滚）。
• 生产建/删索引用 CREATE INDEX CONCURRENTLY / DROP INDEX CONCURRENTLY 避免锁写；它不能在事务块内执行，失败会留下 INVALID 索引需清理。
• 配置改动逐项试，记录基线指标；shared_buffers/部分参数需 restart，work_mem 等可 pg_reload_conf()。work_mem 是每排序/哈希节点的上限，会被并发与多节点放大，设过高易 OOM。
• 统计信息过期是计划变差的头号原因——计划异常先 ANALYZE，再谈索引。
• VACUUM FULL 持 ACCESS EXCLUSIVE 锁并重写整表，避开业务高峰；常规膨胀靠 autovacuum + 定期 VACUUM，大表用 pg_repack 在线整理。
• 索引并非越多越好：拖慢写入、占空间，删除前用 pg_stat_user_indexes.idx_scan 确认确实无人使用。
• 任何 DDL、配置、VACUUM FULL 等非只读操作执行前须用户确认并具备回滚预案；本技能产出方案，不擅自在生产执行。

互见

• requires：无。
• related：sql-query-builder（先写对查询，再来本技能调性能）。
• combines_with：csv-data-cleaner（导出 PG 数据做离线清洗时衔接）。

---

采编自 sickn33/antigravity-awesome-skills（MIT）。已适配重写：将原「调用 @other-skill」的编排式流程改写为自包含、可执行的 PostgreSQL 调优 playbook，保留其七阶段骨架与 EXPLAIN ANALYZE、索引类型、shared_buffers/work_mem/effective_cache_size/checkpoint/autovacuum、VACUUM/膨胀、监控等关键约束。