track_io_timing
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:
EXPLAIN (BUFFERS, ANALYZE)
pg_stat_database
pg_stat_statements
autovacuum
, whenlog_autovacuum_min_duration
is turned on
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
- PostgreSQL 9.2
- added (initial commit 40b9b957)
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"
References
- PostgreSQL documentation: track_io_timing
Useful links
- track_io_timing - June 2017 blog article by malisper