public inbox for [email protected]
help / color / mirror / Atom feedautoanalyze did not run
11+ messages / 4 participants
[nested] [flat]
* autoanalyze did not run
@ 2026-01-26 05:31 Олег Самойлов <[email protected]>
0 siblings, 2 replies; 11+ messages in thread
From: Олег Самойлов @ 2026-01-26 05:31 UTC (permalink / raw)
To: Pgsql-admin <[email protected]>
Hi, all.
I try to investigate why autoanalyze did not run in time of our initial
loading data. Yes, I know, running analyze manually is highly
recommended in such case. But is must run automatically too. Or it us
bugged?
Initial loading data made by COPY command. After this (and after all
autovacuum) I see:
select c.relkind, count(*) from pg_stat_user_tables as s join pg_class
as c on (s.relid=c.oid) where s.autoanalyze_count=0 and s.n_live_tup>0
group by c.relkind;
-[ RECORD 1 ]-
relkind | r
count | 1069
More investigation:
select relid::regclass, n_live_tup, n_tup_ins, n_tup_upd,
n_mod_since_analyze from pg_stat_user_tables as s join pg_class as c on
(s.relid=c.oid) where s.autoanalyze_count=0 and relkind='r' and
n_live_tup>0 limit 1;
-[ RECORD 1 ]-------+----------
relid | addresses
n_live_tup | 13844405
n_tup_ins | 0
n_tup_upd | 0
n_mod_since_analyze | 0
select count(*) from addresses;
-[ RECORD 1 ]---
count | 13844347
This is example for only 1 table, but there are one thousand such. What
is exact criteria to launch autoanalyze? On columns of
pg_stat_user_tables it is based? If only on n_mod_since_analyze, there
can be a logical trap (bug), analyze will have
always n_mod_since_analyze=0 on tables where it never yet ran, so it
will not run. Or may be there is an other reason?
I am afraid that this bug can be not only on initial loading, but in
normal work too.
PostgreSQL 17.7 (Debian 17.7-3.pgdg13+1) on x86_64-pc-linux-gnu,
compiled by gcc (Debian 14.2.0-19) 14.2.0, 64-bit (from PGDG)
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: autoanalyze did not run
@ 2026-01-26 07:23 Laurenz Albe <[email protected]>
parent: Олег Самойлов <[email protected]>
1 sibling, 1 reply; 11+ messages in thread
From: Laurenz Albe @ 2026-01-26 07:23 UTC (permalink / raw)
To: Олег Самойлов <[email protected]>; Pgsql-admin <[email protected]>
On Mon, 2026-01-26 at 08:31 +0300, Олег Самойлов wrote:
> I try to investigate why autoanalyze did not run in time of our initial
> loading data. Yes, I know, running analyze manually is highly
> recommended in such case. But is must run automatically too.
You'd have to show more evidence that this is a bug, or provide a way
to reproduce the problem.
> select relid::regclass, n_live_tup, n_tup_ins, n_tup_upd,
> n_mod_since_analyze from pg_stat_user_tables as s join pg_class as c on
> (s.relid=c.oid) where s.autoanalyze_count=0 and relkind='r' and
> n_live_tup>0 limit 1;
> -[ RECORD 1 ]-------+----------
> relid | addresses
> n_live_tup | 13844405
> n_tup_ins | 0
> n_tup_upd | 0
> n_mod_since_analyze | 0
>
> PostgreSQL 17.7 (Debian 17.7-3.pgdg13+1) on x86_64-pc-linux-gnu,
> compiled by gcc (Debian 14.2.0-19) 14.2.0, 64-bit (from PGDG)
Are you sure that you committed the transaction?
Is "track_activities" set to "on"?
Yours,
Laurenz Albe
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: autoanalyze did not run
@ 2026-01-26 12:14 hubert depesz lubaczewski <[email protected]>
parent: Олег Самойлов <[email protected]>
1 sibling, 1 reply; 11+ messages in thread
From: hubert depesz lubaczewski @ 2026-01-26 12:14 UTC (permalink / raw)
To: Олег Самойлов <[email protected]>; +Cc: Pgsql-admin <[email protected]>
On Mon, Jan 26, 2026 at 08:31:24AM +0300, Олег Самойлов wrote:
> What is exact criteria to launch autoanalyze?
You might want to read
https://www.depesz.com/2020/02/18/which-tables-should-be-auto-vacuumed-or-auto-analyzed-update/
Best regards,
depesz
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: autoanalyze did not run
@ 2026-01-26 14:35 Олег Самойлов <[email protected]>
parent: Laurenz Albe <[email protected]>
0 siblings, 1 reply; 11+ messages in thread
From: Олег Самойлов @ 2026-01-26 14:35 UTC (permalink / raw)
To: Laurenz Albe <[email protected]>; Pgsql-admin <[email protected]>
26.01.2026 10:23, Laurenz Albe пишет:
> On Mon, 2026-01-26 at 08:31 +0300, Олег Самойлов wrote:
>> I try to investigate why autoanalyze did not run in time of our initial
>> loading data. Yes, I know, running analyze manually is highly
>> recommended in such case. But is must run automatically too.
> You'd have to show more evidence that this is a bug, or provide a way
> to reproduce the problem.
I need to narrow my case, that why I need to know on what exactly
depends (on what column) the launching the autoexplain.
>> select relid::regclass, n_live_tup, n_tup_ins, n_tup_upd,
>> n_mod_since_analyze from pg_stat_user_tables as s join pg_class as c on
>> (s.relid=c.oid) where s.autoanalyze_count=0 and relkind='r' and
>> n_live_tup>0 limit 1;
>> -[ RECORD 1 ]-------+----------
>> relid | addresses
>> n_live_tup | 13844405
>> n_tup_ins | 0
>> n_tup_upd | 0
>> n_mod_since_analyze | 0
>>
>> PostgreSQL 17.7 (Debian 17.7-3.pgdg13+1) on x86_64-pc-linux-gnu,
>> compiled by gcc (Debian 14.2.0-19) 14.2.0, 64-bit (from PGDG)
> Are you sure that you committed the transaction?
I am very sure, thats why there are 13844347 rows in that table. By
single copy.
> Is "track_activities" set to "on"?
I checked, yep.
> Yours,
> Laurenz Albe
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: autoanalyze did not run
@ 2026-01-26 15:43 Олег Самойлов <[email protected]>
parent: hubert depesz lubaczewski <[email protected]>
0 siblings, 0 replies; 11+ messages in thread
From: Олег Самойлов @ 2026-01-26 15:43 UTC (permalink / raw)
To: [email protected]; +Cc: Pgsql-admin <[email protected]>
26.01.2026 15:14, hubert depesz lubaczewski пишет:
> On Mon, Jan 26, 2026 at 08:31:24AM +0300, Олег Самойлов wrote:
>> What is exact criteria to launch autoanalyze?
> You might want to read
> https://www.depesz.com/2020/02/18/which-tables-should-be-auto-vacuumed-or-auto-analyzed-update/
>
> Best regards,
>
> depesz
Yep, thanks. I rechecked in source. Indeed, autoanalyze depend only on
mod_since_analyze and anlthresh = (float4) anl_base_thresh +
anl_scale_factor * reltuples;
https://github.com/postgres/postgres/blob/master/src/backend/postmaster/autovacuum.c#L2901
relation_needs_vacanalyze(Oid relid,
AutoVacOpts *relopts,
Form_pg_class classForm,
PgStat_StatTabEntry *tabentry,
int effective_multixact_freeze_max_age,
/* output params below */
bool *dovacuum,
bool *doanalyze,
bool *wraparound)
And wrote such variables into
elog(DEBUG3, "%s: vac: %.0f (threshold %.0f), ins: (disabled), anl: %.0f
(threshold %.0f)"
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: autoanalyze did not run
@ 2026-01-26 16:03 Laurenz Albe <[email protected]>
parent: Олег Самойлов <[email protected]>
0 siblings, 1 reply; 11+ messages in thread
From: Laurenz Albe @ 2026-01-26 16:03 UTC (permalink / raw)
To: Олег Самойлов <[email protected]>; Pgsql-admin <[email protected]>
On Mon, 2026-01-26 at 17:35 +0300, Олег Самойлов wrote:
> > > I try to investigate why autoanalyze did not run in time of our initial
> > > loading data. Yes, I know, running analyze manually is highly
> > > recommended in such case. But is must run automatically too.
> > >
> > > PostgreSQL 17.7 (Debian 17.7-3.pgdg13+1) on x86_64-pc-linux-gnu,
> > > compiled by gcc (Debian 14.2.0-19) 14.2.0, 64-bit (from PGDG)
> >
> > Are you sure that you committed the transaction?
>
> I am very sure, thats why there are 13844347 rows in that table. By single copy.
> >
> > Is "track_activities" set to "on"?
>
> I checked, yep.
Then the only explanation is that all autovacuum workers are active, and this
table is starved. See how many autovacuum workers are visible in pg_stat_activity
and compare that to autovacuum_max_workers.
Yours,
Laurenz Albe
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: autoanalyze did not run
@ 2026-01-27 11:39 Олег Самойлов <[email protected]>
parent: Laurenz Albe <[email protected]>
0 siblings, 2 replies; 11+ messages in thread
From: Олег Самойлов @ 2026-01-27 11:39 UTC (permalink / raw)
To: Laurenz Albe <[email protected]>; Pgsql-admin <[email protected]>
<div> </div><div> </div><div>----------------</div><div>Кому: Олег Самойлов ([email protected]), Pgsql-admin ([email protected]);</div><div>Тема: autoanalyze did not run;</div><div>26.01.2026, 19:03, "Laurenz Albe" <[email protected]>:</div><blockquote><p>On Mon, 2026-01-26 at 17:35 +0300, Олег Самойлов wrote:</p><blockquote> > > I try to investigate why autoanalyze did not run in time of our initial<br /> > > loading data. Yes, I know, running analyze manually is highly<br /> > > recommended in such case. But is must run automatically too.<br /> > ><br /> > > PostgreSQL 17.7 (Debian 17.7-3.pgdg13+1) on x86_64-pc-linux-gnu,<br /> > > compiled by gcc (Debian 14.2.0-19) 14.2.0, 64-bit (from PGDG)<br /> ><br /> > Are you sure that you committed the transaction?<br /> <br /> I am very sure, thats why there are 13844347 rows in that table. By single copy.<br /> ><br /> > Is "track_activities" set to "on"?<br /> <br /> I checked, yep.</blockquote><p><br />Then the only explanation is that all autovacuum workers are active, and this<br />table is starved. See how many autovacuum workers are visible in pg_stat_activity<br />and compare that to autovacuum_max_workers.<br /><br />Yours,<br />Laurenz Albe</p></blockquote><div>Now 0. But they worked. The reason is other. Look, first of all, there was not a statistic reset.</div><div><div><span style="font-family:'courier new' , monospace">SELECT stats_reset is null FROM pg_stat_database WHERE datname = current_database();</span></div><div><span style="font-family:'courier new' , monospace"> ?column?\c </span></div><div><span style="font-family:'courier new' , monospace">----------</span></div><div><span style="font-family:'courier new' , monospace"> t</span></div><div><span style="font-family:'courier new' , monospace">(1 row)</span><div><div><span style="font-family:'courier new' , monospace"> select analyze_count,autoanalyze_count,reltuples, n_live_tup, n_tup_ins, n_tup_upd, n_mod_since_analyze from pg_stat_user_tables as s join pg_class as c on (s.relid=c.oid) where relkind='r' and reltuples>0 limit 10;</span></div><div><span style="font-family:'courier new' , monospace"> analyze_count | autoanalyze_count | reltuples | n_live_tup | n_tup_ins | n_tup_upd | n_mod_since_analyze</span></div><div><span style="font-family:'courier new' , monospace">---------------+-------------------+---------------+------------+-----------+-----------+---------------------</span></div><div><span style="font-family:'courier new' , monospace"> 0 | 0 | 8 | 0 | 0 | 0 | 0</span></div><div><span style="font-family:'courier new' , monospace"> 0 | 0 | 76596 | 0 | 0 | 0 | 0</span></div><div><span style="font-family:'courier new' , monospace"> 0 | 0 | 140997 | 0 | 0 | 0 | 0</span></div><div><span style="font-family:'courier new' , monospace"> 0 | 0 | 2.088436e+07 | 0 | 0 | 0 | 0</span></div><div><span style="font-family:'courier new' , monospace"> 0 | 0 | 1.2661012e+07 | 0 | 0 | 0 | 0</span></div><div><span style="font-family:'courier new' , monospace"> 0 | 0 | 2.288401e+07 | 0 | 0 | 0 | 0</span></div><div><span style="font-family:'courier new' , monospace"> 0 | 0 | 99926 | 0 | 0 | 0 | 0</span></div><div><span style="font-family:'courier new' , monospace"> 0 | 0 | 1.5620866e+07 | 0 | 0 | 0 | 0</span></div><div><span style="font-family:'courier new' , monospace"> 0 | 0 | 17511 | 0 | 0 | 0 | 0</span></div><div><span style="font-family:'courier new' , monospace"> 0 | 0 | 400926 | 0 | 0 | 0 | 0</span></div>The reason is the <span style="background-color:transparent;font-family:'courier new' , monospace">n_mod_since_analyze is 0. Thats why autoanalyze didn't started.</span></div></div></div>
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: autoanalyze did not run
@ 2026-01-27 12:10 Kristjan Mustkivi <[email protected]>
parent: Олег Самойлов <[email protected]>
1 sibling, 1 reply; 11+ messages in thread
From: Kristjan Mustkivi @ 2026-01-27 12:10 UTC (permalink / raw)
To: Pgsql-admin <[email protected]>
On Tue, Jan 27, 2026 at 1:40 PM Олег Самойлов <[email protected]> wrote:
> ----------------
> Кому: Олег Самойлов ([email protected]), Pgsql-admin ([email protected]);
> Тема: autoanalyze did not run;
> 26.01.2026, 19:03, "Laurenz Albe" <[email protected]>:
>
> On Mon, 2026-01-26 at 17:35 +0300, Олег Самойлов wrote:
>
> > > I try to investigate why autoanalyze did not run in time of our initial
> > > loading data. Yes, I know, running analyze manually is highly
> > > recommended in such case. But is must run automatically too.
> > >
> > > PostgreSQL 17.7 (Debian 17.7-3.pgdg13+1) on x86_64-pc-linux-gnu,
> > > compiled by gcc (Debian 14.2.0-19) 14.2.0, 64-bit (from PGDG)
> >
> > Are you sure that you committed the transaction?
>
> I am very sure, thats why there are 13844347 rows in that table. By single copy.
> >
> > Is "track_activities" set to "on"?
>
> I checked, yep.
>
>
> Then the only explanation is that all autovacuum workers are active, and this
> table is starved. See how many autovacuum workers are visible in pg_stat_activity
> and compare that to autovacuum_max_workers.
>
> Yours,
> Laurenz Albe
>
> Now 0. But they worked. The reason is other. Look, first of all, there was not a statistic reset.
> SELECT stats_reset is null FROM pg_stat_database WHERE datname = current_database();
> ?column?\c
> ----------
> t
> (1 row)
> select analyze_count,autoanalyze_count,reltuples, n_live_tup, n_tup_ins, n_tup_upd, n_mod_since_analyze from pg_stat_user_tables as s join pg_class as c on (s.relid=c.oid) where relkind='r' and reltuples>0 limit 10;
> analyze_count | autoanalyze_count | reltuples | n_live_tup | n_tup_ins | n_tup_upd | n_mod_since_analyze
> ---------------+-------------------+---------------+------------+-----------+-----------+---------------------
> 0 | 0 | 8 | 0 | 0 | 0 | 0
> 0 | 0 | 76596 | 0 | 0 | 0 | 0
> 0 | 0 | 140997 | 0 | 0 | 0 | 0
> 0 | 0 | 2.088436e+07 | 0 | 0 | 0 | 0
> 0 | 0 | 1.2661012e+07 | 0 | 0 | 0 | 0
> 0 | 0 | 2.288401e+07 | 0 | 0 | 0 | 0
> 0 | 0 | 99926 | 0 | 0 | 0 | 0
> 0 | 0 | 1.5620866e+07 | 0 | 0 | 0 | 0
> 0 | 0 | 17511 | 0 | 0 | 0 | 0
> 0 | 0 | 400926 | 0 | 0 | 0 | 0
> The reason is the n_mod_since_analyze is 0. Thats why autoanalyze didn't started.
Hello,
I do apologize if I mix something up now, but I would expect COPY to
have an effect on "n_ins_since_vacuum" but not "n_mod_since_analyze".
COPY inserts rows, it does not update them.
With best regards,
--
Kristjan Mustkivi
Email: [email protected]
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: autoanalyze did not run
@ 2026-01-27 17:09 Laurenz Albe <[email protected]>
parent: Kristjan Mustkivi <[email protected]>
0 siblings, 0 replies; 11+ messages in thread
From: Laurenz Albe @ 2026-01-27 17:09 UTC (permalink / raw)
To: Kristjan Mustkivi <[email protected]>; Pgsql-admin <[email protected]>
On Tue, 2026-01-27 at 14:10 +0200, Kristjan Mustkivi wrote:
> I do apologize if I mix something up now, but I would expect COPY to
> have an effect on "n_ins_since_vacuum" but not "n_mod_since_analyze".
> COPY inserts rows, it does not update them.
Rows added by COPY must definitely count for "n_mod_since_analyze".
Yours,
Laurenz Albe
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: autoanalyze did not run
@ 2026-01-27 17:13 Laurenz Albe <[email protected]>
parent: Олег Самойлов <[email protected]>
1 sibling, 1 reply; 11+ messages in thread
From: Laurenz Albe @ 2026-01-27 17:13 UTC (permalink / raw)
To: Олег Самойлов <[email protected]>; Pgsql-admin <[email protected]>
On Tue, 2026-01-27 at 14:39 +0300, Олег Самойлов wrote:
> > > > > I try to investigate why autoanalyze did not run in time of our initial
> > > > > loading data. Yes, I know, running analyze manually is highly
> > > > > recommended in such case. But is must run automatically too.
> > > > >
> > > > > PostgreSQL 17.7 (Debian 17.7-3.pgdg13+1) on x86_64-pc-linux-gnu,
> > > > > compiled by gcc (Debian 14.2.0-19) 14.2.0, 64-bit (from PGDG)
> > > >
> > > > Are you sure that you committed the transaction?
> > >
> > > I am very sure, thats why there are 13844347 rows in that table. By single copy.
> > >
> > > > Is "track_activities" set to "on"?
> > >
> > > I checked, yep.
> >
> > Then the only explanation is that all autovacuum workers are active, and this
> > table is starved. See how many autovacuum workers are visible in pg_stat_activity
> > and compare that to autovacuum_max_workers.
>
> Now 0. But they worked. The reason is other. Look, first of all, there was not a statistic reset.
> SELECT stats_reset is null FROM pg_stat_database WHERE datname = current_database();
> ?column?\c
> ----------
> t
> (1 row)
> select analyze_count,autoanalyze_count,reltuples, n_live_tup, n_tup_ins, n_tup_upd, n_mod_since_analyze from pg_stat_user_tables as s join pg_class as c on (s.relid=c.oid) where relkind='r' and reltuples>0 limit 10;
> analyze_count | autoanalyze_count | reltuples | n_live_tup | n_tup_ins | n_tup_upd | n_mod_since_analyze
> ---------------+-------------------+---------------+------------+-----------+-----------+---------------------
> 0 | 0 | 8 | 0 | 0 | 0 | 0
> 0 | 0 | 76596 | 0 | 0 | 0 | 0
> 0 | 0 | 140997 | 0 | 0 | 0 | 0
> 0 | 0 | 2.088436e+07 | 0 | 0 | 0 | 0
> 0 | 0 | 1.2661012e+07 | 0 | 0 | 0 | 0
> 0 | 0 | 2.288401e+07 | 0 | 0 | 0 | 0
> 0 | 0 | 99926 | 0 | 0 | 0 | 0
> 0 | 0 | 1.5620866e+07 | 0 | 0 | 0 | 0
> 0 | 0 | 17511 | 0 | 0 | 0 | 0
> 0 | 0 | 400926 | 0 | 0 | 0 | 0
> The reason is the n_mod_since_analyze is 0. Thats why autoanalyze didn't started.
Ok. As Sherlock Holmes said, if you have excluded everything impossible, what remains
must be what happened, however unlikely it seems.
I conclude that your database cluster must have crashed. Crash recovery will wipe out all
statistics and reset "pg_stat_database.stats_reset" to NULL.
Yours,
Laurenz Albe
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: autoanalyze did not run
@ 2026-01-28 03:06 Олег Самойлов <[email protected]>
parent: Laurenz Albe <[email protected]>
0 siblings, 0 replies; 11+ messages in thread
From: Олег Самойлов @ 2026-01-28 03:06 UTC (permalink / raw)
To: Laurenz Albe <[email protected]>; Pgsql-admin <[email protected]>
> I conclude that your database cluster must have crashed. Crash recovery will wipe out all
> statistics and reset "pg_stat_database.stats_reset" to NULL.
>
> Yours,
> Laurenz Albe
Thanks, I will recheck.
^ permalink raw reply [nested|flat] 11+ messages in thread
end of thread, other threads:[~2026-01-28 03:06 UTC | newest]
Thread overview: 11+ messages (download: mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2026-01-26 05:31 autoanalyze did not run Олег Самойлов <[email protected]>
2026-01-26 07:23 ` Laurenz Albe <[email protected]>
2026-01-26 14:35 ` Олег Самойлов <[email protected]>
2026-01-26 16:03 ` Laurenz Albe <[email protected]>
2026-01-27 11:39 ` Олег Самойлов <[email protected]>
2026-01-27 12:10 ` Kristjan Mustkivi <[email protected]>
2026-01-27 17:09 ` Laurenz Albe <[email protected]>
2026-01-27 17:13 ` Laurenz Albe <[email protected]>
2026-01-28 03:06 ` Олег Самойлов <[email protected]>
2026-01-26 12:14 ` hubert depesz lubaczewski <[email protected]>
2026-01-26 15:43 ` Олег Самойлов <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox