default, because it will repeatedly query the operating system for

the current time, which may cause significant overhead on some

platforms. You can use the <xref linkend="pgtesttiming"> tool to

measure the overhead of timing on your system. Only superusers can

change this setting.

measure the overhead of timing on your system. Timing information is

displayed in <xref linkend="pg-stat-database-view">, in the output of

<xref linkend="sql-explain"> when the <literal>BUFFERS</> option is

used, and by <xref linkend="pgstatstatements">. Only superusers can

change this setting.

</para>

</listitem>

</varlistentry>

statistics are collected about table and index accesses.

</para>

<para>

The parameter <xref linkend="guc-track-iotiming"> enables monitoring

of block read and write times.

</para>

<para>

The parameter <xref linkend="guc-track-functions"> enables tracking of

usage of user-defined functions.

This value can also be returned by directly calling

the <function>pg_stat_get_db_deadlocks</function> function.</entry>

</row>

<row>

<entry>block_read_time</entry>

<entry><type>bigint</></entry>

<entry>Time spent by backends reading data file blocks, in milliseconds.

The same value can be returned in microseconds by directly calling

the <function>pg_stat_get_db_block_time_read</function> function.</entry>

</row>

<row>

<entry>block_write_time</entry>

<entry><type>bigint</></entry>

<entry>Time spent by backends writing data file blocks, in milliseconds.

The same value can be returned in microseconds by directly calling

the <function>pg_stat_get_db_block_time_write</function> function.</entry>

</row>

<row>

<entry>stats_reset</entry>

<entry><type>timestamptz</></entry>

pg_stat_get_db_temp_files(D.oid)AS temp_files,

pg_stat_get_db_temp_bytes(D.oid)AS temp_bytes,

pg_stat_get_db_deadlocks(D.oid)AS deadlocks,

pg_stat_get_db_block_time_read(D.oid)/1000AS block_read_time,

pg_stat_get_db_block_time_write(D.oid)/1000AS block_write_time,

pg_stat_get_db_stat_reset_time(D.oid)AS stats_reset

FROM pg_database D;

staticintpgStatXactCommit=0;

staticintpgStatXactRollback=0;

PgStat_CounterpgStatBlockTimeRead=0;

PgStat_CounterpgStatBlockTimeWrite=0;

return;

if (OidIsValid(tsmsg->m_databaseid))

{

tsmsg->m_xact_commit=pgStatXactCommit;

tsmsg->m_xact_rollback=pgStatXactRollback;

tsmsg->m_block_time_read=pgStatBlockTimeRead;

tsmsg->m_block_time_write=pgStatBlockTimeWrite;

pgStatXactCommit=0;

pgStatXactRollback=0;

}

{

tsmsg->m_xact_commit=0;

tsmsg->m_xact_rollback=0;

tsmsg->m_block_time_read=0;

tsmsg->m_block_time_write=0;

}

n=tsmsg->m_nentries;

result->n_temp_files=0;

result->n_temp_bytes=0;

result->n_deadlocks=0;

result->n_block_time_read=0;

result->n_block_time_write=0;

result->stat_reset_timestamp=GetCurrentTimestamp();

dbentry->n_xact_commit+= (PgStat_Counter) (msg->m_xact_commit);

dbentry->n_xact_rollback+= (PgStat_Counter) (msg->m_xact_rollback);

dbentry->n_block_time_read+=msg->m_block_time_read;

dbentry->n_block_time_write+=msg->m_block_time_write;

dbentry->n_temp_bytes=0;

dbentry->n_temp_files=0;

dbentry->n_deadlocks=0;

dbentry->n_block_time_read=0;

dbentry->n_block_time_write=0;

dbentry->stat_reset_timestamp=GetCurrentTimestamp();

{

INSTR_TIME_SET_CURRENT(io_time);

INSTR_TIME_SUBTRACT(io_time,io_start);

pgstat_count_buffer_read_time(INSTR_TIME_GET_MICROSEC(io_time));

INSTR_TIME_ADD(pgBufferUsage.time_read,io_time);

}

