Re: Add important info about ANALYZE after create Functional Index

On Mon, Nov 09, 2020 at 06:27:20PM -0500, Bruce Momjian wrote:
> On Tue, Oct 27, 2020 at 12:12:00AM -0700, Nikolay Samokhvalov wrote:
> > On Mon, Oct 26, 2020 at 3:08 PM Fabrízio de Royes Mello <fabriziomello(at)gmail(dot)com> wrote:
> >
> > 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)
> >
> >
> > Attaching the patches for the docs, one for 11 and older, and another for 12+
> > (which have REINDEX CONCURRENTLY not suffering from lack of ANALYZE).
> >
> > I still think that automating is the right thing to do but of course, it's a
> > much bigger topic that a quick fix dor the docs.
>
> I see REINDEX CONCURRENTLY was fixed in head, but the docs didn't get
> updated to mention the need to run ANALYZE or wait for autovacuum before
> expression indexes can be fully used by the optimizer. Instead of
> putting this mention in the maintenance section, I thought the CREATE
> INDEX page make more sense, since it is more of a usability issue,
> rather than "why use expression indexes". Patch attached, which I plan
> to apply to all supported branches.

The commited patch actually says:

--- a/doc/src/sgml/ref/create_index.sgml
+++ b/doc/src/sgml/ref/create_index.sgml
@@ -745,6 +745,16 @@ Indexes:
sort high</quote>, in queries that depend on indexes to avoid sorting steps.
</para>

+ <para>
+ The regularly system collects statistics on all of a table's
+ columns. Newly-created non-expression indexes can immediately
+ use these statistics to determine an index's usefulness.
+ For new expression indexes, it is necessary to run <link
+ linkend="sql-analyze"><command>ANALYZE</command></link> or wait for
+ the <link linkend="autovacuum">autovacuum daemon</link> to analyze
+ the table to generate statistics about new expression indexes.
+ </para>
+

I guess it should say "The system regularly ..."

Also, the last sentence begins "For new expression indexes" and ends with
"about new expression indexes", which I guess could instead say "about the
expressions".

> diff --git a/doc/src/sgml/ref/create_index.sgml b/doc/src/sgml/ref/create_index.sgml
> new file mode 100644
> index 749db28..48c42db
> *** a/doc/src/sgml/ref/create_index.sgml
> --- b/doc/src/sgml/ref/create_index.sgml
> *************** Indexes:
> *** 746,751 ****
> --- 746,761 ----
> </para>
>
> <para>
> + The system collects statistics on all of a table's columns.
> + Newly-created non-expression indexes can immediately
> + use these statistics to determine an index's usefulness.
> + For new expression indexes, it is necessary to run <link
> + linkend="sql-analyze"><command>ANALYZE</command></link> or wait for
> + the <link linkend="autovacuum">autovacuum daemon</link> to analyze
> + the table to generate statistics about new expression indexes.
> + </para>
> +
> + <para>
> For most index methods, the speed of creating an index is
> dependent on the setting of <xref linkend="guc-maintenance-work-mem"/>.
> Larger values will reduce the time needed for index creation, so long

--
Justin Pryzby
System Administrator
Telsasoft
+1-952-707-8581