track_io_timing

• 17
• 16
• 15
• 14
• 13
• 12
• 11
• 10
• 9.6
• 9.5
• 9.4
• 9.3
• 9.2

将 track_io_timing 与 EXPLAIN 结合使用

postgres=# SET track_io_timing = ON;
SET

postgres=# EXPLAIN (BUFFERS, ANALYZE) SELECT COUNT(*) FROM object;
                                                  QUERY PLAN                                                  
--------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=87.40..87.41 rows=1 width=8) (actual time=48.855..48.874 rows=1 loops=1)
   Buffers: shared read=30
   I/O Timings: read=1.411
   ->  Seq Scan on object  (cost=0.00..75.92 rows=4592 width=0) (actual time=0.338..25.602 rows=4477 loops=1)
         Buffers: shared read=30
         I/O Timings: read=1.411
 Planning Time: 5.024 ms
 Execution Time: 49.186 ms
(8 rows)

请注意，在执行上述查询后，数据现在将被缓存，重新执行将不会涉及 I/O，并且 EXPLAIN 输出中将不包含“I/O Timings”值

postgres=# EXPLAIN (BUFFERS, ANALYZE) SELECT COUNT(*) FROM object;
                                                  QUERY PLAN                                                  
--------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=87.40..87.41 rows=1 width=8) (actual time=53.178..53.196 rows=1 loops=1)
   Buffers: shared hit=30
   ->  Seq Scan on object  (cost=0.00..75.92 rows=4592 width=0) (actual time=0.043..26.705 rows=4477 loops=1)
         Buffers: shared hit=30
 Planning Time: 0.174 ms
 Execution Time: 53.295 ms
(6 rows)

尝试以非超级用户身份设置 track_io_timing

postgres=*> SET track_io_timing TO on;
ERROR:  permission denied to set parameter "track_io_timing"

• PostgreSQL 文档： track_io_timing

• track_io_timing - malisper 于 2017 年 6 月发表的博文