Re: Add important info about ANALYZE after create Functional Index

On Mon, Oct 26, 2020 at 3:08 PM Fabrízio de Royes Mello <
fabriziomello(at)gmail(dot)com> wrote:

> Hi all,
>
> As you all already know Postgres supports functions in index expressions
> (marked as immutable ofc) and for this special index the ANALYZE command
> creates some statistics (new pg_statistic entry) about it.
>
> The problem is just after creating a new index or rebuilding concurrently
> (using the new REINDEX .. CONCURRENTLY or the old manner creating new one
> and then swapping) we need to run ANALYZE to update statistics but we don't
> mention it in any part of our documentation.
>
> Last weekend Gitlab went down because the lack of an ANALYZE after
> rebuilding concurrently a functional index and they followed the
> recommendation we have into our documentation [1] about how to rebuild it
> concurrently, but we don't warn users about the ANALYZE after.
>
> Would be nice if add some information about it into our docs but not sure
> where. I'm thinking about:
> - doc/src/sgml/ref/create_index.sgml
> - doc/src/sgml/maintenance.sgml (routine-reindex)
>
> Thoughts?
>
> [1]
> https://gitlab.com/gitlab-com/gl-infra/production/-/issues/2885#note_436310499
>

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.

David J.