PostgreSQL Explain 复杂执行计划怎么看 — 逐个分解PG执行计划的那些操作

每个数据中都希望自己的SQL 的执行计划是最好的，最快的，最妙的，但是在你使用EXPLAIN 的命令的情况下，你看的懂里面的那些东西吗？如果你看不懂，你怎么知道你的执行计划是最好的，最棒的，最妙的。

1  数据扫描获取方式

• Sequential Scan
• Index Scan
• Index Only Scan
• Bitmap Scan
• TID Scan

在PG的数据库获取数据的方式中，有以上的一些方式来获取数据的方式的名字展示。

Sequential Scan

1  顺序扫描，顺序扫描是一种数据获取的方式，举例如果您想获取 1000万行数据中的一条数据，使用这样的方式，即使你的数据可能就在1000万上数据的第一条，那么他还是会将1000万数据全部进行扫描，并匹配后，在给出结果。数据在数据库库存储是通过页面来存储的，假设1000个页面存储了1000万行的数据每个页面，那么扫描的方式就是 1000 （页）* 10000（行）的方式进行数据的扫描。

顺序扫描的方式的使用可能会符合以下的几个条件

1   没有合适的索引使用

2   获取的数据占比整体表是占大多数的

2  INDEX SCAN 

INDEX SCAN 虽然也有SCAN ，扫描，但是这个扫描的COST 相对于上面的扫描，不是一个意义，INDEX SCAN 并不是顺序扫描的方式，而是按照索引查找定位数据的方式，通过非常低廉的索引SEARCH 的方式，并通过索引存储的指针指向具体的数据部分，获取数据。

考量是否用索引来解决问题

1  有合适的对应的索引

2   数据提取的量占据整体的数据量是少数，或极少数

3   随机数据获取相对于顺序范围数据获取，更容易使用索引获取数据

4    在成本估算后，索引扫描优于顺序扫描

在以上的情况下，索引扫描才可以启用

3  INDEX Only Scan 

INDEX only Scan 与上面的index scan 在原理上是一致的，但是在操作的步骤上是不一样的，INDEX only Scan 在获取数据时，并不会在返回到原表中获取数据，而是我们的数据已经在索引中了，所以直接在索引获取数据后就返回了，节省了相关的第二次操作的消耗。

满足使用INDEX ONLY SCAN 方式，需要

1  有合适的索引

2  提取的数据占据整体表的数据量是少数或极少数

3  获取数据的字段在索引中，不需要回表找到索引中未包含的数据

4  Bitmap Scan 

Bitmap Scan 扫描的出现是基于获取的数据在 INDEX SCAN 中的问题点而产生的一个数据的获取的方式，在INDEX SCAN 中获取到数据的位置后，还是需要到对应的数据页面中，在扫描到对应的数据，而BITMAP SCAN 就是要解决数据通过索引定位后，在去原数据页面定位的问题，解决最后一公里的问题。

位图索引扫描:首先它从索引数据结构中获取所有索引数据，并创建所有TID的位图。为了简单理解，您可以认为这个位图包含所有页面的哈希(基于page no进行哈希)，并且每个页面条目包含该页内所有偏移量的数组。

所以通过位图来获取数据的方式，速度更快，当然相对的付出的成本也更多一些。BITMAP  如果用一个粗略的方式来评价为什么会选择这样的方式来获取数据。

数据量多少 

index_scan (index_only_scan)  

5 TID Scan 

TID 数据扫描的方式，是一种特殊的数据扫描的方式，在常见的数据获取中，是没有选择这样的数据获取的方式，但他可以解决一些特殊场景的问题。

实际上这样的数据获取的模式就是在PG的数据查询中直接使用CTID 的方式来获取数据的物理位置上的数据。

postgres=# explain select * from table where ctid='(116,42)’;

                       QUERY PLAN ———————————————————- Tid Scan on demotable  (cost=0.00..4.01 rows=1 width=15)   TID Cond: (ctid = ‘(116,42)’::tid)

在我们清楚了相关的数据搜索的几种方式后，那么数据集合和集合之间的关系如何处理是我们下一个需要理解的部分，在POSTGRESQL 中我们可以将数据集合和数据集合之间的关系处理放方式。

1  Nested Loop join

2  Hash join 

3  Merge join

1   NLJ Nested loop join  ，嵌套循环，嵌套循环是将集合和集合之间的关系进行比较，在操作中，是两个集合比较的关系，将外部数据逐条的与内部的集合的数据进行匹配.

这种数据集合比较的方式，是比较消耗数据库运算性能的,在使用这样的方式进行数据库的连接的情况下应该采用驱动表尽量小的方式来进行数据的处理。

2  Hash join 

hash join 适用于集合和集合之间的等值比较，使用HASH JOIN 方式中的集合和集合之间的关系应该是等于的方式。下面的这个例子中，选择了 bt2 作为hash的表，将相关的数据先进行HASH 存储到内存中，在将bt1 中的数据hash后与hash表中的数据进行对比。显然这样的方式比第一个刚才提到的NLJ的方式要更快，但也有相关的限制条件，就是被HASH的部分可以放入到内存当中。

