PostgreSQL explain 查询计划

文章目录

PostgreSQL explain 查询计划
- Step 1
- Step 2
- Step 3
- Step 4
- Step 5

除第一行以外每个
->
表示一个子动作
执行计划的阅读顺序都是从后至前
width=0
是表示只获取行的位置，没有读取数据，开始读数据后width肯定大于0

EXPLAIN
用于理解和优化
SQL
查询，它接受
SELECT …
、
UPDATE …
或
DELETE …
之类的
sql
命令并返回查询计划，查询计划详细说明了执行
sql
所采取的方法。

Explaining → 执行计划运算类型
     Seq Scan：   扫描表。
     Index Scan： 索引扫描。
     Bitmap Index Scan：索引扫描。
     Bitmap Heap Scan： 索引扫描。
     Subquery Scan： 子查询。
     Tid Scan：ctid = 以CTID为查询条件。
     Function Scan： 函数扫描。
     Nested Loop： 循环连接。
     Merge Join： 排序合并连接。
     Hash Join： 哈希连接。
     Sort：  排序，ORDER BY操作。
     Hash：  哈希运算。
     Result： 函数扫描，和具体的表无关。
     Unique： DISTINCT，UNION操作。
     Limit： LIMIT，OFFSET操作。有
     Aggregate： count, sum,avg, stddev集约函数。
     Group： GROUP BY分组操作。
     Append： UNION操作。
     Materialize： 子查询。
     SetOp： INTERCECT，EXCEPT

下面结合以下实例讲解一个执行计划：

EXPLAIN ANALYZE SELECT *
FROM tenk1 t1, tenk2 t2
WHERE t1.unique1 < 100 AND t1.unique2 = t2.unique2 ORDER BY t1.fivethous;

                                                                 QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------
 Sort  (cost=717.34..717.59 rows=101 width=488) (actual time=7.761..7.774 rows=100 loops=1)
   Sort Key: t1.fivethous
   Sort Method: quicksort  Memory: 77kB
   ->  Hash Join  (cost=230.47..713.98 rows=101 width=488) (actual time=0.711..7.427 rows=100 loops=1)
         Hash Cond: (t2.unique2 = t1.unique2)
         ->  Seq Scan on tenk2 t2  (cost=0.00..445.00 rows=10000 width=244) (actual time=0.007..2.583 rows=10000 loops=1)
         ->  Hash  (cost=229.20..229.20 rows=101 width=244) (actual time=0.659..0.659 rows=100 loops=1)
               Buckets: 1024  Batches: 1  Memory Usage: 28kB
               ->  Bitmap Heap Scan on tenk1 t1  (cost=5.07..229.20 rows=101 width=244) (actual time=0.080..0.526 rows=100 loops=1)
                     Recheck Cond: (unique1 < 100)
                     ->  Bitmap Index Scan on tenk1_unique1  (cost=0.00..5.04 rows=101 width=0) (actual time=0.049..0.049 rows=100 loops=1)
                           Index Cond: (unique1 < 100)
 Planning time: 0.194 ms
 Execution time: 8.008 ms

PostgreSQL
建立了一个计划节点的树状结构，代表所采取的不同动作，除第一行以外每个
->
表示一个子动作，因此上文的查询结构为:

Sort
└── Hash Join
    ├── Seq Scan
    └── Hash
        └── Bitmap Heap Scan
            └── Bitmap Index Scan

每个节点表示子动作，

阅读顺序从后至前

。因此上面的执行计划第一步是在
tenk_unique1
索引上进行
Bitmap Index Scan
。

Step 1

Bitmap Index Scan on tenk1_unique1  (cost=0.00..5.04 rows=101 width=0) (actual time=0.049..0.049 rows=100 loops=1)
     Index Cond: (unique1 < 100)

对应于
SQL
中的
WHERE t1.unique1<100
。
PostgreSQL
匹配索引条件
unique1<100
的行，行本身不会在这里返回
(width=0)
。
成本
(cost)
。
(cost=0.00…5.04 rows=101 width=0)
中的
cost=0.00…5.04
意味着
PostgreSQL
希望花费
5.04
的任意计算单位来找到这些值，
0.00
是该节点起始工作成本(只是启动成本)，
rows=101
是此索引扫描将返回的估计行数，
width
是返回行的估计大小(以字节为单位)，
width=0
是因为此处只获取数据的实际位置
(ctid)
而不是返回行的内容。
因为使用
ANALYZE
选项运行
EXPLAIN
，所以查询实际上已执行并且捕获了时间信息。
(actual time=0.049…0.049 rows=100 loops=1)
，
loops=1
表示索引扫描被执行了1次；
rows=100
表示返回了100行；
actual time=0.049…0.049
表示此步骤的开始时间是0.049，结束时间0.049，单位为毫秒，因此此实际执行时间是0，实际时间是每次迭代的平均值，可以将值乘以循环次数以获得真实的执行时间。
成本或时间范围值可能不同，因此给出了最小/最大时间的概念，上例中查询的每单位成本比率为
0.049ms/5.04
，
成本单位≈0.01ms/每个成本单位
，注意是
actual time的结束时间/cost结束成本
。

