通过理解PostgreSQL的执行计划，可以精准定位性能瓶颈，并进行有效的优化。本文将介绍如何使用PostgreSQL的执行计划进行业务SQL优化。

一、什么是执行计划？

执行计划（Execution Plan）是数据库管理系统（DBMS）用来描述查询如何执行的详细过程。PostgreSQL在接收到SQL查询后，会通过查询优化器生成执行计划，以确定最优的查询执行路径。执行计划不仅可以帮助我们了解数据库如何访问数据，还能揭示潜在的性能瓶颈。

执行计划的核心要素

• 扫描操作：包括Seq Scan（顺序扫描）和Index Scan（索引扫描），决定了数据库如何读取表中的数据。

• 连接操作：如Nested Loop（嵌套循环连接）、Hash Join（哈希连接）和Merge Join（合并连接），它们影响多表查询的性能。

• 排序和聚合操作：例如Sort（排序）和Aggregate（聚合），这些操作会影响查询的执行时间，尤其在处理大数据集时。

二、PostgreSQL执行计划的生成原理

PostgreSQL的查询优化器通过分析SQL语句和数据库的统计信息，生成多个执行计划，并选择其中代价最低的计划。

1、查询优化器的工作机制

• 基于代价的优化（Cost-Based Optimization, CBO）：PostgreSQL使用代价模型评估不同执行计划的成本，选择代价最低的计划来执行。代价模型考虑了I/O操作、CPU使用率和内存消耗等因素。

• 统计信息的作用：统计信息是优化器决策的重要依据。它们包含表中的行数、数据分布和索引可用性等信息，通过ANALYZE命令维护。

2、EXPLAIN 命令的使用

• EXPLAIN：生成并显示查询的执行计划，而不执行查询本身。通过分析这些计划，我们可以了解查询的具体执行步骤。

• EXPLAIN ANALYZE：在执行查询的同时生成执行计划，并显示实际的执行时间和行数，便于更准确地评估查询性能。

EXPLAIN ANALYZE SELECT * FROM orders WHERE customer_id = 123;

三、执行计划的底层实现

PostgreSQL执行计划的生成和执行依赖于多个底层组件，这些组件协同工作，确保查询的高效执行。

1、执行计划生成的核心组件

• 查询解析器（Parser）：将SQL语句解析为抽象语法树（AST），是生成执行计划的第一步。

• 查询重写器（Rewriter）：对解析后的AST进行语义检查和优化，例如视图展开和查询简化。

• 查询优化器（Optimizer）：根据代价模型，生成多个执行计划并选择最优方案。

2、执行器（Executor）的工作流程

• 执行计划的执行：执行器根据优化器选择的执行计划，逐步执行各个操作，如表扫描、连接、排序等。

• 缓存机制：PostgreSQL会缓存执行计划，以便相同的查询可以直接使用缓存，避免重复优化，提高性能。

四、案例分析：如何通过执行计划优化业务SQL

在这一部分，我们将通过具体的实验案例，展示如何使用PostgreSQL的执行计划来优化业务SQL性能。每个案例将包括问题描述、执行计划输出分析以及优化后的SQL示例。

案例一：全表扫描的优化

问题描述：

在查询订单表（orders）时，发现数据库进行了全表扫描（Seq Scan），导致查询性能低下。此查询需要获取特定日期后的订单记录。

原始查询：

EXPLAIN ANALYZE SELECT * FROM orders WHERE order_date > '2024-01-01';

执行计划输出：

Seq Scan on orders (cost=0.00..458.00 rows=10000 width=64) (actual time=0.015..10.274 rows=5000 loops=1)

Filter: (order_date > '2024-01-01'::date)

Planning Time: 0.205 ms

Execution Time: 10.345 ms

分析：

• Seq Scan 表示PostgreSQL对整个订单表进行了顺序扫描，读取所有行后再进行过滤。这在数据量较大时，导致了较高的查询成本和较长的执行时间。

• Rows 表示预计返回的行数和实际返回的行数。实际的5000行表明查询结果集较大，但因为是全表扫描，查询效率较低。

优化方案：

为order_date列创建索引，使查询能够使用索引扫描，而不是全表扫描。

优化后的SQL：

CREATE INDEX idx_order_date ON orders(order_date);

EXPLAIN ANALYZE SELECT * FROM orders WHERE order_date > '2024-01-01';

优化后执行计划输出：

Index Scan using idx_order_date on orders (cost=0.29..230.75 rows=5000 width=64) (actual time=0.015..5.274 rows=5000 loops=1)

Index Cond: (order_date > '2024-01-01'::date)

Planning Time: 0.145 ms

Execution Time: 5.312 ms

优化效果分析：

