PostgreSQL 性能调优入门
原创 红石PG 红石PG
2025年02月24日 07:29 陕西
PostgreSQL 中的性能调优，其实也很简单，只需要掌握最常用的方法，就能应付大多数 SQL 的优化了。
PostgreSQL EXPLAIN 语句简介
EXPLAIN语句返回 PostgreSQL 规划器为给定语句生成的执行计划。
EXPLAIN展示 SQL 语句涉及的表将如何通过索引扫描或顺序扫描等进行扫描，以及如果使用多个表，将使用什么样的连接算法。
EXPLAIN语句返回的最重要和最有用的信息是，返回第一行之前的起始成本以及返回完整结果集的总成本。
下面显示了EXPLAIN语句的语法：
EXPLAIN [ ( option [, ...] ) ] sql_statement;复制
其中option可以是以下之一：
ANALYZE [ boolean ]
VERBOSE [ boolean ]
COSTS [ boolean ]
BUFFERS [ boolean ]
TIMING [ boolean ]
SUMMARY [ boolean ]
FORMAT { TEXT | XML | JSON | YAML }复制
boolean指定是否应打开或关闭所选选项。您可以使用TRUE、ON或1来启用该选项，并使用FALSE、OFF或0来禁用该选项。如果省略boolean，则默认为ON。
ANALYZE
ANALYZE选项会先执行sql_statement，然后在返回信息中的实际运行时统计信息，会包括每个计划节点内花费的总运行时间以及实际返回的行数。
ANALYZE语句实际上执行了 SQL 语句并丢弃了输出信息，因此，如果要分析诸如INSERT、UPDATE 或DELETE之类的任何语句而不影响数据，则应该将EXPLAIN ANALYZE包装在事务中，如下所示：
BEGIN;
EXPLAIN ANALYZE sql_statement;
ROLLBACK;复制
VERBOSE
VERBOSE参数允许您显示有关计划的附加信息。该参数默认设置为FALSE。
COSTS
COSTS选项包括每个计划节点的估计启动成本和总成本，以及查询计划中的估计行数和每行的估计宽度。COSTS默认为TRUE。
SETTINGS
包括有关配置参数的信息。具体来说，包括影响查询计划的设置，其值与内置的默认值不同。此参数默认为FALSE。
BUFFERS
该参数添加缓冲区使用情况信息。仅当启用ANALYZE时才能使用BUFFERS。默认情况下，BUFFERS参数设置为FALSE。
TIMING
该参数在输出中包括每个节点的实际启动时间和花费时间。TIMING默认为TRUE，并且仅在启用ANALYZE时才可以使用。
SUMMARY
SUMMARY参数在查询计划之后添加摘要信息，例如总时间。请注意，当使用ANALYZE选项时，默认会包含摘要信息。
FORMAT
指定查询计划的输出格式，例如TEXT、XML、JSON和YAML。该参数默认设置为TEXT。
PostgreSQL EXPLAIN 示例
以下语句显示了对film表进行简单查询的计划：
EXPLAIN SELECT * FROM film;复制
输出如下：
QUERY PLAN
----------------------------------------------------------
Seq Scan on film (cost=0.00..64.00 rows=1000 width=384)
(1 row)复制
以下示例显示了按特定film_id返回电影的查询计划。
EXPLAIN SELECT * FROM film WHERE film_id = 100;复制
这是输出：
QUERY PLAN
------------------------------------------------------------------------
Index Scan using film_pkey on film (cost=0.28..8.29 rows=1 width=384)
Index Cond: (film_id = 100)
(2 rows)复制
由于已在film_id列建立索引，该语句返回了不同的计划。在输出中，规划器使用索引扫描而不是对film表进行顺序扫描。
为了省略成本信息，您可以使用以下COSTS选项：
EXPLAIN (COSTS FALSE)
SELECT *
FROM
film
WHERE
film_id = 100;复制
QUERY PLAN
------------------------------------
Index Scan using film_pkey on film
Index Cond: (film_id = 100)
(2 rows)复制
以下示例显示使用聚合函数的查询计划：
EXPLAIN SELECT COUNT(*) FROM film;复制
输出是：
QUERY PLAN
--------------------------------------------------------------
Aggregate (cost=66.50..66.51 rows=1 width=8)
-> Seq Scan on film (cost=0.00..64.00 rows=1000 width=0)
(2 rows)复制
EXPLAIN ANALYZE 示例
以下示例返回连接多个表的语句的计划：
EXPLAIN
SELECT
f.film_id,
title,
name category_name
FROM
film f
INNER JOIN film_category fc
ON fc.film_id = f.film_id
INNER JOIN category c
ON c.category_id = fc.category_id
ORDER BY
title;复制
输出是：
QUERY PLAN
--------------------------------------------------------------------------------------
Sort (cost=149.64..152.14 rows=1000 width=87)
Sort Key: f.title
-> Hash Join (cost=77.86..99.81 rows=1000 width=87)
Hash Cond: (fc.category_id = c.category_id)
-> Hash Join (cost=76.50..95.14 rows=1000 width=21)
Hash Cond: (fc.film_id = f.film_id)
-> Seq Scan on film_category fc (cost=0.00..16.00 rows=1000 width=4)
-> Hash (cost=64.00..64.00 rows=1000 width=19)
-> Seq Scan on film f (cost=0.00..64.00 rows=1000 width=19)
-> Hash (cost=1.16..1.16 rows=16 width=72)
-> Seq Scan on category c (cost=0.00..1.16 rows=16 width=72)
(11 rows)复制
要将实际运行时统计信息添加到输出中，您需要使用ANALYZE选项执行语句：
EXPLAIN ANALYZE
SELECT
f.film_id,
title,
name category_name
FROM
film f
INNER JOIN film_category fc
ON fc.film_id = f.film_id
INNER JOIN category c
ON c.category_id = fc.category_id
ORDER BY
title;复制
这是输出：
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------
Sort (cost=149.64..152.14 rows=1000 width=87) (actual time=1.373..1.406 rows=1000 loops=1)
Sort Key: f.title
Sort Method: quicksort Memory: 93kB
-> Hash Join (cost=77.86..99.81 rows=1000 width=87) (actual time=0.317..0.762 rows=1000 loops=1)
Hash Cond: (fc.category_id = c.category_id)
-> Hash Join (cost=76.50..95.14 rows=1000 width=21) (actual time=0.294..0.578 rows=1000 loops=1)
Hash Cond: (fc.film_id = f.film_id)
-> Seq Scan on film_category fc (cost=0.00..16.00 rows=1000 width=4) (actual time=0.003..0.074 rows=1000 loops=1)
-> Hash (cost=64.00..64.00 rows=1000 width=19) (actual time=0.286..0.287 rows=1000 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 59kB
-> Seq Scan on film f (cost=0.00..64.00 rows=1000 width=19) (actual time=0.003..0.172 rows=1000 loops=1)
-> Hash (cost=1.16..1.16 rows=16 width=72) (actual time=0.017..0.017 rows=16 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 9kB
-> Seq Scan on category c (cost=0.00..1.16 rows=16 width=72) (actual time=0.011..0.013 rows=16 loops=1)
Planning Time: 0.262 ms
Execution Time: 1.456 ms
(16 rows)复制
EXPLAIN SETTINGS 示例
从 PostgreSQL 12 开始，您可以要求 EXPLAIN 显示影响了执行计划选择决策的任何已更改过的设置。这可能是如下面所示的优化器参数，但也可能是其他与全局设置不同的参数：
SET work_mem = '64MB';
EXPLAIN (analyze, settings)
SELECT count(*) FROM pg_class;复制
输出：
QUERY PLAN
-------------------------------------------------------------------------------------------------------------
Aggregate (cost=20.01..20.02 rows=1 width=8) (actual time=0.066..0.066 rows=1 loops=1)
-> Seq Scan on pg_class (cost=0.00..18.81 rows=481 width=0) (actual time=0.009..0.044 rows=481 loops=1)
Settings: work_mem = '64MB'
Planning Time: 0.062 ms
Execution Time: 0.087 ms
(5 rows)复制
让我们禁用顺序扫描，看看它会如何影响查询计划：
SET enable_seqscan = off;
EXPLAIN (analyze, settings)
SELECT count(*) FROM pg_class;复制
输出：
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=29.43..29.44 rows=1 width=8) (actual time=0.128..0.129 rows=1 loops=1)
-> Index Only Scan using pg_class_tblspc_relfilenode_index on pg_class (cost=0.15..28.23 rows=481 width=0) (actual time=0.045..0.105 rows=481 loops=1)
Heap Fetches: 145
Settings: enable_seqscan = 'off', work_mem = '64MB'
Planning Time: 0.065 ms
Execution Time: 0.149 ms
(6 rows)复制
通过使用 “settings” 和 analyze 选项，您可以确定从全局设置更改了什么参数，因此更容易重现问题并查看发生了什么。
在本教程中，您学习了如何使用 PostgreSQL 的EXPLAIN语句来显示特定 SQL 语句的查询计划。
