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 > > <fabriziome...@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