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.
From 7e846e6864b48be51bb0afee455f853debe10cb9 Mon Sep 17 00:00:00 2001
From: Nikolay Samokhvalov <samokhvalov@gmail.com>
Date: Tue, 27 Oct 2020 06:26:50 +0000
Subject: [PATCH 1/2] Rebuilding indexes on expressions requires ANALYZE
It is critically important to run ANALYZE before dropping the old index.
This is only relevant to Postgres version up to 11 because which do not have
REINDEX CONCURRENTLY.
---
doc/src/sgml/maintenance.sgml | 4 +++-
1 file changed, 3 insertions(+), 1 deletion(-)
diff --git a/doc/src/sgml/maintenance.sgml b/doc/src/sgml/maintenance.sgml
index 3b649575e9..c430207deb 100644
--- a/doc/src/sgml/maintenance.sgml
+++ b/doc/src/sgml/maintenance.sgml
@@ -895,7 +895,9 @@ analyze threshold = analyze base threshold + analyze scale factor * number of tu
option can instead be recreated that way. If that is successful and the
resulting index is valid, the original index can then be replaced by
the newly built one using a combination of <xref linkend="sql-alterindex"/>
- and <xref linkend="sql-dropindex"/>. When an index is used to enforce
+ and <xref linkend="sql-dropindex"/>. In the case of rebuilding an index on
+ an expression, it is important to run <literal>ANALYZE</literal> on the
+ table before dropping the original one. When an index is used to enforce
uniqueness or other constraints, <xref linkend="sql-altertable"/> might
be necessary to swap the existing constraint with one enforced by
the new index. Review this alternate multistep rebuild approach
--
GitLab
From 58fef5bca6c38e5c6d6ff23896c5f7130631b244 Mon Sep 17 00:00:00 2001
From: Nikolay Samokhvalov <gitlab@samokhvalov.com>
Date: Tue, 27 Oct 2020 00:03:34 -0700
Subject: [PATCH 2/2] Building indexes on expressions requires ANALYZE
It is critically important to run ANALYZE after an index on an
expression is created.
- add a not to "Indexes on Expressions"
- mention that ANALYZE is needed when performing index maintenance
for indexes on expressions (this makes sense only Postgres versions
11 and older, since newer versions have support of REINDEX CONCURRENTLY
that doesn't suffer from lack of ANALYZE)
---
doc/src/sgml/indices.sgml | 9 +++++++++
1 file changed, 9 insertions(+)
diff --git a/doc/src/sgml/indices.sgml b/doc/src/sgml/indices.sgml
index 210a9e0adf..41adbbd4ad 100644
--- a/doc/src/sgml/indices.sgml
+++ b/doc/src/sgml/indices.sgml
@@ -734,6 +734,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 successfuly created, it is important to
+ run <literal>ANALYZE</literal> on the corresponding table to gather
+ statistics for the expression.
+ </para>
+ </note>
</sect1>
From c6ec56913d933f328f4e54ac1ab7123a02d4bccf Mon Sep 17 00:00:00 2001
From: Nikolay Samokhvalov <samokhvalov@gmail.com>
Date: Tue, 27 Oct 2020 07:01:34 +0000
Subject: [PATCH] Building indexes on expressions requires ANALYZE
It is critically important to run ANALYZE after an index on an
expression is created.
---
doc/src/sgml/indices.sgml | 9 +++++++++
1 file changed, 9 insertions(+)
diff --git a/doc/src/sgml/indices.sgml b/doc/src/sgml/indices.sgml
index 671299ff05..bb5d7dfdd5 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 successfuly created, it is important to
+ run <literal>ANALYZE</literal> on the corresponding table to gather
+ statistics for the expression.
+ </para>
+ </note>
</sect1>