select * from table1 bt1, table2 bt2 where bt1.id1 = bt2.id1;

Hash Join  (cost=27.50..220.00 rows=1000 width=16)   Hash Cond: (bt1.id1 = bt2.id1)   ->  Seq Scan on table1 bt1  (cost=0.00..145.00 rows=10000 width=8)   ->  Hash  (cost=15.00..15.00 rows=1000 width=8)         ->  Seq Scan on table2 bt2  (cost=0.00..15.00 rows=1000 width=8) (5 rows)

3  Merge join 

Merge join  也是一种集合和集合之间的进行数据挑选的方式，在进行Merge join 的方式中，需要注意的是集合和集合之间的数据是需要进行排序的，也就是说如果要进行 Merge join 则在除了必须是 = 号运算的基础上，等号两边的的所在的列 ，必须是带有索引的，有序的。

postgres=# explain select * from table1 bt1, table2 bt2 where bt1.id1 = bt2.id1;                    QUERY PLAN ———————————————————————— Merge Join  (cost=0.56..90.36 rows=1000 width=16)   Merge Cond: (bt1.id1 = bt2.id1)   ->  Index Scan using idx1 on table1 bt1  (cost=0.29..318.29 rows=10000 width=8)   ->  Index Scan using idx2 on table2 bt2  (cost=0.28..43.27 rows=1000 width=8) (4 rows)

在说完这些后，细致的同学可能在执行计划中发现过如下的一些工作的项目

• Sort
• Aggregate
• Group By Aggregate
• Limit
• Unique
• LockRows
• SetOp

1  sort , sort 有的时候有，有的时候没有，可能有些同学会发现这个问题，比如昨天我还发现我的执行计划里面有这个，今天我在用就没有了，这是什么原因，我们看下面两个部分，同样的语句，同样的配方，但是执行计划不同，因为在语句中都有 order by 但不同的是，因为后面的表创建了对应ORDER BY 字段的索引，所以不在需要进行显示的排序。

postgres=# explain select * from table order by num;                   QUERY PLAN ———————————————————————- Sort  (cost=819.39..844.39 rows=10000 width=15)   Sort Key: num   ->  Seq Scan on table  (cost=0.00..155.00 rows=10000 width=15) (3 rows)

postgres=# CREATE INDEX demoidx ON table(num); CREATE INDEX postgres=# explain select * from table order by num;                     QUERY PLAN ———————————————————————- Index Scan using demoidx on demotable  (cost=0.29..534.28 rows=10000 width=15) (1 row)

2 Aggregate   聚合操作是我们在SQL 执行计划中经常碰到的，这个操作的意味着你在语句的执行中有使用聚合函数，或对整体的结果进行了count 计算等等，一般对于SQL执行中出现使用进行汇总，分析的函数时都会出现这个聚合测操作。

3  GroupAggregate 和 hashAggregate 出现的情况下，意味着SQL 的操作中出现了 GROUP BY, 而如果是 groupAggregate 并且在此位置的 cost 较大的情况下，则说明进行group by 的字段没有索引的可能性较大.

postgres=# explain select count(*) from demo2 group by id2;                            QUERY PLAN ------------------------------------------------------------------------- GroupAggregate  (cost=9747.82..11497.82 rows=100000 width=12)   Group Key: id2   ->  Sort  (cost=9747.82..9997.82 rows=100000 width=4)      Sort Key: id2      ->  Seq Scan on demo2  (cost=0.00..1443.00 rows=100000 width=4) (5 rows)
postgres=# create index idx1 on demo1(id); CREATE INDEX postgres=# explain select sum(id2), id from demo1 where id=1 group by id;                            QUERY PLAN ------------------------------------------------------------------------ GroupAggregate  (cost=0.28..8.31 rows=1 width=12)   Group Key: id   ->  Index Scan using idx1 on demo1  (cost=0.28..8.29 rows=1 width=8)      Index Cond: (id = 1) (4 rows)
而hashAggregate 一般是结果较小，并且需要分组进行数据的展示的情况下，使用hashAggregate的方式进行处理。
postgres=# explain select count(*) from demo1 group by id2;                       QUERY PLAN --------------------------------------------------------------- HashAggregate  (cost=20.00..30.00 rows=1000 width=12)   Group Key: id2   ->  Seq Scan on demo1  (cost=0.00..15.00 rows=1000 width=4) (3 rows)

除以上的部分，还有如subquery scan ,setOp, lockrows, Unique 等这里就不在多说了。

服务器托管，北京服务器托管，服务器租用 http://www.fwqtg.net
机房租用，北京机房租用，IDC机房托管， http://www.fwqtg.net

相关推荐: ChatGPT工作提效之小鹅通二次开发批量API对接解决方案（学习记录同步、用户注册同步、权益订购同步、开发文档）
ChatGPT工作提效系列
一、项目需求
二、批量修复API接口
三、开发中遇见的问题

ChatGPT工作提效系列 ChatGPT工作提效之初探路径独孤九剑遇强则强 ChatGPT工作提效之在程序开发中的巧劲和指令(创建MySQL语句、PHP语句、Javascript用法、python的交互) ChatGPT工作提效之生成开发需求和报价单并转为E…