PostgreSQL的EXPLAIN使用

EXPLAIN是SQL优化的工具，MYSQL和PostgreSQL都存在EXPLAIN命令，返回结果可以帮助我们了解SQL的执行情况。需要注意MYSQL与PostgreSQL的返回结果样式不同，MYSQL是一张执行表，而PostgreSQL是执行树。

EXPLAIN介绍

EXPLAIN语句返回给定SQL语句的PostgreSQL分析引擎生成的执行计划。展示语句中涉及的表如何被索引扫描或顺序扫描等，如果使用多个表，应用哪种join算法。EXPLAIN 语句返回最重要的、有用的信息是返回第一行之前的起始成本和返回完整结果集的总成本。

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选项,缺省为启用。

ANALYZE

ANALYZE 选项首先执行sql语句，然后返回实际运行时间统计信息。其中包括每个集合节点总耗时及实际返回行数。

ANALYZE 语句实际执行SQL语句并丢弃输出信息，因此如果想分析INSERT, UPDATE, 或 DELETE且不影响数据，应该在事务中包裹EXPLAIN ANALYZE:
```
BEGIN;
    EXPLAIN ANALYZE sql_statement;
ROLLBACK;
```
VERBOSE

VERBOSE 参数可以显示执行集合更多信息，默认为false。
COSTS

COSTS 选项包括每个计划节点的估计启动成本和总成本，以及查询计划中的估计行数和估计行的宽度。默认为TRUE。
BUFFERS

该参数增加信息至buffer。BUFFERS 参数仅当ANALYZE 启用时才能使用。缺省为FALSE。
TIMING

该参数包括实际启动时间及每个节点花费时间。TIMING 缺省为TRUE，仅当ANALYZE 启用时才能使用。
SUMMARY

SUMMARY 参数在查询计划之后摘要信息（如总耗时）。当ANALYZE 启用时，缺省包括摘要信息。
FORMAT

指定查询计划输出格式，如TEXT, XML, JSON, YAML。缺省为TEXT。

PostgreSQL优化器开关

关掉一些优化器更有利与对EXPLAIN结果进行分析

#set enable_bitmapscan = on
#set enable_hashagg = on
#set enable_hashjoin = on
#set enable_indexscan = on
#set enable_indexonlyscan = on
#set enable_material = on
#set enable_mergejoin = on
#set enable_nestloop = on
#set enable_parallel_append = on
#set enable_seqscan = on
#set enable_sort = on
#set enable_tidscan = on
#set enable_partitionwise_join = off
#set enable_partitionwise_aggregate = off
#set enable_parallel_hash = on
#set enable_partition_pruning = on

PostgreSQL扫描方式

Seq Scan

顺序扫描，即数据从头到尾扫描，适合连续的数据读取，不适合分布随机的数据。

Index Scan

先扫描索引，再根据得到的key获取具体数据，涉及到随机读，因此如果索引扫描得到的数据量过大时，大量随机读也会带来很大的性能损失。

-- 通过索引获取少量数据
explain select * from content where id = 100;
-- sql执行计划
Index Scan using content_pkey on content  (cost=0.28..8.29 rows=1 width=739)
  Index Cond: (id = 100)

Index Only Scan

获取的目标字段就是索引的字段，因此仅扫描索引即可，即所谓的索引覆盖

-- 直接获取索引的列
explain select id from content where id = 100;
-- sql执行计划
Index Only Scan using content_pkey on content  (cost=0.28..8.29 rows=1 width=4)
  Index Cond: (id = 100)

位图扫描

核心是传统的index scan每次从索引中去取一个tuple的指针，然后立马去表中取数据，每一次会造成一次随机io。如果数据量较多的情况下，会比较低效。而bitmap scan一次性将符合条件的tuple-pointers全部取出来，然后在内存中进行地址排序，然后去取出数据，这时的读取数据由于进行的地址排序，读取时就变成了顺序的读。其实就是一个随机读转化为顺序读取的过程，但是取出的数据由于进行了地址的排序，就没有顺序。同时，对于limit这种sql，bitmap index scan这种就不适合，因为它一次会取出所有数据。
- BitmapHeap Scan
- BitmapIndex Scan
```
-- 通过索引获取较多数据
explain select * from content where id < 100;
-- sql执行计划
Bitmap Heap Scan on content  (cost=5.08..124.19 rows=103 width=739)
  Recheck Cond: (id < 100)
  ->  Bitmap Index Scan on content_pkey  (cost=0.00..5.05 rows=103 width=0)
        Index Cond: (id < 100)
```

一般来说索引覆盖 > 索引扫描 > 索引+位图 > 顺序扫描，但也并非绝对。

EXPLAIN返回

执行简单的sql语句

EXPLAIN SELECT * FROM "t_comm_param_config" WHERE id = 1

执行结果解析

https://www.bilibili.com/read/cv14410974/

https://www.pianshen.com/article/2126679670/

PostgreSQL的EXPLAIN使用

PostgreSQL的EXPLAIN使用

EXPLAIN介绍

PostgreSQL优化器开关

PostgreSQL扫描方式

EXPLAIN返回

评论