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命令有几种变体，你可以根据需要选择使用。

  1. 基本形式
    这会展示查询的执行计划，但不会执行查询：
EXPLAIN SELECT * FROM your_table WHERE condition;
  1. 带有分析
    这会展示执行计划并实际执行查询，提供实际执行时间等详细信息：
EXPLAIN ANALYZE SELECT * FROM your_table WHERE condition;
  1. 可视化格式
    这个选项将结果格式化为更易读的文本树状结构（在PostgreSQL 13及以上版本中可用）：
EXPLAIN (FORMAT JSON) SELECT * FROM your_table WHERE condition;

或者：

EXPLAIN (FORMAT YAML) SELECT * FROM your_table WHERE condition;
  1. 其他有用参数
  • 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 ms
 Execution Time: 0.029 ms
(4 rows)

通过分析这些信息，你可以更加了解查询的性能瓶颈，并做出相应的优化建议，例如添加索引、更改查询结构等。

谨记：心存敬畏，行有所止。