PostgreSQL查看sql的执行计划
基础信息
OS版本:Red Hat Enterprise Linux Server release 7.9 (Maipo)
DB版本:16.2
pg软件目录:/home/pg16/soft
pg数据目录:/home/pg16/data
端口:5777
在PostgreSQL中,查看SQL查询的执行计划是性能调优和问题诊断的重要步骤。PostgreSQL提供了一个叫做EXPLAIN
的命令,可以让你查看查询的执行计划。通过EXPLAIN
命令,你可以看到查询将如何执行,包括访问表的方法和使用的索引,预计的执行成本等。
使用EXPLAIN
命令
EXPLAIN
命令有几种变体,你可以根据需要选择使用。
-
基本形式
这会展示查询的执行计划,但不会执行查询:
EXPLAIN SELECT * FROM your_table WHERE condition;
-
带有分析
这会展示执行计划并实际执行查询,提供实际执行时间等详细信息:
EXPLAIN ANALYZE SELECT * FROM your_table WHERE condition;
-
可视化格式
这个选项将结果格式化为更易读的文本树状结构(在PostgreSQL 13及以上版本中可用):
EXPLAIN (FORMAT JSON) SELECT * FROM your_table WHERE condition;
或者:
EXPLAIN (FORMAT YAML) SELECT * FROM your_table WHERE condition;
-
其他有用参数
- BUFFERS: 显示缓冲区使用情况。
- VERBOSE: 提供更详细的信息。
- COSTS: 显示执行成本(默认启用)。
- TIMING: 显示每个操作消耗的时间(在
EXPLAIN ANALYZE
中默认启用)。
示例:
EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM your_table WHERE condition;
输出解释
EXPLAIN
和 EXPLAIN ANALYZE
的输出通常包含以下信息:
- Node Type: 表示执行计划的每个节点的类型,如Seq Scan(顺序扫描)、Index Scan(索引扫描)等。
- Cost: 预估的开始和结束成本,单位是cost,主要用来比较不同的查询计划。
- Rows: 预估返回的行数。
- Width: 每行的预估字节宽度。
- Actual Time: 实际执行的时间(仅在
EXPLAIN ANALYZE
中出现)。 - Loops: 循环次数(仅在
EXPLAIN ANALYZE
中出现)。
使用注意事项
- 测试环境:
EXPLAIN ANALYZE
会实际执行查询,因此应在测试环境或在不影响生产的情况下使用。 - 缓存影响: 多次执行同一查询可能会因为数据缓存导致不准确的分析结果。在分析前重启数据库或清除缓存可能会有所帮助。
- 复合查询: 对复杂查询或涉及多个表的查询,通常需要更详细的执行计划分析,查看各个步骤的执行成本和时间。
示例 1:基本形式
查询:
postgres=# SELECT * FROM t2 WHERE id = '99';id | name
----+---------99 | haha_99
(1 row)
执行计划:
postgres=# EXPLAIN SELECT * FROM t2 WHERE id = '99';QUERY PLAN
------------------------------------------------------------------Index Scan using idx_t2 on t2 (cost=0.28..8.29 rows=1 width=12)Index Cond: (id = 99)
(2 rows)
经过格式化的执行计划
postgres=# EXPLAIN (FORMAT YAML) SELECT * FROM t2 WHERE id = '99';QUERY PLAN
-------------------------------- Plan: +Node Type: "Index Scan" +Parallel Aware: false +Async Capable: false +Scan Direction: "Forward"+Index Name: "idx_t2" +Relation Name: "t2" +Alias: "t2" +Startup Cost: 0.28 +Total Cost: 8.29 +Plan Rows: 1 +Plan Width: 12 +Index Cond: "(id = 99)"
(1 row)postgres=#
示例 2:带有分析信息
查询:
postgres=# EXPLAIN ANALYZE SELECT * FROM t2 WHERE id = '99';QUERY PLAN
------------------------------------------------------------------------------------------------------------Index Scan using idx_t2 on t2 (cost=0.28..8.29 rows=1 width=12) (actual time=0.014..0.015 rows=1 loops=1)Index Cond: (id = 99)Planning Time: 0.063 msExecution Time: 0.029 ms
(4 rows)
通过分析这些信息,你可以更加了解查询的性能瓶颈,并做出相应的优化建议,例如添加索引、更改查询结构等。
谨记:心存敬畏,行有所止。