Indexes on Expressions - a note to remind users to run ANALYZE after creation
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}
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
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.