- 论坛徽章:
- 0
|
自PostgreSQL9.0.1之后,explain的功能更强大了。语法也有如下更新:
EXPLAIN [ ( { ANALYZE boolean | VERBOSE boolean | COSTS boolean | BUFFERS boolean | FORMAT { TEXT | XML | JSON | YAML } } [, ...] ) ] statement
EXPLAIN [ ANALYZE ] [ VERBOSE ] statement
下面继续上一篇batch insert in PostgreSQL的几个BATCH操作,看看执行计划分别是什么样的:
test=> begin;explain (analyze,verbose true,costs true,buffers true,format json) execute t_test1(53,54,55,56);rollback;
BEGIN
QUERY PLAN
-----------------------------------------------
[ +
{ +
"Plan": { +
"Node Type": "ModifyTable", +
"Operation": "Insert", +
"Startup Cost": 0.00, +
"Total Cost": 0.05, +
"Plan Rows": 4, +
"Plan Width": 8, +
"Actual Startup Time": 0.049, +
"Actual Total Time": 0.049, +
"Actual Rows": 0, +
"Actual Loops": 1, +
"Shared Hit Blocks": 12, +
"Shared Read Blocks": 0, +
"Shared Written Blocks": 0, +
"Local Hit Blocks": 0, +
"Local Read Blocks": 0, +
"Local Written Blocks": 0, +
"Temp Read Blocks": 0, +
"Temp Written Blocks": 0, +
"Plans": [ +
{ +
"Node Type": "Values Scan", +
"Parent Relationship": "Member", +
"Alias": "*VALUES*", +
"Startup Cost": 0.00, +
"Total Cost": 0.05, +
"Plan Rows": 4, +
"Plan Width": 8, +
"Actual Startup Time": 0.002, +
"Actual Total Time": 0.004, +
"Actual Rows": 4, +
"Actual Loops": 1, +
"Output": ["\"*VALUES*\".column1"],+
"Shared Hit Blocks": 0, +
"Shared Read Blocks": 0, +
"Shared Written Blocks": 0, +
"Local Hit Blocks": 0, +
"Local Read Blocks": 0, +
"Local Written Blocks": 0, +
"Temp Read Blocks": 0, +
"Temp Written Blocks": 0 +
} +
] +
}, +
"Triggers": [ +
], +
"Total Runtime": 0.085 +
} +
]
(1 row)
ROLLBACK
test=> begin;explain (analyze,verbose true,costs true,buffers true,format json) insert into tbl_test (id) values (53),(54),(55),(56);rollback;
BEGIN
QUERY PLAN
-----------------------------------------------
[ +
{ +
"Plan": { +
"Node Type": "ModifyTable", +
"Operation": "Insert", +
"Startup Cost": 0.00, +
"Total Cost": 0.05, +
"Plan Rows": 4, +
"Plan Width": 8, +
"Actual Startup Time": 0.048, +
"Actual Total Time": 0.048, +
"Actual Rows": 0, +
"Actual Loops": 1, +
"Shared Hit Blocks": 12, +
"Shared Read Blocks": 0, +
"Shared Written Blocks": 0, +
"Local Hit Blocks": 0, +
"Local Read Blocks": 0, +
"Local Written Blocks": 0, +
"Temp Read Blocks": 0, +
"Temp Written Blocks": 0, +
"Plans": [ +
{ +
"Node Type": "Values Scan", +
"Parent Relationship": "Member", +
"Alias": "*VALUES*", +
"Startup Cost": 0.00, +
"Total Cost": 0.05, +
"Plan Rows": 4, +
"Plan Width": 8, +
"Actual Startup Time": 0.002, +
"Actual Total Time": 0.005, +
"Actual Rows": 4, +
"Actual Loops": 1, +
"Output": ["\"*VALUES*\".column1"],+
"Shared Hit Blocks": 0, +
"Shared Read Blocks": 0, +
"Shared Written Blocks": 0, +
"Local Hit Blocks": 0, +
"Local Read Blocks": 0, +
"Local Written Blocks": 0, +
"Temp Read Blocks": 0, +
"Temp Written Blocks": 0 +
} +
] +
}, +
"Triggers": [ +
], +
"Total Runtime": 0.076 +
} +
]
(1 row)
ROLLBACK
test=> begin;explain (analyze,verbose true,costs true,buffers true,format json) insert into tbl_test (id) select 53 union all select 54 union all select 55 union all select 56;rollback;
BEGIN
QUERY PLAN
----------------------------------------------------
[ +
{ +
"Plan": { +
"Node Type": "ModifyTable", +
"Operation": "Insert", +
"Startup Cost": 0.00, +
"Total Cost": 0.09, +
"Plan Rows": 4, +
"Plan Width": 4, +
"Actual Startup Time": 0.057, +
"Actual Total Time": 0.057, +
"Actual Rows": 0, +
"Actual Loops": 1, +
"Shared Hit Blocks": 12, +
"Shared Read Blocks": 0, +
"Shared Written Blocks": 0, +
"Local Hit Blocks": 0, +
"Local Read Blocks": 0, +
"Local Written Blocks": 0, +
"Temp Read Blocks": 0, +
"Temp Written Blocks": 0, +
"Plans": [ +
{ +
"Node Type": "Result", +
"Parent Relationship": "Member", +
"Startup Cost": 0.00, +
"Total Cost": 0.09, +
"Plan Rows": 4, +
"Plan Width": 4, +
"Actual Startup Time": 0.006, +
"Actual Total Time": 0.010, +
"Actual Rows": 4, +
"Actual Loops": 1, +
"Output": ["(53)"], +
"Shared Hit Blocks": 0, +
"Shared Read Blocks": 0, +
"Shared Written Blocks": 0, +
"Local Hit Blocks": 0, +
"Local Read Blocks": 0, +
"Local Written Blocks": 0, +
"Temp Read Blocks": 0, +
"Temp Written Blocks": 0, +
"Plans": [ +
{ +
"Node Type": "Append", +
"Parent Relationship": "Outer", +
"Startup Cost": 0.00, +
"Total Cost": 0.08, +
"Plan Rows": 4, +
"Plan Width": 4, +
"Actual Startup Time": 0.002, +
"Actual Total Time": 0.005, +
"Actual Rows": 4, +
"Actual Loops": 1, +
"Shared Hit Blocks": 0, +
"Shared Read Blocks": 0, +
"Shared Written Blocks": 0, +
"Local Hit Blocks": 0, +
"Local Read Blocks": 0, +
"Local Written Blocks": 0, +
"Temp Read Blocks": 0, +
"Temp Written Blocks": 0, +
"Plans": [ +
{ +
"Node Type": "Result", +
"Parent Relationship": "Member",+
"Startup Cost": 0.00, +
"Total Cost": 0.01, +
"Plan Rows": 1, +
"Plan Width": 0, +
"Actual Startup Time": 0.000, +
"Actual Total Time": 0.000, +
"Actual Rows": 1, +
"Actual Loops": 1, +
"Output": ["53"], +
"Shared Hit Blocks": 0, +
"Shared Read Blocks": 0, +
"Shared Written Blocks": 0, +
"Local Hit Blocks": 0, +
"Local Read Blocks": 0, +
"Local Written Blocks": 0, +
"Temp Read Blocks": 0, +
"Temp Written Blocks": 0 +
}, +
{ +
"Node Type": "Result", +
"Parent Relationship": "Member",+
"Startup Cost": 0.00, +
"Total Cost": 0.01, +
"Plan Rows": 1, +
"Plan Width": 0, +
"Actual Startup Time": 0.001, +
"Actual Total Time": 0.002, +
"Actual Rows": 1, +
"Actual Loops": 1, +
"Output": ["54"], +
"Shared Hit Blocks": 0, +
"Shared Read Blocks": 0, +
"Shared Written Blocks": 0, +
"Local Hit Blocks": 0, +
"Local Read Blocks": 0, +
"Local Written Blocks": 0, +
"Temp Read Blocks": 0, +
"Temp Written Blocks": 0 +
}, +
{ +
"Node Type": "Result", +
"Parent Relationship": "Member",+
"Startup Cost": 0.00, +
"Total Cost": 0.01, +
"Plan Rows": 1, +
"Plan Width": 0, +
"Actual Startup Time": 0.000, +
"Actual Total Time": 0.000, +
"Actual Rows": 1, +
"Actual Loops": 1, +
"Output": ["55"], +
"Shared Hit Blocks": 0, +
"Shared Read Blocks": 0, +
"Shared Written Blocks": 0, +
"Local Hit Blocks": 0, +
"Local Read Blocks": 0, +
"Local Written Blocks": 0, +
"Temp Read Blocks": 0, +
"Temp Written Blocks": 0 +
}, +
{ +
"Node Type": "Result", +
"Parent Relationship": "Member",+
"Startup Cost": 0.00, +
"Total Cost": 0.01, +
"Plan Rows": 1, +
"Plan Width": 0, +
"Actual Startup Time": 0.000, +
"Actual Total Time": 0.000, +
"Actual Rows": 1, +
"Actual Loops": 1, +
"Output": ["56"], +
"Shared Hit Blocks": 0, +
"Shared Read Blocks": 0, +
"Shared Written Blocks": 0, +
"Local Hit Blocks": 0, +
"Local Read Blocks": 0, +
"Local Written Blocks": 0, +
"Temp Read Blocks": 0, +
"Temp Written Blocks": 0 +
} +
] +
} +
] +
} +
] +
}, +
"Triggers": [ +
], +
"Total Runtime": 0.096 +
} +
]
(1 row)
ROLLBACK;
执行效率从高到低排序:
insert into tbl_test (id) values (53),(54),(55),(56);
execute t_test1(53,54,55,56);
insert into tbl_test (id) select 53 union all select 54 union all select 55 union all select 56;
另外需要注意的是EXPLAIN ANALYZE将真实的运行被解释的SQL,如果有修改操作,慎用EXPLAIN ANALYZE,即使要用的话,一定要BEGIN;,ROLLBACK; |
|