[Justin Pryzby <pryzby@telsasoft.com>]

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@gmail.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:

inline patch 1: doc/src/sgml/ref/create_index.sgml (+10/-0 lines) download inline-1.patch--- 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