Contents

track_io_timing

A configuration parameter for tracking the timing of database I/O calls

track_io_timing is a configuration parameter controlling whether the timing of database I/O calls is tracked.

track_io_timing was added in PostgreSQL 9.2.

Default

The default value for track_io_timing is: off.

Usage

If enabled, I/O timing information is displayed in the following contexts:

track_io_timing is disabled by default, as it repeatedly queries the operating system for the current time. This can cause significant overhead on some platforms; the contrib module pg_test_timing can help determine whether this is the case.

EXPLAIN (BUFFERS, ANALYZE)

If EXPLAIN is executed with the BUFFERS, ANALYZE  option combination, any I/O incurred will be noted with the annotation "I/O Timings".

pg_stat_database

The following columns will be updated with total I/O time:

  • blk_read_time
  • blk_write_time

pg_stat_statements

The following columns will be updated with total I/O time:

  • blk_read_time
  • blk_write_time

Change history

Examples

Using track_io_timing with 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)

Note that following execution of the above query, the data will now be cached and re-execution will not involve I/O and no "I/O Timings" value will be included in the EXPLAIN output:

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)

Attempting to set track_io_timing as a non-superuser:

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

Categories

GUC configuration item

See also

pg_test_timing, pg_stat_statements, pg_stat_database, track_wal_io_timing, statistics_collector