为什么PostgreSQL查询计划不优？调整执行计划的详细步骤-SQL-PHP中文网

PostgreSQL查询计划不优的根源在于统计信息过时、索引缺失、SQL写法不佳或配置不当。使用EXPLAIN ANALYZE可分析执行计划，识别全表扫描、行数估算偏差、高I/O等瓶颈。据此创建合适索引（如B-tree、GIN、部分索引）、更新统计信息、重写SQL（避免SELECT *、优化WHERE、用EXISTS替代IN）并调整work_mem等参数，形成持续优化闭环。

为什么postgresql查询计划不优？调整执行计划的详细步骤

PostgreSQL查询计划不优，这事儿挺常见的，原因也五花八门。通常来说，它可能是在抱怨统计信息不够新、数据库里压根儿就没建合适的索引、你写的SQL语句本身有点儿‘绕’，又或者是一些核心配置参数没调对。说到底，调整执行计划就像给一个复杂的机器做精密调校，得有耐心，还得知道从哪儿下手。

解决方案

要解决PostgreSQL查询计划不优的问题，我们需要一套系统性的方法，这可不是一蹴而就的。在我看来，它更像是一场侦探游戏，我们需要从多个维度去分析、去尝试、去验证。

首先，最核心的工具就是

EXPLAIN ANALYZE

登录后复制

。它能告诉你查询优化器（Planner）是怎么思考你的SQL的，以及实际执行起来花了多少时间、读了多少行数据、用了哪些操作符。我个人习惯从它的输出中寻找几个关键信号：有没有出现代价高昂的“Seq Scan”（全表扫描）？计划中的

rows

登录后复制

预估值和

actual rows

登录后复制

实际值偏差大不大？

loops

登录后复制

次数多不多？

buffers

登录后复制

里有没有大量读写？这些都是优化器可能“想错了”或者“没法做得更好”的证据。

一旦通过

EXPLAIN ANALYZE

登录后复制

找到了瓶颈，下一步就是针对性地处理。这可能意味着你需要创建或调整索引。索引是PostgreSQL加速查询的利器，但也不是越多越好，或者说不是随便建一个就行。我见过太多因为索引建得不合适，反而拖慢了写入速度的案例。选择正确的索引类型（B-tree、GIN、GiST等），考虑部分索引（Partial Index）和表达式索引（Expression Index），都是需要仔细权衡的。

另一个常被忽视但至关重要的点是统计信息。PostgreSQL的查询优化器高度依赖表和索引的统计信息来估算行数、选择连接顺序和访问路径。如果这些统计信息过时或者不准确，优化器就可能做出错误的决策。手动运行

ANALYZE

登录后复制

命令，或者确保

autovacuum

登录后复制

进程配置得当，定期更新统计信息，是保持查询计划“聪明”的基础。

再来就是SQL语句本身的写法。有时候，一个复杂的查询可以通过简单的重写变得高效。比如，调整

JOIN

登录后复制

的顺序（虽然优化器会尝试优化，但有时人为干预效果更好），将

OR

登录后复制

条件改写成

UNION ALL

登录后复制

，或者用

EXISTS

登录后复制

代替

IN

登录后复制

子查询。这些技巧能显著改变查询计划，让优化器有更多选择。

最后，别忘了PostgreSQL的配置参数。

work_mem

登录后复制

、

shared_buffers

登录后复制

、

effective_cache_size

登录后复制

这些参数对查询性能有着直接影响。如果

work_mem

登录后复制

设置得太小，排序和哈希操作就可能溢出到磁盘，导致I/O瓶颈。调整这些参数需要结合你的服务器硬件和实际工作负载，是个细致活儿。在我看来，这是一个不断迭代和优化的过程，没有一劳永逸的解决方案。

如何使用EXPLAIN ANALYZE诊断PostgreSQL慢查询？

EXPLAIN ANALYZE

登录后复制

是诊断PostgreSQL慢查询的“瑞士军刀”。它的强大之处在于，它不仅展示了查询优化器预期的执行计划（

EXPLAIN

登录后复制

部分），还会实际执行查询并报告真实的运行时间、内存使用和I/O情况（

ANALYZE

登录后复制

部分）。

要使用它，你只需在任何

SELECT

登录后复制

、

INSERT

登录后复制

、

UPDATE

登录后复制

、

DELETE

登录后复制

语句前加上

EXPLAIN ANALYZE

登录后复制

。例如：

EXPLAIN ANALYZE
SELECT
    p.product_name,
    c.category_name,
    COUNT(o.order_id) AS total_orders
FROM
    products p
JOIN
    categories c ON p.category_id = c.category_id