Step 2

Bitmap Heap Scan on tenk1 t1  (cost=5.07..229.20 rows=101 width=244) (actual time=0.080..0.526 rows=100 loops=1)
      Recheck Cond: (unique1 < 100)

Step 1
索引扫描的结果传递给
Bitmap Heap Scan
操作。在这个节点中，
PostgreSQL
取得
tenk1
表(别名为
t1
)中行的位置，
unique1 < 100
从表中取出行。
可以看到成本预期乘以
Step 1
计算的值
0.01
，意味着大概的预期时间
(229.20-5.07) * 0.01≈2.24ms
，但是实际时间为
0.526ms
每行，实际时间是大概的预期时间
1/4
，这可能是因为成本估算值是一个上限值，并非所有行都需要读取，或者因为重新检查条件始终为真。
Bitmap Index Scan
和
Bitmap Heap Scan
的组合比从表中顺序读取行(
Seq Scan
)要昂贵得多，但由于在这种情况下需要访问的行相对较少，所以速度更快。
通过在提取这些行之前将这些行排序为物理顺序可以进一步提高速度，从而最大限度地降低单独提取的成本。

Step 3

Hash  (cost=229.20..229.20 rows=101 width=244) (actual time=0.659..0.659 rows=100 loops=1)
      Buckets: 1024  Batches: 1  Memory Usage: 28kB

堆扫描
Step 2(Heap Scan)
的结果中，来自表
tenk1
且
unique1<100
的行会在读取时插入到内存哈希表中，正如我们从成本上看到的那样，这个步骤完全没有占用运行时间
(cost=229.20…229.20)(actual time=0.659…0.659)
。
Hash
包含
Buckets
和
Batches
以及内存使用情况的信息。如果
Batches>1
，则还涉及磁盘使用情况，本例中无。内存使用情况通过公式大约为
100*244=24.4kB
，接近实际使用的内存
28kB
，多出来的部分假设为
Hash
密钥本身所占用的内存。

Step 4

->  Hash Join  (cost=230.47..713.98 rows=101 width=488) (actual time=0.711..7.427 rows=100 loops=1)
      Hash Cond: (t2.unique2 = t1.unique2)
      ->  Seq Scan on tenk2 t2  (cost=0.00..445.00 rows=10000 width=244) (actual time=0.007..2.583 rows=10000 loops=1)

PostgreSQL
从
tenk2
中读取所有
10000
行(别名为
t2
)，并根据
tenk1
的
Hash
来检查它们。
Hash Join
意味着一个表的行被输入到内存中的
Hash
，之后扫描另一个表的行，并根据
Hash
探测匹配的值，在第二行为匹配条件
Hash Cond: (t2.unique2 = t1.unique2)
。
请注意，因为查询选择了
tenk1
和
tenk2
中的所有值，所以在哈希连接期间每行的宽度都会增加一倍。
注意观察，在本示例中执行时间大部份花在这上步骤上
(actual time=0.711…7.427)
。
Step 4
花费了
7.427ms
，占总执行时间的
7.427/7.774=95%
。

Step 5

Sort  (cost=717.34..717.59 rows=101 width=488) (actual time=7.761..7.774 rows=100 loops=1)
  Sort Key: t1.fivethous
  Sort Method: quicksort  Memory: 77kB

所有符合条件的行都已经收集完毕，然后通过
Sort Key: t1.fivethous
排序结果集。
排序的算法为
quicksort
，
Memory
表示排序是在内存上完成的，使用了
77kB
大小的内存。当排序所需的内存超过
work_mem
参数的大小时，将使用磁盘空间，这将极大影响查询速度。
排序使用磁盘空间时需要增加
work_mem
大小，以避免使用磁盘空间
注意排序是在内存和磁盘上完成的，优先使用内存，因此
实际所需空间大小=所需的内存+所需的磁盘空间
这个查询所需要的时间大约为7.774ms，符合
Execution time: 8.008 ms
的时间。