diff --git a/contrib/pg_stat_statements/Makefile b/contrib/pg_stat_statements/Makefile index e086fd8..971773e 100644 --- a/contrib/pg_stat_statements/Makefile +++ b/contrib/pg_stat_statements/Makefile @@ -4,7 +4,8 @@ MODULE_big = pg_stat_statements OBJS = pg_stat_statements.o EXTENSION = pg_stat_statements -DATA = pg_stat_statements--1.0.sql pg_stat_statements--unpackaged--1.0.sql +DATA = pg_stat_statements--1.1.sql pg_stat_statements--1.0--1.1.sql \ + pg_stat_statements--unpackaged--1.0.sql ifdef USE_PGXS PG_CONFIG = pg_config diff --git a/contrib/pg_stat_statements/pg_stat_statements--1.0.sql b/contrib/pg_stat_statements/pg_stat_statements--1.0.sql deleted file mode 100644 index 5294a01..0000000 --- a/contrib/pg_stat_statements/pg_stat_statements--1.0.sql +++ /dev/null @@ -1,39 +0,0 @@ -/* contrib/pg_stat_statements/pg_stat_statements--1.0.sql */ - --- complain if script is sourced in psql, rather than via CREATE EXTENSION -\echo Use "CREATE EXTENSION pg_stat_statements" to load this file. \quit - --- Register functions. -CREATE FUNCTION pg_stat_statements_reset() -RETURNS void -AS 'MODULE_PATHNAME' -LANGUAGE C; - -CREATE FUNCTION pg_stat_statements( - OUT userid oid, - OUT dbid oid, - OUT query text, - OUT calls int8, - OUT total_time float8, - OUT rows int8, - OUT shared_blks_hit int8, - OUT shared_blks_read int8, - OUT shared_blks_written int8, - OUT local_blks_hit int8, - OUT local_blks_read int8, - OUT local_blks_written int8, - OUT temp_blks_read int8, - OUT temp_blks_written int8 -) -RETURNS SETOF record -AS 'MODULE_PATHNAME' -LANGUAGE C; - --- Register a view on the function for ease of use. -CREATE VIEW pg_stat_statements AS - SELECT * FROM pg_stat_statements(); - -GRANT SELECT ON pg_stat_statements TO PUBLIC; - --- Don't want this to be available to non-superusers. -REVOKE ALL ON FUNCTION pg_stat_statements_reset() FROM PUBLIC; diff --git a/contrib/pg_stat_statements/pg_stat_statements--1.1.sql b/contrib/pg_stat_statements/pg_stat_statements--1.1.sql new file mode 100644 index 0000000..8bd2868 --- /dev/null +++ b/contrib/pg_stat_statements/pg_stat_statements--1.1.sql @@ -0,0 +1,41 @@ +/* contrib/pg_stat_statements/pg_stat_statements--1.1.sql */ + +-- complain if script is sourced in psql, rather than via CREATE EXTENSION +\echo Use "CREATE EXTENSION pg_stat_statements" to load this file. \quit + +-- Register functions. +CREATE FUNCTION pg_stat_statements_reset() +RETURNS void +AS 'MODULE_PATHNAME' +LANGUAGE C; + +CREATE FUNCTION pg_stat_statements( + OUT userid oid, + OUT dbid oid, + OUT query text, + OUT calls int8, + OUT total_time float8, + OUT rows int8, + OUT shared_blks_hit int8, + OUT shared_blks_read int8, + OUT shared_blks_written int8, + OUT local_blks_hit int8, + OUT local_blks_read int8, + OUT local_blks_written int8, + OUT temp_blks_read int8, + OUT temp_blks_written int8, + OUT time_read float8, + OUT time_write float8 +) +RETURNS SETOF record +AS 'MODULE_PATHNAME' +LANGUAGE C; + +-- Register a view on the function for ease of use. +CREATE VIEW pg_stat_statements AS + SELECT * FROM pg_stat_statements(); + +GRANT SELECT ON pg_stat_statements TO PUBLIC; + +-- Don't want this to be available to non-superusers. +REVOKE ALL ON FUNCTION pg_stat_statements_reset() FROM PUBLIC; diff --git a/contrib/pg_stat_statements/pg_stat_statements.c b/contrib/pg_stat_statements/pg_stat_statements.c index 8dc3054..746dfd0 100644 --- a/contrib/pg_stat_statements/pg_stat_statements.c +++ b/contrib/pg_stat_statements/pg_stat_statements.c @@ -83,6 +83,8 @@ typedef struct Counters int64 local_blks_written; /* # of local disk blocks written */ int64 temp_blks_read; /* # of temp blocks read */ int64 temp_blks_written; /* # of temp blocks written */ + double time_read; /* time spent reading in seconds */ + double time_write; /* time spent writing in seconds */ double usage; /* usage factor */ } Counters; @@ -616,9 +618,9 @@ pgss_ProcessUtility(Node *parsetree, const char *queryString, instr_time start; instr_time duration; uint64 rows = 0; - BufferUsage bufusage; + BufferUsage bufusage_start, bufusage; - bufusage = pgBufferUsage; + bufusage_start = pgBufferUsage; INSTR_TIME_SET_CURRENT(start); nested_level++; @@ -649,21 +651,25 @@ pgss_ProcessUtility(Node *parsetree, const char *queryString, /* calc differences of buffer counters. */ bufusage.shared_blks_hit = - pgBufferUsage.shared_blks_hit - bufusage.shared_blks_hit; + pgBufferUsage.shared_blks_hit - bufusage_start.shared_blks_hit; bufusage.shared_blks_read = - pgBufferUsage.shared_blks_read - bufusage.shared_blks_read; + pgBufferUsage.shared_blks_read - bufusage_start.shared_blks_read; bufusage.shared_blks_written = - pgBufferUsage.shared_blks_written - bufusage.shared_blks_written; + pgBufferUsage.shared_blks_written - bufusage_start.shared_blks_written; bufusage.local_blks_hit = - pgBufferUsage.local_blks_hit - bufusage.local_blks_hit; + pgBufferUsage.local_blks_hit - bufusage_start.local_blks_hit; bufusage.local_blks_read = - pgBufferUsage.local_blks_read - bufusage.local_blks_read; + pgBufferUsage.local_blks_read - bufusage_start.local_blks_read; bufusage.local_blks_written = - pgBufferUsage.local_blks_written - bufusage.local_blks_written; + pgBufferUsage.local_blks_written - bufusage_start.local_blks_written; bufusage.temp_blks_read = - pgBufferUsage.temp_blks_read - bufusage.temp_blks_read; + pgBufferUsage.temp_blks_read - bufusage_start.temp_blks_read; bufusage.temp_blks_written = - pgBufferUsage.temp_blks_written - bufusage.temp_blks_written; + pgBufferUsage.temp_blks_written - bufusage_start.temp_blks_written; + bufusage.time_read = pgBufferUsage.time_read; + INSTR_TIME_SUBTRACT(bufusage.time_read, bufusage_start.time_read); + bufusage.time_write = pgBufferUsage.time_write; + INSTR_TIME_SUBTRACT(bufusage.time_write, bufusage_start.time_write); pgss_store(queryString, INSTR_TIME_GET_DOUBLE(duration), rows, &bufusage); @@ -772,6 +778,8 @@ pgss_store(const char *query, double total_time, uint64 rows, e->counters.local_blks_written += bufusage->local_blks_written; e->counters.temp_blks_read += bufusage->temp_blks_read; e->counters.temp_blks_written += bufusage->temp_blks_written; + e->counters.time_read += INSTR_TIME_GET_DOUBLE(bufusage->time_read); + e->counters.time_write += INSTR_TIME_GET_DOUBLE(bufusage->time_write); e->counters.usage += usage; SpinLockRelease(&e->mutex); } @@ -793,7 +801,7 @@ pg_stat_statements_reset(PG_FUNCTION_ARGS) PG_RETURN_VOID(); } -#define PG_STAT_STATEMENTS_COLS 14 +#define PG_STAT_STATEMENTS_COLS 16 /* * Retrieve statement statistics. @@ -893,6 +901,8 @@ pg_stat_statements(PG_FUNCTION_ARGS) values[i++] = Int64GetDatumFast(tmp.local_blks_written); values[i++] = Int64GetDatumFast(tmp.temp_blks_read); values[i++] = Int64GetDatumFast(tmp.temp_blks_written); + values[i++] = Float8GetDatumFast(tmp.time_read); + values[i++] = Float8GetDatumFast(tmp.time_write); Assert(i == PG_STAT_STATEMENTS_COLS); diff --git a/contrib/pg_stat_statements/pg_stat_statements.control b/contrib/pg_stat_statements/pg_stat_statements.control index 6f9a947..428fbb2 100644 --- a/contrib/pg_stat_statements/pg_stat_statements.control +++ b/contrib/pg_stat_statements/pg_stat_statements.control @@ -1,5 +1,5 @@ # pg_stat_statements extension comment = 'track execution statistics of all SQL statements executed' -default_version = '1.0' +default_version = '1.1' module_pathname = '$libdir/pg_stat_statements' relocatable = true diff --git a/doc/src/sgml/config.sgml b/doc/src/sgml/config.sgml index d1e628f..2576385 100644 --- a/doc/src/sgml/config.sgml +++ b/doc/src/sgml/config.sgml @@ -4162,6 +4162,21 @@ COPY postgres_log FROM '/full/path/to/logfile.csv' WITH csv; + + track_iotiming (boolean) + + track_iotiming configuration parameter + + + + Enables timing of database I/O calls. + This parameter is off by default, because it may cause significant + overhead if the platform doesn't support fast timing information. + Only superusers can change this setting. + + + + track_functions (enum) diff --git a/doc/src/sgml/monitoring.sgml b/doc/src/sgml/monitoring.sgml index b9dc1d2..3a8f0d8 100644 --- a/doc/src/sgml/monitoring.sgml +++ b/doc/src/sgml/monitoring.sgml @@ -116,7 +116,8 @@ postgres: user database host PostgreSQL's statistics collector is a subsystem that supports collection and reporting of information about server activity. Presently, the collector can count accesses to tables - and indexes in both disk-block and individual-row terms. It also tracks + and indexes in both disk-block and individual-row terms and time disk-block + accesses. It also tracks the total number of rows in each table, and information about vacuum and analyze actions for each table. It can also count calls to user-defined functions and the total time spent in each one. @@ -145,6 +146,11 @@ postgres: user database host + The parameter enables timing of I/O + requests. + + + The parameter enables tracking of usage of user-defined functions. @@ -402,8 +408,9 @@ postgres: user database host @@ -423,7 +430,8 @@ postgres: user database host pg_statio_all_indexespg_statio_all_indexes For each index in the current database, the table and index OID, schema, table and index name, - numbers of disk blocks read and buffer hits in that index. + numbers of disk blocks read, microseconds spent reading the blocks + and buffer hits in that index. @@ -522,7 +530,10 @@ postgres: user database host PostgreSQL buffer cache might still reside in the kernel's I/O cache, and might therefore still be fetched without - requiring a physical read. Users interested in obtaining more + requiring a physical read. + Timing info shows how much user queries were delayed by buffer reads + in aggregate. + Users interested in obtaining more detailed information on PostgreSQL I/O behavior are advised to use the PostgreSQL statistics collector in combination with operating system utilities that allow insight @@ -598,6 +609,15 @@ postgres: user database host + pg_stat_get_db_blocks_time(oid) + bigint + + Microseconds spent across all backends waiting for disk block fetch + requests for database + + + + pg_stat_get_db_tuples_returned(oid) bigint @@ -783,6 +803,15 @@ postgres: user database host + pg_stat_get_blocks_time(oid) + bigint + + Microseconds spent across all backends waiting for disk block requests + for table or index + + + + pg_stat_get_last_vacuum_time(oid) timestamptz @@ -924,6 +953,14 @@ postgres: user database host + pg_stat_get_xact_blocks_time(oid) + bigint + + Microseconds spent waiting for disk block requests for table or index, in the current transaction + + + + pg_backend_pid() integer diff --git a/doc/src/sgml/pgstatstatements.sgml b/doc/src/sgml/pgstatstatements.sgml index 5a0230c..2d60bdb 100644 --- a/doc/src/sgml/pgstatstatements.sgml +++ b/doc/src/sgml/pgstatstatements.sgml @@ -141,6 +141,20 @@ Total number of temp blocks writes by the statement + + time_read + double precision + + Total time spent by the statement for reading blocks, in seconds + + + + time_write + double precision + + Total time spent by the statement for writing out dirty blocks, in seconds + + diff --git a/src/backend/catalog/system_views.sql b/src/backend/catalog/system_views.sql index 2253ca8..23362ea 100644 --- a/src/backend/catalog/system_views.sql +++ b/src/backend/catalog/system_views.sql @@ -415,15 +415,19 @@ CREATE VIEW pg_statio_all_tables AS pg_stat_get_blocks_fetched(C.oid) - pg_stat_get_blocks_hit(C.oid) AS heap_blks_read, pg_stat_get_blocks_hit(C.oid) AS heap_blks_hit, + pg_stat_get_blocks_time(C.oid) AS heap_blks_time, sum(pg_stat_get_blocks_fetched(I.indexrelid) - pg_stat_get_blocks_hit(I.indexrelid))::bigint AS idx_blks_read, sum(pg_stat_get_blocks_hit(I.indexrelid))::bigint AS idx_blks_hit, + sum(pg_stat_get_blocks_time(I.indexrelid))::bigint AS idx_blks_time, pg_stat_get_blocks_fetched(T.oid) - pg_stat_get_blocks_hit(T.oid) AS toast_blks_read, pg_stat_get_blocks_hit(T.oid) AS toast_blks_hit, + pg_stat_get_blocks_time(T.oid) AS toast_blks_time, pg_stat_get_blocks_fetched(X.oid) - pg_stat_get_blocks_hit(X.oid) AS tidx_blks_read, - pg_stat_get_blocks_hit(X.oid) AS tidx_blks_hit + pg_stat_get_blocks_hit(X.oid) AS tidx_blks_hit, + pg_stat_get_blocks_time(X.oid) AS tidx_blks_time FROM pg_class C LEFT JOIN pg_index I ON C.oid = I.indrelid LEFT JOIN pg_class T ON C.reltoastrelid = T.oid LEFT JOIN @@ -477,7 +481,8 @@ CREATE VIEW pg_statio_all_indexes AS I.relname AS indexrelname, pg_stat_get_blocks_fetched(I.oid) - pg_stat_get_blocks_hit(I.oid) AS idx_blks_read, - pg_stat_get_blocks_hit(I.oid) AS idx_blks_hit + pg_stat_get_blocks_hit(I.oid) AS idx_blks_hit, + pg_stat_get_blocks_time(I.oid) AS idx_blks_time FROM pg_class C JOIN pg_index X ON C.oid = X.indrelid JOIN pg_class I ON I.oid = X.indexrelid @@ -501,7 +506,8 @@ CREATE VIEW pg_statio_all_sequences AS C.relname AS relname, pg_stat_get_blocks_fetched(C.oid) - pg_stat_get_blocks_hit(C.oid) AS blks_read, - pg_stat_get_blocks_hit(C.oid) AS blks_hit + pg_stat_get_blocks_hit(C.oid) AS blks_hit, + pg_stat_get_blocks_time(C.oid) AS blks_time FROM pg_class C LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace) WHERE C.relkind = 'S'; @@ -568,6 +574,7 @@ CREATE VIEW pg_stat_database AS 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_blocks_time(D.oid) AS blks_time, 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, diff --git a/src/backend/commands/explain.c b/src/backend/commands/explain.c index e38de5c..b9c86b7 100644 --- a/src/backend/commands/explain.c +++ b/src/backend/commands/explain.c @@ -1150,9 +1150,12 @@ ExplainNode(PlanState *planstate, List *ancestors, usage->local_blks_written); bool has_temp = (usage->temp_blks_read > 0 || usage->temp_blks_written); + bool has_timing = (!INSTR_TIME_IS_ZERO(usage->time_read) || + !INSTR_TIME_IS_ZERO(usage->time_write)); + /* Show only positive counter values. */ - if (has_shared || has_local || has_temp) + if (has_shared || has_local || has_temp || has_timing) { appendStringInfoSpaces(es->str, es->indent * 2); appendStringInfoString(es->str, "Buffers:"); @@ -1197,6 +1200,18 @@ ExplainNode(PlanState *planstate, List *ancestors, appendStringInfo(es->str, " written=%ld", usage->temp_blks_written); } + if (has_timing) + { + appendStringInfoString(es->str, " timing"); + if (!INSTR_TIME_IS_ZERO(usage->time_read)) { + appendStringInfo(es->str, " read=%0.2f", + INSTR_TIME_GET_MILLISEC(usage->time_read)); + } + if (!INSTR_TIME_IS_ZERO(usage->time_write)) { + appendStringInfo(es->str, " write=%0.2f", + INSTR_TIME_GET_MILLISEC(usage->time_write)); + } + } appendStringInfoChar(es->str, '\n'); } } @@ -1210,6 +1225,8 @@ ExplainNode(PlanState *planstate, List *ancestors, ExplainPropertyLong("Local Written Blocks", usage->local_blks_written, es); ExplainPropertyLong("Temp Read Blocks", usage->temp_blks_read, es); ExplainPropertyLong("Temp Written Blocks", usage->temp_blks_written, es); + ExplainPropertyFloat("Read Waits", INSTR_TIME_GET_MILLISEC(usage->time_read), 3, es); + ExplainPropertyFloat("Write Waits", INSTR_TIME_GET_MILLISEC(usage->time_write), 3, es); } } diff --git a/src/backend/executor/instrument.c b/src/backend/executor/instrument.c index 9d30200..fdee851 100644 --- a/src/backend/executor/instrument.c +++ b/src/backend/executor/instrument.c @@ -135,4 +135,6 @@ BufferUsageAccumDiff(BufferUsage *dst, dst->local_blks_written += add->local_blks_written - sub->local_blks_written; dst->temp_blks_read += add->temp_blks_read - sub->temp_blks_read; dst->temp_blks_written += add->temp_blks_written - sub->temp_blks_written; + INSTR_TIME_ACCUM_DIFF(dst->time_read, add->time_read, sub->time_read); + INSTR_TIME_ACCUM_DIFF(dst->time_write, add->time_write, sub->time_write); } diff --git a/src/backend/postmaster/pgstat.c b/src/backend/postmaster/pgstat.c index 24f4cde..49ffc3e 100644 --- a/src/backend/postmaster/pgstat.c +++ b/src/backend/postmaster/pgstat.c @@ -3255,6 +3255,7 @@ pgstat_get_db_entry(Oid databaseid, bool create) result->n_xact_rollback = 0; result->n_blocks_fetched = 0; result->n_blocks_hit = 0; + INSTR_TIME_SET_ZERO(result->blocks_time); result->n_tuples_returned = 0; result->n_tuples_fetched = 0; result->n_tuples_inserted = 0; @@ -3326,6 +3327,7 @@ pgstat_get_tab_entry(PgStat_StatDBEntry *dbentry, Oid tableoid, bool create) result->changes_since_analyze = 0; result->blocks_fetched = 0; result->blocks_hit = 0; + INSTR_TIME_SET_ZERO(result->blocks_time); result->vacuum_timestamp = 0; result->vacuum_count = 0; result->autovac_vacuum_timestamp = 0; @@ -4014,6 +4016,7 @@ pgstat_recv_tabstat(PgStat_MsgTabstat *msg, int len) tabentry->changes_since_analyze = tabmsg->t_counts.t_changed_tuples; tabentry->blocks_fetched = tabmsg->t_counts.t_blocks_fetched; tabentry->blocks_hit = tabmsg->t_counts.t_blocks_hit; + tabentry->blocks_time = tabmsg->t_counts.t_blocks_time; tabentry->vacuum_timestamp = 0; tabentry->vacuum_count = 0; @@ -4041,6 +4044,7 @@ pgstat_recv_tabstat(PgStat_MsgTabstat *msg, int len) tabentry->changes_since_analyze += tabmsg->t_counts.t_changed_tuples; tabentry->blocks_fetched += tabmsg->t_counts.t_blocks_fetched; tabentry->blocks_hit += tabmsg->t_counts.t_blocks_hit; + INSTR_TIME_ADD(tabentry->blocks_time, tabmsg->t_counts.t_blocks_time); } /* Clamp n_live_tuples in case of negative delta_live_tuples */ @@ -4058,6 +4062,7 @@ pgstat_recv_tabstat(PgStat_MsgTabstat *msg, int len) dbentry->n_tuples_deleted += tabmsg->t_counts.t_tuples_deleted; dbentry->n_blocks_fetched += tabmsg->t_counts.t_blocks_fetched; dbentry->n_blocks_hit += tabmsg->t_counts.t_blocks_hit; + INSTR_TIME_ADD(dbentry->blocks_time, tabmsg->t_counts.t_blocks_time); } } @@ -4171,6 +4176,7 @@ pgstat_recv_resetcounter(PgStat_MsgResetcounter *msg, int len) dbentry->n_xact_rollback = 0; dbentry->n_blocks_fetched = 0; dbentry->n_blocks_hit = 0; + INSTR_TIME_SET_ZERO(dbentry->blocks_time); dbentry->n_tuples_returned = 0; dbentry->n_tuples_fetched = 0; dbentry->n_tuples_inserted = 0; diff --git a/src/backend/storage/buffer/bufmgr.c b/src/backend/storage/buffer/bufmgr.c index 71fe8c6..32fbfd5 100644 --- a/src/backend/storage/buffer/bufmgr.c +++ b/src/backend/storage/buffer/bufmgr.c @@ -67,6 +67,7 @@ bool zero_damaged_pages = false; int bgwriter_lru_maxpages = 100; double bgwriter_lru_multiplier = 2.0; +bool track_iotiming = false; /* * How many buffers PrefetchBuffer callers should try to stay ahead of their @@ -86,7 +87,7 @@ static volatile BufferDesc *PinCountWaitBuf = NULL; static Buffer ReadBuffer_common(SMgrRelation reln, char relpersistence, ForkNumber forkNum, BlockNumber blockNum, ReadBufferMode mode, BufferAccessStrategy strategy, - bool *hit); + bool *hit, instr_time *io_time); static bool PinBuffer(volatile BufferDesc *buf, BufferAccessStrategy strategy); static void PinBuffer_Locked(volatile BufferDesc *buf); static void UnpinBuffer(volatile BufferDesc *buf, bool fixOwner); @@ -224,6 +225,7 @@ ReadBufferExtended(Relation reln, ForkNumber forkNum, BlockNumber blockNum, ReadBufferMode mode, BufferAccessStrategy strategy) { bool hit; + instr_time io_time; Buffer buf; /* Open it at the smgr level if not already done */ @@ -245,9 +247,11 @@ ReadBufferExtended(Relation reln, ForkNumber forkNum, BlockNumber blockNum, */ pgstat_count_buffer_read(reln); buf = ReadBuffer_common(reln->rd_smgr, reln->rd_rel->relpersistence, - forkNum, blockNum, mode, strategy, &hit); + forkNum, blockNum, mode, strategy, &hit, &io_time); if (hit) pgstat_count_buffer_hit(reln); + else + pgstat_count_buffer_time(reln, io_time); return buf; } @@ -267,11 +271,12 @@ ReadBufferWithoutRelcache(RelFileNode rnode, ForkNumber forkNum, BufferAccessStrategy strategy) { bool hit; + instr_time io_time; SMgrRelation smgr = smgropen(rnode, InvalidBackendId); return ReadBuffer_common(smgr, RELPERSISTENCE_PERMANENT, forkNum, blockNum, - mode, strategy, &hit); + mode, strategy, &hit, &io_time); } @@ -279,19 +284,22 @@ ReadBufferWithoutRelcache(RelFileNode rnode, ForkNumber forkNum, * ReadBuffer_common -- common logic for all ReadBuffer variants * * *hit is set to true if the request was satisfied from shared buffer cache. + * If track_iotiming is enabled, *io_time is set to the time the read took. */ static Buffer ReadBuffer_common(SMgrRelation smgr, char relpersistence, ForkNumber forkNum, BlockNumber blockNum, ReadBufferMode mode, - BufferAccessStrategy strategy, bool *hit) + BufferAccessStrategy strategy, bool *hit, instr_time *io_time) { volatile BufferDesc *bufHdr; Block bufBlock; bool found; bool isExtend; bool isLocalBuf = SmgrIsTemp(smgr); + instr_time io_start, io_end; *hit = false; + INSTR_TIME_SET_ZERO(*io_time); /* Make sure we will have room to remember the buffer pin */ ResourceOwnerEnlargeBuffers(CurrentResourceOwner); @@ -437,8 +445,18 @@ ReadBuffer_common(SMgrRelation smgr, char relpersistence, ForkNumber forkNum, MemSet((char *) bufBlock, 0, BLCKSZ); else { + if (track_iotiming) + INSTR_TIME_SET_CURRENT(io_start); + smgrread(smgr, forkNum, blockNum, (char *) bufBlock); + if (track_iotiming) + { + INSTR_TIME_SET_CURRENT(io_end); + INSTR_TIME_ACCUM_DIFF(*io_time, io_end, io_start); + INSTR_TIME_ADD(pgBufferUsage.time_read, *io_time); + } + /* check for garbage data */ if (!PageHeaderIsValid((PageHeader) bufBlock)) { @@ -1860,6 +1878,7 @@ FlushBuffer(volatile BufferDesc *buf, SMgrRelation reln) { XLogRecPtr recptr; ErrorContextCallback errcontext; + instr_time io_start, io_end; /* * Acquire the buffer's io_in_progress lock. If StartBufferIO returns @@ -1907,12 +1926,21 @@ FlushBuffer(volatile BufferDesc *buf, SMgrRelation reln) buf->flags &= ~BM_JUST_DIRTIED; UnlockBufHdr(buf); + if (track_iotiming) + INSTR_TIME_SET_CURRENT(io_start); + smgrwrite(reln, buf->tag.forkNum, buf->tag.blockNum, (char *) BufHdrGetBlock(buf), false); + if (track_iotiming) + { + INSTR_TIME_SET_CURRENT(io_end); + INSTR_TIME_ACCUM_DIFF(pgBufferUsage.time_write, io_end, io_start); + } + pgBufferUsage.shared_blks_written++; /* @@ -2648,6 +2676,7 @@ WaitIO(volatile BufferDesc *buf) static bool StartBufferIO(volatile BufferDesc *buf, bool forInput) { + instr_time wait_start, wait_end; Assert(!InProgressBuf); for (;;) @@ -2656,7 +2685,28 @@ StartBufferIO(volatile BufferDesc *buf, bool forInput) * Grab the io_in_progress lock so that other processes can wait for * me to finish the I/O. */ - LWLockAcquire(buf->io_in_progress_lock, LW_EXCLUSIVE); + if (forInput && track_iotiming) { + /* + * We need to time the lock wait to account for I/O waits where + * someone else is doing the work for us. Conditional acquire + * avoids double timing overhead when we do the I/O ourselves. + */ + if (!LWLockConditionalAcquire(buf->io_in_progress_lock, LW_EXCLUSIVE)) { + INSTR_TIME_SET_CURRENT(wait_start); + + LWLockAcquire(buf->io_in_progress_lock, LW_EXCLUSIVE); + + /* + * Only do backend local accounting, stats collector will get the + * wait from the backend doing the I/O. + */ + INSTR_TIME_SET_CURRENT(wait_end); + INSTR_TIME_ACCUM_DIFF(pgBufferUsage.time_read, wait_end, wait_start); + } + } else { + LWLockAcquire(buf->io_in_progress_lock, LW_EXCLUSIVE); + } + LockBufHdr(buf); diff --git a/src/backend/utils/adt/pgstatfuncs.c b/src/backend/utils/adt/pgstatfuncs.c index 7792b33..4e42ce7 100644 --- a/src/backend/utils/adt/pgstatfuncs.c +++ b/src/backend/utils/adt/pgstatfuncs.c @@ -22,6 +22,7 @@ #include "utils/builtins.h" #include "utils/inet.h" #include "utils/timestamp.h" +#include "portability/instr_time.h" /* bogus ... these externs should be in a header file */ extern Datum pg_stat_get_numscans(PG_FUNCTION_ARGS); @@ -35,6 +36,7 @@ extern Datum pg_stat_get_live_tuples(PG_FUNCTION_ARGS); extern Datum pg_stat_get_dead_tuples(PG_FUNCTION_ARGS); extern Datum pg_stat_get_blocks_fetched(PG_FUNCTION_ARGS); extern Datum pg_stat_get_blocks_hit(PG_FUNCTION_ARGS); +extern Datum pg_stat_get_blocks_time(PG_FUNCTION_ARGS); extern Datum pg_stat_get_last_vacuum_time(PG_FUNCTION_ARGS); extern Datum pg_stat_get_last_autovacuum_time(PG_FUNCTION_ARGS); extern Datum pg_stat_get_last_analyze_time(PG_FUNCTION_ARGS); @@ -67,6 +69,7 @@ extern Datum pg_stat_get_db_xact_commit(PG_FUNCTION_ARGS); extern Datum pg_stat_get_db_xact_rollback(PG_FUNCTION_ARGS); extern Datum pg_stat_get_db_blocks_fetched(PG_FUNCTION_ARGS); extern Datum pg_stat_get_db_blocks_hit(PG_FUNCTION_ARGS); +extern Datum pg_stat_get_db_blocks_time(PG_FUNCTION_ARGS); extern Datum pg_stat_get_db_tuples_returned(PG_FUNCTION_ARGS); extern Datum pg_stat_get_db_tuples_fetched(PG_FUNCTION_ARGS); extern Datum pg_stat_get_db_tuples_inserted(PG_FUNCTION_ARGS); @@ -99,6 +102,7 @@ extern Datum pg_stat_get_xact_tuples_deleted(PG_FUNCTION_ARGS); extern Datum pg_stat_get_xact_tuples_hot_updated(PG_FUNCTION_ARGS); extern Datum pg_stat_get_xact_blocks_fetched(PG_FUNCTION_ARGS); extern Datum pg_stat_get_xact_blocks_hit(PG_FUNCTION_ARGS); +extern Datum pg_stat_get_xact_blocks_time(PG_FUNCTION_ARGS); extern Datum pg_stat_get_xact_function_calls(PG_FUNCTION_ARGS); extern Datum pg_stat_get_xact_function_time(PG_FUNCTION_ARGS); @@ -289,6 +293,22 @@ pg_stat_get_blocks_hit(PG_FUNCTION_ARGS) } Datum +pg_stat_get_blocks_time(PG_FUNCTION_ARGS) +{ + Oid relid = PG_GETARG_OID(0); + double result; + PgStat_StatTabEntry *tabentry; + + if ((tabentry = pgstat_fetch_stat_tabentry(relid)) == NULL) + result = 0; + else + /* Cast overflows in about 300'000 years of io time */ + result = (int64) INSTR_TIME_GET_MICROSEC(tabentry->blocks_time); + + PG_RETURN_INT64(result); +} + +Datum pg_stat_get_last_vacuum_time(PG_FUNCTION_ARGS) { Oid relid = PG_GETARG_OID(0); @@ -1083,6 +1103,22 @@ pg_stat_get_db_blocks_hit(PG_FUNCTION_ARGS) Datum +pg_stat_get_db_blocks_time(PG_FUNCTION_ARGS) +{ + Oid dbid = PG_GETARG_OID(0); + int64 result; + PgStat_StatDBEntry *dbentry; + + if ((dbentry = pgstat_fetch_stat_dbentry(dbid)) == NULL) + result = 0; + else + result = (int64) INSTR_TIME_GET_MICROSEC(dbentry->blocks_time); + + PG_RETURN_INT64(result); +} + + +Datum pg_stat_get_db_tuples_returned(PG_FUNCTION_ARGS) { Oid dbid = PG_GETARG_OID(0); @@ -1482,6 +1518,21 @@ pg_stat_get_xact_blocks_hit(PG_FUNCTION_ARGS) } Datum +pg_stat_get_xact_blocks_time(PG_FUNCTION_ARGS) +{ + Oid relid = PG_GETARG_OID(0); + int64 result; + PgStat_TableStatus *tabentry; + + if ((tabentry = find_tabstat_entry(relid)) == NULL) + result = 0; + else + result = (int64) INSTR_TIME_GET_MICROSEC(tabentry->t_counts.t_blocks_time); + + PG_RETURN_INT64(result); +} + +Datum pg_stat_get_xact_function_calls(PG_FUNCTION_ARGS) { Oid funcid = PG_GETARG_OID(0); diff --git a/src/backend/utils/misc/guc.c b/src/backend/utils/misc/guc.c index da7b6d4..b78908c 100644 --- a/src/backend/utils/misc/guc.c +++ b/src/backend/utils/misc/guc.c @@ -1017,6 +1017,15 @@ static struct config_bool ConfigureNamesBool[] = true, NULL, NULL, NULL }, + { + {"track_iotiming", PGC_SUSET, STATS_COLLECTOR, + gettext_noop("Collects timing information for database IO activity."), + NULL + }, + &track_iotiming, + false, + NULL, NULL, NULL + }, { {"update_process_title", PGC_SUSET, STATS_COLLECTOR, diff --git a/src/backend/utils/misc/postgresql.conf.sample b/src/backend/utils/misc/postgresql.conf.sample index 315db46..9877afc 100644 --- a/src/backend/utils/misc/postgresql.conf.sample +++ b/src/backend/utils/misc/postgresql.conf.sample @@ -420,6 +420,7 @@ #track_activities = on #track_counts = on +#track_iotiming = off #track_functions = none # none, pl, all #track_activity_query_size = 1024 # (change requires restart) #update_process_title = on diff --git a/src/include/catalog/pg_proc.h b/src/include/catalog/pg_proc.h index 28e53b7..b837fa8 100644 --- a/src/include/catalog/pg_proc.h +++ b/src/include/catalog/pg_proc.h @@ -2530,6 +2530,8 @@ DATA(insert OID = 1934 ( pg_stat_get_blocks_fetched PGNSP PGUID 12 1 0 0 0 f f DESCR("statistics: number of blocks fetched"); DATA(insert OID = 1935 ( pg_stat_get_blocks_hit PGNSP PGUID 12 1 0 0 0 f f f t f s 1 0 20 "26" _null_ _null_ _null_ _null_ pg_stat_get_blocks_hit _null_ _null_ _null_ )); DESCR("statistics: number of blocks found in cache"); +DATA(insert OID = 3947 ( pg_stat_get_blocks_time PGNSP PGUID 12 1 0 0 0 f f f t f s 1 0 20 "26" _null_ _null_ _null_ _null_ pg_stat_get_blocks_time _null_ _null_ _null_ )); +DESCR("statistics: duration of block reads"); DATA(insert OID = 2781 ( pg_stat_get_last_vacuum_time PGNSP PGUID 12 1 0 0 0 f f f t f s 1 0 1184 "26" _null_ _null_ _null_ _null_ pg_stat_get_last_vacuum_time _null_ _null_ _null_ )); DESCR("statistics: last manual vacuum time for a table"); DATA(insert OID = 2782 ( pg_stat_get_last_autovacuum_time PGNSP PGUID 12 1 0 0 0 f f f t f s 1 0 1184 "26" _null_ _null_ _null_ _null_ pg_stat_get_last_autovacuum_time _null_ _null_ _null_ )); @@ -2584,6 +2586,8 @@ DATA(insert OID = 1944 ( pg_stat_get_db_blocks_fetched PGNSP PGUID 12 1 0 0 0 f DESCR("statistics: blocks fetched for database"); DATA(insert OID = 1945 ( pg_stat_get_db_blocks_hit PGNSP PGUID 12 1 0 0 0 f f f t f s 1 0 20 "26" _null_ _null_ _null_ _null_ pg_stat_get_db_blocks_hit _null_ _null_ _null_ )); DESCR("statistics: blocks found in cache for database"); +DATA(insert OID = 3948 ( pg_stat_get_db_blocks_time PGNSP PGUID 12 1 0 0 0 f f f t f s 1 0 20 "26" _null_ _null_ _null_ _null_ pg_stat_get_db_blocks_time _null_ _null_ _null_ )); +DESCR("statistics: duration of block reads for database"); DATA(insert OID = 2758 ( pg_stat_get_db_tuples_returned PGNSP PGUID 12 1 0 0 0 f f f t f s 1 0 20 "26" _null_ _null_ _null_ _null_ pg_stat_get_db_tuples_returned _null_ _null_ _null_ )); DESCR("statistics: tuples returned for database"); DATA(insert OID = 2759 ( pg_stat_get_db_tuples_fetched PGNSP PGUID 12 1 0 0 0 f f f t f s 1 0 20 "26" _null_ _null_ _null_ _null_ pg_stat_get_db_tuples_fetched _null_ _null_ _null_ )); @@ -2652,6 +2656,8 @@ DATA(insert OID = 3044 ( pg_stat_get_xact_blocks_fetched PGNSP PGUID 12 1 0 0 DESCR("statistics: number of blocks fetched in current transaction"); DATA(insert OID = 3045 ( pg_stat_get_xact_blocks_hit PGNSP PGUID 12 1 0 0 0 f f f t f v 1 0 20 "26" _null_ _null_ _null_ _null_ pg_stat_get_xact_blocks_hit _null_ _null_ _null_ )); DESCR("statistics: number of blocks found in cache in current transaction"); +DATA(insert OID = 3949 ( pg_stat_get_xact_blocks_time PGNSP PGUID 12 1 0 0 0 f f f t f v 1 0 20 "26" _null_ _null_ _null_ _null_ pg_stat_get_xact_blocks_time _null_ _null_ _null_ )); +DESCR("statistics: duration of block reads in current transaction"); DATA(insert OID = 3046 ( pg_stat_get_xact_function_calls PGNSP PGUID 12 1 0 0 0 f f f t f v 1 0 20 "26" _null_ _null_ _null_ _null_ pg_stat_get_xact_function_calls _null_ _null_ _null_ )); DESCR("statistics: number of function calls in current transaction"); DATA(insert OID = 3047 ( pg_stat_get_xact_function_time PGNSP PGUID 12 1 0 0 0 f f f t f v 1 0 20 "26" _null_ _null_ _null_ _null_ pg_stat_get_xact_function_time _null_ _null_ _null_ )); diff --git a/src/include/executor/instrument.h b/src/include/executor/instrument.h index 22c3106..b301be3 100644 --- a/src/include/executor/instrument.h +++ b/src/include/executor/instrument.h @@ -26,6 +26,8 @@ typedef struct BufferUsage long local_blks_written; /* # of local disk blocks written */ long temp_blks_read; /* # of temp blocks read */ long temp_blks_written; /* # of temp blocks written */ + instr_time time_read; /* time spent reading */ + instr_time time_write; /* time spent writing */ } BufferUsage; /* Flag bits included in InstrAlloc's instrument_options bitmask */ diff --git a/src/include/pgstat.h b/src/include/pgstat.h index 651b7d9..6dc9a2e 100644 --- a/src/include/pgstat.h +++ b/src/include/pgstat.h @@ -95,6 +95,7 @@ typedef struct PgStat_TableCounts PgStat_Counter t_blocks_fetched; PgStat_Counter t_blocks_hit; + instr_time t_blocks_time; } PgStat_TableCounts; /* Possible targets for resetting cluster-wide shared values */ @@ -496,6 +497,7 @@ typedef struct PgStat_StatDBEntry PgStat_Counter n_xact_rollback; PgStat_Counter n_blocks_fetched; PgStat_Counter n_blocks_hit; + instr_time blocks_time; PgStat_Counter n_tuples_returned; PgStat_Counter n_tuples_fetched; PgStat_Counter n_tuples_inserted; @@ -543,6 +545,7 @@ typedef struct PgStat_StatTabEntry PgStat_Counter blocks_fetched; PgStat_Counter blocks_hit; + instr_time blocks_time; TimestampTz vacuum_timestamp; /* user initiated vacuum */ PgStat_Counter vacuum_count; @@ -765,6 +768,13 @@ extern void pgstat_initstats(Relation rel); if ((rel)->pgstat_info != NULL) \ (rel)->pgstat_info->t_counts.t_blocks_hit++; \ } while (0) +#define pgstat_count_buffer_time(rel, io) \ + do { \ + if ((rel)->pgstat_info != NULL) \ + INSTR_TIME_ADD( \ + (rel)->pgstat_info->t_counts.t_blocks_time, \ + (io)); \ + } while (0) extern void pgstat_count_heap_insert(Relation rel, int n); extern void pgstat_count_heap_update(Relation rel, bool hot); diff --git a/src/include/storage/bufmgr.h b/src/include/storage/bufmgr.h index 49b5d31..4009cc7 100644 --- a/src/include/storage/bufmgr.h +++ b/src/include/storage/bufmgr.h @@ -48,6 +48,7 @@ extern PGDLLIMPORT int NBuffers; extern bool zero_damaged_pages; extern int bgwriter_lru_maxpages; extern double bgwriter_lru_multiplier; +extern bool track_iotiming; extern int target_prefetch_pages; /* in buf_init.c */ diff --git a/src/test/regress/expected/rules.out b/src/test/regress/expected/rules.out index 454e1f9..983df54 100644 --- a/src/test/regress/expected/rules.out +++ b/src/test/regress/expected/rules.out @@ -1296,7 +1296,7 @@ SELECT viewname, definition FROM pg_views WHERE schemaname <> 'information_schem 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_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_blocks_time(d.oid) AS blks_time, 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_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.procpid, 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, procpid, usesysid, application_name, current_query, waiting, xact_start, query_start, backend_start, client_addr, client_hostname, client_port), pg_authid u, pg_stat_get_wal_senders() w(procpid, state, sent_location, write_location, flush_location, replay_location, sync_priority, sync_state) WHERE ((s.usesysid = u.oid) AND (s.procpid = w.procpid)); 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)); @@ -1308,15 +1308,15 @@ SELECT viewname, definition FROM pg_views WHERE schemaname <> 'information_schem pg_stat_xact_sys_tables | SELECT pg_stat_xact_all_tables.relid, pg_stat_xact_all_tables.schemaname, pg_stat_xact_all_tables.relname, pg_stat_xact_all_tables.seq_scan, pg_stat_xact_all_tables.seq_tup_read, pg_stat_xact_all_tables.idx_scan, pg_stat_xact_all_tables.idx_tup_fetch, pg_stat_xact_all_tables.n_tup_ins, pg_stat_xact_all_tables.n_tup_upd, pg_stat_xact_all_tables.n_tup_del, pg_stat_xact_all_tables.n_tup_hot_upd FROM pg_stat_xact_all_tables WHERE ((pg_stat_xact_all_tables.schemaname = ANY (ARRAY['pg_catalog'::name, 'information_schema'::name])) OR (pg_stat_xact_all_tables.schemaname ~ '^pg_toast'::text)); pg_stat_xact_user_functions | SELECT p.oid AS funcid, n.nspname AS schemaname, p.proname AS funcname, pg_stat_get_xact_function_calls(p.oid) AS calls, (pg_stat_get_xact_function_time(p.oid) / 1000) AS total_time, (pg_stat_get_xact_function_self_time(p.oid) / 1000) AS self_time FROM (pg_proc p LEFT JOIN pg_namespace n ON ((n.oid = p.pronamespace))) WHERE ((p.prolang <> (12)::oid) AND (pg_stat_get_xact_function_calls(p.oid) IS NOT NULL)); pg_stat_xact_user_tables | SELECT pg_stat_xact_all_tables.relid, pg_stat_xact_all_tables.schemaname, pg_stat_xact_all_tables.relname, pg_stat_xact_all_tables.seq_scan, pg_stat_xact_all_tables.seq_tup_read, pg_stat_xact_all_tables.idx_scan, pg_stat_xact_all_tables.idx_tup_fetch, pg_stat_xact_all_tables.n_tup_ins, pg_stat_xact_all_tables.n_tup_upd, pg_stat_xact_all_tables.n_tup_del, pg_stat_xact_all_tables.n_tup_hot_upd FROM pg_stat_xact_all_tables WHERE ((pg_stat_xact_all_tables.schemaname <> ALL (ARRAY['pg_catalog'::name, 'information_schema'::name])) AND (pg_stat_xact_all_tables.schemaname !~ '^pg_toast'::text)); - pg_statio_all_indexes | SELECT c.oid AS relid, i.oid AS indexrelid, n.nspname AS schemaname, c.relname, i.relname AS indexrelname, (pg_stat_get_blocks_fetched(i.oid) - pg_stat_get_blocks_hit(i.oid)) AS idx_blks_read, pg_stat_get_blocks_hit(i.oid) AS idx_blks_hit 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_statio_all_sequences | SELECT c.oid AS relid, n.nspname AS schemaname, c.relname, (pg_stat_get_blocks_fetched(c.oid) - pg_stat_get_blocks_hit(c.oid)) AS blks_read, pg_stat_get_blocks_hit(c.oid) AS blks_hit FROM (pg_class c LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace))) WHERE (c.relkind = 'S'::"char"); - pg_statio_all_tables | SELECT c.oid AS relid, n.nspname AS schemaname, c.relname, (pg_stat_get_blocks_fetched(c.oid) - pg_stat_get_blocks_hit(c.oid)) AS heap_blks_read, pg_stat_get_blocks_hit(c.oid) AS heap_blks_hit, (sum((pg_stat_get_blocks_fetched(i.indexrelid) - pg_stat_get_blocks_hit(i.indexrelid))))::bigint AS idx_blks_read, (sum(pg_stat_get_blocks_hit(i.indexrelid)))::bigint AS idx_blks_hit, (pg_stat_get_blocks_fetched(t.oid) - pg_stat_get_blocks_hit(t.oid)) AS toast_blks_read, pg_stat_get_blocks_hit(t.oid) AS toast_blks_hit, (pg_stat_get_blocks_fetched(x.oid) - pg_stat_get_blocks_hit(x.oid)) AS tidx_blks_read, pg_stat_get_blocks_hit(x.oid) AS tidx_blks_hit FROM ((((pg_class c LEFT JOIN pg_index i ON ((c.oid = i.indrelid))) LEFT JOIN pg_class t ON ((c.reltoastrelid = t.oid))) LEFT JOIN pg_class x ON ((t.reltoastidxid = x.oid))) 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, t.oid, x.oid; - pg_statio_sys_indexes | SELECT pg_statio_all_indexes.relid, pg_statio_all_indexes.indexrelid, pg_statio_all_indexes.schemaname, pg_statio_all_indexes.relname, pg_statio_all_indexes.indexrelname, pg_statio_all_indexes.idx_blks_read, pg_statio_all_indexes.idx_blks_hit FROM pg_statio_all_indexes WHERE ((pg_statio_all_indexes.schemaname = ANY (ARRAY['pg_catalog'::name, 'information_schema'::name])) OR (pg_statio_all_indexes.schemaname ~ '^pg_toast'::text)); - pg_statio_sys_sequences | SELECT pg_statio_all_sequences.relid, pg_statio_all_sequences.schemaname, pg_statio_all_sequences.relname, pg_statio_all_sequences.blks_read, pg_statio_all_sequences.blks_hit FROM pg_statio_all_sequences WHERE ((pg_statio_all_sequences.schemaname = ANY (ARRAY['pg_catalog'::name, 'information_schema'::name])) OR (pg_statio_all_sequences.schemaname ~ '^pg_toast'::text)); - pg_statio_sys_tables | SELECT pg_statio_all_tables.relid, pg_statio_all_tables.schemaname, pg_statio_all_tables.relname, pg_statio_all_tables.heap_blks_read, pg_statio_all_tables.heap_blks_hit, pg_statio_all_tables.idx_blks_read, pg_statio_all_tables.idx_blks_hit, pg_statio_all_tables.toast_blks_read, pg_statio_all_tables.toast_blks_hit, pg_statio_all_tables.tidx_blks_read, pg_statio_all_tables.tidx_blks_hit FROM pg_statio_all_tables WHERE ((pg_statio_all_tables.schemaname = ANY (ARRAY['pg_catalog'::name, 'information_schema'::name])) OR (pg_statio_all_tables.schemaname ~ '^pg_toast'::text)); - pg_statio_user_indexes | SELECT pg_statio_all_indexes.relid, pg_statio_all_indexes.indexrelid, pg_statio_all_indexes.schemaname, pg_statio_all_indexes.relname, pg_statio_all_indexes.indexrelname, pg_statio_all_indexes.idx_blks_read, pg_statio_all_indexes.idx_blks_hit FROM pg_statio_all_indexes WHERE ((pg_statio_all_indexes.schemaname <> ALL (ARRAY['pg_catalog'::name, 'information_schema'::name])) AND (pg_statio_all_indexes.schemaname !~ '^pg_toast'::text)); - pg_statio_user_sequences | SELECT pg_statio_all_sequences.relid, pg_statio_all_sequences.schemaname, pg_statio_all_sequences.relname, pg_statio_all_sequences.blks_read, pg_statio_all_sequences.blks_hit FROM pg_statio_all_sequences WHERE ((pg_statio_all_sequences.schemaname <> ALL (ARRAY['pg_catalog'::name, 'information_schema'::name])) AND (pg_statio_all_sequences.schemaname !~ '^pg_toast'::text)); - pg_statio_user_tables | SELECT pg_statio_all_tables.relid, pg_statio_all_tables.schemaname, pg_statio_all_tables.relname, pg_statio_all_tables.heap_blks_read, pg_statio_all_tables.heap_blks_hit, pg_statio_all_tables.idx_blks_read, pg_statio_all_tables.idx_blks_hit, pg_statio_all_tables.toast_blks_read, pg_statio_all_tables.toast_blks_hit, pg_statio_all_tables.tidx_blks_read, pg_statio_all_tables.tidx_blks_hit FROM pg_statio_all_tables WHERE ((pg_statio_all_tables.schemaname <> ALL (ARRAY['pg_catalog'::name, 'information_schema'::name])) AND (pg_statio_all_tables.schemaname !~ '^pg_toast'::text)); + pg_statio_all_indexes | SELECT c.oid AS relid, i.oid AS indexrelid, n.nspname AS schemaname, c.relname, i.relname AS indexrelname, (pg_stat_get_blocks_fetched(i.oid) - pg_stat_get_blocks_hit(i.oid)) AS idx_blks_read, pg_stat_get_blocks_hit(i.oid) AS idx_blks_hit, pg_stat_get_blocks_time(i.oid) AS idx_blks_time 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_statio_all_sequences | SELECT c.oid AS relid, n.nspname AS schemaname, c.relname, (pg_stat_get_blocks_fetched(c.oid) - pg_stat_get_blocks_hit(c.oid)) AS blks_read, pg_stat_get_blocks_hit(c.oid) AS blks_hit, pg_stat_get_blocks_time(c.oid) AS blks_time FROM (pg_class c LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace))) WHERE (c.relkind = 'S'::"char"); + pg_statio_all_tables | SELECT c.oid AS relid, n.nspname AS schemaname, c.relname, (pg_stat_get_blocks_fetched(c.oid) - pg_stat_get_blocks_hit(c.oid)) AS heap_blks_read, pg_stat_get_blocks_hit(c.oid) AS heap_blks_hit, pg_stat_get_blocks_time(c.oid) AS heap_blks_time, (sum((pg_stat_get_blocks_fetched(i.indexrelid) - pg_stat_get_blocks_hit(i.indexrelid))))::bigint AS idx_blks_read, (sum(pg_stat_get_blocks_hit(i.indexrelid)))::bigint AS idx_blks_hit, (sum(pg_stat_get_blocks_time(i.indexrelid)))::bigint AS idx_blks_time, (pg_stat_get_blocks_fetched(t.oid) - pg_stat_get_blocks_hit(t.oid)) AS toast_blks_read, pg_stat_get_blocks_hit(t.oid) AS toast_blks_hit, pg_stat_get_blocks_time(t.oid) AS toast_blks_time, (pg_stat_get_blocks_fetched(x.oid) - pg_stat_get_blocks_hit(x.oid)) AS tidx_blks_read, pg_stat_get_blocks_hit(x.oid) AS tidx_blks_hit, pg_stat_get_blocks_time(x.oid) AS tidx_blks_time FROM ((((pg_class c LEFT JOIN pg_index i ON ((c.oid = i.indrelid))) LEFT JOIN pg_class t ON ((c.reltoastrelid = t.oid))) LEFT JOIN pg_class x ON ((t.reltoastidxid = x.oid))) 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, t.oid, x.oid; + pg_statio_sys_indexes | SELECT pg_statio_all_indexes.relid, pg_statio_all_indexes.indexrelid, pg_statio_all_indexes.schemaname, pg_statio_all_indexes.relname, pg_statio_all_indexes.indexrelname, pg_statio_all_indexes.idx_blks_read, pg_statio_all_indexes.idx_blks_hit, pg_statio_all_indexes.idx_blks_time FROM pg_statio_all_indexes WHERE ((pg_statio_all_indexes.schemaname = ANY (ARRAY['pg_catalog'::name, 'information_schema'::name])) OR (pg_statio_all_indexes.schemaname ~ '^pg_toast'::text)); + pg_statio_sys_sequences | SELECT pg_statio_all_sequences.relid, pg_statio_all_sequences.schemaname, pg_statio_all_sequences.relname, pg_statio_all_sequences.blks_read, pg_statio_all_sequences.blks_hit, pg_statio_all_sequences.blks_time FROM pg_statio_all_sequences WHERE ((pg_statio_all_sequences.schemaname = ANY (ARRAY['pg_catalog'::name, 'information_schema'::name])) OR (pg_statio_all_sequences.schemaname ~ '^pg_toast'::text)); + pg_statio_sys_tables | SELECT pg_statio_all_tables.relid, pg_statio_all_tables.schemaname, pg_statio_all_tables.relname, pg_statio_all_tables.heap_blks_read, pg_statio_all_tables.heap_blks_hit, pg_statio_all_tables.heap_blks_time, pg_statio_all_tables.idx_blks_read, pg_statio_all_tables.idx_blks_hit, pg_statio_all_tables.idx_blks_time, pg_statio_all_tables.toast_blks_read, pg_statio_all_tables.toast_blks_hit, pg_statio_all_tables.toast_blks_time, pg_statio_all_tables.tidx_blks_read, pg_statio_all_tables.tidx_blks_hit, pg_statio_all_tables.tidx_blks_time FROM pg_statio_all_tables WHERE ((pg_statio_all_tables.schemaname = ANY (ARRAY['pg_catalog'::name, 'information_schema'::name])) OR (pg_statio_all_tables.schemaname ~ '^pg_toast'::text)); + pg_statio_user_indexes | SELECT pg_statio_all_indexes.relid, pg_statio_all_indexes.indexrelid, pg_statio_all_indexes.schemaname, pg_statio_all_indexes.relname, pg_statio_all_indexes.indexrelname, pg_statio_all_indexes.idx_blks_read, pg_statio_all_indexes.idx_blks_hit, pg_statio_all_indexes.idx_blks_time FROM pg_statio_all_indexes WHERE ((pg_statio_all_indexes.schemaname <> ALL (ARRAY['pg_catalog'::name, 'information_schema'::name])) AND (pg_statio_all_indexes.schemaname !~ '^pg_toast'::text)); + pg_statio_user_sequences | SELECT pg_statio_all_sequences.relid, pg_statio_all_sequences.schemaname, pg_statio_all_sequences.relname, pg_statio_all_sequences.blks_read, pg_statio_all_sequences.blks_hit, pg_statio_all_sequences.blks_time FROM pg_statio_all_sequences WHERE ((pg_statio_all_sequences.schemaname <> ALL (ARRAY['pg_catalog'::name, 'information_schema'::name])) AND (pg_statio_all_sequences.schemaname !~ '^pg_toast'::text)); + pg_statio_user_tables | SELECT pg_statio_all_tables.relid, pg_statio_all_tables.schemaname, pg_statio_all_tables.relname, pg_statio_all_tables.heap_blks_read, pg_statio_all_tables.heap_blks_hit, pg_statio_all_tables.heap_blks_time, pg_statio_all_tables.idx_blks_read, pg_statio_all_tables.idx_blks_hit, pg_statio_all_tables.idx_blks_time, pg_statio_all_tables.toast_blks_read, pg_statio_all_tables.toast_blks_hit, pg_statio_all_tables.toast_blks_time, pg_statio_all_tables.tidx_blks_read, pg_statio_all_tables.tidx_blks_hit, pg_statio_all_tables.tidx_blks_time FROM pg_statio_all_tables WHERE ((pg_statio_all_tables.schemaname <> ALL (ARRAY['pg_catalog'::name, 'information_schema'::name])) AND (pg_statio_all_tables.schemaname !~ '^pg_toast'::text)); pg_stats | SELECT n.nspname AS schemaname, c.relname AS tablename, a.attname, s.stainherit AS inherited, s.stanullfrac AS null_frac, s.stawidth AS avg_width, s.stadistinct AS n_distinct, CASE WHEN (s.stakind1 = ANY (ARRAY[1, 4])) THEN s.stavalues1 WHEN (s.stakind2 = ANY (ARRAY[1, 4])) THEN s.stavalues2 WHEN (s.stakind3 = ANY (ARRAY[1, 4])) THEN s.stavalues3 WHEN (s.stakind4 = ANY (ARRAY[1, 4])) THEN s.stavalues4 ELSE NULL::anyarray END AS most_common_vals, CASE WHEN (s.stakind1 = ANY (ARRAY[1, 4])) THEN s.stanumbers1 WHEN (s.stakind2 = ANY (ARRAY[1, 4])) THEN s.stanumbers2 WHEN (s.stakind3 = ANY (ARRAY[1, 4])) THEN s.stanumbers3 WHEN (s.stakind4 = ANY (ARRAY[1, 4])) THEN s.stanumbers4 ELSE NULL::real[] END AS most_common_freqs, CASE WHEN (s.stakind1 = 2) THEN s.stavalues1 WHEN (s.stakind2 = 2) THEN s.stavalues2 WHEN (s.stakind3 = 2) THEN s.stavalues3 WHEN (s.stakind4 = 2) THEN s.stavalues4 ELSE NULL::anyarray END AS histogram_bounds, CASE WHEN (s.stakind1 = 3) THEN s.stanumbers1[1] WHEN (s.stakind2 = 3) THEN s.stanumbers2[1] WHEN (s.stakind3 = 3) THEN s.stanumbers3[1] WHEN (s.stakind4 = 3) THEN s.stanumbers4[1] ELSE NULL::real END AS correlation FROM (((pg_statistic s JOIN pg_class c ON ((c.oid = s.starelid))) JOIN pg_attribute a ON (((c.oid = a.attrelid) AND (a.attnum = s.staattnum)))) LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace))) WHERE ((NOT a.attisdropped) AND has_column_privilege(c.oid, a.attnum, 'select'::text)); pg_tables | SELECT n.nspname AS schemaname, c.relname AS tablename, pg_get_userbyid(c.relowner) AS tableowner, t.spcname AS tablespace, c.relhasindex AS hasindexes, c.relhasrules AS hasrules, c.relhastriggers AS hastriggers FROM ((pg_class c LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace))) LEFT JOIN pg_tablespace t ON ((t.oid = c.reltablespace))) WHERE (c.relkind = 'r'::"char"); pg_timezone_abbrevs | SELECT pg_timezone_abbrevs.abbrev, pg_timezone_abbrevs.utc_offset, pg_timezone_abbrevs.is_dst FROM pg_timezone_abbrevs() pg_timezone_abbrevs(abbrev, utc_offset, is_dst);