如何在PostgreSQL中优化复杂查询？教你编写高效SQL的步骤

答案是优化PostgreSQL复杂查询需系统化分析执行计划、合理创建索引、重写SQL语句、调整配置参数并定期维护。首先通过pg_stat_statements定位慢查询，再用EXPLAIN ANALYZE分析执行路径，识别Seq Scan、高耗时节点等瓶颈；根据WHERE、JOIN、ORDER BY等条件创建B-tree、GIN等合适索引，避免过度索引；重写非Sargable条件、优先使用JOIN和EXISTS、用UNION ALL替代UNION、避免SELECT *；调整shared_buffers、work_mem等参数以提升内存利用；定期VACUUM ANALYZE更新统计信息，REINDEX优化索引结构。整个过程需迭代验证，精准施策而非盲目尝试。

PostgreSQL中优化复杂查询，核心在于理解数据库如何执行你的SQL，然后有针对性地进行调整。这通常涉及对查询执行计划的深入分析，合理利用索引，精妙地重写SQL语句，以及恰当配置数据库参数。这更像是一场侦探游戏，你需要找到性能瓶颈，然后像外科医生一样精准施治，而不是盲目地尝试各种“灵丹妙药”。在我看来，这是一个不断迭代、试错和学习的过程。

解决方案

优化PostgreSQL复杂查询并非一蹴而就，它通常遵循一套系统化的步骤，而这套步骤，在我多年的实践中，被证明是相当有效的：

1. 识别并定位问题查询： 你不能优化一个你不知道它慢的查询。最直接的方法是使用

pg_stat_statements

2. 深入分析查询执行计划： 这是优化的灵魂。使用

EXPLAIN ANALYZE

Seq Scan

Index Scan

Hash Join

Nested Loop Join

Sort

EXPLAIN ANALYZE

3. 精心设计和管理索引： 索引是提升查询速度的利器，但并非越多越好。你需要根据

WHERE

JOIN

ORDER BY

GROUP BY

INSERT

UPDATE

DELETE

4. 优化SQL语句的写法： 有时候，换一种表达方式，数据库的优化器就能找到更好的执行路径。

• *避免`SELECT `：** 只选取你需要的列，减少网络传输和内存消耗。
• Sargable条件： 确保

  WHERE

  登录后复制
  子句中的条件能够利用索引。比如

  column = value

  登录后复制
  是好的，而

  function(column) = value

  登录后复制
  通常会阻止索引使用（除非你创建了表达式索引）。
• 巧用

  JOIN

  登录后复制
  与

  EXISTS

  登录后复制
  ： 在某些场景下，

  JOIN

  登录后复制
  或

  EXISTS

  登录后复制
  子句比

  IN

  登录后复制
  子句中的子查询效率更高，尤其是当子查询返回大量数据时。

• UNION ALL

  登录后复制
  vs

  UNION

  登录后复制
  ： 如果你不需要去除重复行，使用

  UNION ALL

  登录后复制
  ，它比

  UNION

  登录后复制
  快得多，因为它省去了排序和去重步骤。

• LIMIT

  登录后复制
  和

  OFFSET

  登录后复制
  的陷阱： 大量的

  OFFSET

  登录后复制
  会导致数据库扫描并丢弃大量行，性能极差。考虑使用基于键的（Keyset）分页方式。
• CTE（Common Table Expressions）的妙用： CTE能提高SQL的可读性，虽然PostgreSQL通常会将其内联，但有时通过

  MATERIALIZED

  登录后复制
  提示可以强制其具体化，这在某些复杂查询中可能带来性能提升。

5. 调整数据库配置参数： PostgreSQL有大量的配置参数，其中一些对查询性能影响巨大。

• shared_buffers

  登录后复制
  ：用于缓存数据块，越大越好，但不能超过物理内存的25%左右。

• work_mem

  登录后复制
  ：用于排序和哈希表操作的内存。如果

  EXPLAIN ANALYZE

  登录后复制
  显示有

  Sort Method: external merge Disk

  登录后复制
  或

  HashAggregate

  登录后复制
  溢出到磁盘，增加此值会有帮助。

• effective_cache_size

  登录后复制
  ：告诉优化器操作系统大概有多少内存可以用于缓存数据，影响优化器对索引使用的倾向。

• random_page_cost

  登录后复制
  ：调整随机I/O的成本，影响优化器对索引扫描和全表扫描的选择。

6. 定期进行数据库维护： 统计信息是优化器做出决策的依据。

VACUUM ANALYZE

autovacuum

REINDEX

如何有效解读PostgreSQL的查询执行计划？

要优化查询，首先得“看懂”数据库的“想法”，也就是它的执行计划。

EXPLAIN ANALYZE

首先，你要关注每个节点的

actual time

total time

actual rows

rows

ANALYZE

再者，留意操作类型。

Seq Scan

Filter

Index Scan

Bitmap Heap Scan

Bitmap Heap Scan

Index Scan

连接操作（

JOIN

Nested Loop Join

Hash Join

Merge Join

work_mem

Hash Join

spill to disk

work_mem

最后，别忘了看

Buffers

shared hit

shared read

shared read

