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