pg.ddx.io pgsql-docs@postgresql.org mailing list archive
help / color / mirror / Atom feedIndexes on Expressions - a note to remind users to run ANALYZE after creation
3+ messages / 3 participants
[nested] [flat]
* Indexes on Expressions - a note to remind users to run ANALYZE after creation
@ 2021-07-17 05:00 Nikolay Samokhvalov <samokhvalov@gmail.com>
0 siblings, 1 reply; 3+ messages in thread
From: Nikolay Samokhvalov @ 2021-07-17 05:00 UTC (permalink / raw)
To: pgsql-docs
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}
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Indexes on Expressions - a note to remind users to run ANALYZE after creation
@ 2021-07-19 10:59 Laurenz Albe <laurenz.albe@cybertec.at>
parent: Nikolay Samokhvalov <samokhvalov@gmail.com>
0 siblings, 1 reply; 3+ messages in thread
From: Laurenz Albe @ 2021-07-19 10:59 UTC (permalink / raw)
To: Nikolay Samokhvalov <samokhvalov@gmail.com>; pgsql-docs
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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Indexes on Expressions - a note to remind users to run ANALYZE after creation
@ 2021-07-29 01:03 Bruce Momjian <bruce@momjian.us>
parent: Laurenz Albe <laurenz.albe@cybertec.at>
0 siblings, 0 replies; 3+ messages in thread
From: Bruce Momjian @ 2021-07-29 01:03 UTC (permalink / raw)
To: Laurenz Albe <laurenz.albe@cybertec.at>; +Cc: Nikolay Samokhvalov <samokhvalov@gmail.com>; pgsql-docs
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.
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2021-07-29 01:03 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2021-07-17 05:00 Indexes on Expressions - a note to remind users to run ANALYZE after creation Nikolay Samokhvalov <samokhvalov@gmail.com>
2021-07-19 10:59 ` Laurenz Albe <laurenz.albe@cybertec.at>
2021-07-29 01:03 ` Bruce Momjian <bruce@momjian.us>
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