n_mod_since_analyze isn't reset at table truncation
Hi,
While reviewing "autoanalyze on partitioned table" patch, I realized
that pg_stat_xxx_tables.n_mod_since_analyze is not reset at TRUNCATE.
On the other hand, n_ins_since_vacuum is reset. I think it should be
reset because otherwise we end up unnecessarily triggering autoanalyze
even when the actual number of newly inserted tuples is less than the
autoanalyze thresholds.
Steps to reproduce:
=# create table t (c int);
=# insert into t values (1), (2), (3);
=# update t set c = 999;
=# select relname, n_mod_since_analyze, n_ins_since_vacuum from
pg_stat_user_tables;
relname | n_mod_since_analyze | n_ins_since_vacuum
---------+---------------------+--------------------
t | 6 | 3
(1 row)
=# truncate t;
=# select relname, n_mod_since_analyze, n_ins_since_vacuum from
pg_stat_user_tables;
relname | n_mod_since_analyze | n_ins_since_vacuum
---------+---------------------+--------------------
t | 6 | 0
(1 row)
I've attached a small patch to fix this. Please review it.
Regards,
--
Masahiko Sawada
EDB: https://www.enterprisedb.com/
Attachments:
reset_n_mod_since_analyze.patchapplication/octet-stream; name=reset_n_mod_since_analyze.patchDownload
diff --git a/src/backend/postmaster/pgstat.c b/src/backend/postmaster/pgstat.c
index f75b52719d..d1825e0e46 100644
--- a/src/backend/postmaster/pgstat.c
+++ b/src/backend/postmaster/pgstat.c
@@ -6455,6 +6455,7 @@ pgstat_recv_tabstat(PgStat_MsgTabstat *msg, int len)
{
tabentry->n_live_tuples = 0;
tabentry->n_dead_tuples = 0;
+ tabentry->changes_since_analyze = 0;
tabentry->inserts_since_vacuum = 0;
}
tabentry->n_live_tuples += tabmsg->t_counts.t_delta_live_tuples;
On Thu, Mar 04, 2021 at 10:35:19AM +0900, Masahiko Sawada wrote:
While reviewing "autoanalyze on partitioned table" patch, I realized
that pg_stat_xxx_tables.n_mod_since_analyze is not reset at TRUNCATE.
On the other hand, n_ins_since_vacuum is reset. I think it should be
reset because otherwise we end up unnecessarily triggering autoanalyze
even when the actual number of newly inserted tuples is less than the
autoanalyze thresholds.
Agreed.
I've attached a small patch to fix this. Please review it.
The patch looks sensible to me, but the stats.sql (around l. 158) test should
be modified to also check for effect on that field.
On 2021/03/04 11:24, Julien Rouhaud wrote:
On Thu, Mar 04, 2021 at 10:35:19AM +0900, Masahiko Sawada wrote:
While reviewing "autoanalyze on partitioned table" patch, I realized
that pg_stat_xxx_tables.n_mod_since_analyze is not reset at TRUNCATE.
On the other hand, n_ins_since_vacuum is reset. I think it should be
reset because otherwise we end up unnecessarily triggering autoanalyze
even when the actual number of newly inserted tuples is less than the
autoanalyze thresholds.
In that case, conversely, we want to trigger autoanalyze ASAP because the contents in the table was changed very much?
Regards,
--
Fujii Masao
Advanced Computing Technology Center
Research and Development Headquarters
NTT DATA CORPORATION
On Thu, Mar 04, 2021 at 12:21:14PM +0900, Fujii Masao wrote:
On 2021/03/04 11:24, Julien Rouhaud wrote:
On Thu, Mar 04, 2021 at 10:35:19AM +0900, Masahiko Sawada wrote:
While reviewing "autoanalyze on partitioned table" patch, I realized
that pg_stat_xxx_tables.n_mod_since_analyze is not reset at TRUNCATE.
On the other hand, n_ins_since_vacuum is reset. I think it should be
reset because otherwise we end up unnecessarily triggering autoanalyze
even when the actual number of newly inserted tuples is less than the
autoanalyze thresholds.In that case, conversely, we want to trigger autoanalyze ASAP because the contents in the table was changed very much?
We might want, but wouldn't keeping the current n_mod_since_analyze would make
things unpredictable?
Also the selectivity estimation functions already take into account the actual
relation size, so the estimates aren't completely off after a truncate.
On Thu, Mar 4, 2021 at 11:23 AM Julien Rouhaud <rjuju123@gmail.com> wrote:
On Thu, Mar 04, 2021 at 10:35:19AM +0900, Masahiko Sawada wrote:
While reviewing "autoanalyze on partitioned table" patch, I realized
that pg_stat_xxx_tables.n_mod_since_analyze is not reset at TRUNCATE.
On the other hand, n_ins_since_vacuum is reset. I think it should be
reset because otherwise we end up unnecessarily triggering autoanalyze
even when the actual number of newly inserted tuples is less than the
autoanalyze thresholds.Agreed.
I've attached a small patch to fix this. Please review it.
The patch looks sensible to me, but the stats.sql (around l. 158) test should
be modified to also check for effect on that field.
Thank you for looking at the patch!
Agreed. I've attached the updated patch.
I was going to add tests for both n_mod_since_analyze and
n_ins_since_analyze but it seems to require somewhat change pgstat
code to check n_ins_since_vacuum. Since n_ins_since_vacuum internally
uses the counter used also for n_tup_ins whose value isn't reset at
TRUNCATE, the values isn’t reset in some cases, for example, where a
table stats message for a new table has a truncation information
(i.g., tabmsg->t_counts.t_truncated is true). For example, even if we
add a check of n_ins_since_vacuum in stats.sql (at L158), the value is
not reset by running stats.sql regression test. So in this patch, I
added a check just for n_mod_since_analyze.
Regards,
--
Masahiko Sawada
EDB: https://www.enterprisedb.com/
Attachments:
reset_n_mod_since_analyze_v2.patchapplication/octet-stream; name=reset_n_mod_since_analyze_v2.patchDownload
diff --git a/src/backend/postmaster/pgstat.c b/src/backend/postmaster/pgstat.c
index f75b52719d..eb17eddd05 100644
--- a/src/backend/postmaster/pgstat.c
+++ b/src/backend/postmaster/pgstat.c
@@ -2370,6 +2370,7 @@ AtEOXact_PgStat(bool isCommit, bool parallel)
/* forget live/dead stats seen by backend thus far */
tabstat->t_counts.t_delta_live_tuples = 0;
tabstat->t_counts.t_delta_dead_tuples = 0;
+ tabstat->t_counts.t_changed_tuples = 0;
}
/* insert adds a live tuple, delete removes one */
tabstat->t_counts.t_delta_live_tuples +=
@@ -2605,6 +2606,7 @@ pgstat_twophase_postcommit(TransactionId xid, uint16 info,
/* forget live/dead stats seen by backend thus far */
pgstat_info->t_counts.t_delta_live_tuples = 0;
pgstat_info->t_counts.t_delta_dead_tuples = 0;
+ pgstat_info->t_counts.t_changed_tuples = 0;
}
pgstat_info->t_counts.t_delta_live_tuples +=
rec->tuples_inserted - rec->tuples_deleted;
@@ -6455,6 +6457,7 @@ pgstat_recv_tabstat(PgStat_MsgTabstat *msg, int len)
{
tabentry->n_live_tuples = 0;
tabentry->n_dead_tuples = 0;
+ tabentry->changes_since_analyze = 0;
tabentry->inserts_since_vacuum = 0;
}
tabentry->n_live_tuples += tabmsg->t_counts.t_delta_live_tuples;
diff --git a/src/test/regress/expected/stats.out b/src/test/regress/expected/stats.out
index b01e58b98c..e8d96eabd5 100644
--- a/src/test/regress/expected/stats.out
+++ b/src/test/regress/expected/stats.out
@@ -77,7 +77,7 @@ begin
extract(epoch from clock_timestamp() - start_time);
end
$$ language plpgsql;
--- test effects of TRUNCATE on n_live_tup/n_dead_tup counters
+-- test effects of TRUNCATE on n_live_tup/n_dead_tup/n_mod_since_analyze counters
CREATE TABLE trunc_stats_test(id serial);
CREATE TABLE trunc_stats_test1(id serial, stuff text);
CREATE TABLE trunc_stats_test2(id serial);
@@ -160,16 +160,16 @@ SELECT wait_for_stats();
(1 row)
-- check effects
-SELECT relname, n_tup_ins, n_tup_upd, n_tup_del, n_live_tup, n_dead_tup
+SELECT relname, n_tup_ins, n_tup_upd, n_tup_del, n_live_tup, n_dead_tup, n_mod_since_analyze
FROM pg_stat_user_tables
WHERE relname like 'trunc_stats_test%' order by relname;
- relname | n_tup_ins | n_tup_upd | n_tup_del | n_live_tup | n_dead_tup
--------------------+-----------+-----------+-----------+------------+------------
- trunc_stats_test | 3 | 0 | 0 | 0 | 0
- trunc_stats_test1 | 4 | 2 | 1 | 1 | 0
- trunc_stats_test2 | 1 | 0 | 0 | 1 | 0
- trunc_stats_test3 | 4 | 0 | 0 | 2 | 2
- trunc_stats_test4 | 2 | 0 | 0 | 0 | 2
+ relname | n_tup_ins | n_tup_upd | n_tup_del | n_live_tup | n_dead_tup | n_mod_since_analyze
+-------------------+-----------+-----------+-----------+------------+------------+---------------------
+ trunc_stats_test | 3 | 0 | 0 | 0 | 0 | 0
+ trunc_stats_test1 | 4 | 2 | 1 | 1 | 0 | 1
+ trunc_stats_test2 | 1 | 0 | 0 | 1 | 0 | 1
+ trunc_stats_test3 | 4 | 0 | 0 | 2 | 2 | 2
+ trunc_stats_test4 | 2 | 0 | 0 | 0 | 2 | 0
(5 rows)
SELECT st.seq_scan >= pr.seq_scan + 1,
diff --git a/src/test/regress/sql/stats.sql b/src/test/regress/sql/stats.sql
index feaaee6326..70227a0a6c 100644
--- a/src/test/regress/sql/stats.sql
+++ b/src/test/regress/sql/stats.sql
@@ -77,7 +77,7 @@ begin
end
$$ language plpgsql;
--- test effects of TRUNCATE on n_live_tup/n_dead_tup counters
+-- test effects of TRUNCATE on n_live_tup/n_dead_tup/n_mod_since_analyze counters
CREATE TABLE trunc_stats_test(id serial);
CREATE TABLE trunc_stats_test1(id serial, stuff text);
CREATE TABLE trunc_stats_test2(id serial);
@@ -155,7 +155,7 @@ RESET enable_bitmapscan;
SELECT wait_for_stats();
-- check effects
-SELECT relname, n_tup_ins, n_tup_upd, n_tup_del, n_live_tup, n_dead_tup
+SELECT relname, n_tup_ins, n_tup_upd, n_tup_del, n_live_tup, n_dead_tup, n_mod_since_analyze
FROM pg_stat_user_tables
WHERE relname like 'trunc_stats_test%' order by relname;
On Thu, Mar 4, 2021 at 12:39 PM Julien Rouhaud <rjuju123@gmail.com> wrote:
On Thu, Mar 04, 2021 at 12:21:14PM +0900, Fujii Masao wrote:
On 2021/03/04 11:24, Julien Rouhaud wrote:
On Thu, Mar 04, 2021 at 10:35:19AM +0900, Masahiko Sawada wrote:
While reviewing "autoanalyze on partitioned table" patch, I realized
that pg_stat_xxx_tables.n_mod_since_analyze is not reset at TRUNCATE.
On the other hand, n_ins_since_vacuum is reset. I think it should be
reset because otherwise we end up unnecessarily triggering autoanalyze
even when the actual number of newly inserted tuples is less than the
autoanalyze thresholds.In that case, conversely, we want to trigger autoanalyze ASAP because the contents in the table was changed very much?
We might want, but wouldn't keeping the current n_mod_since_analyze would make
things unpredictable?
Right. I guess we should manually execute ANALYZE on the table to
refresh the statistics if we want to do that ASAP. It seems to me it
doesn't make any sense to trigger autoanalyze earlier than expected.
Regards,
--
Masahiko Sawada
EDB: https://www.enterprisedb.com/
On 2021/03/04 12:40, Julien Rouhaud wrote:
On Thu, Mar 04, 2021 at 12:21:14PM +0900, Fujii Masao wrote:
On 2021/03/04 11:24, Julien Rouhaud wrote:
On Thu, Mar 04, 2021 at 10:35:19AM +0900, Masahiko Sawada wrote:
While reviewing "autoanalyze on partitioned table" patch, I realized
that pg_stat_xxx_tables.n_mod_since_analyze is not reset at TRUNCATE.
On the other hand, n_ins_since_vacuum is reset. I think it should be
reset because otherwise we end up unnecessarily triggering autoanalyze
even when the actual number of newly inserted tuples is less than the
autoanalyze thresholds.In that case, conversely, we want to trigger autoanalyze ASAP because the contents in the table was changed very much?
We might want, but wouldn't keeping the current n_mod_since_analyze would make
things unpredictable?
I don't think so. autoanalyze still works based on the number of
modifications since last analyze, so ISTM that's predictable...
Also the selectivity estimation functions already take into account the actual
relation size, so the estimates aren't completely off after a truncate.
But the statistics is out of date and which may affect the planning badly?
Also if generic plan is used, it's not updated until next analyze, i.e.,
generic plan created based on old statistics is used for a while.
So I'm still not sure why you want to defer autoanalyze in that case.
Regards,
--
Fujii Masao
Advanced Computing Technology Center
Research and Development Headquarters
NTT DATA CORPORATION
On Fri, Mar 05, 2021 at 03:31:33PM +0900, Fujii Masao wrote:
On 2021/03/04 12:40, Julien Rouhaud wrote:
In that case, conversely, we want to trigger autoanalyze ASAP because the contents in the table was changed very much?
We might want, but wouldn't keeping the current n_mod_since_analyze would make
things unpredictable?I don't think so. autoanalyze still works based on the number of
modifications since last analyze, so ISTM that's predictable...
If we keep n_mod_since_analyze, autoanalyze might trigger after the first write
or might wait for a full cycle of writes, depending on the kept value. So yes
it can make autoanalyze triggers earlier in some cases, but that's not
predictable from the TRUNCATE even point of view.
Also the selectivity estimation functions already take into account the actual
relation size, so the estimates aren't completely off after a truncate.But the statistics is out of date and which may affect the planning badly?
Also if generic plan is used, it's not updated until next analyze, i.e.,
generic plan created based on old statistics is used for a while.So I'm still not sure why you want to defer autoanalyze in that case.
I don't especially want to defer autoanalyze in that case. But an autoanalyze
happening quickly after a TRUNCATE is critical for performance, I'd prefer to
find a way to trigger autoanalyze reliably.
On 2021/03/05 15:59, Julien Rouhaud wrote:
On Fri, Mar 05, 2021 at 03:31:33PM +0900, Fujii Masao wrote:
On 2021/03/04 12:40, Julien Rouhaud wrote:
In that case, conversely, we want to trigger autoanalyze ASAP because the contents in the table was changed very much?
We might want, but wouldn't keeping the current n_mod_since_analyze would make
things unpredictable?I don't think so. autoanalyze still works based on the number of
modifications since last analyze, so ISTM that's predictable...If we keep n_mod_since_analyze, autoanalyze might trigger after the first write
or might wait for a full cycle of writes, depending on the kept value. So yes
it can make autoanalyze triggers earlier in some cases, but that's not
predictable from the TRUNCATE even point of view.Also the selectivity estimation functions already take into account the actual
relation size, so the estimates aren't completely off after a truncate.But the statistics is out of date and which may affect the planning badly?
Also if generic plan is used, it's not updated until next analyze, i.e.,
generic plan created based on old statistics is used for a while.So I'm still not sure why you want to defer autoanalyze in that case.
I don't especially want to defer autoanalyze in that case. But an autoanalyze
happening quickly after a TRUNCATE is critical for performance, I'd prefer to
find a way to trigger autoanalyze reliably.
One just idea is to make TRUNCATE increase n_mod_since_analyze by
the number of records to truncate. That is, we treat TRUNCATE
in the same way as "DELETE without WHERE".
If the table has lots of records and is truncated, n_mod_since_analyze
will be increased very much and which would trigger autoanalyze soon.
This might be expected behavior because the statistics collected before
truncate is very "different" from the status of the table after truncate.
OTOH, if the table is very small, TRUNCATE doesn't increase
n_mod_since_analyze so much. So analyze might not be triggered soon.
But this might be ok because the statistics collected before truncate is
not so "different" from the status of the table after truncate.
I'm not sure how much this idea is "reliable" and would be helpful in
practice, though.
Regards,
--
Fujii Masao
Advanced Computing Technology Center
Research and Development Headquarters
NTT DATA CORPORATION
On Fri, Mar 05, 2021 at 06:07:05PM +0900, Fujii Masao wrote:
On 2021/03/05 15:59, Julien Rouhaud wrote:
I don't especially want to defer autoanalyze in that case. But an autoanalyze
happening quickly after a TRUNCATE is critical for performance, I'd prefer to
find a way to trigger autoanalyze reliably.One just idea is to make TRUNCATE increase n_mod_since_analyze by
the number of records to truncate. That is, we treat TRUNCATE
in the same way as "DELETE without WHERE".
Yes, that's the approach I had in mind to make it more reliable.
If the table has lots of records and is truncated, n_mod_since_analyze
will be increased very much and which would trigger autoanalyze soon.
This might be expected behavior because the statistics collected before
truncate is very "different" from the status of the table after truncate.OTOH, if the table is very small, TRUNCATE doesn't increase
n_mod_since_analyze so much. So analyze might not be triggered soon.
But this might be ok because the statistics collected before truncate is
not so "different" from the status of the table after truncate.I'm not sure how much this idea is "reliable" and would be helpful in
practice, though.
It seems like a better approach as it it would have the same results on
autovacuum as a DELETE, so +1 from me.
On Fri, Mar 5, 2021 at 6:51 PM Julien Rouhaud <rjuju123@gmail.com> wrote:
On Fri, Mar 05, 2021 at 06:07:05PM +0900, Fujii Masao wrote:
On 2021/03/05 15:59, Julien Rouhaud wrote:
I don't especially want to defer autoanalyze in that case. But an autoanalyze
happening quickly after a TRUNCATE is critical for performance, I'd prefer to
find a way to trigger autoanalyze reliably.One just idea is to make TRUNCATE increase n_mod_since_analyze by
the number of records to truncate. That is, we treat TRUNCATE
in the same way as "DELETE without WHERE".
Makes sense. I had been thinking we can treat TRUNCATE as like "DROP
TABLE and CREATE TABLE" in terms of the statistics but it's rather
"DELETE without WHERE" as you mentioned.
If the table has lots of records and is truncated, n_mod_since_analyze
will be increased very much and which would trigger autoanalyze soon.
This might be expected behavior because the statistics collected before
truncate is very "different" from the status of the table after truncate.OTOH, if the table is very small, TRUNCATE doesn't increase
n_mod_since_analyze so much. So analyze might not be triggered soon.
But this might be ok because the statistics collected before truncate is
not so "different" from the status of the table after truncate.I'm not sure how much this idea is "reliable" and would be helpful in
practice, though.It seems like a better approach as it it would have the same results on
autovacuum as a DELETE, so +1 from me.
I think we can use n_live_tup for that but since it's an estimation
value it doesn't necessarily have the same result as DELETE and I'm
not sure it's reliable.
Regards,
--
Masahiko Sawada
EDB: https://www.enterprisedb.com/
On Fri, Mar 05, 2021 at 10:43:51PM +0900, Masahiko Sawada wrote:
I think we can use n_live_tup for that but since it's an estimation
value it doesn't necessarily have the same result as DELETE and I'm
not sure it's reliable.
I agree that it's not 100% reliable, but in my experience those estimates are
quite good and of the same order of magnitude, which should be enough for this
use case. It will be in any case better that simply keeping the old value, and
I doubt that we can do better anyway.
On Fri, Mar 5, 2021 at 10:43 PM Masahiko Sawada <sawada.mshk@gmail.com> wrote:
On Fri, Mar 5, 2021 at 6:51 PM Julien Rouhaud <rjuju123@gmail.com> wrote:
On Fri, Mar 05, 2021 at 06:07:05PM +0900, Fujii Masao wrote:
On 2021/03/05 15:59, Julien Rouhaud wrote:
I don't especially want to defer autoanalyze in that case. But an autoanalyze
happening quickly after a TRUNCATE is critical for performance, I'd prefer to
find a way to trigger autoanalyze reliably.One just idea is to make TRUNCATE increase n_mod_since_analyze by
the number of records to truncate. That is, we treat TRUNCATE
in the same way as "DELETE without WHERE".Makes sense. I had been thinking we can treat TRUNCATE as like "DROP
TABLE and CREATE TABLE" in terms of the statistics but it's rather
"DELETE without WHERE" as you mentioned.
Hmm I'm a bit confused. Executing TRANCATE against the table set
pg_class.reltuples to -1, meaning it's never yet vacuumed and the
planner applies 10 pages. Also, it seems to clear plan caches
involving the table being truncated. The table statistics in
pg_statistic and pg_statistic_ext might be out of date but that would
not affect the plan badly since we assume the table has 10 pages. That
behavior makes me think that TRUNCATE is something like "DROP and
CREATE table" in terms of statistics. I'm concerned that if we hasten
autoanalyze after TRUNCATE, we could trigger autoanalyze soon and the
statistics could be out of date at the time when we insert rows enough
to exceed autoanalyze threshold. I might be missing something though.
Regards,
--
Masahiko Sawada
EDB: https://www.enterprisedb.com/
On Mon, Mar 08, 2021 at 10:49:20AM +0900, Masahiko Sawada wrote:
On Fri, Mar 5, 2021 at 10:43 PM Masahiko Sawada <sawada.mshk@gmail.com> wrote:
On Fri, Mar 5, 2021 at 6:51 PM Julien Rouhaud <rjuju123@gmail.com> wrote:
On Fri, Mar 05, 2021 at 06:07:05PM +0900, Fujii Masao wrote:
On 2021/03/05 15:59, Julien Rouhaud wrote:
I don't especially want to defer autoanalyze in that case. But an autoanalyze
happening quickly after a TRUNCATE is critical for performance, I'd prefer to
find a way to trigger autoanalyze reliably.One just idea is to make TRUNCATE increase n_mod_since_analyze by
the number of records to truncate. That is, we treat TRUNCATE
in the same way as "DELETE without WHERE".Makes sense. I had been thinking we can treat TRUNCATE as like "DROP
TABLE and CREATE TABLE" in terms of the statistics but it's rather
"DELETE without WHERE" as you mentioned.Hmm I'm a bit confused. Executing TRANCATE against the table set
pg_class.reltuples to -1, meaning it's never yet vacuumed and the
planner applies 10 pages. Also, it seems to clear plan caches
involving the table being truncated. The table statistics in
pg_statistic and pg_statistic_ext might be out of date but that would
not affect the plan badly since we assume the table has 10 pages. That
behavior makes me think that TRUNCATE is something like "DROP and
CREATE table" in terms of statistics. I'm concerned that if we hasten
autoanalyze after TRUNCATE, we could trigger autoanalyze soon and the
statistics could be out of date at the time when we insert rows enough
to exceed autoanalyze threshold. I might be missing something though.
Ah, I mentioned previously that the planner would already come up with sensible
estimates as it takes into account the relation size, but if truncates actually
sets pg_class.reltuples to -1 then indeed it's kind of behaving as a DROP/CREATE
for the size estimate.
But the previous stats will be kept so estimates will still be done according
to what used to be in that table, but there's no guarantee that further writes
on that table will have the same pattern as before.
Maybe we should also delete the related pg_statistic entries, reset
n_mod_since_analyze and let autoanalyze behave as it would do after a
DROP/CREATE?