• Index Scan 取代了 Seq Scan，表示数据库使用了索引进行扫描，大幅度减少了需要扫描的行数。

• 查询执行时间从 10.345 ms 减少到了 5.312 ms，性能提升明显。

案例二：多表连接的优化

问题描述：

查询需要从订单表和客户表中获取订单和对应客户的信息。然而，由于缺乏合适的索引，查询执行时间过长。

原始查询：

EXPLAIN ANALYZE

SELECT o.order_id, c.customer_name

FROM orders o

JOIN customers c ON o.customer_id = c.customer_id;

执行计划输出：

Hash Join (cost=35.00..70.75 rows=1000 width=64) (actual time=10.105..40.456 rows=1000 loops=1)

Hash Cond: (o.customer_id = c.customer_id)

-> Seq Scan on orders o (cost=0.00..35.00 rows=1000 width=32) (actual time=0.015..10.274 rows=1000 loops=1)

-> Hash (cost=17.50..17.50 rows=1000 width=32) (actual time=10.074..10.074 rows=1000 loops=1)

-> Seq Scan on customers c (cost=0.00..17.50 rows=1000 width=32) (actual time=0.015..8.774 rows=1000 loops=1)

Planning Time: 0.215 ms

Execution Time: 40.562 ms

分析：

• Hash Join 表示使用哈希连接，但由于两个表都进行了顺序扫描（Seq Scan），导致了较高的I/O成本和较长的执行时间。

优化方案：

为customer_id列创建索引，使连接操作能够利用索引扫描，从而减少扫描行数并提高连接效率。

优化后的SQL：

CREATE INDEX idx_orders_customer_id ON orders(customer_id);

CREATE INDEX idx_customers_customer_id ON customers(customer_id);

EXPLAIN ANALYZE

SELECT o.order_id, c.customer_name

FROM orders o

JOIN customers c ON o.customer_id = c.customer_id;

优化后执行计划输出：

Nested Loop (cost=0.58..45.75 rows=1000 width=64) (actual time=0.014..15.234 rows=1000 loops=1)

-> Index Scan using idx_customers_customer_id on customers c (cost=0.29..22.50 rows=1000 width=32) (actual time=0.015..5.014 rows=1000 loops=1)

-> Index Scan using idx_orders_customer_id on orders o (cost=0.29..22.50 rows=1000 width=32) (actual time=0.014..8.714 rows=1000 loops=1)

Planning Time: 0.185 ms

Execution Time: 15.456 ms

优化效果分析：

• Nested Loop 连接替代了 Hash Join，结合索引扫描，显著减少了扫描的行数。

• 执行时间从 40.562 ms 减少到了 15.456 ms，优化效果显著。

案例三：排序操作的优化

问题描述：

查询需要按订单日期排序输出，但由于未使用索引，查询性能较差，尤其在处理大数据量时，排序操作消耗大量资源。

原始查询：

EXPLAIN ANALYZE SELECT * FROM orders ORDER BY order_date DESC;

执行计划输出：

Sort (cost=230.00..245.00 rows=10000 width=64) (actual time=10.105..30.456 rows=10000 loops=1)

Sort Key: order_date DESC

Sort Method: quicksort Memory: 1024kB

-> Seq Scan on orders (cost=0.00..210.00 rows=10000 width=64) (actual time=0.015..10.274 rows=10000 loops=1)

Planning Time: 0.205 ms

Execution Time: 30.512 ms

分析：

• Sort 表示对结果集进行了排序操作，使用了快速排序算法，但由于是全表扫描，导致排序效率较低。

• Sort Method 显示排序方法为 quicksort，虽然速度较快，但对大数据量仍然消耗大量资源。

优化方案：

通过在order_date列上创建索引，减少排序操作的资源消耗。

优化后的SQL：

CREATE INDEX idx_order_date_desc ON orders(order_date DESC);

EXPLAIN ANALYZE SELECT * FROM orders ORDER BY order_date DESC;

优化后执行计划输出：

Index Scan using idx_order_date_desc on orders (cost=0.29..210.75 rows=10000 width=64) (actual time=0.015..20.274 rows=10000 loops=1)

Planning Time: 0.145 ms

Execution Time: 20.345 ms

优化效果分析：

• Index Scan 取代了排序操作，因为索引已经按照需要的顺序组织数据，因此不再需要额外的排序。

• 查询执行时间从 30.512 ms 减少到了 20.345 ms，性能提升明显。

通过以上案例，我们可以看到，使用PostgreSQL的执行计划分析可以显著提高业务SQL的性能。通过合理使用索引和优化数据库配置，可以避免不必要的全表扫描、降低连接成本，并减少排序操作的资源消耗。这不仅可以提升数据库的整体响应速度，还可以为系统的稳定运行提供保障。

五、优化SQL的实用技巧

