目录

第9章 PostgreSQL中执行计划（一）

9.1 执行计划的解释

9.1.1 EXPLAIN 命令

9.1.2 EXPLAIN输出结果解释

9.1.3 EXPLAIN使用示例

9.1.4 全表扫描

9.1.5 索引扫描

9.1.6 位图扫描

9.1.7 条件过滤

9.1.8 Nestloop Join

9.1.9 Hash Join

9.1.10 Merge Join

第9章 PostgreSQL中执行计划（一）

9.1 执行计划的解释

9.1.1 EXPLAIN 命令

在关系型数据库中，一般使用explain命令来显示SQL的执行计划，只是不同的数据库中，这个命令的具体格式会有一些差别。

postgres 固定执行计划 pgsql的执行计划_postgres 固定执行计划

 PostgresQL中explain命令的格式如下:

EXPLAIN [ ( option [, ...] )] statement
EXPLAIN [ ANALYZE ] [ VERBOSE ] statement

命令的可选选项“options”为:

ANALYZE [ boolean ]
VERBOSE [ boolean ]
COSTS   [ boolean ]
BUFFERS [ boolean ]
FORMAT {TEXT | XML |JSoN | YAML }

ANALYZE选项通过实际执行的SQL来获得相应的执行计划。因为它真正被执行，所以可以看到执行计划每一步花掉了多少时间，以及它实际返回的行数。

VERBOSE选项用于显示计划的附加信息。这些附加信息有:计划树中每个节点输出的各个列，如果触发器被触发，还会输出触发器的名称。该选项值默认为FALSE。
COSTS选项显示每个计划节点的启动成本和总成本，以及估计行数和每行宽度。该选项值默认为TRUE。
BUFFERS选项显示关于缓冲区使用的信息。该参数只能与ANALYZE 参数一起使用。显示的缓冲区信息包括共享块、本地块和临时块读和写的块数。共享块、本地块和临时块分别包含表和索引、临时表和临时索引，以及在排序和物化计划中使用的磁盘块。上层节点显示出来的块数包括其所有子节点使用的块数。该选项值默认为FALSE。
FORMAT选项指定输出格式，输出格式可以是TEXT、XML、JSON或YAML。非文本输出包含与文本输出格式相同的信息，但其他程序更容易解析。该参数默认为TEXT。

9.1.2 EXPLAIN输出结果解释

下面以一个最简单的EXPLAIN的输出结果做解释：

osdba=# explain select * from testtab01;
                        QUERY PLAN
Seq Scan on testtab01 (cost=0.00..184.00 rows=10000 width=36)
(1 row)

结果中“Seq Scan on testtab01”表示顺序扫描表“testtab01”，顺序扫描也就是全表扫描，即从头到尾地扫描表。后面的内容“(cost=0.00..184.00 rows=10000 width=36)”可以分为三部分。
cost=0.00..184.00：“ cost=”后面有两个数字，中间是由“..”分隔，第一个数字
“0.00”表示启动的成本，也就是说返回第一行需要多少cost值;第二个数字表示返回所有的数据的成本，成本“cost”是什么后面会解释。
rows=10000：表示会返回10000行。
width=36：表示每行平均宽度为36字节。

9.1.3 EXPLAIN使用示例

默认情况下，输出的执行计划是文本格式，但也可以输出Json格式，例如：

postgres 固定执行计划 pgsql的执行计划_postgresql_02

9.1.4 全表扫描

全表扫描在PostgreSQL也称为顺序扫描（ seq scan)，全表扫描就是把表的所有数据块从头到尾读一遍，然后从数据块中找到符合条件的数据块。
全表扫描在EXPLAIN命令输出的结果中用“Seq Scan”表示，如下：

osdba=# EXPLAIN SELECT *FROM testtab01;
                        QUERY PLAN
Seq Scan on testtab01(cost=0.00..2754.05 rows=151905 width=36)
( l row)

9.1.5 索引扫描

索引通常是为了加快查询数据的速度而增加的。索引扫描，就是在索引中找出需要的数据行的物理位置，然后再到表的数据块中把相应的数据读出来的过程。
索引扫描在EXPLAIN命令输出的结果中用“Index Scan”表示，如下：

