Am 22.03.22 um 12:23 schrieb Gunnar "Nick" Bluth: > Am 22.03.22 um 02:17 schrieb Andres Freund: >> Hi, >> >> On 2022-03-08 19:32:03 +0100, Gunnar "Nick" Bluth wrote: >>> v8 (applies cleanly to today's HEAD/master) attached. >> >> This doesn't apply anymore, likely due to my recent pgstat changes - which >> you'd need to adapt to... > > Now, that's been quite an overhaul... kudos! > > >> http://cfbot.cputube.org/patch_37_3457.log >> >> Marked as waiting on author. > > v9 attached. > > TBTH, I don't fully understand all the external/static stuff, but it > applies to HEAD/master, compiles and passes all tests, so... ;-)
And v10 catches up to master once again. Best, -- 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 | 161 ++++++++++++++++++++++++- src/backend/utils/activity/Makefile | 1 + src/backend/utils/activity/pgstat_toast.c | 157 +++++++++++++++++++++++++ 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 | 110 ++++++++++++++++- src/include/utils/pgstat_internal.h | 1 + src/test/regress/expected/rules.out | 17 +++ src/test/regress/expected/stats.out | 62 ++++++++++ src/test/regress/sql/stats.sql | 28 +++++ 17 files changed, 897 insertions(+), 8 deletions(-) diff --git a/doc/src/sgml/config.sgml b/doc/src/sgml/config.sgml index 43e4ade83e..e6f0768472 100644 --- a/doc/src/sgml/config.sgml +++ b/doc/src/sgml/config.sgml @@ -7935,6 +7935,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 3b9172f65b..cd0a5bea35 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 @@ -4946,6 +4957,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 f4b9f66589..d0e165d5f1 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"> @@ -1069,7 +1078,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..98d10a0670 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" +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 9eaa51df29..e9cce5c51a 100644 --- a/src/backend/catalog/system_views.sql +++ b/src/backend/catalog/system_views.sql @@ -1066,6 +1066,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 c10311e036..c4cf0a33fc 100644 --- a/src/backend/postmaster/pgstat.c +++ b/src/backend/postmaster/pgstat.c @@ -116,7 +116,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); @@ -159,6 +159,7 @@ static void pgstat_recv_replslot(PgStat_MsgReplSlot *msg, int len); static void pgstat_recv_tempfile(PgStat_MsgTempFile *msg, int len); static void pgstat_recv_subscription_drop(PgStat_MsgSubscriptionDrop *msg, int len); static void pgstat_recv_subscription_error(PgStat_MsgSubscriptionError *msg, int len); +static void pgstat_recv_toaststat(PgStat_MsgToaststat *msg, int len); /* ---------- @@ -168,7 +169,6 @@ static void pgstat_recv_subscription_error(PgStat_MsgSubscriptionError *msg, int bool pgstat_track_counts = false; - /* ---------- * Built from GUC parameter * ---------- @@ -972,6 +972,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); @@ -1494,6 +1497,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() - @@ -1914,6 +1946,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); @@ -2050,6 +2086,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); } /* @@ -2359,7 +2402,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)) @@ -2490,8 +2533,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; @@ -2546,6 +2591,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 @@ -2784,6 +2840,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 @@ -2821,6 +2878,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. @@ -2829,6 +2894,7 @@ pgstat_read_statsfiles(Oid onlydb, bool permanent, bool deep) pgstat_read_db_statsfile(dbentry->databaseid, dbentry->tables, dbentry->functions, + dbentry->toastactivity, permanent); break; @@ -2951,13 +3017,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; @@ -3071,6 +3139,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. */ @@ -3745,6 +3839,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, @@ -3781,7 +3877,8 @@ 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; @@ -4444,6 +4541,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/activity/Makefile b/src/backend/utils/activity/Makefile index 25a967ab7d..229cdaefd3 100644 --- a/src/backend/utils/activity/Makefile +++ b/src/backend/utils/activity/Makefile @@ -26,6 +26,7 @@ OBJS = \ pgstat_subscription.o \ pgstat_wal.o \ pgstat_slru.o \ + pgstat_toast.o \ wait_event.o include $(top_srcdir)/src/backend/common.mk diff --git a/src/backend/utils/activity/pgstat_toast.c b/src/backend/utils/activity/pgstat_toast.c new file mode 100644 index 0000000000..b6ba62c302 --- /dev/null +++ b/src/backend/utils/activity/pgstat_toast.c @@ -0,0 +1,157 @@ +/* ------------------------------------------------------------------------- + * + * pgstat_toast.c + * Implementation of TOAST statistics. + * + * This file contains the implementation of TOAST statistics. It is kept + * separate from pgstat.c to enforce the line between the statistics access / + * storage implementation and the details about individual types of + * statistics. + * + * Copyright (c) 2001-2022, PostgreSQL Global Development Group + * + * IDENTIFICATION + * src/backend/utils/activity/pgstat_toast.c + * ------------------------------------------------------------------------- + */ + +#include "postgres.h" + +#include "utils/pgstat_internal.h" +#include "utils/timestamp.h" + +/* + * Indicates if backend has some function stats that it hasn't yet + * sent to the collector. + */ +bool have_toast_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; + +/* + * 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; +} + +/* + * Subroutine for pgstat_report_stat: populate and send a toast stat message + */ +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; +} + diff --git a/src/backend/utils/adt/pgstatfuncs.c b/src/backend/utils/adt/pgstatfuncs.c index ce84525d40..a1f74c74ff 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 9e8ab1420d..339d2553d4 100644 --- a/src/backend/utils/misc/guc.c +++ b/src/backend/utils/misc/guc.c @@ -1557,6 +1557,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 93d221a37b..4c3b7ae29b 100644 --- a/src/backend/utils/misc/postgresql.conf.sample +++ b/src/backend/utils/misc/postgresql.conf.sample @@ -613,6 +613,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 25304430f4..6d7ba55293 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 534d595ca0..7c8191aefe 100644 --- a/src/include/pgstat.h +++ b/src/include/pgstat.h @@ -33,7 +33,6 @@ /* Default directory to store temporary statistics data in */ #define PG_STAT_TMP_DIR "pg_stat_tmp" - /* Values for track_functions GUC variable --- order is significant! */ typedef enum TrackFunctionsLevel { @@ -252,6 +251,7 @@ typedef enum StatMsgType PGSTAT_MTYPE_DISCONNECT, PGSTAT_MTYPE_SUBSCRIPTIONDROP, PGSTAT_MTYPE_SUBSCRIPTIONERROR, + PGSTAT_MTYPE_TOASTSTAT, } StatMsgType; /* ---------- @@ -726,6 +726,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. * ---------- @@ -754,6 +828,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; @@ -774,7 +849,7 @@ typedef union PgStat_Msg * ------------------------------------------------------------ */ -#define PGSTAT_FILE_FORMAT_ID 0x01A5BCA6 +#define PGSTAT_FILE_FORMAT_ID 0x01A5BCA7 /* * Archiver statistics kept in the stats collector @@ -864,6 +939,7 @@ typedef struct PgStat_StatDBEntry */ HTAB *tables; HTAB *functions; + HTAB *toastactivity; } PgStat_StatDBEntry; /* ---------- @@ -936,6 +1012,22 @@ typedef struct PgStat_StatSubEntry } 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; + + +/* * PgStat_StatTabEntry The collector's data per table (or index) * ---------- */ @@ -1027,6 +1119,7 @@ extern PgStat_BgWriterStats *pgstat_fetch_stat_bgwriter(void); extern PgStat_CheckpointerStats *pgstat_fetch_stat_checkpointer(void); extern PgStat_StatDBEntry *pgstat_fetch_stat_dbentry(Oid dbid); extern PgStat_StatFuncEntry *pgstat_fetch_stat_funcentry(Oid funcid); +extern PgStat_StatToastEntry *pgstat_fetch_stat_toastentry(Oid rel_id, int attr); extern PgStat_GlobalStats *pgstat_fetch_global(void); extern PgStat_StatReplSlotEntry *pgstat_fetch_replslot(NameData slotname); extern PgStat_StatSubEntry *pgstat_fetch_stat_subscription(Oid subid); @@ -1090,6 +1183,7 @@ extern void pgstat_end_function_usage(PgStat_FunctionCallUsage *fcu, extern PgStat_BackendFunctionEntry *find_funcstat_entry(Oid func_id); + /* * Functions in pgstat_relation.c */ @@ -1199,6 +1293,17 @@ extern void pgstat_report_subscription_drop(Oid subid); extern void pgstat_send_wal(bool force); +/* + * Functions in pgstat_toast.c + */ + +extern void pgstat_send_toaststats(void); +extern void pgstat_report_toast_activity(Oid relid, int attr, + bool externalized, + bool compressed, + int32 old_size, + int32 new_size, + instr_time start_time); /* * Variables in pgstat.c @@ -1207,6 +1312,7 @@ extern void pgstat_send_wal(bool force); /* GUC parameters */ 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; diff --git a/src/include/utils/pgstat_internal.h b/src/include/utils/pgstat_internal.h index abbb4f8d96..44b0aeb7af 100644 --- a/src/include/utils/pgstat_internal.h +++ b/src/include/utils/pgstat_internal.h @@ -27,6 +27,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 diff --git a/src/test/regress/expected/rules.out b/src/test/regress/expected/rules.out index 423b9b99fb..91358a5b62 100644 --- a/src/test/regress/expected/rules.out +++ b/src/test/regress/expected/rules.out @@ -2128,6 +2128,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 06a1d2f229..b9514e1f34 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,6 +281,42 @@ 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) + -- ensure that stats accessors handle NULL input correctly SELECT pg_stat_get_replication_slot(NULL); pg_stat_get_replication_slot diff --git a/src/test/regress/sql/stats.sql b/src/test/regress/sql/stats.sql index ae1ec173e3..b30de2fa7a 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,6 +242,20 @@ 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'; -- ensure that stats accessors handle NULL input correctly SELECT pg_stat_get_replication_slot(NULL);