SET allow_in_place_tablespaces = true;

CREATE TABLESPACE regress_test_stats_tblspc LOCATION '';

SELECT sum(reads) AS stats_bulkreads_before

FROM pg_stat_io WHEREio_context = 'bulkread' \gset

FROM pg_stat_io WHEREcontext = 'bulkread' \gset

ALTER TABLE heaptest SET TABLESPACE regress_test_stats_tblspc;

-- Check that valid options are not rejected nor corruption reported

-- for a non-empty table

(1 row)

SELECT sum(reads) AS stats_bulkreads_after

FROM pg_stat_io WHEREio_context = 'bulkread' \gset

FROM pg_stat_io WHEREcontext = 'bulkread' \gset

SELECT :stats_bulkreads_after > :stats_bulkreads_before;

?column?

----------

SET allow_in_place_tablespaces= true;

CREATETABLESPACEregress_test_stats_tblspc LOCATION'';

SELECTsum(reads)AS stats_bulkreads_before

FROM pg_stat_ioWHEREio_context='bulkread' \gset

FROM pg_stat_ioWHEREcontext='bulkread' \gset

ALTERTABLE heaptestSET TABLESPACE regress_test_stats_tblspc;

SELECT pg_stat_force_next_flush();

SELECTsum(reads)AS stats_bulkreads_after

FROM pg_stat_ioWHEREio_context='bulkread' \gset

FROM pg_stat_ioWHEREcontext='bulkread' \gset

SELECT :stats_bulkreads_after> :stats_bulkreads_before;

CREATE ROLE regress_heaptest_role;

<row>

<entry role="catalog_table_entry">

<para role="column_definition">

<structfield>io_object</structfield> <type>text</type>

<structfield>object</structfield> <type>text</type>

</para>

<para>

Target object of an I/O operation. Possible values are:

<row>

<entry role="catalog_table_entry">

<para role="column_definition">

<structfield>io_context</structfield> <type>text</type>

<structfield>context</structfield> <type>text</type>

</para>

<para>

The context of an I/O operation. Possible values are:

<listitem>

<para>

<literal>normal</literal>: The default or standard

<varname>io_context</varname> for a type of I/O operation. For

<varname>context</varname> for a type of I/O operation. For

example, by default, relation data is read into and written out from

shared buffers. Thus, reads and writes of relation data to and from

shared buffers are tracked in <varname>io_context</varname>

shared buffers are tracked in <varname>context</varname>

<literal>normal</literal>.

</para>

</listitem>

<literal>vacuum</literal>: I/O operations performed outside of shared

buffers while vacuuming and analyzing permanent relations. Temporary

table vacuums use the same local buffer pool as other temporary table

IO operations and are tracked in <varname>io_context</varname>

IO operations and are tracked in <varname>context</varname>

<literal>normal</literal>.

</para>

</listitem>

buffer in order to make it available for another use.

</para>

<para>

In <varname>io_context</varname> <literal>normal</literal>, this counts

In <varname>context</varname> <literal>normal</literal>, this counts

the number of times a block was evicted from a buffer and replaced with

another block. In <varname>io_context</varname>s

another block. In <varname>context</varname>s

<literal>bulkwrite</literal>, <literal>bulkread</literal>, and

<literal>vacuum</literal>, this counts the number of times a block was

evicted from shared buffers in order to add the shared buffer to a

The number of times an existing buffer in a size-limited ring buffer

outside of shared buffers was reused as part of an I/O operation in the

<literal>bulkread</literal>, <literal>bulkwrite</literal>, or

<literal>vacuum</literal> <varname>io_context</varname>s.

<literal>vacuum</literal> <varname>context</varname>s.

</para>

</entry>

</row>

</para>

<para>

Number of <literal>fsync</literal> calls. These are only tracked in

<varname>io_context</varname> <literal>normal</literal>.

<varname>context</varname> <literal>normal</literal>.

</para>

</entry>

</row>

in some I/O contexts. These rows are omitted from the view. For example, the

checkpointer does not checkpoint temporary tables, so there will be no rows

for <varname>backend_type</varname> <literal>checkpointer</literal> and

<varname>io_object</varname> <literal>temp relation</literal>.

<varname>object</varname> <literal>temp relation</literal>.

</para>

<para>

In addition, some I/O operations will never be performed either by certain

backend types or on certain I/O objects and/or in certain I/O contexts.

These cells will be NULL. For example, temporary tables are not

<literal>fsync</literal>ed, so <varname>fsyncs</varname> will be NULL for

<varname>io_object</varname> <literal>temp relation</literal>. Also, the

<varname>object</varname> <literal>temp relation</literal>. Also, the

background writer does not perform reads, so <varname>reads</varname> will

be NULL in rows for <varname>backend_type</varname> <literal>background

writer</literal>.

b.backend_type,

b.io_object,

b.io_context,

b.object,

b.context,

b.reads,

b.read_time,

b.writes,

