From: Bruce Momjian <bruce@momjian.us>
To: Laurenz Albe <laurenz.albe@cybertec.at>
Cc: Nikolay Samokhvalov <samokhvalov@gmail.com>
Cc: PostgreSQL-documentation <pgsql-docs@postgresql.org>
Subject: Re: Indexes on Expressions - a note to remind users to run ANALYZE after creation
Date: Wed, 28 Jul 2021 21:03:08 -0400
Message-ID: <20210729010308.GB9600@momjian.us> (raw)
In-Reply-To: <77bbd451633a2852181fc6259baf9796361ce2a1.camel@cybertec.at>
References: <CANNMO+K179bhRrzsJZn-tS8evH4uRSonCUHoMQdRCSvzmKyo1Q@mail.gmail.com>
<77bbd451633a2852181fc6259baf9796361ce2a1.camel@cybertec.at>
On Mon, Jul 19, 2021 at 12:59:10PM +0200, Laurenz Albe wrote:
> On Fri, 2021-07-16 at 22:00 -0700, Nikolay Samokhvalov wrote:
> > diff --git a/doc/src/sgml/indices.sgml b/doc/src/sgml/indices.sgml
> > index 671299ff059d972ff95bdb1d67ed4c89bf5040b2..c7eaf9a608e995ef9957b4e0f677b36a8303be55 100644
> > --- a/doc/src/sgml/indices.sgml
> > +++ b/doc/src/sgml/indices.sgml
> > @@ -741,6 +741,15 @@ CREATE INDEX people_names ON people ((first_name || ' ' || last_name));
> > query. Thus, indexes on expressions are useful when retrieval speed
> > is more important than insertion and update speed.
> > </para>
> > +
> > + <note>
> > + <title>Note</title>
> > + <para>
> > + Once an index on an expression is successfully created, it is important to
> > + run <literal>ANALYZE</literal> on the corresponding table to gather
> > + statistics for the expression.
> > + </para>
> > + </note>
> > </sect1>
>
> I agree in principle. It should be "has been created" rather than "is created",
> and I would say something less drastic like "usually a good idea" rather than
> "important".
Uh, CREATE INDEX already has this mentioend:
The system regularly 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 for these indexes.
Do we need it here too?
--
Bruce Momjian <bruce@momjian.us> https://momjian.us
EDB https://enterprisedb.com
If only the physical world exists, free will is an illusion.
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-docs@postgresql.org
Cc: bruce@momjian.us, laurenz.albe@cybertec.at, samokhvalov@gmail.com
Subject: Re: Indexes on Expressions - a note to remind users to run ANALYZE after creation
In-Reply-To: <20210729010308.GB9600@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