PostgreSQL SQL 优化指南四句真言（SQL 优化系列 1）

最新推荐文章于 2025-10-29 10:12:01 发布

原创最新推荐文章于 2025-10-29 10:12:01 发布 · 811 阅读

CC 4.0 BY-SA版权

文章标签：

❝
开头还是介绍一下群，如果感兴趣PolarDB ,MongoDB ,MySQL ,PostgreSQL ,Redis, OceanBase, Sql Server等有问题，有需求都可以加群群内有各大数据库行业大咖，可以解决你的问题。加群请联系 liuaustin3 ，（共3300人左右 1 + 2 + 3 + 4 +5 + 6 + 7 + 8 +9）(1 2 3 4 5 6 7群均已爆满，开8群近400 9群 200+，开10群PolarDB专业学习群100+)

暑期过去了，该干正事了。DBA其中的重要工作，SQL优化，至少到目前为止，这个工作还存在。以后这个工作有没有不好说，随着AI的能力的爆发，这个工作被替代也是在时间表上的事情了。

所以搞一个系列，数据库SQL和查询语句优化系列，纪念可能逝去的需求。今天先拿PG开刀，优化SQL的根本是提高单位时间SQL运行的个数，减少SQL运行期间可能触发的锁冲突，降低单位时间数据库CPU,IOPS，内存的消耗。

所以从一个SQL优化的角度上，如果光强调SQL执行的时间快与慢，那就太单纯了。

这里有一个四句真言

减少IO全扫描，合理利用“小”索引，

降低芯片计算量，减少无效排序与哈希，

提高并发与吞吐，避免大锁的冲突，

稳定执行的计划，数量大小都稳定。

PG 的优化和其他数据库相比，更加的复杂和多变。

复杂在哪里？

索引的多变，数据表状态的多变，factor 表的设置初始的多变，与并行的使用与优化。

1 索引的复杂和多变

PG的索引不光是常见的，B-TREE类型的索引，或者形式上的覆盖索引，主键，唯一索引，复合索引等等，PG的索引类型很多，如BRIN，BLOOM，GIN ，Gist等，熟悉多种索引的使用场景，建立合适的索引类型，也是PG DBA需要只晓得知识，因为其他的数据库类型没有这块功能，这就导致大部分其他DBA都忽略了 PG的这块能力和优化的手段。

举例，大量的时间数据的查询建立索引，可以考虑Brin索引的超大表，Brin比Btree索引要小几十倍，甚至上百倍。虽然查询速度上会变慢，但存储和内存的节省也是一种SQL的优化。如你以前使用 BTREE索引查询的时间是 0.01秒，而通过BRIN索引后，查询时间是0.1秒，但索引大小小了100倍，索引大小从50G 变成50MB，这样的优化也算是SQL的一种优化。

2 表factor的初始化设置

这点很少人提到过，这也是其他数据库DBA不具有的知识。在SQL优化中，我们希望我们提取的数据的空间是连续的，PG的原理大家也都理解，由于这个问题，很容易导致频繁UPDATE的表的数据看似应该连续的，却四分五裂。提取数据会导致机械磁盘的磁头频繁的移动，导致物理性的慢，所以PG的数据库建议还是上SSD更好，这里可以通过在建表的时候降低factor的比率，提高SQL在读取连续数据时的速度。

3 降低无效CPU的消耗

这里假设一个例子：

EXPLAIN (ANALYZE, BUFFERS)
SELECT customer_id, SUM(amount)
FROM orders
WHERE amount > 100
GROUP BY customer_id
ORDER BY SUM(amount) DESC
LIMIT 10;

GroupAggregate  (cost=10000..20000 rows=1000 width=40)
                (actual time=1200.500..1500.200 rows=10 loops=1)
  Group Key: customer_id
  ->  Sort  (cost=10000..12000 rows=100000 width=40)
            (actual time=1190.100..1350.400 rows=1000000 loops=1)
        Sort Key: customer_id
        Sort Method: quicksort  Memory: 120MB
        Buffers: shared hit=200000 read=50000
        ->  Seq Scan on orders  (cost=0..8000 rows=1000000 width=40)
                                (actual time=0.020..300.150 rows=1000000 loops=1)
              Filter: (amount > 100)
              Rows Removed by Filter: 200000
Planning Time: 0.100 ms
Execution Time: 1502.003 ms

在查询中，很容易看到第一个问题，全表扫描添加索引，但排序在这里也是一个消耗CPU的点，建立索引我会把索引建立成

CREATE INDEX idx_orders_customer_amount ON orders(customer_id, amount);

有人会问为什么不是

CREATE INDEX idx_orders_customer_amount ON orders(amount,customer_id,);

在这条语句里面包含了分组，分组的机制中就天然包含了排序，将customer_id放到前面，可以有效的进行有序扫描amount > 100的数据扫描，最后拿出的数据也是就满足了分组中的排序需求。

