Re: Add important info about ANALYZE after create Functional Index - Mailing list pgsql-hackers
| From | Fabrízio de Royes Mello |
|---|---|
| Subject | Re: Add important info about ANALYZE after create Functional Index |
| Date | |
| Msg-id | CAFcNs+qk4pb8+mWKRQoNJ=gvF95vt=hns6KZDakd_6pGGGZ6=w@mail.gmail.com Whole thread Raw |
| In response to | Re: Add important info about ANALYZE after create Functional Index ("David G. Johnston" <david.g.johnston@gmail.com>) |
| Responses |
Re: Add important info about ANALYZE after create Functional Index
Re: Add important info about ANALYZE after create Functional Index |
| List | pgsql-hackers |
On Mon, Oct 26, 2020 at 7:46 PM David G. Johnston <david.g.johnston@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)
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,
--
pgsql-hackers by date: