Re: Add important info about ANALYZE after create Functional Index

On Wed, Oct 28, 2020 at 2:15 AM Michael Paquier <michael(at)paquier(dot)xyz> wrote:
>
> On Tue, Oct 27, 2020 at 11:06:22AM -0300, Fabrízio de Royes Mello wrote:
> > 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:
> >
> > [...]
> >
> > So IMHO here is the place we should rework a bit to execute ANALYZE as a
> > last step.
>
> I agree that this is not user-friendly, and I suspect that we will
> need to do something within index_concurrently_swap() to fill in the
> stats of the new index from the data of the old one (not looked at
> that in details yet).
>

We already do a similar thing for PgStats [1] so maybe we should also copy
pg_statistics from old to new index during the swap.

But I'm not sure if it's totally safe anyway and would be better to create
a new phase to issue ANALYZE if necessary (exists statistics for old index).

Regards,

[1]
https://git.postgresql.org/gitweb/?p=postgresql.git;a=blob;f=src/backend/catalog/index.c;h=0974f3e23a23726b63246cd3a1347e10923dd541;hb=HEAD#l1693

--
Fabrízio de Royes Mello
PostgreSQL Developer at OnGres Inc. - https://ongres.com