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)