在实际的数据库优化过程中，掌握一些实用技巧不仅能帮助你更好地理解SQL性能问题，还能有效提升查询效率。以下是针对PostgreSQL的几个详细优化技巧，结合了索引管理、数据库配置、查询写法优化等方面。

1、索引的合理使用与管理

索引是优化SQL性能最直接、最有效的手段之一。合理使用索引可以大幅度减少查询的扫描范围，提高查询速度。

1）索引类型选择

• B-Tree索引：这是PostgreSQL中最常用的索引类型，适用于大多数查询，尤其是等值查询和范围查询。

示例：

CREATE INDEX idx_customer_id ON customers(customer_id);

• GIN索引：适用于全文搜索、数组类型列的查询。

示例：

CREATE INDEX idx_fts ON documents USING GIN(to_tsvector('english', content));

• GiST索引：适用于地理空间数据类型、模糊匹配等。

示例：

CREATE INDEX idx_gist_location ON locations USING GiST(geom);

• HASH索引：适用于等值查询，但在PostgreSQL中应用较少，因为B-Tree索引通常足够高效。

示例：

CREATE INDEX idx_hash_email ON users USING HASH(email);

2）覆盖索引（Covering Index）

通过在索引中包含所有需要查询的字段，可以避免查询过程中回表操作，从而提高查询性能。

示例：

CREATE INDEX idx_orders_cover ON orders(customer_id, order_date, total_amount);

这样查询时，如果查询的字段都在索引中，PostgreSQL可以直接从索引中返回结果，避免扫描表数据。

3）索引维护

• 定期重建索引：由于表的频繁插入、更新、删除操作，索引可能会变得不再紧凑，影响性能。定期使用REINDEX命令重建索引，可以提高查询效率。

示例：

REINDEX INDEX idx_customer_id;

• 删除不必要的索引：过多的索引会增加INSERT、UPDATE、DELETE操作的开销，定期审查并删除冗余或不常用的索引。

2、数据库配置的优化

PostgreSQL的配置参数对查询性能有直接影响，合理配置这些参数可以显著提高性能。

1）内存配置

• shared_buffers：决定PostgreSQL使用多少内存来缓存数据页。一般设置为服务器内存的25%-40%。

示例：

shared_buffers = 8GB

• work_mem：每个查询操作（如排序和哈希表）可使用的内存量。提高work_mem有助于减少磁盘I/O，但不要设置得过高，以免系统内存耗尽。

示例：

work_mem = 64MB

• maintenance_work_mem：用于VACUUM、CREATE INDEX等维护操作的内存。适当提高可以加快这些操作的速度。

示例：

maintenance_work_mem = 512MB

2）自动化维护

• autovacuum：定期清理过期的行并更新统计信息，保持表和索引的健康状态。确保autovacuum功能开启，并适当调整相关参数以适应工作负载。

示例：

autovacuum = on

autovacuum_naptime = 1min

autovacuum_vacuum_threshold = 50

• ANALYZE：更新表的统计信息，优化器基于这些信息生成更有效的执行计划。可以通过自动化任务定期运行ANALYZE。

示例：

ANALYZE customers;

3、SQL查询的优化写法

通过优化SQL语句的写法，可以大幅度提升查询的效率。

1）避免SELECT *

查询只选择需要的列，避免使用SELECT *，减少不必要的数据传输和处理。

示例：

SELECT customer_id, order_date, total_amount FROM orders WHERE order_date > '2024-01-01';

2）使用子查询优化JOIN

在某些情况下，将复杂的JOIN操作转换为子查询可以提高性能，尤其是当子查询可以被优化器简化时。

示例：

SELECT * FROM customers WHERE customer_id IN (SELECT customer_id FROM orders WHERE order_date > '2024-01-01');

3）限制结果集

对于返回大量数据的查询，使用LIMIT和OFFSET限制结果集大小，可以减少查询负担。

示例：

SELECT * FROM orders ORDER BY order_date DESC LIMIT 100 OFFSET 0;

4）使用WHERE条件进行数据过滤

在JOIN前尽量使用WHERE条件过滤数据，以减少参与连接的数据量。

示例：

SELECT o.order_id, c.customer_name

FROM orders o

JOIN customers c ON o.customer_id = c.customer_id

WHERE o.order_date > '2024-01-01';

六、总结

通过分析PostgreSQL的执行计划，可以更好地理解SQL查询的执行过程，并找到潜在的性能瓶颈。结合实际项目中的经验，不仅可以优化业务SQL的性能，还能为系统的长久稳定运行提供支持。

作者丨TechCraft

来源丨https://blog.csdn.net/jinhope/article/details/140839694

dbaplus社群欢迎广大技术人员投稿，投稿邮箱：editor@dbaplus.cn