Re: Add last_vacuum_index_scans in pg_stat_all_tables
On Tue, 4 Oct 2022 at 10:20, Ken Kato wrote: > > > The problem is that you're not closing the > > Thank you for the reviews and comments. > I closed the so that the problem should be fixed now. The patch does not apply on top of HEAD as in [1], 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
Re: Add last_vacuum_index_scans in pg_stat_all_tables
The problem is that you're not closing the
Thank you for the reviews and comments.
I closed the so that the problem should be fixed now.
Regards,
--
Ken Kato
Advanced Computing Technology Center
Research and Development Headquarters
NTT DATA CORPORATIONdiff --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
+
+
+
+ last_vacuum_index_scans bigint
+
+
+ Number of splitted index scans performed during the the last vacuum
+ on this 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;
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=# \d pg_stat_all_tables View "pg_catalog.pg_stat_all_tables" Column | Type | Collation | Nullable | Default -+--+---+--+- relid | oid | | | schemaname | name | | | relname | name | | | seq_scan| bigint | | | seq_tup_read| bigint | | | idx_scan| bigint | | | idx_tup_fetch | bigint | | | n_tup_ins | bigint | | | n_tup_upd | bigint | | | n_tup_del | bigint | | | n_tup_hot_upd | bigint | | | n_live_tup | bigint | | | n_dead_tup | bigint | | | n_mod_since_analyze | bigint | | | n_ins_since_vacuum | bigint | | | last_vacuum | timestamp with time zone | | | last_autovacuum | timestamp with time zone | | | last_analyze| timestamp with time zone | | | last_autoanalyze| timestamp with time zone | | | vacuum_count| bigint | | | autovacuum_count| bigint | | | analyze_count | bigint | | | autoanalyze_count | bigint | | | last_vacuum_index_scans | bigint | | | 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)
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 HerreraBreisgau, Deutschland — https://www.EnterpriseDB.com/ "Ninguna manada de bestias tiene una voz tan horrible como la humana" (Orual)
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] [15:05:39.683]^ The problem is that you're not closing the Greetings, Andres Freund
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
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 CORPORATIONdiff --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
+
+
+
+ last_vacuum_index_scans bigint
+
+
+ Number of splitted index scans performed during the the last vacuum
+ on this 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
Re: Add last_vacuum_index_scans in pg_stat_all_tables
On Fri, Jul 15, 2022 at 1:49 PM Ken Kato wrote: > On 2022-07-09 03:18, Peter Geoghegan wrote: > > On Fri, Jul 8, 2022 at 10:47 AM Alvaro Herrera > > 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
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 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
Re: Add last_vacuum_index_scans in pg_stat_all_tables
On Fri, Jul 8, 2022 at 10:47 AM Alvaro Herrera 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
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] > https://www.postgresql.org/message-id/20171010.192616.108347483.horiguchi.kyotaro%40lab.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 HerreraBreisgau, Deutschland — https://www.EnterpriseDB.com/
Add last_vacuum_index_scans in pg_stat_all_tables
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], 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]
https://www.postgresql.org/message-id/20171010.192616.108347483.horiguchi.kyotaro%40lab.ntt.co.jpdiff --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
+
+
+
+ last_vacuum_index_scans bigint
+
+
+ Number of splitted index scans performed during the the last vacuum
+ on this 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 =>