另外还有一个关键点，这里也是希望AI目前无法涉及到的点，DBA的经验。岁数大的DBA都明白，业务逻辑是优化SQL的核心之一，customer_id 和 amount 在其他SQL的查询方式，大脚豆都能想出来，所以问出为什么amount不在复合索引前的，你的经验还是嫩了点，俗称你是一根筋。

4 制造稳定性的执行计划，而不是不稳定的执行计划

我们还以一个实例来说明

EXPLAIN (ANALYZE, BUFFERS)
SELECT *
FROM orders
WHERE customer_id = 123
  AND amount > 1000;

稳定的索引，制造稳定的执行计划。

CREATE INDEX idx_orders_customer_amount ON orders(customer_id);
CREATE INDEX idx_orders_customer_amount ON orders(amount);

不稳定的索引，制造不稳定的执行计划

CREATE INDEX idx_orders_customer_amount ON orders( amount,customer_id);

上面两个索引建立的方式，从稳定性上讲，第一个索引会让执行计划更加稳定，而第二个索引有一定可能性产生问题，通常amount是一个忽大忽小的量，一个客户可能有amount很大的情况，导致行数过多，那么就会导致执行计划放弃索引直接走全表扫描。所以第一个索引建立的时候考虑到这个问题。

那么这里又引出第二个问题，到底我是建立复合索引还是单个多个索引，大部分情况下，我们建议有效的复合索引，有效的复合索引会降低PG在使用多个单个索引处理查询时，使用bitmap index scan 将多个单独的索引合并走交集的情况，这样的方式会消耗CPU，不如复合索引。

所以评估稳定性的问题，还需要考虑你大部分查询的语句的查询情况和查询频率后，才能找到索引建立的更优方案。

至于PG的并行查询，在我们的经验中，一般的设置都不会针对每个SQL超过 4个并行。

PG SQL的优化一篇文章是无法说完整的，SQL的优化也是要凭借对业务的更多了解和之前SQL优化的经验。

但这里需要注意，PG更多的SQL优化在于非SQL本身的一些优化点，而那些优化点是其他的数据库不具有的经验常识。

总结：

PostgreSQL SQL 优化要点总结表

优化方向	说明	示例 / 对比	PG 特有点

1. 索引的复杂和多变	PG 索引种类多：B-TREE、BRIN、BLOOM、GIN、GiST 等，需要 DBA 熟悉场景	- 时间序列大表 → BRIN 节省空间 - 文本搜索 → GIN/GiST - 常规点查 → B-TREE	不同于其他数据库，PG 索引类型极丰富
2. 表 fillfactor 的初始化设置	控制行存储密度，避免频繁更新导致数据碎片，提高顺序扫描性能	- 频繁 UPDATE 的表降低 fillfactor - 让数据连续，减少磁盘随机 IO	其他 DB 很少涉及，PG 特有的调优点
3. 降低无效 CPU 消耗	SQL 优化不仅是加索引，还要减少排序、哈希等 CPU 消耗	示例 SQL：`GROUP BY + ORDER BY` - 索引写成 `(customer_id, amount)` 而不是 `(amount, customer_id)` - 保证分组和排序自然有序	PG 的执行计划依赖经验判断，索引顺序影响很大
4. 稳定执行计划	稳定性比“偶尔快”更重要，避免执行计划飘忽	- 单列索引 `(customer_id)` 更稳定 - `(amount, customer_id)` 在 amount 值分布大时不稳定，可能导致全表扫描	PG 对统计信息和分布敏感，稳定性尤为关键
5. 单索引 vs. 复合索引	大多数情况推荐有效复合索引，避免 bitmap index scan 的 CPU 开销	- 多单列索引 → bitmap 合并，耗 CPU - 合理复合索引 → 更优执行	PG 特别依赖 bitmap scan，复合索引能更稳
6. 并行查询	并行度一般不建议超过 4，否则开销 > 收益	- 并行扫描大表有效 - 但需注意资源竞争	PG 的并行机制复杂，需结合业务测试
7. LIMIT 语句优化	LIMIT 本身很快，但当结果集过大时，排序会拖慢性能	- 尽量结合索引 ORDER BY 使用 - 避免 `LIMIT offset,N` 大偏移，推荐 keyset pagination（基于索引游标翻页）	PG 排序开销随行数急剧增加，需特别注意 OFFSET 的代价
8. 多表 JOIN 优化	JOIN 性能依赖统计信息和索引，顺序不同执行计划差异大	- 为 JOIN 键建立索引 - 小表驱动大表（Nested Loop 效果更佳） - 使用 `EXPLAIN` 检查是否出现 Hash Join 的内存溢出	PG 的 JOIN 算法多变：Nested Loop / Merge Join / Hash Join，需根据场景调优