什么时候应该为PostgreSQL表创建索引？

创建索引的时机，说白了就是当你的查询在某个列上“工作”得特别频繁，而且工作量还挺大的时候。我个人的经验是，如果你发现一个查询因为某个条件而慢得像蜗牛，那这个条件涉及的列很可能需要索引。

一览AI编剧

创意生成+情节生成+脚本生成，AI编剧3步走，AI自动帮你搞定剧情！

87

查看详情

具体来说：

• WHERE

  登录后复制
  子句中的过滤条件： 这是最常见的场景。比如

  SELECT * FROM users WHERE email = 'xxx@example.com';

  登录后复制
  ，

  email

  登录后复制
  列就应该有索引。

• JOIN

  登录后复制
  条件中的列： 几乎所有的

  JOIN

  登录后复制
  操作，其连接键都应该有索引。比如

  SELECT u.name, o.order_id FROM users u JOIN orders o ON u.id = o.user_id;

  登录后复制
  ，

  users.id

  登录后复制
  和

  orders.user_id

  登录后复制
  都应该有索引。特别是外键列，它们几乎总是连接条件，所以给外键列创建索引是一个非常好的习惯。

• ORDER BY

  登录后复制
  和

  GROUP BY

  登录后复制
  子句中的列： 如果查询结果需要排序或分组，索引可以帮助PostgreSQL避免昂贵的排序操作。一个覆盖了

  ORDER BY

  登录后复制
  和

  WHERE

  登录后复制
  条件的复合索引尤其有效。

• DISTINCT

  登录后复制
  操作中的列：

  DISTINCT

  登录后复制
  也常常涉及到排序和去重，索引同样能加速这一过程。
• 高基数列： 也就是那些包含大量不同值的列（比如用户ID、邮箱地址）。在这些列上创建索引通常效果最好。
• 特定数据类型： 对于JSONB、全文搜索（

  tsvector

  登录后复制
  ）或地理空间数据（

  geometry

  登录后复制
  ），你需要使用专门的索引类型，如GIN或GiST。

然而，索引并非万能药，也不是越多越好。

• 小表不需要： 对于只有几百行甚至更少的表，全表扫描可能比索引扫描更快，因为索引本身也有开销。
• 低基数列： 像布尔值（

  is_active

  登录后复制
  ）这种只有少数几个值的列，单独创建索引意义不大，除非它是复合索引的一部分，且能显著减少扫描的数据量。
• 写入密集型表： 如果你的表

  INSERT

  登录后复制
  、

  UPDATE

  登录后复制
  、

  DELETE

  登录后复制
  操作非常频繁，而查询相对较少，那么过多的索引会显著拖慢写入速度，因为每次数据变更，所有相关索引也需要更新。
• 列的更新频率： 如果一个列经常被更新，那么为它创建索引的维护成本也会更高。

总结一下，创建索引的决策需要权衡查询性能提升和写入性能下降以及存储空间增加的成本。通常，通过

EXPLAIN ANALYZE

Seq Scan

Sort

如何编写更易于PostgreSQL优化器理解和执行的SQL？

编写高效的SQL，很大程度上是编写“友善”的SQL，让PostgreSQL的查询优化器能够更容易地理解你的意图，并选择最佳的执行路径。这不仅仅是语法正确，更是关于如何表达你的数据需求。

1. 使用“Sargable”条件： “Sargable”是一个很重要的概念，它指的是

WHERE

WHERE date_trunc('day', created_at) = '2023-01-01'

created_at

WHERE created_at >= '2023-01-01' AND created_at < '2023-01-02'

WHERE col + 1 = 10

WHERE col = 9

2. 优先使用

JOIN

JOIN

EXISTS

SELECT * FROM users WHERE id IN (SELECT user_id FROM orders WHERE amount > 100)

SELECT u.* FROM users u JOIN orders o ON u.id = o.user_id WHERE o.amount > 100;

3. 利用

EXISTS

EXISTS

IN

COUNT > 0

SELECT * FROM users u WHERE EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.id AND o.status = 'pending');

SELECT * FROM users u WHERE u.id IN (SELECT user_id FROM orders WHERE status = 'pending');

4.

UNION ALL

UNION

UNION

DISTINCT

UNION ALL

5. 警惕

LIMIT

OFFSET

OFFSET

SELECT * FROM items WHERE (id > last_id OR (id = last_id AND created_at > last_created_at)) ORDER BY id, created_at LIMIT N;

*6. 避免`SELECT `：** 这看起来是小事，但只选择你需要的列可以减少I/O、网络传输和内存消耗。特别是当表有很多列或包含大型文本/JSONB列时，差异会很明显。

7. 善用

UPDATE FROM

DELETE FROM

UPDATE

DELETE

FROM

WHERE

UPDATE products p SET price = p.price * 1.1 FROM categories c WHERE p.category_id = c.id AND c.name = 'Electronics';

编写高效SQL并非一门玄学，它更多的是一种思维方式：站在优化器的角度去思考，它会如何解析我的指令？我怎样才能让它少做无用功？

以上就是如何在PostgreSQL中优化复杂查询？教你编写高效SQL的步骤的详细内容，更多请关注php中文网其它相关文章！