Add last_vacuum_index_scans in pg_stat_all_tables

Started by Ken Katoover 3 years ago12 messages

katouknl@oss.nttdata.com

over 3 years ago

1 attachment(s)

Hi hackers,

I think having number of index scans of the last vacuum in
pg_stat_all_tables can be helpful. This value shows how efficiently
vacuums have performed and can be an indicator to increase
maintenance_work_mem.

It was proposed previously[1]/messages/by-id/20171010.192616.108347483.horiguchi.kyotaro@lab.ntt.co.jp, but it was not accepted due to the
limitation of stats collector. Statistics are now stored in shared
memory, so we got more rooms to store statistics. I think this
statistics is still valuable for some people, so I am proposing this
again.

Best wishes,

--
Ken Kato
Advanced Computing Technology Center
Research and Development Headquarters
NTT DATA CORPORATION

[1]: /messages/by-id/20171010.192616.108347483.horiguchi.kyotaro@lab.ntt.co.jp
/messages/by-id/20171010.192616.108347483.horiguchi.kyotaro@lab.ntt.co.jp

Attachments:

show_index_scans_in_pg_stat_all_tables_v1.patchtext/x-diff; name=show_index_scans_in_pg_stat_all_tables_v1.patchDownload

diff --git a/doc/src/sgml/monitoring.sgml b/doc/src/sgml/monitoring.sgml
index 4549c2560e..bd7bfa7d9d 100644
--- a/doc/src/sgml/monitoring.sgml
+++ b/doc/src/sgml/monitoring.sgml
@@ -4557,6 +4557,16 @@ SELECT pid, wait_event_type, wait_event FROM pg_stat_activity WHERE wait_event i
        daemon
       </para></entry>
      </row>
+
+     <row>
+      <entry role="catalog_table_entry"><para role="column_definition">
+       <structfield>last_vacuum_index_scans</structfield> <type>bigint</type>
+      </para>
+      <para>
+       Number of splitted index scans performed during the the last vacuum
+       on this table
+      </para>
+     </row>
     </tbody>
    </tgroup>
   </table>
diff --git a/src/backend/access/heap/vacuumlazy.c b/src/backend/access/heap/vacuumlazy.c
index b802ed247e..bf47d6837d 100644
--- a/src/backend/access/heap/vacuumlazy.c
+++ b/src/backend/access/heap/vacuumlazy.c
@@ -625,7 +625,8 @@ heap_vacuum_rel(Relation rel, VacuumParams *params,
 						 rel->rd_rel->relisshared,
 						 Max(vacrel->new_live_tuples, 0),
 						 vacrel->recently_dead_tuples +
-						 vacrel->missed_dead_tuples);
+						 vacrel->missed_dead_tuples,
+						 vacrel->num_index_scans);
 	pgstat_progress_end_command();
 
 	if (instrument)
diff --git a/src/backend/catalog/system_views.sql b/src/backend/catalog/system_views.sql
index fedaed533b..475551d9b3 100644
--- a/src/backend/catalog/system_views.sql
+++ b/src/backend/catalog/system_views.sql
@@ -676,7 +676,9 @@ CREATE VIEW pg_stat_all_tables AS
             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
+            pg_stat_get_autoanalyze_count(C.oid) AS autoanalyze_count,
+            pg_stat_get_last_vacuum_index_scans(C.oid) AS last_vacuum_index_scans
+
     FROM pg_class C LEFT JOIN
          pg_index I ON C.oid = I.indrelid
          LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
diff --git a/src/backend/utils/activity/pgstat_relation.c b/src/backend/utils/activity/pgstat_relation.c
index a846d9ffb6..ffc9daf944 100644
--- a/src/backend/utils/activity/pgstat_relation.c
+++ b/src/backend/utils/activity/pgstat_relation.c
@@ -208,8 +208,8 @@ pgstat_drop_relation(Relation rel)
  * Report that the table was just vacuumed.
  */
 void
