Am 04.01.22 um 12:29 schrieb Gunnar "Nick" Bluth: > Am 03.01.22 um 22:23 schrieb Alvaro Herrera: >> Overall I think this is a good feature to have; assessing the need for >> compression is important for tuning, so +1 for the goal of the patch. > > Much appreciated! > > >> I didn't look into the patch carefully, but here are some minor >> comments: >> >> On 2022-Jan-03, Gunnar "Nick" Bluth wrote: >> >>> @@ -229,7 +230,9 @@ toast_tuple_try_compression(ToastTupleContext >>> *ttc, int attribute) >>> Datum *value = &ttc->ttc_values[attribute]; >>> Datum new_value; >>> ToastAttrInfo *attr = &ttc->ttc_attr[attribute]; >>> + instr_time start_time; >>> + INSTR_TIME_SET_CURRENT(start_time); >>> new_value = toast_compress_datum(*value, attr->tai_compression); >>> if (DatumGetPointer(new_value) != NULL) >> >> Don't INSTR_TIME_SET_CURRENT unconditionally; in some systems it's an >> expensive syscall. Find a way to only do it if the feature is enabled. > > Yeah, I was worried about that (and asking if it would be required) > already. > Adding the check was easier than I expected, though I'm absolutely > clueless if I did it right! > > #include "pgstat.h" > extern PGDLLIMPORT bool pgstat_track_toast; >
As it works and nobody objected, it seems to be the right way... v8 (applies cleanly to today's HEAD/master) attached. Any takers? -- Gunnar "Nick" Bluth Eimermacherweg 106 D-48159 Münster Mobil +49 172 8853339 Email: gunnar.bl...@pro-open.de __________________________________________________________________________ "Ceterum censeo SystemD esse delendam" - Cato
doc/src/sgml/config.sgml | 26 +++ doc/src/sgml/monitoring.sgml | 163 ++++++++++++++ doc/src/sgml/storage.sgml | 12 +- src/backend/access/table/toast_helper.c | 40 ++++ src/backend/catalog/system_views.sql | 20 ++ src/backend/postmaster/pgstat.c | 302 +++++++++++++++++++++++++- src/backend/utils/adt/pgstatfuncs.c | 72 ++++++ src/backend/utils/misc/guc.c | 9 + src/backend/utils/misc/postgresql.conf.sample | 1 + src/include/catalog/pg_proc.dat | 25 +++ src/include/pgstat.h | 107 ++++++++- src/test/regress/expected/rules.out | 17 ++ src/test/regress/expected/stats.out | 62 ++++++ src/test/regress/sql/stats.sql | 28 +++ 14 files changed, 877 insertions(+), 7 deletions(-) diff --git a/doc/src/sgml/config.sgml b/doc/src/sgml/config.sgml index 7ed8c82a9d..adbd516373 100644 --- a/doc/src/sgml/config.sgml +++ b/doc/src/sgml/config.sgml @@ -7889,6 +7889,32 @@ COPY postgres_log FROM '/full/path/to/logfile.csv' WITH csv; </listitem> </varlistentry> + <varlistentry id="guc-track-toast" xreflabel="track_toast"> + <term><varname>track_toast</varname> (<type>boolean</type>) + <indexterm> + <primary><varname>track_toast</varname> configuration parameter</primary> + </indexterm> + </term> + <listitem> + <para> + Enables tracking of <link linkend="storage-toast">TOAST</link> activities. + Compressions and externalizations are tracked. + The default is <literal>off</literal>. + Only superusers can change this setting. + </para> + + <note> + <para> + Be aware that this feature, depending on the amount of TOASTable columns in + your databases, may significantly increase the size of the statistics files + and the workload of the statistics collector. It is recommended to only + temporarily activate this to assess the right compression and storage method + for a column. + </para> + </note> + </listitem> + </varlistentry> + <varlistentry id="guc-stats-temp-directory" xreflabel="stats_temp_directory"> <term><varname>stats_temp_directory</varname> (<type>string</type>) <indexterm> diff --git a/doc/src/sgml/monitoring.sgml b/doc/src/sgml/monitoring.sgml index 9fb62fec8e..06258e777a 100644 --- a/doc/src/sgml/monitoring.sgml +++ b/doc/src/sgml/monitoring.sgml @@ -610,6 +610,17 @@ postgres 27093 0.0 0.0 30096 2752 ? Ss 11:34 0:00 postgres: ser yet included in <structname>pg_stat_user_functions</structname>).</entry> </row> + <row> + <entry><structname>pg_stat_toast</structname><indexterm><primary>pg_stat_toast</primary></indexterm></entry> + <entry> + One row for each column that has ever been TOASTed (compressed and/or externalized). + Showing the number of externalizations, compression attempts / successes, compressed and + uncompressed sizes etc. + <link linkend="monitoring-pg-stat-toast-view"> + <structname>pg_stat_toast</structname></link> for details. + </entry> + </row> + <row> <entry><structname>pg_stat_slru</structname><indexterm><primary>pg_stat_slru</primary></indexterm></entry> <entry>One row per SLRU, showing statistics of operations. See @@ -4933,6 +4944,158 @@ SELECT pid, wait_event_type, wait_event FROM pg_stat_activity WHERE wait_event i </sect2> + <sect2 id="monitoring-pg-stat-toast-view"> + <title><structname>pg_stat_toast</structname></title> + + <indexterm> + <primary>pg_stat_toast</primary> + </indexterm> + + <para> + The <structname>pg_stat_toast</structname> view will contain + one row for each column of variable size that has been TOASTed since + the last statistics reset. The <xref linkend="guc-track-toast"/> parameter + controls whether TOAST activities are tracked or not. + </para> + + <table id="pg-stat-toast-view" xreflabel="pg_stat_toast"> + <title><structname>pg_stat_toast</structname> View</title> + <tgroup cols="1"> + <thead> + <row> + <entry role="catalog_table_entry"><para role="column_definition"> + Column Type + </para> + <para> + Description + </para></entry> + </row> + </thead> + + <tbody> + <row> + <entry role="catalog_table_entry"><para role="column_definition"> + <structfield>schemaname</structfield> <type>name</type> + </para> + <para> + Name of the schema the relation is in + </para></entry> + </row> + + <row> + <entry role="catalog_table_entry"><para role="column_definition"> + <structfield>reloid</structfield> <type>oid</type> + </para> + <para> + OID of the relation + </para></entry> + </row> + + <row> + <entry role="catalog_table_entry"><para role="column_definition"> + <structfield>attnum</structfield> <type>int</type> + </para> + <para> + Attribute (column) number in the relation + </para></entry> + </row> + + <row> + <entry role="catalog_table_entry"><para role="column_definition"> + <structfield>relname</structfield> <type>name</type> + </para> + <para> + Name of the relation + </para></entry> + </row> + + <row> + <entry role="catalog_table_entry"><para role="column_definition"> + <structfield>attname</structfield> <type>name</type> + </para> + <para> + Name of the attribute (column) + </para></entry> + </row> + + <row> + <entry role="catalog_table_entry"><para role="column_definition"> + <structfield>storagemethod</structfield> <type>char</type> + </para> + <para> + Storage method of the attribute + </para></entry> + </row> + + <row> + <entry role="catalog_table_entry"><para role="column_definition"> + <structfield>externalized</structfield> <type>bigint</type> + </para> + <para> + Number of times this attribute was externalized (pushed to TOAST relation) + </para></entry> + </row> + + <row> + <entry role="catalog_table_entry"><para role="column_definition"> + <structfield>compressmethod</structfield> <type>char</type> + </para> + <para> + Current compression method of the attribute (empty means default) + </para></entry> + </row> + + <row> + <entry role="catalog_table_entry"><para role="column_definition"> + <structfield>compressattempts</structfield> <type>bigint</type> + </para> + <para> + Number of times compression of this attribute was attempted + </para></entry> + </row> + + <row> + <entry role="catalog_table_entry"><para role="column_definition"> + <structfield>compresssuccesses</structfield> <type>bigint</type> + </para> + <para> + Number of times compression of this attribute was successful + </para></entry> + </row> + + <row> + <entry role="catalog_table_entry"><para role="column_definition"> + <structfield>compressedsize</structfield> <type>bigint</type> + </para> + <para> + Total size of all compressed datums + </para></entry> + </row> + + <row> + <entry role="catalog_table_entry"><para role="column_definition"> + <structfield>originalsize</structfield> <type>bigint</type> + </para> + <para> + Total size of all compressed datums before compression + </para></entry> + </row> + + <row> + <entry role="catalog_table_entry"><para role="column_definition"> + <structfield>total_time</structfield> <type>double precision</type> + </para> + <para> + Total time spent TOASTing this attribute, in microseconds. + </para></entry> + </row> + + </tbody> + </tgroup> + </table> + + </sect2> + <sect2 id="monitoring-pg-stat-slru-view"> <title><structname>pg_stat_slru</structname></title> diff --git a/doc/src/sgml/storage.sgml b/doc/src/sgml/storage.sgml index 7136bbe7a3..2a47922573 100644 --- a/doc/src/sgml/storage.sgml +++ b/doc/src/sgml/storage.sgml @@ -517,6 +517,15 @@ pages). There was no run time difference compared to an un-<acronym>TOAST</acron comparison table, in which all the HTML pages were cut down to 7 kB to fit. </para> +<para> +When you enable <xref linkend="guc-track-toast"/>, the system view +<link linkend="monitoring-pg-stat-toast-view"><structname>pg_stat_toast</structname> +</link> provides details on the number and effect of compression attempts, +number of externalizations and some more useful information that enables you +to decide if a different storage method and/or compression method would suite a +column better. +</para> + </sect2> <sect2 id="storage-toast-inmemory"> @@ -1068,7 +1077,8 @@ data. Empty in ordinary tables.</entry> <type>struct varlena</type>, which includes the total length of the stored value and some flag bits. Depending on the flags, the data can be either inline or in a <acronym>TOAST</acronym> table; - it might be compressed, too (see <xref linkend="storage-toast"/>). + it might be compressed, too (see <xref linkend="storage-toast"/> and + <xref linkend="monitoring-pg-stat-toast-view"/>). </para> </sect2> diff --git a/src/backend/access/table/toast_helper.c b/src/backend/access/table/toast_helper.c index 0cc5a30f9b..43154f2be0 100644 --- a/src/backend/access/table/toast_helper.c +++ b/src/backend/access/table/toast_helper.c @@ -19,7 +19,9 @@ #include "access/toast_helper.h" #include "access/toast_internals.h" #include "catalog/pg_type_d.h" +#include "pgstat.h" +extern PGDLLIMPORT bool pgstat_track_toast; /* * Prepare to TOAST a tuple. @@ -229,7 +231,12 @@ toast_tuple_try_compression(ToastTupleContext *ttc, int attribute) Datum *value = &ttc->ttc_values[attribute]; Datum new_value; ToastAttrInfo *attr = &ttc->ttc_attr[attribute]; + instr_time start_time; + if (pgstat_track_toast) + { + INSTR_TIME_SET_CURRENT(start_time); + } new_value = toast_compress_datum(*value, attr->tai_compression); if (DatumGetPointer(new_value) != NULL) @@ -239,6 +246,15 @@ toast_tuple_try_compression(ToastTupleContext *ttc, int attribute) pfree(DatumGetPointer(*value)); *value = new_value; attr->tai_colflags |= TOASTCOL_NEEDS_FREE; + if (pgstat_track_toast) + { + pgstat_report_toast_activity(ttc->ttc_rel->rd_rel->oid, attribute, + false, + true, + attr->tai_size, + VARSIZE(DatumGetPointer(*value)), + start_time); + } attr->tai_size = VARSIZE(DatumGetPointer(*value)); ttc->ttc_flags |= (TOAST_NEEDS_CHANGE | TOAST_NEEDS_FREE); } @@ -246,6 +262,15 @@ toast_tuple_try_compression(ToastTupleContext *ttc, int attribute) { /* incompressible, ignore on subsequent compression passes */ attr->tai_colflags |= TOASTCOL_INCOMPRESSIBLE; + if (pgstat_track_toast) + { + pgstat_report_toast_activity(ttc->ttc_rel->rd_rel->oid, attribute, + false, + true, + 0, + 0, + start_time); + } } } @@ -258,6 +283,12 @@ toast_tuple_externalize(ToastTupleContext *ttc, int attribute, int options) Datum *value = &ttc->ttc_values[attribute]; Datum old_value = *value; ToastAttrInfo *attr = &ttc->ttc_attr[attribute]; + instr_time start_time; + + if (pgstat_track_toast) + { + INSTR_TIME_SET_CURRENT(start_time); + } attr->tai_colflags |= TOASTCOL_IGNORE; *value = toast_save_datum(ttc->ttc_rel, old_value, attr->tai_oldexternal, @@ -266,6 +297,15 @@ toast_tuple_externalize(ToastTupleContext *ttc, int attribute, int options) pfree(DatumGetPointer(old_value)); attr->tai_colflags |= TOASTCOL_NEEDS_FREE; ttc->ttc_flags |= (TOAST_NEEDS_CHANGE | TOAST_NEEDS_FREE); + if (pgstat_track_toast) + { + pgstat_report_toast_activity(ttc->ttc_rel->rd_rel->oid, attribute, + true, + false, + 0, + 0, + start_time); +} } /* diff --git a/src/backend/catalog/system_views.sql b/src/backend/catalog/system_views.sql index 40b7bca5a9..c78ab0947d 100644 --- a/src/backend/catalog/system_views.sql +++ b/src/backend/catalog/system_views.sql @@ -1041,6 +1041,26 @@ CREATE VIEW pg_stat_user_functions AS WHERE P.prolang != 12 -- fast check to eliminate built-in functions AND pg_stat_get_function_calls(P.oid) IS NOT NULL; + +CREATE OR REPLACE VIEW pg_stat_toast AS + SELECT + n.nspname AS schemaname, + a.attrelid AS reloid, + a.attnum AS attnum, + c.relname AS relname, + a.attname AS attname, + attstorage AS storagemethod, + pg_stat_get_toast_externalizations(a.attrelid,a.attnum) AS externalized, + attcompression AS compressmethod, + pg_stat_get_toast_compressions(a.attrelid,a.attnum) AS compressattempts, + pg_stat_get_toast_compressionsuccesses(a.attrelid,a.attnum) AS compresssuccesses, + pg_stat_get_toast_compressedsizesum(a.attrelid,a.attnum) AS compressedsize, + pg_stat_get_toast_originalsizesum(a.attrelid,a.attnum) AS originalsize, + pg_stat_get_toast_total_time(a.attrelid,a.attnum) AS total_time + FROM pg_attribute a + JOIN pg_class c ON c.oid = a.attrelid + LEFT JOIN pg_namespace n ON n.oid = c.relnamespace + WHERE pg_stat_get_toast_externalizations(a.attrelid,a.attnum) IS NOT NULL; CREATE VIEW pg_stat_xact_user_functions AS SELECT P.oid AS funcid, diff --git a/src/backend/postmaster/pgstat.c b/src/backend/postmaster/pgstat.c index 53ddd930e6..37290d9a9e 100644 --- a/src/backend/postmaster/pgstat.c +++ b/src/backend/postmaster/pgstat.c @@ -106,6 +106,7 @@ #define PGSTAT_DB_HASH_SIZE 16 #define PGSTAT_TAB_HASH_SIZE 512 #define PGSTAT_FUNCTION_HASH_SIZE 512 +#define PGSTAT_TOAST_HASH_SIZE 64 #define PGSTAT_SUBSCRIPTION_HASH_SIZE 32 #define PGSTAT_REPLSLOT_HASH_SIZE 32 @@ -116,6 +117,7 @@ */ bool pgstat_track_counts = false; int pgstat_track_functions = TRACK_FUNC_OFF; +bool pgstat_track_toast = false; /* ---------- * Built from GUC parameter @@ -228,6 +230,19 @@ static HTAB *pgStatFunctions = NULL; */ static bool have_function_stats = false; +/* + * Backends store per-toast-column info that's waiting to be sent to the collector + * in this hash table (indexed by column's PgStat_BackendAttrIdentifier). + */ +static HTAB *pgStatToastActions = NULL; + + +/* + * Indicates if backend has some toast stats that it hasn't yet + * sent to the collector. + */ +static bool have_toast_stats = false; + /* * Tuple insertion/deletion counts for an open transaction can't be propagated * into PgStat_TableStatus counters until we know if it is going to commit @@ -328,7 +343,7 @@ static void pgstat_reset_subscription(PgStat_StatSubEntry *subentry, TimestampTz static void pgstat_write_statsfiles(bool permanent, bool allDbs); static void pgstat_write_db_statsfile(PgStat_StatDBEntry *dbentry, bool permanent); static HTAB *pgstat_read_statsfiles(Oid onlydb, bool permanent, bool deep); -static void pgstat_read_db_statsfile(Oid databaseid, HTAB *tabhash, HTAB *funchash, +static void pgstat_read_db_statsfile(Oid databaseid, HTAB *tabhash, HTAB *funchash, HTAB *toasthash, bool permanent); static void backend_read_statsfile(void); @@ -340,6 +355,7 @@ static void pgstat_reset_replslot(PgStat_StatReplSlotEntry *slotstats, Timestamp static void pgstat_send_tabstat(PgStat_MsgTabstat *tsmsg, TimestampTz now); static void pgstat_send_funcstats(void); +static void pgstat_send_toaststats(void); static void pgstat_send_slru(void); static HTAB *pgstat_collect_oids(Oid catalogid, AttrNumber anum_oid); static bool pgstat_should_report_connstat(void); @@ -373,6 +389,7 @@ static void pgstat_recv_wal(PgStat_MsgWal *msg, int len); static void pgstat_recv_slru(PgStat_MsgSLRU *msg, int len); static void pgstat_recv_funcstat(PgStat_MsgFuncstat *msg, int len); static void pgstat_recv_funcpurge(PgStat_MsgFuncpurge *msg, int len); +static void pgstat_recv_toaststat(PgStat_MsgToaststat *msg, int len); static void pgstat_recv_recoveryconflict(PgStat_MsgRecoveryConflict *msg, int len); static void pgstat_recv_deadlock(PgStat_MsgDeadlock *msg, int len); static void pgstat_recv_checksum_failure(PgStat_MsgChecksumFailure *msg, int len); @@ -891,7 +908,7 @@ pgstat_report_stat(bool disconnect) pgStatXactCommit == 0 && pgStatXactRollback == 0 && pgWalUsage.wal_records == prevWalUsage.wal_records && WalStats.m_wal_write == 0 && WalStats.m_wal_sync == 0 && - !have_function_stats && !disconnect) + !have_function_stats && !have_toast_stats && !disconnect) return; /* @@ -983,6 +1000,9 @@ pgstat_report_stat(bool disconnect) /* Now, send function statistics */ pgstat_send_funcstats(); + /* Now, send TOAST statistics */ + pgstat_send_toaststats(); + /* Send WAL statistics */ pgstat_send_wal(true); @@ -1116,6 +1136,63 @@ pgstat_send_funcstats(void) have_function_stats = false; } +/* + * Subroutine for pgstat_report_stat: populate and send a toast stat message + */ +static void +pgstat_send_toaststats(void) +{ + static const PgStat_ToastCounts all_zeroes; + + PgStat_MsgToaststat msg; + PgStat_BackendToastEntry *entry; + HASH_SEQ_STATUS tstat; + + if (pgStatToastActions == NULL) + return; + + pgstat_setheader(&msg.m_hdr, PGSTAT_MTYPE_TOASTSTAT); + msg.m_databaseid = MyDatabaseId; + msg.m_nentries = 0; + + hash_seq_init(&tstat, pgStatToastActions); + while ((entry = (PgStat_BackendToastEntry *) hash_seq_search(&tstat)) != NULL) + { + PgStat_ToastEntry *m_ent; + + /* Skip it if no counts accumulated since last time */ + if (memcmp(&entry->t_counts, &all_zeroes, + sizeof(PgStat_ToastCounts)) == 0) + continue; + + /* need to convert format of time accumulators */ + m_ent = &msg.m_entry[msg.m_nentries]; + m_ent->attr = entry->attr; + m_ent->t_numexternalized = entry->t_counts.t_numexternalized; + m_ent->t_numcompressed = entry->t_counts.t_numcompressed; + m_ent->t_numcompressionsuccess = entry->t_counts.t_numcompressionsuccess; + m_ent->t_size_orig = entry->t_counts.t_size_orig; + m_ent->t_size_compressed = entry->t_counts.t_size_compressed; + m_ent->t_comp_time = INSTR_TIME_GET_MICROSEC(entry->t_counts.t_comp_time); + + if (++msg.m_nentries >= PGSTAT_NUM_TOASTENTRIES) + { + pgstat_send(&msg, offsetof(PgStat_MsgToaststat, m_entry[0]) + + msg.m_nentries * sizeof(PgStat_ToastEntry)); + msg.m_nentries = 0; + } + + /* reset the entry's counts */ + MemSet(&entry->t_counts, 0, sizeof(PgStat_ToastCounts)); + } + + if (msg.m_nentries > 0) + pgstat_send(&msg, offsetof(PgStat_MsgToaststat, m_entry[0]) + + msg.m_nentries * sizeof(PgStat_ToastEntry)); + + have_toast_stats = false; +} + /* ---------- * pgstat_vacuum_stat() - @@ -2119,6 +2196,72 @@ pgstat_end_function_usage(PgStat_FunctionCallUsage *fcu, bool finalize) have_function_stats = true; } +/* + * Report TOAST activity + * Called by toast_helper functions. + */ +void +pgstat_report_toast_activity(Oid relid, int attr, + bool externalized, + bool compressed, + int32 old_size, + int32 new_size, + instr_time start_time) +{ + PgStat_BackendAttrIdentifier toastattr = { relid, attr }; + PgStat_BackendToastEntry *htabent; + instr_time time_spent; + bool found; + + if (pgStatSock == PGINVALID_SOCKET || !pgstat_track_toast) + return; + + INSTR_TIME_SET_CURRENT(time_spent); + INSTR_TIME_SUBTRACT(time_spent, start_time); + + if (!pgStatToastActions) + { + /* First time through - initialize toast stat table */ + HASHCTL hash_ctl; + + hash_ctl.keysize = sizeof(PgStat_BackendAttrIdentifier); + hash_ctl.entrysize = sizeof(PgStat_BackendToastEntry); + pgStatToastActions = hash_create("TOAST stat entries", + PGSTAT_TOAST_HASH_SIZE, + &hash_ctl, + HASH_ELEM | HASH_BLOBS); + } + + /* Get the stats entry for this TOAST attribute, create if necessary */ + htabent = hash_search(pgStatToastActions, &toastattr, + HASH_ENTER, &found); + if (!found) + { + MemSet(&htabent->t_counts, 0, sizeof(PgStat_ToastCounts)); + } + + /* update counters */ + if (externalized) + { + htabent->t_counts.t_numexternalized++; + } + if (compressed) + { + htabent->t_counts.t_numcompressed++; + htabent->t_counts.t_size_orig+=old_size; + if (new_size) + { + htabent->t_counts.t_numcompressionsuccess++; + htabent->t_counts.t_size_compressed+=new_size; + } + } + /* record time spent */ + INSTR_TIME_ADD(htabent->t_counts.t_comp_time, time_spent); + + /* indicate that we have something to send */ + have_toast_stats = true; +} + /* ---------- * pgstat_initstats() - @@ -2966,6 +3109,35 @@ pgstat_fetch_stat_funcentry(Oid func_id) return funcentry; } +/* ---------- + * pgstat_fetch_stat_toastentry() - + * + * Support function for the SQL-callable pgstat* functions. Returns + * the collected statistics for one TOAST attribute or NULL. + * ---------- + */ +PgStat_StatToastEntry * +pgstat_fetch_stat_toastentry(Oid rel_id, int attr) +{ + PgStat_StatDBEntry *dbentry; + PgStat_BackendAttrIdentifier toast_id = { rel_id, attr }; + PgStat_StatToastEntry *toastentry = NULL; + + /* load the stats file if needed */ + backend_read_statsfile(); + + /* Lookup our database, then find the requested TOAST activity stats. */ + dbentry = pgstat_fetch_stat_dbentry(MyDatabaseId); + if (dbentry != NULL && dbentry->toastactivity != NULL) + { + toastentry = (PgStat_StatToastEntry *) hash_search(dbentry->toastactivity, + (void *) &toast_id, + HASH_FIND, NULL); + } + + return toastentry; +} + /* * --------- * pgstat_fetch_stat_archiver() - @@ -3649,6 +3821,10 @@ PgstatCollectorMain(int argc, char *argv[]) pgstat_recv_funcpurge(&msg.msg_funcpurge, len); break; + case PGSTAT_MTYPE_TOASTSTAT: + pgstat_recv_toaststat(&msg.msg_toaststat, len); + break; + case PGSTAT_MTYPE_RECOVERYCONFLICT: pgstat_recv_recoveryconflict(&msg.msg_recoveryconflict, len); @@ -3785,6 +3961,13 @@ reset_dbentry_counters(PgStat_StatDBEntry *dbentry) PGSTAT_FUNCTION_HASH_SIZE, &hash_ctl, HASH_ELEM | HASH_BLOBS); + + hash_ctl.keysize = sizeof(PgStat_BackendAttrIdentifier); + hash_ctl.entrysize = sizeof(PgStat_StatToastEntry); + dbentry->toastactivity = hash_create("Per-database TOAST", + PGSTAT_TOAST_HASH_SIZE, + &hash_ctl, + HASH_ELEM | HASH_BLOBS); } /* @@ -3950,7 +4133,7 @@ pgstat_write_statsfiles(bool permanent, bool allDbs) while ((dbentry = (PgStat_StatDBEntry *) hash_seq_search(&hstat)) != NULL) { /* - * Write out the table and function stats for this DB into the + * Write out the table, function and TOAST stats for this DB into the * appropriate per-DB stat file, if required. */ if (allDbs || pgstat_db_requested(dbentry->databaseid)) @@ -4081,8 +4264,10 @@ pgstat_write_db_statsfile(PgStat_StatDBEntry *dbentry, bool permanent) { HASH_SEQ_STATUS tstat; HASH_SEQ_STATUS fstat; + HASH_SEQ_STATUS ostat; PgStat_StatTabEntry *tabentry; PgStat_StatFuncEntry *funcentry; + PgStat_StatToastEntry *toastentry; FILE *fpout; int32 format_id; Oid dbid = dbentry->databaseid; @@ -4137,6 +4322,17 @@ pgstat_write_db_statsfile(PgStat_StatDBEntry *dbentry, bool permanent) (void) rc; /* we'll check for error with ferror */ } + /* + * Walk through the database's TOAST stats table. + */ + hash_seq_init(&ostat, dbentry->toastactivity); + while ((toastentry = (PgStat_StatToastEntry *) hash_seq_search(&ostat)) != NULL) + { + fputc('O', fpout); + rc = fwrite(toastentry, sizeof(PgStat_StatToastEntry), 1, fpout); + (void) rc; /* we'll check for error with ferror */ + } + /* * No more output to be done. Close the temp file and replace the old * pgstat.stat with it. The ferror() check replaces testing for error @@ -4375,6 +4571,7 @@ pgstat_read_statsfiles(Oid onlydb, bool permanent, bool deep) memcpy(dbentry, &dbbuf, sizeof(PgStat_StatDBEntry)); dbentry->tables = NULL; dbentry->functions = NULL; + dbentry->toastactivity = NULL; /* * In the collector, disregard the timestamp we read from the @@ -4412,6 +4609,14 @@ pgstat_read_statsfiles(Oid onlydb, bool permanent, bool deep) &hash_ctl, HASH_ELEM | HASH_BLOBS | HASH_CONTEXT); + hash_ctl.keysize = sizeof(PgStat_BackendAttrIdentifier); + hash_ctl.entrysize = sizeof(PgStat_StatToastEntry); + hash_ctl.hcxt = pgStatLocalContext; + dbentry->toastactivity = hash_create("Per-database toast information", + PGSTAT_TOAST_HASH_SIZE, + &hash_ctl, + HASH_ELEM | HASH_BLOBS | HASH_CONTEXT); + /* * If requested, read the data from the database-specific * file. Otherwise we just leave the hashtables empty. @@ -4420,6 +4625,7 @@ pgstat_read_statsfiles(Oid onlydb, bool permanent, bool deep) pgstat_read_db_statsfile(dbentry->databaseid, dbentry->tables, dbentry->functions, + dbentry->toastactivity, permanent); break; @@ -4542,13 +4748,15 @@ done: * ---------- */ static void -pgstat_read_db_statsfile(Oid databaseid, HTAB *tabhash, HTAB *funchash, +pgstat_read_db_statsfile(Oid databaseid, HTAB *tabhash, HTAB *funchash, HTAB *toasthash, bool permanent) { PgStat_StatTabEntry *tabentry; PgStat_StatTabEntry tabbuf; PgStat_StatFuncEntry funcbuf; PgStat_StatFuncEntry *funcentry; + PgStat_StatToastEntry toastbuf; + PgStat_StatToastEntry *toastentry; FILE *fpin; int32 format_id; bool found; @@ -4662,6 +4870,32 @@ pgstat_read_db_statsfile(Oid databaseid, HTAB *tabhash, HTAB *funchash, memcpy(funcentry, &funcbuf, sizeof(funcbuf)); break; + + /* + * 'O' A PgStat_StatToastEntry follows (tOast) + */ + case 'O': + if (fread(&toastbuf, 1, sizeof(PgStat_StatToastEntry), + fpin) != sizeof(PgStat_StatToastEntry)) + { + ereport(pgStatRunningInCollector ? LOG : WARNING, + (errmsg("corrupted statistics file \"%s\"", + statfile))); + goto done; + } + + /* + * Skip if TOAST data not wanted. + */ + if (toasthash == NULL) + break; + + toastentry = (PgStat_StatToastEntry *) hash_search(toasthash, + (void *) &toastbuf.t_id, + HASH_ENTER, &found); + memcpy(toastentry, &toastbuf, sizeof(toastbuf)); + break; + /* * 'E' The EOF marker of a complete stats file. */ @@ -5353,6 +5587,8 @@ pgstat_recv_dropdb(PgStat_MsgDropdb *msg, int len) hash_destroy(dbentry->tables); if (dbentry->functions != NULL) hash_destroy(dbentry->functions); + if (dbentry->toastactivity != NULL) + hash_destroy(dbentry->toastactivity); if (hash_search(pgStatDBHash, (void *) &dbid, @@ -5390,9 +5626,11 @@ pgstat_recv_resetcounter(PgStat_MsgResetcounter *msg, int len) hash_destroy(dbentry->tables); if (dbentry->functions != NULL) hash_destroy(dbentry->functions); - + if (dbentry->toastactivity != NULL) + hash_destroy(dbentry->toastactivity); dbentry->tables = NULL; dbentry->functions = NULL; + dbentry->toastactivity = NULL; /* * Reset database-level stats, too. This creates empty hash tables for @@ -6052,6 +6290,60 @@ pgstat_recv_funcpurge(PgStat_MsgFuncpurge *msg, int len) } } +/* ---------- + * pgstat_recv_toaststat() - + * + * Count what the backend has done. + * ---------- + */ +static void +pgstat_recv_toaststat(PgStat_MsgToaststat *msg, int len) +{ + PgStat_ToastEntry *toastmsg = &(msg->m_entry[0]); + PgStat_StatDBEntry *dbentry; + PgStat_StatToastEntry *toastentry; + int i; + bool found; + + dbentry = pgstat_get_db_entry(msg->m_databaseid, true); + + /* + * Process all TOAST entries in the message. + */ + for (i = 0; i < msg->m_nentries; i++, toastmsg++) + { + toastentry = (PgStat_StatToastEntry *) hash_search(dbentry->toastactivity, + (void *) &(toastmsg->attr), + HASH_ENTER, &found); + + if (!found) + { + /* + * If it's a new entry, initialize counters to the values + * we just got. + */ + toastentry->t_numexternalized = toastmsg->t_numexternalized; + toastentry->t_numcompressed = toastmsg->t_numcompressed; + toastentry->t_numcompressionsuccess = toastmsg->t_numcompressionsuccess; + toastentry->t_size_orig = toastmsg->t_size_orig; + toastentry->t_size_compressed = toastmsg->t_size_compressed; + toastentry->t_comp_time = toastmsg->t_comp_time; + } + else + { + /* + * Otherwise add the values to the existing entry. + */ + toastentry->t_numexternalized += toastmsg->t_numexternalized; + toastentry->t_numcompressed += toastmsg->t_numcompressed; + toastentry->t_numcompressionsuccess += toastmsg->t_numcompressionsuccess; + toastentry->t_size_orig += toastmsg->t_size_orig; + toastentry->t_size_compressed += toastmsg->t_size_compressed; + toastentry->t_comp_time += toastmsg->t_comp_time; + } + } +} + /* ---------- * pgstat_recv_subscription_drop() - * diff --git a/src/backend/utils/adt/pgstatfuncs.c b/src/backend/utils/adt/pgstatfuncs.c index eff45b16f2..203c279795 100644 --- a/src/backend/utils/adt/pgstatfuncs.c +++ b/src/backend/utils/adt/pgstatfuncs.c @@ -410,6 +410,78 @@ pg_stat_get_function_self_time(PG_FUNCTION_ARGS) PG_RETURN_FLOAT8(((double) funcentry->f_self_time) / 1000.0); } +Datum +pg_stat_get_toast_externalizations(PG_FUNCTION_ARGS) +{ + Oid relid = PG_GETARG_OID(0); + int attr = PG_GETARG_INT16(1); + PgStat_StatToastEntry *toastentry; + + if ((toastentry = pgstat_fetch_stat_toastentry(relid,attr - 1)) == NULL) + PG_RETURN_NULL(); + PG_RETURN_INT64(toastentry->t_numexternalized); +} + +Datum +pg_stat_get_toast_compressions(PG_FUNCTION_ARGS) +{ + Oid relid = PG_GETARG_OID(0); + int attr = PG_GETARG_INT16(1); + PgStat_StatToastEntry *toastentry; + + if ((toastentry = pgstat_fetch_stat_toastentry(relid,attr - 1)) == NULL) + PG_RETURN_NULL(); + PG_RETURN_INT64(toastentry->t_numcompressed); +} + +Datum +pg_stat_get_toast_compressionsuccesses(PG_FUNCTION_ARGS) +{ + Oid relid = PG_GETARG_OID(0); + int attr = PG_GETARG_INT16(1); + PgStat_StatToastEntry *toastentry; + + if ((toastentry = pgstat_fetch_stat_toastentry(relid,attr - 1)) == NULL) + PG_RETURN_NULL(); + PG_RETURN_INT64(toastentry->t_numcompressionsuccess); +} + +Datum +pg_stat_get_toast_originalsizesum(PG_FUNCTION_ARGS) +{ + Oid relid = PG_GETARG_OID(0); + int attr = PG_GETARG_INT16(1); + PgStat_StatToastEntry *toastentry; + + if ((toastentry = pgstat_fetch_stat_toastentry(relid,attr - 1)) == NULL) + PG_RETURN_NULL(); + PG_RETURN_INT64(toastentry->t_size_orig); +} + +Datum +pg_stat_get_toast_compressedsizesum(PG_FUNCTION_ARGS) +{ + Oid relid = PG_GETARG_OID(0); + int attr = PG_GETARG_INT16(1); + PgStat_StatToastEntry *toastentry; + + if ((toastentry = pgstat_fetch_stat_toastentry(relid,attr - 1)) == NULL) + PG_RETURN_NULL(); + PG_RETURN_INT64(toastentry->t_size_compressed); +} + +Datum +pg_stat_get_toast_total_time(PG_FUNCTION_ARGS) +{ + Oid relid = PG_GETARG_OID(0); + int attr = PG_GETARG_INT16(1); + PgStat_StatToastEntry *toastentry; + + if ((toastentry = pgstat_fetch_stat_toastentry(relid,attr - 1)) == NULL) + PG_RETURN_NULL(); + PG_RETURN_INT64(toastentry->t_comp_time); +} + Datum pg_stat_get_backend_idset(PG_FUNCTION_ARGS) { diff --git a/src/backend/utils/misc/guc.c b/src/backend/utils/misc/guc.c index 6d11f9c71b..7728f8165d 100644 --- a/src/backend/utils/misc/guc.c +++ b/src/backend/utils/misc/guc.c @@ -1543,6 +1543,15 @@ static struct config_bool ConfigureNamesBool[] = true, NULL, NULL, NULL }, + { + {"track_toast", PGC_SUSET, STATS_COLLECTOR, + gettext_noop("Collects statistics on TOAST activity."), + NULL + }, + &pgstat_track_toast, + false, + NULL, NULL, NULL + }, { {"track_io_timing", PGC_SUSET, STATS_COLLECTOR, gettext_noop("Collects timing statistics for database I/O activity."), diff --git a/src/backend/utils/misc/postgresql.conf.sample b/src/backend/utils/misc/postgresql.conf.sample index 4a094bb38b..64d559c804 100644 --- a/src/backend/utils/misc/postgresql.conf.sample +++ b/src/backend/utils/misc/postgresql.conf.sample @@ -611,6 +611,7 @@ #track_io_timing = off #track_wal_io_timing = off #track_functions = none # none, pl, all +#track_toast = off #stats_temp_directory = 'pg_stat_tmp' diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat index d8e8715ed1..5ce9667b0b 100644 --- a/src/include/catalog/pg_proc.dat +++ b/src/include/catalog/pg_proc.dat @@ -5673,6 +5673,31 @@ proparallel => 'r', prorettype => 'float8', proargtypes => 'oid', prosrc => 'pg_stat_get_function_self_time' }, +{ oid => '9700', descr => 'statistics: number of TOAST externalizations', + proname => 'pg_stat_get_toast_externalizations', provolatile => 's', + proparallel => 'r', prorettype => 'int8', proargtypes => 'oid int4', + prosrc => 'pg_stat_get_toast_externalizations' }, +{ oid => '9701', descr => 'statistics: number of TOAST compressions', + proname => 'pg_stat_get_toast_compressions', provolatile => 's', + proparallel => 'r', prorettype => 'int8', proargtypes => 'oid int4', + prosrc => 'pg_stat_get_toast_compressions' }, + { oid => '9702', descr => 'statistics: number of successful TOAST compressions', + proname => 'pg_stat_get_toast_compressionsuccesses', provolatile => 's', + proparallel => 'r', prorettype => 'int8', proargtypes => 'oid int4', + prosrc => 'pg_stat_get_toast_compressionsuccesses' }, +{ oid => '9703', descr => 'statistics: total original size of compressed TOAST data', + proname => 'pg_stat_get_toast_originalsizesum', provolatile => 's', + proparallel => 'r', prorettype => 'int8', proargtypes => 'oid int4', + prosrc => 'pg_stat_get_toast_originalsizesum' }, +{ oid => '9704', descr => 'statistics: total compressed size of compressed TOAST data', + proname => 'pg_stat_get_toast_compressedsizesum', provolatile => 's', + proparallel => 'r', prorettype => 'int8', proargtypes => 'oid int4', + prosrc => 'pg_stat_get_toast_compressedsizesum' }, +{ oid => '9705', descr => 'statistics: total time spend TOASTing data', + proname => 'pg_stat_get_toast_total_time', provolatile => 's', + proparallel => 'r', prorettype => 'int8', proargtypes => 'oid int4', + prosrc => 'pg_stat_get_toast_total_time' }, + { oid => '3037', descr => 'statistics: number of scans done for table/index in current transaction', proname => 'pg_stat_get_xact_numscans', provolatile => 'v', diff --git a/src/include/pgstat.h b/src/include/pgstat.h index be2f7e2bcc..e17ed5e09b 100644 --- a/src/include/pgstat.h +++ b/src/include/pgstat.h @@ -87,6 +87,7 @@ typedef enum StatMsgType PGSTAT_MTYPE_DISCONNECT, PGSTAT_MTYPE_SUBSCRIPTIONDROP, PGSTAT_MTYPE_SUBSCRIPTIONERROR, + PGSTAT_MTYPE_TOASTSTAT, } StatMsgType; /* ---------- @@ -720,6 +721,80 @@ typedef struct PgStat_MsgDisconnect SessionEndType m_cause; } PgStat_MsgDisconnect; +/* ---------- + * PgStat_BackendAttrIdentifier Identifier for a single attribute/column (OID + attr) + * Used as a hashable identifier for (e.g.) TOAST columns + * ---------- + */ +typedef struct PgStat_BackendAttrIdentifier +{ + Oid relid; + int attr; +} PgStat_BackendAttrIdentifier; + +/* ---------- + * PgStat_ToastCounts The actual per-TOAST counts kept by a backend + * + * This struct should contain only actual event counters, because we memcmp + * it against zeroes to detect whether there are any counts to transmit. + * + * Note that the time counters are in instr_time format here. We convert to + * microseconds in PgStat_Counter format when transmitting to the collector. + * ---------- + */ +typedef struct PgStat_ToastCounts +{ + PgStat_Counter t_numexternalized; + PgStat_Counter t_numcompressed; + PgStat_Counter t_numcompressionsuccess; + uint64 t_size_orig; + uint64 t_size_compressed; + instr_time t_comp_time; +} PgStat_ToastCounts; + +/* ---------- + * PgStat_BackendToastEntry Entry in backend's per-toast-attr hash table + * ---------- + */ +typedef struct PgStat_BackendToastEntry +{ + PgStat_BackendAttrIdentifier attr; + PgStat_ToastCounts t_counts; +} PgStat_BackendToastEntry; + +/* ---------- + * PgStat_ToastEntry Per-TOAST-column info in a MsgToaststat + * ---------- + */ +typedef struct PgStat_ToastEntry +{ + PgStat_BackendAttrIdentifier attr; + PgStat_Counter t_numexternalized; + PgStat_Counter t_numcompressed; + PgStat_Counter t_numcompressionsuccess; + uint64 t_size_orig; + uint64 t_size_compressed; + PgStat_Counter t_comp_time; /* time in microseconds */ +} PgStat_ToastEntry; + +/* ---------- + * PgStat_MsgToaststat Sent by the backend to report function + * usage statistics. + * ---------- + */ +#define PGSTAT_NUM_TOASTENTRIES \ + ((PGSTAT_MSG_PAYLOAD - sizeof(Oid) - sizeof(int)) \ + / sizeof(PgStat_ToastEntry)) + +typedef struct PgStat_MsgToaststat +{ + PgStat_MsgHdr m_hdr; + Oid m_databaseid; + int m_nentries; + PgStat_ToastEntry m_entry[PGSTAT_NUM_TOASTENTRIES]; +} PgStat_MsgToaststat; + + /* ---------- * PgStat_Msg Union over all possible messages. * ---------- @@ -748,6 +823,7 @@ typedef union PgStat_Msg PgStat_MsgSLRU msg_slru; PgStat_MsgFuncstat msg_funcstat; PgStat_MsgFuncpurge msg_funcpurge; + PgStat_MsgToaststat msg_toaststat; PgStat_MsgRecoveryConflict msg_recoveryconflict; PgStat_MsgDeadlock msg_deadlock; PgStat_MsgTempFile msg_tempfile; @@ -768,7 +844,7 @@ typedef union PgStat_Msg * ------------------------------------------------------------ */ -#define PGSTAT_FILE_FORMAT_ID 0x01A5BCA6 +#define PGSTAT_FILE_FORMAT_ID 0x01A5BCA7 /* ---------- * PgStat_StatDBEntry The collector's data per database @@ -816,6 +892,7 @@ typedef struct PgStat_StatDBEntry */ HTAB *tables; HTAB *functions; + HTAB *toastactivity; } PgStat_StatDBEntry; @@ -984,6 +1061,22 @@ typedef struct PgStat_StatSubEntry TimestampTz stat_reset_timestamp; } PgStat_StatSubEntry; +/* ---------- + * PgStat_StatToastEntry The collector's data per TOAST attribute + * ---------- + */ +typedef struct PgStat_StatToastEntry +{ + PgStat_BackendAttrIdentifier t_id; + PgStat_Counter t_numexternalized; + PgStat_Counter t_numcompressed; + PgStat_Counter t_numcompressionsuccess; + uint64 t_size_orig; + uint64 t_size_compressed; + PgStat_Counter t_comp_time; /* time in microseconds */ +} PgStat_StatToastEntry; + + /* * Working state needed to accumulate per-function-call timing statistics. */ @@ -1007,6 +1100,7 @@ typedef struct PgStat_FunctionCallUsage */ extern PGDLLIMPORT bool pgstat_track_counts; extern PGDLLIMPORT int pgstat_track_functions; +extern PGDLLIMPORT bool pgstat_track_toast; extern char *pgstat_stat_directory; extern char *pgstat_stat_tmpname; extern char *pgstat_stat_filename; @@ -1156,12 +1250,22 @@ extern void pgstat_count_heap_delete(Relation rel); extern void pgstat_count_truncate(Relation rel); extern void pgstat_update_heap_dead_tuples(Relation rel, int delta); +extern void pgstat_count_toast_insert(Relation rel, PgStat_Counter n); + struct FunctionCallInfoBaseData; extern void pgstat_init_function_usage(struct FunctionCallInfoBaseData *fcinfo, PgStat_FunctionCallUsage *fcu); extern void pgstat_end_function_usage(PgStat_FunctionCallUsage *fcu, bool finalize); +extern void +pgstat_report_toast_activity(Oid relid, int attr, + bool externalized, + bool compressed, + int32 old_size, + int32 new_size, + instr_time start_time); + extern void AtEOXact_PgStat(bool isCommit, bool parallel); extern void AtEOSubXact_PgStat(bool isCommit, int nestDepth); @@ -1186,6 +1290,7 @@ extern void pgstat_send_wal(bool force); extern PgStat_StatDBEntry *pgstat_fetch_stat_dbentry(Oid dbid); extern PgStat_StatTabEntry *pgstat_fetch_stat_tabentry(Oid relid); extern PgStat_StatFuncEntry *pgstat_fetch_stat_funcentry(Oid funcid); +extern PgStat_StatToastEntry *pgstat_fetch_stat_toastentry(Oid rel_id, int attr); extern PgStat_StatSubEntry *pgstat_fetch_stat_subscription(Oid subid); extern PgStat_ArchiverStats *pgstat_fetch_stat_archiver(void); extern PgStat_BgWriterStats *pgstat_fetch_stat_bgwriter(void); diff --git a/src/test/regress/expected/rules.out b/src/test/regress/expected/rules.out index ac468568a1..f40d7bb208 100644 --- a/src/test/regress/expected/rules.out +++ b/src/test/regress/expected/rules.out @@ -2114,6 +2114,23 @@ pg_stat_sys_tables| SELECT pg_stat_all_tables.relid, pg_stat_all_tables.autoanalyze_count FROM pg_stat_all_tables WHERE ((pg_stat_all_tables.schemaname = ANY (ARRAY['pg_catalog'::name, 'information_schema'::name])) OR (pg_stat_all_tables.schemaname ~ '^pg_toast'::text)); +pg_stat_toast| SELECT n.nspname AS schemaname, + a.attrelid AS reloid, + a.attnum, + c.relname, + a.attname, + a.attstorage AS storagemethod, + pg_stat_get_toast_externalizations(a.attrelid, (a.attnum)::integer) AS externalized, + a.attcompression AS compressmethod, + pg_stat_get_toast_compressions(a.attrelid, (a.attnum)::integer) AS compressattempts, + pg_stat_get_toast_compressionsuccesses(a.attrelid, (a.attnum)::integer) AS compresssuccesses, + pg_stat_get_toast_compressedsizesum(a.attrelid, (a.attnum)::integer) AS compressedsize, + pg_stat_get_toast_originalsizesum(a.attrelid, (a.attnum)::integer) AS originalsize, + pg_stat_get_toast_total_time(a.attrelid, (a.attnum)::integer) AS total_time + FROM ((pg_attribute a + JOIN pg_class c ON ((c.oid = a.attrelid))) + LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace))) + WHERE (pg_stat_get_toast_externalizations(a.attrelid, (a.attnum)::integer) IS NOT NULL); pg_stat_user_functions| SELECT p.oid AS funcid, n.nspname AS schemaname, p.proname AS funcname, diff --git a/src/test/regress/expected/stats.out b/src/test/regress/expected/stats.out index b7416c8f8f..da8631f7b1 100644 --- a/src/test/regress/expected/stats.out +++ b/src/test/regress/expected/stats.out @@ -11,6 +11,32 @@ SHOW track_counts; -- must be on on (1 row) +-- prepare and fill the pg_stat_toast table now: +SHOW track_toast; + track_toast +------------- + off +(1 row) + +SET track_toast TO on; +SHOW track_toast; + track_toast +------------- + on +(1 row) + +TABLE pg_stat_toast; -- view exists + schemaname | reloid | attnum | relname | attname | storagemethod | externalized | compressmethod | compressattempts | compresssuccesses | compressedsize | originalsize | total_time +------------+--------+--------+---------+---------+---------------+--------------+----------------+------------------+-------------------+----------------+--------------+------------ +(0 rows) + +CREATE TABLE toast_test (cola TEXT, colb TEXT, colc TEXT , cold TEXT); +ALTER TABLE toast_test ALTER colb SET STORAGE EXTERNAL; +ALTER TABLE toast_test ALTER colc SET STORAGE MAIN; +ALTER TABLE toast_test ALTER cold SET STORAGE PLAIN; +INSERT INTO toast_test VALUES (repeat(md5('a'),100), repeat(md5('a'),100), repeat(md5('a'),100), repeat(md5('a'),100) ); +-- make sure we don't interfere with the other tests: +SET track_toast TO off; -- ensure that both seqscan and indexscan plans are allowed SET enable_seqscan TO on; SET enable_indexscan TO on; @@ -255,4 +281,40 @@ SELECT pg_stat_get_tuples_hot_updated('brin_hot'::regclass::oid); DROP TABLE brin_hot; DROP FUNCTION wait_for_hot_stats(); +-- now check that the track_toast stuff worked: +SELECT attname + ,storagemethod + ,externalized + ,compressmethod + ,compressattempts + ,compresssuccesses + ,compressedsize < originalsize AS compression_works + , total_time > 0 AS takes_time +FROM pg_stat_toast WHERE relname = 'toast_test' ORDER BY attname; + attname | storagemethod | externalized | compressmethod | compressattempts | compresssuccesses | compression_works | takes_time +---------+---------------+--------------+----------------+------------------+-------------------+-------------------+------------ + cola | x | 1 | | 1 | 1 | t | t + colb | e | 1 | | 0 | 0 | f | t + colc | m | 0 | | 1 | 1 | t | t +(3 rows) + +SELECT compressattempts=0 AS external_doesnt_compress FROM pg_stat_toast WHERE relname = 'toast_test' AND storagemethod = 'e'; + external_doesnt_compress +-------------------------- + t +(1 row) + +SELECT externalized=0 AS main_doesnt_externalize FROM pg_stat_toast WHERE relname = 'toast_test' AND storagemethod = 'm'; + main_doesnt_externalize +------------------------- + t +(1 row) + +DROP TABLE toast_test; +SELECT count(*) FROM pg_stat_toast WHERE relname = 'toast_test'; + count +------- + 0 +(1 row) + -- End of Stats Test diff --git a/src/test/regress/sql/stats.sql b/src/test/regress/sql/stats.sql index dbc2dd28b6..81890d1238 100644 --- a/src/test/regress/sql/stats.sql +++ b/src/test/regress/sql/stats.sql @@ -8,6 +8,20 @@ -- conditio sine qua non SHOW track_counts; -- must be on +-- prepare and fill the pg_stat_toast table now: +SHOW track_toast; +SET track_toast TO on; +SHOW track_toast; +TABLE pg_stat_toast; -- view exists + +CREATE TABLE toast_test (cola TEXT, colb TEXT, colc TEXT , cold TEXT); +ALTER TABLE toast_test ALTER colb SET STORAGE EXTERNAL; +ALTER TABLE toast_test ALTER colc SET STORAGE MAIN; +ALTER TABLE toast_test ALTER cold SET STORAGE PLAIN; +INSERT INTO toast_test VALUES (repeat(md5('a'),100), repeat(md5('a'),100), repeat(md5('a'),100), repeat(md5('a'),100) ); +-- make sure we don't interfere with the other tests: +SET track_toast TO off; + -- ensure that both seqscan and indexscan plans are allowed SET enable_seqscan TO on; SET enable_indexscan TO on; @@ -228,5 +242,19 @@ SELECT pg_stat_get_tuples_hot_updated('brin_hot'::regclass::oid); DROP TABLE brin_hot; DROP FUNCTION wait_for_hot_stats(); +-- now check that the track_toast stuff worked: +SELECT attname + ,storagemethod + ,externalized + ,compressmethod + ,compressattempts + ,compresssuccesses + ,compressedsize < originalsize AS compression_works + , total_time > 0 AS takes_time +FROM pg_stat_toast WHERE relname = 'toast_test' ORDER BY attname; +SELECT compressattempts=0 AS external_doesnt_compress FROM pg_stat_toast WHERE relname = 'toast_test' AND storagemethod = 'e'; +SELECT externalized=0 AS main_doesnt_externalize FROM pg_stat_toast WHERE relname = 'toast_test' AND storagemethod = 'm'; +DROP TABLE toast_test; +SELECT count(*) FROM pg_stat_toast WHERE relname = 'toast_test'; -- End of Stats Test