From: Bruce Momjian <bruce@momjian.us>
To: Nikolay Samokhvalov <samokhvalov@gmail.com>
Cc: fabriziomello@gmail.com, PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
Subject: Re: Add important info about ANALYZE after create Functional Index
Date: Mon, 9 Nov 2020 18:27:20 -0500
Message-ID: <20201109232720.GA8930@momjian.us> (raw)
In-Reply-To: <CANNMO++5rw9RDA=p40iMVbMNPaW6O=S0AFzTU=KpYHRpCd1voA@mail.gmail.com>
References: <CAFcNs+qpFPmiHd1oTXvcPdvAHicJDA9qBUSujgAhUMJyUMb+SA@mail.gmail.com>
<CANNMO++5rw9RDA=p40iMVbMNPaW6O=S0AFzTU=KpYHRpCd1voA@mail.gmail.com>
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.
--
Bruce Momjian <bruce@momjian.us> https://momjian.us
EnterpriseDB https://enterprisedb.com
The usefulness of a cup is in its emptiness, Bruce Lee
Attachments:expindex.diff (text/x-diff, 996B) downloaddiff --git a/doc/src/sgml/ref/create_index.sgml b/doc/src/sgml/ref/create_index.sgmlnew file mode 100644index 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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-hackers@postgresql.org
Cc: bruce@momjian.us, samokhvalov@gmail.com, pgsql-hackers@lists.postgresql.org
Subject: Re: Add important info about ANALYZE after create Functional Index
In-Reply-To: <20201109232720.GA8930@momjian.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox