pg执行计划
-
查看执行计划
explain 接语句查看评估的执行计划
explain analyze实际执行后输出执行计划（如果执行非select语句，如update,delete,insert，要用事务来执行，避免真的改变了数据）
postgres@findb:explain analyze select * from tbl_user_json where id=10;
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------
Gather (cost=1000.00..45811.12 rows=1 width=106) (actual time=1.089..553.786 rows=1 loops=1)
Workers Planned: 2
Workers Launched: 2
-> Parallel Seq Scan on tbl_user_json (cost=0.00..44811.02 rows=1 width=106) (actual time=359.419..542.514 rows=0 loops=3)
Filter: (id = 10)
Rows Removed by Filter: 666666
Planning Time: 0.105 ms
Execution Time: 553.823 ms
(8 rows)
除了常用的analyze选项外，还有其它选项，如：
postgres@findb:explain (analyze on,timing on,verbose on,buffers on) select * from tbl_user_json where id=10;
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------
Gather (cost=1000.00..45811.12 rows=1 width=106) (actual time=0.652..199.759 rows=1 loops=1)
Output: id, userinfo
Workers Planned: 2
Workers Launched: 2
Buffers: shared hit=97 read=34297
-> Parallel Seq Scan on public.tbl_user_json (cost=0.00..44811.02 rows=1 width=106) (actual time=126.943..192.335 rows=0 loops=3)
Output: id, userinfo
Filter: (tbl_user_json.id = 10)
Rows Removed by Filter: 666666
Buffers: shared hit=97 read=34297
Worker 0: actual time=190.402..190.402 rows=0 loops=1
Buffers: shared hit=19 read=10709
Worker 1: actual time=190.400..190.401 rows=0 loops=1
Buffers: shared hit=40 read=11767
Planning Time: 0.081 ms
Execution Time: 199.795 ms
(16 rows)
更是可以使用log_***_stats参数来输出更详细的各个组件的资源消耗情况：
postgres@findb:select name,setting from pg_settings where name like 'log_%_stats';
name | setting
---------------------+---------
log_executor_stats | off
log_parser_stats | off
log_planner_stats | off
log_statement_stats | off
(4 rows)
postgres@findb:select name,setting from pg_settings where name like 'client_min_messages';
name | setting
---------------------+---------
client_min_messages | notice
(1 row)
postgres@findb:set client_min_messages=log;
SET
postgres@findb:set log_parser_stats=on;
SET
postgres@findb:explain analyze select * from tbl_user_json where id=10;
LOG: PARSER STATISTICS
DETAIL: ! system usage stats:
! 0.000000 s user, 0.000000 s system, 0.000084 s elapsed
! [0.249962 s user, 0.479927 s system total]
! 6616 kB max resident size
! 0/0 [720/72] filesystem blocks in/out
! 0/0 [1/1499] page faults/reclaims, 0 [0] swaps
! 0 [0] signals rcvd, 0/0 [0/0] messages rcvd/sent
! 0/0 [63/11] voluntary/involuntary context switches
LOG: PARSE ANALYSIS STATISTICS
DETAIL: ! system usage stats:
! 0.000000 s user, 0.000000 s system, 0.000072 s elapsed
! [0.249962 s user, 0.479927 s system total]
! 6644 kB max resident size
! 0/0 [720/72] filesystem blocks in/out
! 0/0 [1/1507] page faults/reclaims, 0 [0] swaps
! 0 [0] signals rcvd, 0/0 [0/0] messages rcvd/sent
! 0/0 [63/11] voluntary/involuntary context switches
LOG: REWRITER STATISTICS
DETAIL: ! system usage stats:
! 0.000000 s user, 0.000000 s system, 0.000001 s elapsed
! [0.249962 s user, 0.479927 s system total]
! 6644 kB max resident size
! 0/0 [720/72] filesystem blocks in/out
! 0/0 [1/1507] page faults/reclaims, 0 [0] swaps
! 0 [0] signals rcvd, 0/0 [0/0] messages rcvd/sent
! 0/0 [63/11] voluntary/involuntary context switches
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------
Gather (cost=1000.00..45811.12 rows=1 width=106) (actual time=0.750..200.168 rows=1 loops=1)
Workers Planned: 2
Workers Launched: 2
-> Parallel Seq Scan on tbl_user_json (cost=0.00..44811.02 rows=1 width=106) (actual time=125.853..191.451 rows=0 loops=3)
Filter: (id = 10)
Rows Removed by Filter: 666666
Planning Time: 0.121 ms
Execution Time: 200.213 ms
(8 rows)
