postgres执行计划和索引

postgres执行计划和索引

执行计划

命令

explain[(option[,...])]
explain analyzestatement
可选的option选项有：
Analyze[boolean]：得到statement的真实运行时间。默认是false
verbose[boolean]：得到statement语句的执行计划和执行计划中的每个节点的详细信息。默认为false
costs[boolean]：得到计划中每个接地哪的cost，rows，width的估算值，默认为true
buffers[boolean]：analyze出现时可选。缓存的使用情况
共享缓存(shared blocks)的hit，read，dirtied，written数值
本地缓存(local blocks)的hit，read，dirtied,written数值
临时快(temp blocks)的read,written数值
timing[boolean]：analyze出现时可选。显示每个节点的启动时间和总时间花费。默认true
format{text|xml|json|yaml}：指定执行计划的输出格式
text：默认值。以行为单位，显示每个结点的计划信息，以缩进格式表示子节点的计划信息。buffers参数时的文本格式，只输出非零值
xml：xml格式
json：json格式
yaml：以yaml格式显示执行计划

关键字

postgres=# explain select relname from pg_class;
                         QUERY PLAN                         
------------------------------------------------------------
 Seq Scan on pg_class  (cost=0.00..16.11 rows=311 width=64)
(1 row)
cost:cost是比较重要的指标。cost=0.00..16.11有两个部分，启动成本（startup）=0.00 和总成本（total）=16.11。这个指标也只是预测值。
	启动成本也可解释为找到符合条件的第一行所花的成本。
rows:返回的行数，如果执行vacuum和analyze那么返回的结果更加接近实际值
width：查询结果所有字段的总宽度，并非关键指标，字段宽度定义：
  text [ n 文字]：n+４
　　varchar(n)：n+1
　     　char(n)：n+1
          boolean：1
        　 bigint ：8
  　      integer：4

节点解释

扫描操作
index Scan：索引扫描(读取索引块，然后读取数据文件)
Index Only Scan：索引只读扫描(只读取索引文件，根据映射文件获取数据)
Tid Scan：根据元组头中的ctid直接定位元祖的位置，读取数据文件，(page_number, item_number)
Bitmap Index Scan：位图索引扫描(利用索引，获得满足条件选择的元组的位图，直接读取索引文件，不读取数据)
BitMap Heap Scan：位图Heap索引扫描(利用Bitmap Index Scan得到的位图，获取元组，直接在数据文件中读取)
Function Scan：函数扫描 ，处理范围表中有函数的情况(PostgreSQL允许函数可以返回元祖集合，类似表的元组集合)
SubQuery：子查询扫描
Values Scan ：Values扫描(Postgresql提供了Values语句格式，允许Values链表可以返回元祖集合，类似表的元组集合)
CTE Scan：CTE扫描(select查询中用了With子句定义子查询，对With子句的扫描成为CTE扫描)
WorkTable Scan：工作表扫描(与RecursiveUnion结合共同完成递归合并子查询)

节点解释

表连接操作
Nested [(type)] Loop：嵌套循环连接。type可能是Inner，left，right，full，semi，anti。inner的可以显示省略
Merge[(type)] Join 归并连接。type同上
Hash[(type)] Join：哈希连接。type同上 
物化操作
Material ：物化操作(缓存子节点的结果，适用于需要多次扫描的节点)
Unique：distinct操作(对下层已经排序节点的输出去重)
Group：分组操作(Group By 对下层节点的输出分组)
Sort：排序操作(Order By对下层节点的输出排序)
Limit：limit/offset操作(从下层节点的输出中选出一定范围的元组)
Aggregate：带有聚集函数的Group By操作
GroupAggregate：分组聚集(带有分组操作的聚集，需要排序)
HashAggregate：Hash聚集(使用hash算法完成聚集，不需要排序)
WindwoAgg：窗口函数

节点解释

集合操作
Append：Union，intercect，except操作(多个子查询的结合操作)
SetOp[type]：intercect/except(需要排序)。type的值可能为：intersect，intersectAll，except，exceptAll。涉及All的操作需要去重。常以Append为输入节点
HashSetOp[type]：intercect/except(用hash算法完成不需要排序)。其他同上
控制类型
Result：一次性结果获取(如子查询可以在查询优化阶段获得结果，多以initplan作为输入)
ModifyTable：插入/修改/删除(inert/update/delete操作的sql)
BitmapAnd：多个单独索引作为and操作符的左右操作数
BitmapOr：多个索引或一个索引的多个索引列作为or操作符的左右操作数
recursiveUnion：用于处理递归定义的union语句(与workTable Scan配合使用)
过滤器
filter：常规过滤(对元组按条件进行过滤，完成选择操作)
join filter：连接操作过滤(hashjoin，mergejoin，nestedloop join连接操作情况下使用过滤器)
One-Time：一趟过滤器(对于能一次性获得结果的Result操作，一趟过滤即可满足过滤要求)

节点解释

连接条件
Hash cond：显示hash连接的条件
merge cond：显示merge连接的条件
其他类型
Foreign Scan：外部表(对外部对象的访问)
LockRows：查询中带有For update/For share的操作，需要对被操作对象加锁
Remote SQL：配合Foreign Scan表示远程的sql
InitPlan：与Result配合，在初始化查询计划的阶段即可完成的操作
Rechck Cond：对条件重新进行检查
Output：表示输出信息

如何阅读查看pg执行计划

postgres=# explain analyze select relname,attname from pg_class cl join pg_attribute att on cl.oid = att.attrelid;
1                                                       QUERY PLAN                                                        
2------------------------------------------------------------------------------------------------------------
3 Hash Join  (cost=20.00..121.97 rows=2399 width=128) (actual time=0.323..26.276 rows=2399 loops=1)
4  Hash Cond: (att.attrelid = cl.oid)
5  ->  Seq Scan on pg_attribute att  (cost=0.00..68.99 rows=2399 width=68) (actual time=0.008..24.227 rows=2399 loops=1)
6  ->  Hash  (cost=16.11..16.11 rows=311 width=68) (actual time=0.274..0.274 rows=311 loops=1)
7         Buckets: 1024  Batches: 1  Memory Usage: 39kB
8         ->  Seq Scan on pg_class cl  (cost=0.00..16.11 rows=311 width=68) (actual time=0.007..0.146 rows=311 loops=1)
从第三行到第八行逐行嵌套，表示了查询的执行顺序，嵌套层次最深的，最先执行
同样嵌套深度的，从上到下，先予执行
每一步的cost包括上一步