{

IO_COL_INVALID=-1,

IO_COL_BACKEND_TYPE,

IO_COL_IO_OBJECT,

IO_COL_IO_CONTEXT,

IO_COL_OBJECT,

IO_COL_CONTEXT,

IO_COL_READS,

IO_COL_READ_TIME,

IO_COL_WRITES,

continue;

values[IO_COL_BACKEND_TYPE]=bktype_desc;

values[IO_COL_IO_CONTEXT]=CStringGetTextDatum(context_name);

values[IO_COL_IO_OBJECT]=CStringGetTextDatum(obj_name);

values[IO_COL_CONTEXT]=CStringGetTextDatum(context_name);

values[IO_COL_OBJECT]=CStringGetTextDatum(obj_name);

values[IO_COL_RESET_TIME]=TimestampTzGetDatum(reset_time);

proparallel => 'r', prorettype => 'record', proargtypes => '',

proallargtypes => '{text,text,text,int8,float8,int8,float8,int8,float8,int8,int8,int8,int8,int8,float8,timestamptz}',

proargmodes => '{o,o,o,o,o,o,o,o,o,o,o,o,o,o,o,o}',

proargnames => '{backend_type,io_object,io_context,reads,read_time,writes,write_time,extends,extend_time,op_bytes,hits,evictions,reuses,fsyncs,fsync_time,stats_reset}',

proargnames => '{backend_type,object,context,reads,read_time,writes,write_time,extends,extend_time,op_bytes,hits,evictions,reuses,fsyncs,fsync_time,stats_reset}',

prosrc => 'pg_stat_get_io' },