{

XLogRecPtrrecptr;

ErrorContextCallbackerrcontext;

instr_timeio_start,io_end;

instr_timeio_start,

io_time;

if (track_iotiming)

{

INSTR_TIME_SET_CURRENT(io_end);

INSTR_TIME_ACCUM_DIFF(pgBufferUsage.time_write,io_end,io_start);

INSTR_TIME_SET_CURRENT(io_time);

INSTR_TIME_SUBTRACT(io_time,io_start);

pgstat_count_buffer_write_time(INSTR_TIME_GET_MICROSEC(io_time));

INSTR_TIME_ADD(pgBufferUsage.time_write,io_time);

}

pgBufferUsage.shared_blks_written++;

externDatumpg_stat_get_db_stat_reset_time(PG_FUNCTION_ARGS);

externDatumpg_stat_get_db_temp_files(PG_FUNCTION_ARGS);

externDatumpg_stat_get_db_temp_bytes(PG_FUNCTION_ARGS);

externDatumpg_stat_get_db_block_time_read(PG_FUNCTION_ARGS);

externDatumpg_stat_get_db_block_time_write(PG_FUNCTION_ARGS);

externDatumpg_stat_get_bgwriter_timed_checkpoints(PG_FUNCTION_ARGS);

externDatumpg_stat_get_bgwriter_requested_checkpoints(PG_FUNCTION_ARGS);

PG_RETURN_INT64(result);

}

pg_stat_get_db_block_time_read(PG_FUNCTION_ARGS)

{

Oiddbid=PG_GETARG_OID(0);

int64result;

PgStat_StatDBEntry*dbentry;

if ((dbentry=pgstat_fetch_stat_dbentry(dbid))==NULL)

result=0;

result= (int64) (dbentry->n_block_time_read);

PG_RETURN_INT64(result);

}

pg_stat_get_db_block_time_write(PG_FUNCTION_ARGS)

{

Oiddbid=PG_GETARG_OID(0);

int64result;

PgStat_StatDBEntry*dbentry;

if ((dbentry=pgstat_fetch_stat_dbentry(dbid))==NULL)

result=0;

result= (int64) (dbentry->n_block_time_write);

PG_RETURN_INT64(result);

}

pg_stat_get_bgwriter_timed_checkpoints(PG_FUNCTION_ARGS)

{

DESCR("statistics: number of temporary files written");

DATA(insertOID=3151 (pg_stat_get_db_temp_bytesPGNSPPGUID121000fffftfs1020"26"_null__null__null__null_pg_stat_get_db_temp_bytes_null__null__null_ ));

DESCR("statistics: number of bytes in temporary files written");

DATA(insertOID=2844 (pg_stat_get_db_block_time_readPGNSPPGUID121000fffftfs1020"26"_null__null__null__null_pg_stat_get_db_block_time_read_null__null__null_ ));

DESCR("statistics: block read time in microseconds");

DATA(insertOID=2845 (pg_stat_get_db_block_time_writePGNSPPGUID121000fffftfs1020"26"_null__null__null__null_pg_stat_get_db_block_time_write_null__null__null_ ));

DESCR("statistics: block write time in microseconds");

DATA(insertOID=2769 (pg_stat_get_bgwriter_timed_checkpointsPGNSPPGUID121000fffftfs0020""_null__null__null__null_pg_stat_get_bgwriter_timed_checkpoints_null__null__null_ ));

DESCR("statistics: number of timed checkpoints started by the bgwriter");

DATA(insertOID=2770 (pg_stat_get_bgwriter_requested_checkpointsPGNSPPGUID121000fffftfs0020""_null__null__null__null_pg_stat_get_bgwriter_requested_checkpoints_null__null__null_ ));

intm_nentries;

intm_xact_commit;

intm_xact_rollback;

PgStat_Counterm_block_time_read;

PgStat_Counterm_block_time_write;

PgStat_TableEntrym_entry[PGSTAT_NUM_TABENTRIES];

}PgStat_MsgTabstat;

PgStat_Countern_temp_files;

PgStat_Countern_temp_bytes;

PgStat_Countern_deadlocks;

PgStat_Countern_block_time_read;/* times in microseconds */

PgStat_Countern_block_time_write;

TimestampTzstat_reset_timestamp;

externPgStat_MsgBgWriterBgWriterStats;

externPgStat_CounterpgStatBlockTimeRead;

