Обсуждение: Indexes on Expressions - a note to remind users to run ANALYZE after creation
Indexes on Expressions - a note to remind users to run ANALYZE after creation
От
Nikolay Samokhvalov
Дата:
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>
{"mode":"full","isActive":false}
Re: Indexes on Expressions - a note to remind users to run ANALYZE after creation
От
Laurenz Albe
Дата:
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". Yours, Laurenz Albe
Re: Indexes on Expressions - a note to remind users to run ANALYZE after creation
От
Bruce Momjian
Дата:
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.