Subject: autoanalyze did not run

From: Олег Самойлов <[email-masked]>
Date: Mon, 26 Jan 2026 08:31:24 +0300

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)

---

From: Laurenz Albe <[email-masked]>
Date: Mon, 26 Jan 2026 08:23:19 +0100

On Mon, 2026-01-26 at 08:31 +0300, Олег Самойлов wrote:
> 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.

> 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

---

From: hubert depesz lubaczewski <[email-masked]>
Date: Mon, 26 Jan 2026 13:14:36 +0100

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

---

From: Олег Самойлов <[email-masked]>
Date: Mon, 26 Jan 2026 17:35:42 +0300

> 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.
> 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.

---

From: Олег Самойлов <[email-masked]>
Date: Mon, 26 Jan 2026 18:43:19 +0300

> https://www.depesz.com/2020/02/18/which-tables-should-be-auto-vacuumed-or-auto-analyzed-update/

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)"

---

From: Laurenz Albe <[email-masked]>
Date: Mon, 26 Jan 2026 17:03:21 +0100

On Mon, 2026-01-26 at 17:35 +0300, Олег Самойлов wrote:
> I am very sure, thats why there are 13844347 rows in that table. By single copy.
> > 

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

---

From: Олег Самойлов <[email-masked]>
Date: Tue, 27 Jan 2026 14:39:44 +0300

----------------

Кому: Олег Самойлов ([email-masked]), Pgsql-admin ([email-masked]);

Тема: autoanalyze did not run;

26.01.2026, 19:03, "Laurenz Albe" <[email-masked]>:

I checked, yep.

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.

---

From: Kristjan Mustkivi <[email-masked]>
Date: Tue, 27 Jan 2026 14:10:07 +0200

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-masked]

---

From: Laurenz Albe <[email-masked]>
Date: Tue, 27 Jan 2026 18:09:12 +0100

> COPY inserts rows, it does not update them.

Rows added by COPY must definitely count for "n_mod_since_analyze".

Yours,
Laurenz Albe

---

From: Laurenz Albe <[email-masked]>
Date: Tue, 27 Jan 2026 18:13:01 +0100

> SELECT stats_reset is null FROM pg_stat_database WHERE datname = current_database();

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

---

From: Олег Самойлов <[email-masked]>
Date: Wed, 28 Jan 2026 06:06:38 +0300

> statistics and reset "pg_stat_database.stats_reset" to NULL.

Thanks, I will recheck.