externPgStat_CounterpgStatBlockTimeWrite;

if ((rel)->pgstat_info != NULL)\

(rel)->pgstat_info->t_counts.t_blocks_hit++;\

} while (0)

#definepgstat_count_buffer_read_time(n)\

pgStatBlockTimeRead += (n);

#definepgstat_count_buffer_write_time(n)\

pgStatBlockTimeWrite += (n);

externvoidpgstat_count_heap_insert(Relationrel,intn);

externvoidpgstat_count_heap_update(Relationrel,boolhot);

pg_stat_all_indexes | SELECT c.oid AS relid, i.oid AS indexrelid, n.nspname AS schemaname, c.relname, i.relname AS indexrelname, pg_stat_get_numscans(i.oid) AS idx_scan, pg_stat_get_tuples_returned(i.oid) AS idx_tup_read, pg_stat_get_tuples_fetched(i.oid) AS idx_tup_fetch FROM (((pg_class c JOIN pg_index x ON ((c.oid = x.indrelid))) JOIN pg_class i ON ((i.oid = x.indexrelid))) LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace))) WHERE (c.relkind = ANY (ARRAY['r'::"char", 't'::"char"]));

pg_stat_all_tables | SELECT c.oid AS relid, n.nspname AS schemaname, c.relname, pg_stat_get_numscans(c.oid) AS seq_scan, pg_stat_get_tuples_returned(c.oid) AS seq_tup_read, (sum(pg_stat_get_numscans(i.indexrelid)))::bigint AS idx_scan, ((sum(pg_stat_get_tuples_fetched(i.indexrelid)))::bigint + pg_stat_get_tuples_fetched(c.oid)) AS idx_tup_fetch, pg_stat_get_tuples_inserted(c.oid) AS n_tup_ins, pg_stat_get_tuples_updated(c.oid) AS n_tup_upd, pg_stat_get_tuples_deleted(c.oid) AS n_tup_del, pg_stat_get_tuples_hot_updated(c.oid) AS n_tup_hot_upd, pg_stat_get_live_tuples(c.oid) AS n_live_tup, pg_stat_get_dead_tuples(c.oid) AS n_dead_tup, pg_stat_get_last_vacuum_time(c.oid) AS last_vacuum, pg_stat_get_last_autovacuum_time(c.oid) AS last_autovacuum, pg_stat_get_last_analyze_time(c.oid) AS last_analyze, pg_stat_get_last_autoanalyze_time(c.oid) AS last_autoanalyze, pg_stat_get_vacuum_count(c.oid) AS vacuum_count, pg_stat_get_autovacuum_count(c.oid) AS autovacuum_count, pg_stat_get_analyze_count(c.oid) AS analyze_count, pg_stat_get_autoanalyze_count(c.oid) AS autoanalyze_count FROM ((pg_class c LEFT JOIN pg_index i ON ((c.oid = i.indrelid))) LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace))) WHERE (c.relkind = ANY (ARRAY['r'::"char", 't'::"char"])) GROUP BY c.oid, n.nspname, c.relname;

pg_stat_bgwriter | SELECT pg_stat_get_bgwriter_timed_checkpoints() AS checkpoints_timed, pg_stat_get_bgwriter_requested_checkpoints() AS checkpoints_req, pg_stat_get_bgwriter_buf_written_checkpoints() AS buffers_checkpoint, pg_stat_get_bgwriter_buf_written_clean() AS buffers_clean, pg_stat_get_bgwriter_maxwritten_clean() AS maxwritten_clean, pg_stat_get_buf_written_backend() AS buffers_backend, pg_stat_get_buf_fsync_backend() AS buffers_backend_fsync, pg_stat_get_buf_alloc() AS buffers_alloc, pg_stat_get_bgwriter_stat_reset_time() AS stats_reset;

