PostgreSQL / psql 调优

概述

PostgreSQL 是堆表（heap）单机库。性能首先由统计信息准不准、索引类型对不对、计划是 Seq Scan 还是 Index Scan决定。

语法像 GaussDB，执行不是 MPP。看到 DISTRIBUTE BY HASH、CN/DN、倾斜率，改用 gaussdb-sql-tuning。MySQL / Hive 分别用 mysql-sql-tuning、hsql-tuning。

本技能基线：PostgreSQL 14+（CTE 默认可内联；用 EXPLAIN ANALYZE）。psql 只是客户端。

何时使用

• 写/改 PostgreSQL 建表、索引、JOIN、分页、JSONB、模糊查询
• EXPLAIN ANALYZE 出现 Seq Scan、外部排序、行数估计差几个数量级
• 表膨胀、统计信息过期、部分索引 / 表达式索引 / GIN 选型

不要用本技能处理：GaussDB 分布列、Hive 分区分桶、MySQL utf8mb3、装库运维。

调优收益顺序

统计信息与索引 > SQL 改写 > VACUUM / 锁 > 参数 / 硬件

计划拿着过期 pg_statistic 做选择。先 ANALYZE，再谈 work_mem。

写任何表之前

堆表不会按主键聚簇。主键仍要窄、单调，方便二级索引和分页，不要指望它决定物理顺序。

CREATE TABLE order_info (
    order_id BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    cust_id BIGINT NOT NULL,
    order_status SMALLINT NOT NULL DEFAULT 1,
    amount NUMERIC(12,2) NOT NULL,
    created_at TIMESTAMPTZ NOT NULL,
    title TEXT
);

CREATE INDEX idx_order_cust_created
    ON order_info (cust_id, created_at DESC);

索引：references/indexes.md 类型与函数：references/types.md

SQL 改写速查

完整正反例：references/sql-patterns.md

诊断

默认 EXPLAIN (ANALYZE, BUFFERS)，不要只看 EXPLAIN。

红旗：

• Seq Scan 扫大表
• rows 估计与 actual 差一个数量级以上
• Sort Method: external merge（落盘排序）
• 该走 Index Only Scan 却在 Heap Fetches 很高
• CTE 被 Materialize 后仍被当小结果去 Nested Loop

步骤：references/diagnose.md

交付检查

写完 SQL / 索引后按顺序回答，缺一项就改：

1. EXPLAIN ANALYZE 的估计行数是否离谱？有没有先 ANALYZE？
2. 计划是否还是大表 Seq Scan？索引类型（B-Tree / GIN / 部分 / 表达式）是否匹配谓词？
3. 索引列有没有被 ::date / 函数包住？
4. 分页是否还在用大 OFFSET？
5. 是否还在用 SELECT *、DISTINCT、NOT IN？
6. 这是不是 GaussDB？有没有误写 DISTRIBUTE BY？

常见借口