osdba=#EXPLAIN SELECT * FROM testtab01 where id=1000;
                        QUERY PLAN
----------------------------------
Index Scan using idx_testtab0l_id on testtab0l(cost=0.29..8.31 rows=1
width=70)
    Index cond: (id = 1000)
( 2rows)

9.1.6 位图扫描

位图扫描也是走索引的一种方式。方法是扫描索引，把满足条件的行或块在内存中建一个位图，扫描完索引后，再根据位图到表的数据文件中把相应的数据读出来。如果走了两个索引，可以把两个索引形成的位图进行“and”或“or”计算，合并成一个位图，再到表的数据文件中把数据读出来。
当执行计划的结果行数很多时会进行这种扫描，如非等值查询、IN子句或有多个条件都可以走不同的索引时。
下面是非等值的一个示例：

osdba=# explain select * from testtab02 where id2 >10000;
                            QUERY PLAN
Bitmap Heap Scan on testtab02(cost=18708.13..36596.06 rows=998155 width=16)
Recheck Cond: (id2 >10000)
->Bitmap Index Scan on idx_testtab02_id2(cost=0.00..18458.59 rowS=998155
width=0)
Index Cond : (id2 > 10000)
( 4 rows)

postgres 固定执行计划 pgsql的执行计划_postgresql_03

9.1.7 条件过滤

条件过滤，一般就是在where条件上加的过滤条件，当扫描数据行时，会找出满足过滤条件的行。条件过滤在执行计划中显示为“Filter”，示例如下：

osdba=# EXPLAIN SBLECT * FROM testtab01 where id<1000 and note like 'asdk% ' ;
QUERY PLAN
Index Scan using idx_testtab01_id on testtab01 (cost=0.29..48.11 rows=1
width=70)
Index cond: (id < 1000)
Filter: (note ~~ 'asdk%': : text)

9.1.8 Nestloop Join

嵌套循环连接（Nestloop Join)是在两个表做连接时最朴素的一种连接方式。在嵌套循环中，内表被外表驱动，外表返回的每一行都要在内表中检索找到与它匹配的行，因此整个查询返回的结果集不能太大(>10000不适合)，要把返回子集较小的表作为外表，而且在内表的连接字段上要有索引，否则会很慢。
执行的过程为:确定一个驱动表( outer table)，另一个表为inner table，驱动表中的每一行与inner表中的相应记录JOIN类似一个嵌套的循环。适用于驱动表的记录集比较小(<10000 ）而且 inner表有有效的访问方法( Index)。需要注意的是，JOIN的顺序很重要，驱动表的记录集一定要小，返回结果集的响应时间是最快的。

9.1.9 Hash Join

优化器使用两个表中较小的表，并利用连接键在内存中建立散列表，然后扫描较大的表并探测散列表，找出与散列表匹配的行。
这种方式适用于较小的表可以完全放于内存中的情况，这样总成本就是访问两个表的成本之和。但是如果表很大，不能完全放人内存，优化器会将它分割成若干不同的分区，把不能放人内存的部分写人磁盘的临时段，此时要有较大的临时段以便尽量提高IO 的性能。

9.1.10 Merge Join

通常情况下散列连接的效果比合并连接好，然而如果源数据上有索引，或者结果已经被排过序，在执行排序合并连接时就不需要排序了，这时合并连接的性能会优于散列连接。
下面的示例中，表testtab01和表testtab02的id字段上都有索引，且从索引扫描的数据已经排好序了，可以直接走Merge Join了:

osdba=# explain select a.id,b.id, a.note from testtab01 a, testtab02 b where a.id=b.
id and b.id<=100000;
                                    QUERY PLAN
------------------------------------------------------------------------------------
Merge Join (cost=1.47..47922.57 rows=99040 width=93)
Merge Cond: (a.id = b.id)
->  Index Scan using idx_testtab01_id on testtab01 a(cost=0.43..413538.43
    rows=10000000 width=89)
->  Index only Scan using idx_testtab02_id on testtab02 b (cost=0.42..4047.63
rows=99040 width=4)
        Index cond: (id <= 100000)
(5 rows)