LEFT JOIN
    order_items oi ON p.product_id = oi.product_id
LEFT JOIN
    orders o ON oi.order_id = o.order_id
WHERE
    p.price > 100
GROUP BY
    p.product_name, c.category_name
ORDER BY
    total_orders DESC
LIMIT 10;

登录后复制

输出通常是一棵树状结构，每个节点代表一个操作符（如

Seq Scan

登录后复制

、

Index Scan

登录后复制

、

Hash Join

登录后复制

、

Sort

登录后复制

等）。关键的指标包括：

cost
登录后复制
: 优化器估算的开销，分为启动成本和总成本。这是优化器选择计划的主要依据。
rows
登录后复制
: 优化器估算的返回行数。
actual time
登录后复制
: 实际执行该操作的耗时，分为启动时间和总时间。
loops
登录后复制
: 该操作实际执行的次数。
buffers
登录后复制
: 读写了多少共享内存块（
```
shared hit
```
登录后复制
、
```
shared read
```
登录后复制
）和临时文件（
```
temp read
```
登录后复制
、
```
temp write
```
登录后复制
）。大量
```
shared read
```
登录后复制
或
```
temp write
```
登录后复制
通常意味着I/O瓶颈。
WAL
登录后复制
: 如果是写操作，会显示WAL记录和字节数。

当你看到

Seq Scan

登录后复制

在大型表上出现，并且

actual time

登录后复制

很高时，这通常是个警报，意味着可能需要索引。如果

rows

登录后复制

的估算值与

actual rows

登录后复制

相差巨大，这暗示着统计信息可能不准确，导致优化器做出了错误的决策。此外，高

loops

登录后复制

结合高

actual time

登录后复制

可能指向嵌套循环连接（Nested Loop Join）效率低下。关注那些耗时最长的节点，它们就是你的优化重点。

PostgreSQL索引策略：何时创建，如何选择合适的索引类型？

索引是提升查询性能的基石，但创建和选择索引并非一概而论，需要根据具体场景和数据特性来决定。

何时创建索引？

WHERE
登录后复制
子句中频繁使用的列：这是最常见的场景，无论是等值查询还是范围查询，索引都能大幅提升查找速度。
JOIN
登录后复制
条件中的列：连接操作的效率直接影响查询性能，为连接列创建索引能加速查找匹配行。
ORDER BY
登录后复制
和
GROUP BY
登录后复制
子句中的列：索引可以帮助避免或减少排序操作（
```
Sort
```
登录后复制
），或者加速分组聚合。
高基数列： 即列中唯一值较多的列，索引效果通常更好。对于低基数列（如性别、状态），索引效果可能不明显，甚至可能因为额外的I/O开销而适得其反。

如何选择合适的索引类型？

PostgreSQL提供了多种索引类型，每种都有其适用场景：

B-tree（默认）： 这是最常用也最通用的索引类型。它适用于：

创客贴设计
创客贴设计，一款智能在线设计工具，设计不求人，AI助你零基础完成专业设计！

51

查看详情
- 等值查询（
```
=
```
  登录后复制
  ）
- 范围查询（
```
<
```
  登录后复制
  ,
```
>
```
  登录后复制
  ,
```
<=
```
  登录后复制
  ,
```
>=
```
  登录后复制
  ,
```
BETWEEN
```
  登录后复制
  ）
- ```
LIKE
```
  登录后复制
  模式匹配（当模式不以通配符开头时，如
```
'abc%'
```
  登录后复制
  ）
- ```
ORDER BY
```
  登录后复制
  和
```
GROUP BY
```
  登录后复制
  操作
- 主键和唯一约束默认使用B-tree索引。
GIN (Generalized Inverted Index)： 适用于处理包含多个值的列，如数组、JSONB、全文搜索（
```
tsvector
```
登录后复制
）。它能高效地查找包含特定元素或子文档的行。
GiST (Generalized Search Tree)： 适用于更复杂的、非标准的数据类型，如几何数据（点、线、多边形）、范围类型（
```
tstzrange
```
登录后复制
）、全文搜索等。它能处理重叠和包含等空间或时间关系查询。
BRIN (Block Range Index)： 适用于大型表，且数据在物理存储上具有某种自然顺序的场景（如时间序列数据）。它非常小巧，但只在查询条件与数据的物理存储顺序高度相关时才有效。

其他索引考量：