pg_stat_database | SELECT d.oid AS datid, d.datname, pg_stat_get_db_numbackends(d.oid) AS numbackends, pg_stat_get_db_xact_commit(d.oid) AS xact_commit, pg_stat_get_db_xact_rollback(d.oid) AS xact_rollback, (pg_stat_get_db_blocks_fetched(d.oid) - pg_stat_get_db_blocks_hit(d.oid)) AS blks_read, pg_stat_get_db_blocks_hit(d.oid) AS blks_hit, pg_stat_get_db_tuples_returned(d.oid) AS tup_returned, pg_stat_get_db_tuples_fetched(d.oid) AS tup_fetched, pg_stat_get_db_tuples_inserted(d.oid) AS tup_inserted, pg_stat_get_db_tuples_updated(d.oid) AS tup_updated, pg_stat_get_db_tuples_deleted(d.oid) AS tup_deleted, pg_stat_get_db_conflict_all(d.oid) AS conflicts, pg_stat_get_db_temp_files(d.oid) AS temp_files, pg_stat_get_db_temp_bytes(d.oid) AS temp_bytes, pg_stat_get_db_deadlocks(d.oid) AS deadlocks, pg_stat_get_db_stat_reset_time(d.oid) AS stats_reset FROM pg_database d;

pg_stat_database | SELECT d.oid AS datid, d.datname, pg_stat_get_db_numbackends(d.oid) AS numbackends, pg_stat_get_db_xact_commit(d.oid) AS xact_commit, pg_stat_get_db_xact_rollback(d.oid) AS xact_rollback, (pg_stat_get_db_blocks_fetched(d.oid) - pg_stat_get_db_blocks_hit(d.oid)) AS blks_read, pg_stat_get_db_blocks_hit(d.oid) AS blks_hit, pg_stat_get_db_tuples_returned(d.oid) AS tup_returned, pg_stat_get_db_tuples_fetched(d.oid) AS tup_fetched, pg_stat_get_db_tuples_inserted(d.oid) AS tup_inserted, pg_stat_get_db_tuples_updated(d.oid) AS tup_updated, pg_stat_get_db_tuples_deleted(d.oid) AS tup_deleted, pg_stat_get_db_conflict_all(d.oid) AS conflicts, pg_stat_get_db_temp_files(d.oid) AS temp_files, pg_stat_get_db_temp_bytes(d.oid) AS temp_bytes, pg_stat_get_db_deadlocks(d.oid) AS deadlocks, (pg_stat_get_db_block_time_read(d.oid) / 1000) AS block_read_time, (pg_stat_get_db_block_time_write(d.oid) / 1000) AS block_write_time, pg_stat_get_db_stat_reset_time(d.oid) AS stats_reset FROM pg_database d;

pg_stat_database_conflicts | SELECT d.oid AS datid, d.datname, pg_stat_get_db_conflict_tablespace(d.oid) AS confl_tablespace, pg_stat_get_db_conflict_lock(d.oid) AS confl_lock, pg_stat_get_db_conflict_snapshot(d.oid) AS confl_snapshot, pg_stat_get_db_conflict_bufferpin(d.oid) AS confl_bufferpin, pg_stat_get_db_conflict_startup_deadlock(d.oid) AS confl_deadlock FROM pg_database d;

pg_stat_replication | SELECT s.pid, s.usesysid, u.rolname AS usename, s.application_name, s.client_addr, s.client_hostname, s.client_port, s.backend_start, w.state, w.sent_location, w.write_location, w.flush_location, w.replay_location, w.sync_priority, w.sync_state FROM pg_stat_get_activity(NULL::integer) s(datid, pid, usesysid, application_name, state, query, waiting, xact_start, query_start, backend_start, state_change, client_addr, client_hostname, client_port), pg_authid u, pg_stat_get_wal_senders() w(pid, state, sent_location, write_location, flush_location, replay_location, sync_priority, sync_state) WHERE ((s.usesysid = u.oid) AND (s.pid = w.pid));

pg_stat_sys_indexes | SELECT pg_stat_all_indexes.relid, pg_stat_all_indexes.indexrelid, pg_stat_all_indexes.schemaname, pg_stat_all_indexes.relname, pg_stat_all_indexes.indexrelname, pg_stat_all_indexes.idx_scan, pg_stat_all_indexes.idx_tup_read, pg_stat_all_indexes.idx_tup_fetch FROM pg_stat_all_indexes WHERE ((pg_stat_all_indexes.schemaname = ANY (ARRAY['pg_catalog'::name, 'information_schema'::name])) OR (pg_stat_all_indexes.schemaname ~ '^pg_toast'::text));