Date: Thu, 12 Nov 2020 15:11:43 -0600
From: Justin Pryzby <pryzby@telsasoft.com>
To: Bruce Momjian <bruce@momjian.us>
Cc: Nikolay Samokhvalov <samokhvalov@gmail.com>, fabriziomello@gmail.com,
	pgsql-hackers@lists.postgresql.org
Subject: Re: Add important info about ANALYZE after create Functional Index
Message-ID: <20201112211143.GL30691@telsasoft.com>
References: 
 <CAFcNs+qpFPmiHd1oTXvcPdvAHicJDA9qBUSujgAhUMJyUMb+SA@mail.gmail.com>
 <CANNMO++5rw9RDA=p40iMVbMNPaW6O=S0AFzTU=KpYHRpCd1voA@mail.gmail.com>
 <20201109232720.GA8930@momjian.us>
MIME-Version: 1.0
Content-Type: text/plain; charset=iso-8859-1
Content-Disposition: inline
Content-Transfer-Encoding: 8bit
In-Reply-To: <20201109232720.GA8930@momjian.us>
User-Agent: Mutt/1.9.4 (2018-02-28)
Precedence: bulk

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:

--- 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