复合索引 (Compound Index)： 当查询条件涉及多个列时，可以创建复合索引。例如，
```
CREATE INDEX ON users (last_name, first_name);
```
登录后复制
。顺序很重要，查询条件应尽量匹配索引的最左前缀。
部分索引 (Partial Index)： 仅对表中满足特定条件的行建立索引。这可以减小索引大小，提高索引维护效率。例如，
```
CREATE INDEX ON orders (customer_id) WHERE status = 'active';
```
登录后复制
表达式索引 (Expression Index)： 对表达式或函数的结果建立索引。当
```
WHERE
```
登录后复制
子句中频繁使用某个表达式时非常有用。例如，
```
CREATE INDEX ON users ((lower(email)));
```
登录后复制

创建索引时，需要权衡查询性能提升和写入性能下降（每次数据修改都需要更新索引）之间的关系。定期使用

pg_stat_user_indexes

登录后复制

和

pg_stat_all_tables

登录后复制

视图来监控索引的使用情况和膨胀情况，及时调整不必要的索引。

优化PostgreSQL查询语句：常见的重写技巧与最佳实践

很多时候，查询计划不优并不是数据库配置或索引的问题，而是我们编写的SQL语句本身不够“聪明”。通过一些重写技巧，我们可以引导优化器生成更高效的计划。

*避免`SELECT `：** 这是最基本的原则。只选择你需要的列，可以减少数据传输量，有时还能让优化器选择更窄的索引扫描。
优化
```
WHERE
```
登录后复制
子句：
- Sargable Predicates： 确保
```
WHERE
```
  登录后复制
  子句中的条件是“可索引的”（sargable）。这意味着不要在索引列上使用函数或表达式，除非你为该表达式创建了表达式索引。例如，
```
WHERE EXTRACT(YEAR FROM order_date) = 2023
```
  登录后复制
  就无法直接使用
```
order_date
```
  登录后复制
  上的B-tree索引，而
```
WHERE order_date BETWEEN '2023-01-01' AND '2023-12-31'
```
  登录后复制
  则可以。
- 简化复杂条件： 有时复杂的
```
OR
```
  登录后复制
  条件可以分解成多个
```
UNION ALL
```
  登录后复制
  子句，让优化器更容易处理。例如，
```
SELECT * FROM users WHERE status = 'active' OR region = 'EU'
```
  登录后复制
  可能不如
```
SELECT * FROM users WHERE status = 'active' UNION ALL SELECT * FROM users WHERE region = 'EU' AND status != 'active'
```
  登录后复制
  效率高，尤其是在各自条件都有索引的情况下。
```
EXISTS
```
登录后复制
vs.
IN
登录后复制
vs.
JOIN
登录后复制
：
- 对于子查询，当子查询返回的行数非常大时，
```
EXISTS
```
  登录后复制
  通常比
```
IN
```
  登录后复制
  更高效，因为它在找到第一个匹配项后就会停止扫描。
- 如果子查询需要返回列，或者需要聚合，那么使用
```
JOIN
```
  登录后复制
  通常是更好的选择。优化器在处理
```
JOIN
```
  登录后复制
  时有更多的优化策略。
理解
```
JOIN
```
登录后复制
类型和顺序： 尽管PostgreSQL优化器在大多数情况下能选择最优的
```
JOIN
```
登录后复制
顺序，但了解
```
INNER JOIN
```
登录后复制
、
```
LEFT JOIN
```
登录后复制
、
```
RIGHT JOIN
```
登录后复制
、
```
FULL JOIN
```
登录后复制
的语义和性能特点仍然重要。在某些极端复杂的查询中，你可能需要通过
```
SET join_collapse_limit = 1;
```
登录后复制
等方式来强制优化器使用你指定的连接顺序（但这通常不推荐，除非你非常确定）。
使用
```
LIMIT
```
登录后复制
和
OFFSET
登录后复制
时考虑性能：对于分页查询，
```
OFFSET
```
登录后复制
随着偏移量的增大性能会急剧下降，因为它需要扫描并丢弃前面的所有行。在可能的情况下，尝试使用基于上次查询结果的条件来替代
```
OFFSET
```
登录后复制
，例如
```
WHERE id > last_id_from_previous_page LIMIT N
```
登录后复制
。
```
CTE
```
登录后复制
(Common Table Expressions) 的妙用： CTEs（
```
WITH
```
登录后复制
子句）可以提高查询的可读性和模块化，但它们本身不一定能直接提升性能。在某些情况下，优化器可能会将CTE“内联”到主查询中进行优化。但如果CTE被多次引用，PostgreSQL可能会将其物化（materialize），即先计算结果并存储起来，这可能带来性能提升，也可能带来额外的I/O开销，具体取决于优化器的决策。
避免不必要的排序和聚合： 只有在必要时才使用
```
ORDER BY
```
登录后复制
和
```
GROUP BY
```
登录后复制
。如果可以通过索引避免排序，那就尽量利用索引。