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)