{ oid => '1136', descr => 'statistics: information about WAL activity',

FROM pg_stat_get_activity(NULL::integer) s(datid, pid, usesysid, application_name, state, query, wait_event_type, wait_event, xact_start, query_start, backend_start, state_change, client_addr, client_hostname, client_port, backend_xid, backend_xmin, backend_type, ssl, sslversion, sslcipher, sslbits, ssl_client_dn, ssl_client_serial, ssl_issuer_dn, gss_auth, gss_princ, gss_enc, gss_deleg, leader_pid, query_id)

WHERE (client_port IS NOT NULL);

pg_stat_io| SELECT backend_type,

io_object,

io_context,

object,

context,

reads,

read_time,

writes,

fsyncs,

fsync_time,

stats_reset

FROM pg_stat_get_io() b(backend_type,io_object, io_context, reads, read_time, writes, write_time, extends, extend_time, op_bytes, hits, evictions, reuses, fsyncs, fsync_time, stats_reset);

FROM pg_stat_get_io() b(backend_type,object, context, reads, read_time, writes, write_time, extends, extend_time, op_bytes, hits, evictions, reuses, fsyncs, fsync_time, stats_reset);

pg_stat_progress_analyze| SELECT s.pid,

s.datid,

d.datname,

-- Create a regular table and insert some data to generate IOCONTEXT_NORMAL

-- extends.

SELECT sum(extends) AS io_sum_shared_before_extends

FROM pg_stat_io WHEREio_context = 'normal' ANDio_object = 'relation' \gset

FROM pg_stat_io WHEREcontext = 'normal' ANDobject = 'relation' \gset

SELECT sum(writes) AS writes, sum(fsyncs) AS fsyncs

FROM pg_stat_io

WHEREio_object = 'relation' \gset io_sum_shared_before_

WHEREobject = 'relation' \gset io_sum_shared_before_

CREATE TABLE test_io_shared(a int);

INSERT INTO test_io_shared SELECT i FROM generate_series(1,100)i;

SELECT pg_stat_force_next_flush();

(1 row)

SELECT sum(extends) AS io_sum_shared_after_extends

FROM pg_stat_io WHEREio_context = 'normal' ANDio_object = 'relation' \gset

FROM pg_stat_io WHEREcontext = 'normal' ANDobject = 'relation' \gset

SELECT :io_sum_shared_after_extends > :io_sum_shared_before_extends;

?column?

----------

CHECKPOINT;

SELECT sum(writes) AS writes, sum(fsyncs) AS fsyncs

FROM pg_stat_io

WHEREio_object = 'relation' \gset io_sum_shared_after_

WHEREobject = 'relation' \gset io_sum_shared_after_

SELECT :io_sum_shared_after_writes > :io_sum_shared_before_writes;

?column?

----------

-- Change the tablespace so that the table is rewritten directly, then SELECT

-- from it to cause it to be read back into shared buffers.

SELECT sum(reads) AS io_sum_shared_before_reads

FROM pg_stat_io WHEREio_context = 'normal' ANDio_object = 'relation' \gset

FROM pg_stat_io WHEREcontext = 'normal' ANDobject = 'relation' \gset

-- Do this in a transaction to prevent spurious failures due to concurrent accesses to our newly

-- rewritten table, e.g. by autovacuum.

BEGIN;

ALTER TABLE test_io_shared SET TABLESPACE regress_tblspace;

-- SELECT from the table so that the data is read into shared buffers and

--io_context 'normal',io_object 'relation' reads are counted.

--context 'normal',object 'relation' reads are counted.

SELECT COUNT(*) FROM test_io_shared;

count

-------

(1 row)

SELECT sum(reads) AS io_sum_shared_after_reads

FROM pg_stat_io WHEREio_context = 'normal' ANDio_object = 'relation' \gset

FROM pg_stat_io WHEREcontext = 'normal' ANDobject = 'relation' \gset

SELECT :io_sum_shared_after_reads > :io_sum_shared_before_reads;

?column?

----------

t

(1 row)

SELECT sum(hits) AS io_sum_shared_before_hits

FROM pg_stat_io WHEREio_context = 'normal' ANDio_object = 'relation' \gset

FROM pg_stat_io WHEREcontext = 'normal' ANDobject = 'relation' \gset

-- Select from the table again to count hits.

-- Ensure we generate hits by forcing a nested loop self-join with no

-- materialize node. The outer side's buffer will stay pinned, preventing its

(1 row)

SELECT sum(hits) AS io_sum_shared_after_hits

FROM pg_stat_io WHEREio_context = 'normal' ANDio_object = 'relation' \gset

FROM pg_stat_io WHEREcontext = 'normal' ANDobject = 'relation' \gset

SELECT :io_sum_shared_after_hits > :io_sum_shared_before_hits;

?column?

----------

CREATE TEMPORARY TABLE test_io_local(a int, b TEXT);

SELECT sum(extends) AS extends, sum(evictions) AS evictions, sum(writes) AS writes

FROM pg_stat_io

WHEREio_context = 'normal' ANDio_object = 'temp relation' \gset io_sum_local_before_

WHEREcontext = 'normal' ANDobject = 'temp relation' \gset io_sum_local_before_

-- Insert tuples into the temporary table, generating extends in the stats.

-- Insert enough values that we need to reuse and write out dirty local

-- buffers, generating evictions and writes.

(1 row)

SELECT sum(reads) AS io_sum_local_before_reads

FROM pg_stat_io WHEREio_context = 'normal' ANDio_object = 'temp relation' \gset

FROM pg_stat_io WHEREcontext = 'normal' ANDobject = 'temp relation' \gset

-- Read in evicted buffers, generating reads.

SELECT COUNT(*) FROM test_io_local;

count

sum(writes) AS writes,

sum(extends) AS extends

FROM pg_stat_io

WHEREio_context = 'normal' ANDio_object = 'temp relation' \gset io_sum_local_after_

WHEREcontext = 'normal' ANDobject = 'temp relation' \gset io_sum_local_after_

SELECT :io_sum_local_after_evictions > :io_sum_local_before_evictions,

:io_sum_local_after_reads > :io_sum_local_before_reads,

:io_sum_local_after_writes > :io_sum_local_before_writes,

(1 row)

SELECT sum(writes) AS io_sum_local_new_tblspc_writes

FROM pg_stat_io WHEREio_context = 'normal' ANDio_object = 'temp relation' \gset

FROM pg_stat_io WHEREcontext = 'normal' ANDobject = 'temp relation' \gset

SELECT :io_sum_local_new_tblspc_writes > :io_sum_local_after_writes;

?column?

----------

-- reads.

SET wal_skip_threshold = '1 kB';

SELECT sum(reuses) AS reuses, sum(reads) AS reads

FROM pg_stat_io WHEREio_context = 'vacuum' \gset io_sum_vac_strategy_before_

FROM pg_stat_io WHEREcontext = 'vacuum' \gset io_sum_vac_strategy_before_

CREATE TABLE test_io_vac_strategy(a int, b int) WITH (autovacuum_enabled = 'false');

INSERT INTO test_io_vac_strategy SELECT i, i from generate_series(1, 8000)i;

-- Ensure that the next VACUUM will need to perform IO by rewriting the table

(1 row)

SELECT sum(reuses) AS reuses, sum(reads) AS reads

FROM pg_stat_io WHEREio_context = 'vacuum' \gset io_sum_vac_strategy_after_

FROM pg_stat_io WHEREcontext = 'vacuum' \gset io_sum_vac_strategy_after_

SELECT :io_sum_vac_strategy_after_reads > :io_sum_vac_strategy_before_reads,

:io_sum_vac_strategy_after_reuses > :io_sum_vac_strategy_before_reuses;

?column? | ?column?

-- Test that extends done by a CTAS, which uses a BAS_BULKWRITE

-- BufferAccessStrategy, are tracked in pg_stat_io.

SELECT sum(extends) AS io_sum_bulkwrite_strategy_extends_before

FROM pg_stat_io WHEREio_context = 'bulkwrite' \gset

FROM pg_stat_io WHEREcontext = 'bulkwrite' \gset

CREATE TABLE test_io_bulkwrite_strategy AS SELECT i FROM generate_series(1,100)i;

SELECT pg_stat_force_next_flush();

pg_stat_force_next_flush

(1 row)

SELECT sum(extends) AS io_sum_bulkwrite_strategy_extends_after

FROM pg_stat_io WHEREio_context = 'bulkwrite' \gset

FROM pg_stat_io WHEREcontext = 'bulkwrite' \gset

SELECT :io_sum_bulkwrite_strategy_extends_after > :io_sum_bulkwrite_strategy_extends_before;

?column?

----------