On Mon, Oct 26, 2020 at 7:46 PM David G. Johnston < david.g.johns...@gmail.com> wrote: > > It would seem preferable to call the lack of auto-analyzing after these operations a bug and back-patch a fix that injects an analyze side-effect just before their completion. It doesn't have to be smart either, analyzing things even if the created (or newly validated) index doesn't have statistics of its own isn't a problem in my book. >
When we create a new table or index they will not have statistics until an ANALYZE happens. This is the default behaviour and I think is not a big problem here, but we need to add some note on docs about the need of statistics for indexes on expressions. But IMHO there is a misbehaviour with the implementation of CONCURRENTLY on REINDEX because running it will lose the statistics. Have a look the example below: fabrizio=# SELECT version(); version --------------------------------------------------------------------------------------------------------- PostgreSQL 14devel on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 9.3.0-17ubuntu1~20.04) 9.3.0, 64-bit (1 row) fabrizio=# CREATE TABLE t(f1 BIGSERIAL PRIMARY KEY, f2 TEXT) WITH (autovacuum_enabled = false); CREATE TABLE fabrizio=# INSERT INTO t(f2) SELECT repeat(chr(65+(random()*26)::int), (random()*300)::int) FROM generate_series(1, 10000); INSERT 0 10000 fabrizio=# CREATE INDEX t_idx2 ON t(lower(f2)); CREATE INDEX fabrizio=# SELECT count(*) FROM pg_statistic WHERE starelid = 't_pkey'::regclass; count ------- 0 (1 row) fabrizio=# SELECT count(*) FROM pg_statistic WHERE starelid = 't_idx2'::regclass; count ------- 0 (1 row) fabrizio=# ANALYZE t; ANALYZE fabrizio=# SELECT count(*) FROM pg_statistic WHERE starelid = 't_pkey'::regclass; count ------- 0 (1 row) fabrizio=# SELECT count(*) FROM pg_statistic WHERE starelid = 't_idx2'::regclass; count ------- 1 (1 row) fabrizio=# REINDEX INDEX t_idx2; REINDEX fabrizio=# REINDEX INDEX t_pkey; REINDEX fabrizio=# SELECT count(*) FROM pg_statistic WHERE starelid = 't_pkey'::regclass; count ------- 0 (1 row) fabrizio=# SELECT count(*) FROM pg_statistic WHERE starelid = 't_idx2'::regclass; count ------- 1 (1 row) ^^^^^^^^ -- A regular REINDEX don't lose the statistics. fabrizio=# REINDEX INDEX CONCURRENTLY t_idx2; REINDEX fabrizio=# SELECT count(*) FROM pg_statistic WHERE starelid = 't_idx2'::regclass; count ------- 0 (1 row) ^^^^^^^^ -- But the REINDEX CONCURRENTLY loses. So IMHO here is the place we should rework a bit to execute ANALYZE as a last step. Regards, -- Fabrízio de Royes Mello PostgreSQL Developer at OnGres Inc. - https://ongres.com