-pgstat_report_vacuum(Oid tableoid, bool shared,
-					 PgStat_Counter livetuples, PgStat_Counter deadtuples)
+pgstat_report_vacuum(Oid tableoid, bool shared, PgStat_Counter livetuples,
+					 PgStat_Counter deadtuples, PgStat_Counter num_index_scans)
 {
 	PgStat_EntryRef *entry_ref;
 	PgStatShared_Relation *shtabentry;
@@ -232,6 +232,7 @@ pgstat_report_vacuum(Oid tableoid, bool shared,
 
 	tabentry->n_live_tuples = livetuples;
 	tabentry->n_dead_tuples = deadtuples;
+	tabentry->n_index_scans = num_index_scans;
 
 	/*
 	 * It is quite possible that a non-aggressive VACUUM ended up skipping
diff --git a/src/backend/utils/adt/pgstatfuncs.c b/src/backend/utils/adt/pgstatfuncs.c
index 893690dad5..57d1de52e6 100644
--- a/src/backend/utils/adt/pgstatfuncs.c
+++ b/src/backend/utils/adt/pgstatfuncs.c
@@ -179,6 +179,20 @@ pg_stat_get_dead_tuples(PG_FUNCTION_ARGS)
 	PG_RETURN_INT64(result);
 }
 
+Datum
+pg_stat_get_last_vacuum_index_scans(PG_FUNCTION_ARGS)
+{
+	Oid			relid = PG_GETARG_OID(0);
+	int64		result;
+	PgStat_StatTabEntry *tabentry;
+
+	if ((tabentry = pgstat_fetch_stat_tabentry(relid)) == NULL)
+		result = 0;
+	else
+		result = tabentry->n_index_scans;
+
+	PG_RETURN_INT64(result);
+}
 
 Datum
 pg_stat_get_mod_since_analyze(PG_FUNCTION_ARGS)
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index a77b293723..d061cd2d8d 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -5331,6 +5331,10 @@
   proname => 'pg_stat_get_autoanalyze_count', provolatile => 's',
   proparallel => 'r', prorettype => 'int8', proargtypes => 'oid',
   prosrc => 'pg_stat_get_autoanalyze_count' },
+{ oid => '3813', descr => 'statistics: last vacuum index scans for a table',
+  proname => 'pg_stat_get_last_vacuum_index_scans', provolatile => 's',
+  proparallel => 'r', prorettype => 'int8', proargtypes => 'oid',
+  prosrc => 'pg_stat_get_last_vacuum_index_scans' },
 { oid => '1936', descr => 'statistics: currently active backend IDs',
   proname => 'pg_stat_get_backend_idset', prorows => '100', proretset => 't',
   provolatile => 's', proparallel => 'r', prorettype => 'int4',
diff --git a/src/include/pgstat.h b/src/include/pgstat.h
index ac28f813b4..89989c8c19 100644
--- a/src/include/pgstat.h
+++ b/src/include/pgstat.h
@@ -366,6 +366,7 @@ typedef struct PgStat_StatTabEntry
 
 	PgStat_Counter n_live_tuples;
 	PgStat_Counter n_dead_tuples;
+	PgStat_Counter n_index_scans;
 	PgStat_Counter changes_since_analyze;
 	PgStat_Counter inserts_since_vacuum;
 
@@ -506,7 +507,9 @@ extern void pgstat_assoc_relation(Relation rel);
 extern void pgstat_unlink_relation(Relation rel);
 
 extern void pgstat_report_vacuum(Oid tableoid, bool shared,
-								 PgStat_Counter livetuples, PgStat_Counter deadtuples);
+								 PgStat_Counter livetuples,
+								 PgStat_Counter deadtuples,
+								 PgStat_Counter num_index_scans);
 extern void pgstat_report_analyze(Relation rel,
 								  PgStat_Counter livetuples, PgStat_Counter deadtuples,
 								  bool resetcounter);
diff --git a/src/test/regress/expected/rules.out b/src/test/regress/expected/rules.out
index fc3cde3226..e93445378c 100644
--- a/src/test/regress/expected/rules.out
+++ b/src/test/regress/expected/rules.out
@@ -1796,7 +1796,8 @@ pg_stat_all_tables| SELECT c.oid AS relid,
     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
+    pg_stat_get_autoanalyze_count(c.oid) AS autoanalyze_count,
+    pg_stat_get_last_vacuum_index_scans(C.oid) AS last_vacuum_index_scans
    FROM ((pg_class c
      LEFT JOIN pg_index i ON ((c.oid = i.indrelid)))
      LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace)))
@@ -2137,7 +2138,8 @@ pg_stat_sys_tables| SELECT pg_stat_all_tables.relid,
     pg_stat_all_tables.vacuum_count,
     pg_stat_all_tables.autovacuum_count,
     pg_stat_all_tables.analyze_count,
-    pg_stat_all_tables.autoanalyze_count
+    pg_stat_all_tables.autoanalyze_count,
+    pg_stat_all_tables.last_vacuum_index_scans
    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_user_functions| SELECT p.oid AS funcid,
@@ -2181,7 +2183,8 @@ pg_stat_user_tables| SELECT pg_stat_all_tables.relid,
     pg_stat_all_tables.vacuum_count,
     pg_stat_all_tables.autovacuum_count,
     pg_stat_all_tables.analyze_count,
-    pg_stat_all_tables.autoanalyze_count
+    pg_stat_all_tables.autoanalyze_count,
+    pg_stat_all_tables.last_vacuum_index_scans
    FROM pg_stat_all_tables
   WHERE ((pg_stat_all_tables.schemaname <> ALL (ARRAY['pg_catalog'::name, 'information_schema'::name])) AND (pg_stat_all_tables.schemaname !~ '^pg_toast'::text));
 pg_stat_wal| SELECT w.wal_records,

Alvaro Herrera

alvherre@alvh.no-ip.org

over 3 years ago

In reply to: Ken Kato (#1)

Re: Add last_vacuum_index_scans in pg_stat_all_tables

On 2022-Jul-04, Ken Kato wrote:

I think having number of index scans of the last vacuum in
pg_stat_all_tables can be helpful. This value shows how efficiently vacuums
have performed and can be an indicator to increase maintenance_work_mem.

Yeah, this would be a good metric to expose, since it directly tells how
to set autovacuum_work_mem. I'm not sure that the shape you propose is
correct, though, because each vacuum run would clobber whatever value
was there before. No other stats counter works that way; they are all
additive. But I'm not sure that adding the current number each time is
sensible, either, because then the only thing you know is the average of
the last X runs, which doesn't tell you much.

Saving some sort of history would be much more useful, but of course a
lot more work.

It was proposed previously[1], but it was not accepted due to the limitation
of stats collector. Statistics are now stored in shared memory, so we got
more rooms to store statistics. I think this statistics is still valuable
for some people, so I am proposing this again.

[1] /messages/by-id/20171010.192616.108347483.horiguchi.kyotaro@lab.ntt.co.jp

I read this thread, but what was proposed there is a bunch of metrics
that are not this one. The discussions there centered about how it
would be unacceptable to incur in the space cost that would be taken by
adding autovacuum-related metrics completely different from the one you
propose. That debate is now over, so we're clear to proceed. But we
need to agree on what to add.

--
Álvaro Herrera Breisgau, Deutschland — https://www.EnterpriseDB.com/

Peter Geoghegan

pg@bowt.ie

over 3 years ago

In reply to: Alvaro Herrera (#2)

Re: Add last_vacuum_index_scans in pg_stat_all_tables

On Fri, Jul 8, 2022 at 10:47 AM Alvaro Herrera <alvherre@alvh.no-ip.org> wrote:

Saving some sort of history would be much more useful, but of course a
lot more work.

I think that storing a certain amount of history would be very useful,
for lots of reasons. Not just for instrumentation purposes; I envisage
a design where VACUUM itself makes certain decisions based on the
history of each VACUUM operation against the table. The direction that
things have taken suggests a certain amount about the direction that
things are going in, which we should try to influence.

The simplest and best example of how this could help is probably
freezing, and freeze debt. Currently, the visibility map interacts
with vacuum_freeze_min_age in a way that allows unfrozen all-visible
pages to accumulate. These pages won't be frozen until the next
aggressive VACUUM. But there is no fixed relationship between the
number of XIDs consumed by the system (per unit of wallclock time) and
the number of unfrozen all-visible pages (over the same duration). So
we might end up having to freeze an absolutely enormous number of
pages in the eventual aggressive vacuum. We also might not -- it's
really hard to predict, for reasons that just don't make much sense.

There are a few things we could do here, but having a sense of history
seems like the important part. If (say) the table exceeds a certain
size, and the number of all-visible pages grows and grows (without any
freezing taking place), then we should "proactively" freeze at least
some of the unfrozen all-visible pages in earlier VACUUM operations.
In other words, we should (at the very least) spread out the burden of
freezing those pages over time, while being careful to not pay too
much more than we would with the old approach if and when the workload
characteristics change again.

More generally, I think that we should blur the distinction between
aggressive and non-aggressive autovacuum. Sure, we'd still need VACUUM
to "behave aggressively" in some sense, but that could all happen
dynamically, without committing to a particular course of action until
the last moment -- being able to change our minds at the last minute
can be very valuable, even though we probably won't change our minds
too often.

--
Peter Geoghegan

Ken Kato

katouknl@oss.nttdata.com

over 3 years ago

In reply to: Peter Geoghegan (#3)

Re: Add last_vacuum_index_scans in pg_stat_all_tables

On 2022-07-09 03:18, Peter Geoghegan wrote:

On Fri, Jul 8, 2022 at 10:47 AM Alvaro Herrera
<alvherre@alvh.no-ip.org> wrote:

Saving some sort of history would be much more useful, but of course a
lot more work.

Thank you for the comments!
Yes, having some sort of history would be ideal in this case.
However, I am not sure how to implement those features at this moment,
so I will take some time to consider.

At the same time, I think having this metrics exposed in the
pg_stat_all_tables comes in handy when tuning the
maintenance_work_mem/autovacuume_work_mem even though it shows the value
of only last vacuum/autovacuum.

Regards,

--
Ken Kato
Advanced Computing Technology Center
Research and Development Headquarters
NTT DATA CORPORATION

Ibrar Ahmed

ibrar.ahmad@gmail.com

over 3 years ago

In reply to: Ken Kato (#4)

Re: Add last_vacuum_index_scans in pg_stat_all_tables

On Fri, Jul 15, 2022 at 1:49 PM Ken Kato <katouknl@oss.nttdata.com> wrote:

On 2022-07-09 03:18, Peter Geoghegan wrote:

On Fri, Jul 8, 2022 at 10:47 AM Alvaro Herrera
<alvherre@alvh.no-ip.org> wrote:

Saving some sort of history would be much more useful, but of course a
lot more work.

Thank you for the comments!
Yes, having some sort of history would be ideal in this case.
However, I am not sure how to implement those features at this moment,
so I will take some time to consider.

At the same time, I think having this metrics exposed in the
pg_stat_all_tables comes in handy when tuning the
maintenance_work_mem/autovacuume_work_mem even though it shows the value
of only last vacuum/autovacuum.

Regards,

--
Ken Kato
Advanced Computing Technology Center
Research and Development Headquarters
NTT DATA CORPORATION

Regression is failing on all platforms; please correct that and resubmit

the patch.

[06:17:08.194] Failed test: 2
[06:17:08.194] Non-zero exit status: 1
[06:17:08.194] Files=33, Tests=411, 167 wallclock secs ( 0.20 usr 0.05 sys
+ 37.96 cusr 21.61 csys = 59.82 CPU)
[06:17:08.194] Result: FAIL
[06:17:08.194] make[2]: *** [Makefile:23: check] Error 1
[06:17:08.194] make[1]: *** [Makefile:52: check-recovery-recurse] Error 2
[06:17:08.194] make: *** [GNUmakefile:71: check-world-src/test-recurse]
Error 2

--
Ibrar Ahmed

Ken Kato

katouknl@oss.nttdata.com

over 3 years ago

In reply to: Ibrar Ahmed (#5)

1 attachment(s)

Re: Add last_vacuum_index_scans in pg_stat_all_tables

Regression is failing on all platforms; please correct that and
resubmit the patch.

Hi,

Thank you for the review!
I fixed it and resubmitting the patch.

Regards,

--
Ken Kato
Advanced Computing Technology Center
Research and Development Headquarters
NTT DATA CORPORATION

Attachments:

show_index_scans_in_pg_stat_all_tables_v2.patchtext/x-diff; name=show_index_scans_in_pg_stat_all_tables_v2.patchDownload

diff --git a/doc/src/sgml/monitoring.sgml b/doc/src/sgml/monitoring.sgml
index 1d9509a2f6..d58dbc36bc 100644
--- a/doc/src/sgml/monitoring.sgml
+++ b/doc/src/sgml/monitoring.sgml
@@ -4562,6 +4562,16 @@ SELECT pid, wait_event_type, wait_event FROM pg_stat_activity WHERE wait_event i
        daemon
       </para></entry>
      </row>
+
+     <row>
+      <entry role="catalog_table_entry"><para role="column_definition">
+       <structfield>last_vacuum_index_scans</structfield> <type>bigint</type>
+      </para>
+      <para>
+       Number of splitted index scans performed during the the last vacuum
+       on this table
+      </para>
+     </row>
     </tbody>
    </tgroup>
   </table>
diff --git a/src/backend/access/heap/vacuumlazy.c b/src/backend/access/heap/vacuumlazy.c
index dfbe37472f..c5b4405f4b 100644
--- a/src/backend/access/heap/vacuumlazy.c
+++ b/src/backend/access/heap/vacuumlazy.c
@@ -629,7 +629,8 @@ heap_vacuum_rel(Relation rel, VacuumParams *params,
 						 rel->rd_rel->relisshared,
 						 Max(vacrel->new_live_tuples, 0),
 						 vacrel->recently_dead_tuples +
-						 vacrel->missed_dead_tuples);
+						 vacrel->missed_dead_tuples,
+						 vacrel->num_index_scans);
 	pgstat_progress_end_command();
 
 	if (instrument)
diff --git a/src/backend/catalog/system_views.sql b/src/backend/catalog/system_views.sql
index 55f7ec79e0..f854576b20 100644
--- a/src/backend/catalog/system_views.sql
+++ b/src/backend/catalog/system_views.sql
@@ -675,7 +675,9 @@ CREATE VIEW pg_stat_all_tables AS
             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
+            pg_stat_get_autoanalyze_count(C.oid) AS autoanalyze_count,
+            pg_stat_get_last_vacuum_index_scans(C.oid) AS last_vacuum_index_scans
+
     FROM pg_class C LEFT JOIN
          pg_index I ON C.oid = I.indrelid
          LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
diff --git a/src/backend/utils/activity/pgstat_relation.c b/src/backend/utils/activity/pgstat_relation.c
index a846d9ffb6..ffc9daf944 100644
--- a/src/backend/utils/activity/pgstat_relation.c
+++ b/src/backend/utils/activity/pgstat_relation.c
@@ -208,8 +208,8 @@ pgstat_drop_relation(Relation rel)
  * Report that the table was just vacuumed.
  */
 void
-pgstat_report_vacuum(Oid tableoid, bool shared,
-					 PgStat_Counter livetuples, PgStat_Counter deadtuples)
+pgstat_report_vacuum(Oid tableoid, bool shared, PgStat_Counter livetuples,
+					 PgStat_Counter deadtuples, PgStat_Counter num_index_scans)
 {
 	PgStat_EntryRef *entry_ref;
 	PgStatShared_Relation *shtabentry;
@@ -232,6 +232,7 @@ pgstat_report_vacuum(Oid tableoid, bool shared,
 
 	tabentry->n_live_tuples = livetuples;
 	tabentry->n_dead_tuples = deadtuples;
+	tabentry->n_index_scans = num_index_scans;
 
 	/*
 	 * It is quite possible that a non-aggressive VACUUM ended up skipping
diff --git a/src/backend/utils/adt/pgstatfuncs.c b/src/backend/utils/adt/pgstatfuncs.c
index be15b4b2e5..d5fd34ee0e 100644
--- a/src/backend/utils/adt/pgstatfuncs.c
+++ b/src/backend/utils/adt/pgstatfuncs.c
@@ -179,6 +179,20 @@ pg_stat_get_dead_tuples(PG_FUNCTION_ARGS)
 	PG_RETURN_INT64(result);
 }
 
+Datum
+pg_stat_get_last_vacuum_index_scans(PG_FUNCTION_ARGS)
+{
+	Oid			relid = PG_GETARG_OID(0);
+	int64		result;
+	PgStat_StatTabEntry *tabentry;
+
+	if ((tabentry = pgstat_fetch_stat_tabentry(relid)) == NULL)
+		result = 0;
+	else
+		result = tabentry->n_index_scans;
+
+	PG_RETURN_INT64(result);
+}
 
 Datum
 pg_stat_get_mod_since_analyze(PG_FUNCTION_ARGS)
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index a07e737a33..36c6c53e65 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -5331,6 +5331,10 @@
   proname => 'pg_stat_get_autoanalyze_count', provolatile => 's',
   proparallel => 'r', prorettype => 'int8', proargtypes => 'oid',
   prosrc => 'pg_stat_get_autoanalyze_count' },
+{ oid => '3813', descr => 'statistics: last vacuum index scans for a table',
+  proname => 'pg_stat_get_last_vacuum_index_scans', provolatile => 's',
+  proparallel => 'r', prorettype => 'int8', proargtypes => 'oid',
+  prosrc => 'pg_stat_get_last_vacuum_index_scans' },
 { oid => '1936', descr => 'statistics: currently active backend IDs',
   proname => 'pg_stat_get_backend_idset', prorows => '100', proretset => 't',
   provolatile => 's', proparallel => 'r', prorettype => 'int4',
diff --git a/src/include/pgstat.h b/src/include/pgstat.h
index ac28f813b4..89989c8c19 100644
--- a/src/include/pgstat.h
+++ b/src/include/pgstat.h
@@ -366,6 +366,7 @@ typedef struct PgStat_StatTabEntry
 
 	PgStat_Counter n_live_tuples;
 	PgStat_Counter n_dead_tuples;
+	PgStat_Counter n_index_scans;
 	PgStat_Counter changes_since_analyze;
 	PgStat_Counter inserts_since_vacuum;
 
@@ -506,7 +507,9 @@ extern void pgstat_assoc_relation(Relation rel);
 extern void pgstat_unlink_relation(Relation rel);
 
 extern void pgstat_report_vacuum(Oid tableoid, bool shared,
-								 PgStat_Counter livetuples, PgStat_Counter deadtuples);
+								 PgStat_Counter livetuples,
+								 PgStat_Counter deadtuples,
+								 PgStat_Counter num_index_scans);
 extern void pgstat_report_analyze(Relation rel,
 								  PgStat_Counter livetuples, PgStat_Counter deadtuples,
 								  bool resetcounter);
diff --git a/src/test/regress/expected/rules.out b/src/test/regress/expected/rules.out
index 9dd137415e..070b4ef2e1 100644
--- a/src/test/regress/expected/rules.out
+++ b/src/test/regress/expected/rules.out
@@ -1792,7 +1792,8 @@ pg_stat_all_tables| SELECT c.oid AS relid,
     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
+    pg_stat_get_autoanalyze_count(c.oid) AS autoanalyze_count,
+    pg_stat_get_last_vacuum_index_scans(c.oid) AS last_vacuum_index_scans
    FROM ((pg_class c
      LEFT JOIN pg_index i ON ((c.oid = i.indrelid)))
      LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace)))
@@ -2133,7 +2134,8 @@ pg_stat_sys_tables| SELECT pg_stat_all_tables.relid,
     pg_stat_all_tables.vacuum_count,
     pg_stat_all_tables.autovacuum_count,
     pg_stat_all_tables.analyze_count,
-    pg_stat_all_tables.autoanalyze_count
+    pg_stat_all_tables.autoanalyze_count,
+    pg_stat_all_tables.last_vacuum_index_scans
    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_user_functions| SELECT p.oid AS funcid,
@@ -2177,7 +2179,8 @@ pg_stat_user_tables| SELECT pg_stat_all_tables.relid,
     pg_stat_all_tables.vacuum_count,
     pg_stat_all_tables.autovacuum_count,
     pg_stat_all_tables.analyze_count,
-    pg_stat_all_tables.autoanalyze_count
+    pg_stat_all_tables.autoanalyze_count,
+    pg_stat_all_tables.last_vacuum_index_scans
    FROM pg_stat_all_tables
   WHERE ((pg_stat_all_tables.schemaname <> ALL (ARRAY['pg_catalog'::name, 'information_schema'::name])) AND (pg_stat_all_tables.schemaname !~ '^pg_toast'::text));
 pg_stat_wal| SELECT w.wal_records,

Fujii Masao

masao.fujii@oss.nttdata.com

over 3 years ago

In reply to: Ken Kato (#6)

Re: Add last_vacuum_index_scans in pg_stat_all_tables

On 2022/09/16 13:23, Ken Kato wrote:

Regression is failing on all platforms; please correct that and
resubmit the patch.

Hi,

Thank you for the review!
I fixed it and resubmitting the patch.

Could you tell me why the number of index scans should be tracked for
each table? Instead, isn't it enough to have one global counter, to
check whether the current setting of maintenance_work_mem is sufficient
or not? That is, I'm thinking to have something like pg_stat_vacuum view
that reports, for example, the number of vacuum runs, the total
number of index scans, the maximum number of index scans by one
vacuum run, the number of cancellation of vacuum because of
lock conflicts, etc. If so, when these global counters are high or
increasing, we can think that it may worth tuning maintenance_work_mem.

Regards,

--
Fujii Masao
Advanced Computing Technology Center
Research and Development Headquarters
NTT DATA CORPORATION

Andres Freund

andres@anarazel.de

over 3 years ago

In reply to: Ken Kato (#6)

Re: Add last_vacuum_index_scans in pg_stat_all_tables

Hi,

On 2022-09-16 13:23:06 +0900, Ken Kato wrote:

Thank you for the review!
I fixed it and resubmitting the patch.

cfbot flags that the docs aren't valid:
https://cirrus-ci.com/task/5309377937670144?logs=docs_build#L295
[15:05:39.683] monitoring.sgml:4574: parser error : Opening and ending tag mismatch: entry line 4567 and row
[15:05:39.683] </row>
[15:05:39.683] ^

The problem is that you're not closing the <entry>

Greetings,

Andres Freund

Alvaro Herrera

alvherre@alvh.no-ip.org

over 3 years ago

In reply to: Fujii Masao (#7)

Re: Add last_vacuum_index_scans in pg_stat_all_tables

On 2022-Sep-16, Fujii Masao wrote:

Could you tell me why the number of index scans should be tracked for
each table? Instead, isn't it enough to have one global counter, to
check whether the current setting of maintenance_work_mem is sufficient
or not? That is, I'm thinking to have something like pg_stat_vacuum view
that reports, for example, the number of vacuum runs, the total
number of index scans, the maximum number of index scans by one
vacuum run, the number of cancellation of vacuum because of
lock conflicts, etc. If so, when these global counters are high or
increasing, we can think that it may worth tuning maintenance_work_mem.

I think that there are going to be cases where some tables in a database
definitely require multiple index scans no matter what; but you
definitely want to know how many occurred for others, not so highly
trafficked tables. So I *think* a single counter across the whole
database might not be sufficient.

The way I imagine using this (and I haven't operated databases in quite
a while so this may be all wet) is that I would have a report of which
tables have the highest numbers of indexscans, then study the detailed
vacuum reports for those tables as a way to change autovacuum_work_mem.

On the other hand, we have an absolute high cap of 1 GB for autovacuum's
work_mem, and many systems are already using that as the configured
value. Maybe trying to fine-tune it is a waste of time. If a 1TB table
says that it had 4 index scans, what are you going to do about it? It's
a lost cause. It sounds like we need more code changes so that more
memory can be used; and also changes so that that memory is used more
efficiently. We had a patch for this, I don't know if that was
committed already.

--
Álvaro Herrera Breisgau, Deutschland — https://www.EnterpriseDB.com/
"Ninguna manada de bestias tiene una voz tan horrible como la humana" (Orual)

#10

Kshetrapaldesi Tutika

kshetra1@gmail.com

over 3 years ago

In reply to: Alvaro Herrera (#9)

Re: Add last_vacuum_index_scans in pg_stat_all_tables

I applied this patch in my local environment and would like to reviewthe same before 14-October-2022 with some test data

postgres=# select version();
version
----------------------------------------------------------------------------------------------------------
PostgreSQL 15beta4 on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 9.4.0-1ubuntu1~20.04.1) 9.4.0, 64-bit
(1 row)

#11

Ken Kato

katouknl@oss.nttdata.com

over 3 years ago

In reply to: Kshetrapaldesi Tutika (#10)

1 attachment(s)

Re: Add last_vacuum_index_scans in pg_stat_all_tables

The problem is that you're not closing the <entry>

Thank you for the reviews and comments.
I closed the <entry> so that the problem should be fixed now.

Regards,

--
Ken Kato
Advanced Computing Technology Center
Research and Development Headquarters
NTT DATA CORPORATION

Attachments:

show_index_scans_in_pg_stat_all_tables_v3.patchtext/x-diff; charset=us-ascii; name=show_index_scans_in_pg_stat_all_tables_v3.patchDownload

diff --git a/doc/src/sgml/monitoring.sgml b/doc/src/sgml/monitoring.sgml
index 342b20ebeb..14cb496fdc 100644
--- a/doc/src/sgml/monitoring.sgml
+++ b/doc/src/sgml/monitoring.sgml
@@ -4562,6 +4562,16 @@ SELECT pid, wait_event_type, wait_event FROM pg_stat_activity WHERE wait_event i
        daemon
       </para></entry>
      </row>
+
+     <row>
+      <entry role="catalog_table_entry"><para role="column_definition">
+       <structfield>last_vacuum_index_scans</structfield> <type>bigint</type>
+      </para>
+      <para>
+       Number of splitted index scans performed during the the last vacuum
+       on this table
+      </para></entry>
+     </row>
     </tbody>
    </tgroup>
   </table>
diff --git a/src/backend/access/heap/vacuumlazy.c b/src/backend/access/heap/vacuumlazy.c
index dfbe37472f..c5b4405f4b 100644
--- a/src/backend/access/heap/vacuumlazy.c
+++ b/src/backend/access/heap/vacuumlazy.c
@@ -629,7 +629,8 @@ heap_vacuum_rel(Relation rel, VacuumParams *params,
 						 rel->rd_rel->relisshared,
 						 Max(vacrel->new_live_tuples, 0),
 						 vacrel->recently_dead_tuples +
-						 vacrel->missed_dead_tuples);
+						 vacrel->missed_dead_tuples,
+						 vacrel->num_index_scans);
 	pgstat_progress_end_command();
 
 	if (instrument)
diff --git a/src/backend/catalog/system_views.sql b/src/backend/catalog/system_views.sql
index 55f7ec79e0..f854576b20 100644
--- a/src/backend/catalog/system_views.sql
+++ b/src/backend/catalog/system_views.sql
@@ -675,7 +675,9 @@ CREATE VIEW pg_stat_all_tables AS
             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
+            pg_stat_get_autoanalyze_count(C.oid) AS autoanalyze_count,
+            pg_stat_get_last_vacuum_index_scans(C.oid) AS last_vacuum_index_scans
+
     FROM pg_class C LEFT JOIN
          pg_index I ON C.oid = I.indrelid
          LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
diff --git a/src/backend/utils/activity/pgstat_relation.c b/src/backend/utils/activity/pgstat_relation.c
index a846d9ffb6..ffc9daf944 100644
--- a/src/backend/utils/activity/pgstat_relation.c
+++ b/src/backend/utils/activity/pgstat_relation.c
@@ -208,8 +208,8 @@ pgstat_drop_relation(Relation rel)
  * Report that the table was just vacuumed.
  */
 void
-pgstat_report_vacuum(Oid tableoid, bool shared,
-					 PgStat_Counter livetuples, PgStat_Counter deadtuples)
+pgstat_report_vacuum(Oid tableoid, bool shared, PgStat_Counter livetuples,
+					 PgStat_Counter deadtuples, PgStat_Counter num_index_scans)
 {
 	PgStat_EntryRef *entry_ref;
 	PgStatShared_Relation *shtabentry;
@@ -232,6 +232,7 @@ pgstat_report_vacuum(Oid tableoid, bool shared,
 
 	tabentry->n_live_tuples = livetuples;
 	tabentry->n_dead_tuples = deadtuples;
+	tabentry->n_index_scans = num_index_scans;
 
 	/*
 	 * It is quite possible that a non-aggressive VACUUM ended up skipping
diff --git a/src/backend/utils/adt/pgstatfuncs.c b/src/backend/utils/adt/pgstatfuncs.c
index eadd8464ff..573f761f6b 100644
--- a/src/backend/utils/adt/pgstatfuncs.c
+++ b/src/backend/utils/adt/pgstatfuncs.c
@@ -179,6 +179,20 @@ pg_stat_get_dead_tuples(PG_FUNCTION_ARGS)
 	PG_RETURN_INT64(result);
 }
 
+Datum
+pg_stat_get_last_vacuum_index_scans(PG_FUNCTION_ARGS)
+{
+	Oid			relid = PG_GETARG_OID(0);
+	int64		result;
+	PgStat_StatTabEntry *tabentry;
+
+	if ((tabentry = pgstat_fetch_stat_tabentry(relid)) == NULL)
+		result = 0;
+	else
+		result = tabentry->n_index_scans;
+
+	PG_RETURN_INT64(result);
+}
 
 Datum
 pg_stat_get_mod_since_analyze(PG_FUNCTION_ARGS)
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index 68bb032d3e..9cadf4df7a 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -5334,6 +5334,10 @@
   proname => 'pg_stat_get_autoanalyze_count', provolatile => 's',
   proparallel => 'r', prorettype => 'int8', proargtypes => 'oid',
   prosrc => 'pg_stat_get_autoanalyze_count' },
+{ oid => '3813', descr => 'statistics: last vacuum index scans for a table',
+  proname => 'pg_stat_get_last_vacuum_index_scans', provolatile => 's',
+  proparallel => 'r', prorettype => 'int8', proargtypes => 'oid',
+  prosrc => 'pg_stat_get_last_vacuum_index_scans' },
 { oid => '1936', descr => 'statistics: currently active backend IDs',
   proname => 'pg_stat_get_backend_idset', prorows => '100', proretset => 't',
   provolatile => 's', proparallel => 'r', prorettype => 'int4',
diff --git a/src/include/pgstat.h b/src/include/pgstat.h
index ad7334a0d2..4fa6894ec8 100644
--- a/src/include/pgstat.h
+++ b/src/include/pgstat.h
@@ -366,6 +366,7 @@ typedef struct PgStat_StatTabEntry
 
 	PgStat_Counter n_live_tuples;
 	PgStat_Counter n_dead_tuples;
+	PgStat_Counter n_index_scans;
 	PgStat_Counter changes_since_analyze;
 	PgStat_Counter inserts_since_vacuum;
 
@@ -506,7 +507,9 @@ extern void pgstat_assoc_relation(Relation rel);
 extern void pgstat_unlink_relation(Relation rel);
 
 extern void pgstat_report_vacuum(Oid tableoid, bool shared,
-								 PgStat_Counter livetuples, PgStat_Counter deadtuples);
+								 PgStat_Counter livetuples,
+								 PgStat_Counter deadtuples,
+								 PgStat_Counter num_index_scans);
 extern void pgstat_report_analyze(Relation rel,
 								  PgStat_Counter livetuples, PgStat_Counter deadtuples,
 								  bool resetcounter);
diff --git a/src/test/regress/expected/rules.out b/src/test/regress/expected/rules.out
index 9dd137415e..070b4ef2e1 100644
--- a/src/test/regress/expected/rules.out
+++ b/src/test/regress/expected/rules.out
@@ -1792,7 +1792,8 @@ pg_stat_all_tables| SELECT c.oid AS relid,
     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
+    pg_stat_get_autoanalyze_count(c.oid) AS autoanalyze_count,
+    pg_stat_get_last_vacuum_index_scans(c.oid) AS last_vacuum_index_scans
    FROM ((pg_class c
      LEFT JOIN pg_index i ON ((c.oid = i.indrelid)))
      LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace)))
@@ -2133,7 +2134,8 @@ pg_stat_sys_tables| SELECT pg_stat_all_tables.relid,
     pg_stat_all_tables.vacuum_count,
     pg_stat_all_tables.autovacuum_count,
     pg_stat_all_tables.analyze_count,
-    pg_stat_all_tables.autoanalyze_count
+    pg_stat_all_tables.autoanalyze_count,
+    pg_stat_all_tables.last_vacuum_index_scans
    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_user_functions| SELECT p.oid AS funcid,
@@ -2177,7 +2179,8 @@ pg_stat_user_tables| SELECT pg_stat_all_tables.relid,
     pg_stat_all_tables.vacuum_count,
     pg_stat_all_tables.autovacuum_count,
     pg_stat_all_tables.analyze_count,
-    pg_stat_all_tables.autoanalyze_count
+    pg_stat_all_tables.autoanalyze_count,
+    pg_stat_all_tables.last_vacuum_index_scans
    FROM pg_stat_all_tables
   WHERE ((pg_stat_all_tables.schemaname <> ALL (ARRAY['pg_catalog'::name, 'information_schema'::name])) AND (pg_stat_all_tables.schemaname !~ '^pg_toast'::text));
 pg_stat_wal| SELECT w.wal_records,

#12

vignesh C

vignesh21@gmail.com

about 3 years ago

In reply to: Ken Kato (#11)

Re: Add last_vacuum_index_scans in pg_stat_all_tables

On Tue, 4 Oct 2022 at 10:20, Ken Kato <katouknl@oss.nttdata.com> wrote:

The problem is that you're not closing the <entry>

Thank you for the reviews and comments.
I closed the <entry> so that the problem should be fixed now.

The patch does not apply on top of HEAD as in [1]http://cfbot.cputube.org/patch_41_3756.log, please post a rebased patch:
=== Applying patches on top of PostgreSQL commit ID
e351f85418313e97c203c73181757a007dfda6d0 ===
=== applying patch ./show_index_scans_in_pg_stat_all_tables_v3.patch
patching file src/backend/utils/activity/pgstat_relation.c
Hunk #1 succeeded at 209 (offset 1 line).
Hunk #2 FAILED at 232.
1 out of 2 hunks FAILED -- saving rejects to file
src/backend/utils/activity/pgstat_relation.c.rej
patching file src/include/pgstat.h
Hunk #1 FAILED at 366.
1 out of 2 hunks FAILED -- saving rejects to file src/include/pgstat.h.rej
patching file src/test/regress/expected/rules.out
Hunk #1 succeeded at 1800 (offset 8 lines).
Hunk #2 succeeded at 2145 (offset 11 lines).
Hunk #3 succeeded at 2193 (offset 14 lines).

[1]: http://cfbot.cputube.org/patch_41_3756.log

Regards,
Vignesh