pg.ddx.io pgsql-docs@postgresql.org mailing list archive
help / color / mirror / Atom feedvacuumdb --analyze-in-stages
8+ messages / 4 participants
[nested] [flat]
* vacuumdb --analyze-in-stages
@ 2021-10-08 09:33 nikolai.berkoff <nikolai.berkoff@pm.me>
2021-10-18 18:12 ` Re: vacuumdb --analyze-in-stages Euler Taveira <euler@eulerto.com>
0 siblings, 1 reply; 8+ messages in thread
From: nikolai.berkoff @ 2021-10-08 09:33 UTC (permalink / raw)
To: pgsql-docs@lists.postgresql.org <pgsql-docs@lists.postgresql.org>
Running --analyze-in-stages on a database with statistics causes the statistics to become significantly worse before they are improved. This could be made clearer in the documentation.
Attachments:
[text/x-patch] analyze_in_stages.patch (807B, ../../s-kSljtWXMWgMfGTztPTPcS80R8FHdOrBxDTnrQI6GMZbT7au1A4b0fzaSFtKwCI8nwN0MhgPLfVOTvJ7DwTjkip4P3d0o4VgrMJs4OLN-o=@pm.me/2-analyze_in_stages.patch)
download | inline diff:
diff --git i/doc/src/sgml/ref/vacuumdb.sgml w/doc/src/sgml/ref/vacuumdb.sgml
index 223b986..d76c18e 100644
--- i/doc/src/sgml/ref/vacuumdb.sgml
+++ w/doc/src/sgml/ref/vacuumdb.sgml
@@ -403,9 +403,10 @@ PostgreSQL documentation
</para>
<para>
- This option is useful to analyze a database that was newly populated
+ This option is useful to analyze a database that current has no,
+ or bad, statistics e.g. it was newly populated
from a restored dump or by <command>pg_upgrade</command>. This option
- will try to create some statistics as fast as possible, to make the
+ will create coarse statistics as fast as possible, to make the
database usable, and then produce full statistics in the subsequent
stages.
</para>
[application/pgp-signature] signature.asc (507B, ../../s-kSljtWXMWgMfGTztPTPcS80R8FHdOrBxDTnrQI6GMZbT7au1A4b0fzaSFtKwCI8nwN0MhgPLfVOTvJ7DwTjkip4P3d0o4VgrMJs4OLN-o=@pm.me/3-signature.asc)
download
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: vacuumdb --analyze-in-stages
2021-10-08 09:33 vacuumdb --analyze-in-stages nikolai.berkoff <nikolai.berkoff@pm.me>
@ 2021-10-18 18:12 ` Euler Taveira <euler@eulerto.com>
2021-10-18 23:02 ` Re: vacuumdb --analyze-in-stages Alvaro Herrera <alvherre@alvh.no-ip.org>
0 siblings, 1 reply; 8+ messages in thread
From: Euler Taveira @ 2021-10-18 18:12 UTC (permalink / raw)
To: nikolai.berkoff <nikolai.berkoff@pm.me>; pgsql-docs@lists.postgresql.org <pgsql-docs@lists.postgresql.org>
On Fri, Oct 8, 2021, at 6:33 AM, nikolai.berkoff wrote:
> Running --analyze-in-stages on a database with statistics causes the statistics to become significantly worse before they are improved. This could be made clearer in the documentation.
I think the paragraph you modified *already* stated that usefulness of
--analyze-in-stages (newly populated from a restored dump or by pg_upgrade). I
don't think your change is an improvement for --analyze-in-stages description.
If you read the previous paragraph, it explains _how_ (3 stages of ANALYZE with
different settings) and _why_ (produce usable statistics faster).
--
Euler Taveira
EDB https://www.enterprisedb.com/
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: vacuumdb --analyze-in-stages
2021-10-08 09:33 vacuumdb --analyze-in-stages nikolai.berkoff <nikolai.berkoff@pm.me>
2021-10-18 18:12 ` Re: vacuumdb --analyze-in-stages Euler Taveira <euler@eulerto.com>
@ 2021-10-18 23:02 ` Alvaro Herrera <alvherre@alvh.no-ip.org>
2021-10-18 23:34 ` Re: vacuumdb --analyze-in-stages David G. Johnston <david.g.johnston@gmail.com>
0 siblings, 1 reply; 8+ messages in thread
From: Alvaro Herrera @ 2021-10-18 23:02 UTC (permalink / raw)
To: Euler Taveira <euler@eulerto.com>; +Cc: nikolai.berkoff <nikolai.berkoff@pm.me>; pgsql-docs@lists.postgresql.org <pgsql-docs@lists.postgresql.org>
On 2021-Oct-18, Euler Taveira wrote:
> On Fri, Oct 8, 2021, at 6:33 AM, nikolai.berkoff wrote:
> > Running --analyze-in-stages on a database with statistics causes the
> > statistics to become significantly worse before they are improved.
> > This could be made clearer in the documentation.
>
> I think the paragraph you modified *already* stated that usefulness of
> --analyze-in-stages (newly populated from a restored dump or by pg_upgrade). I
> don't think your change is an improvement for --analyze-in-stages description.
> If you read the previous paragraph, it explains _how_ (3 stages of ANALYZE with
> different settings) and _why_ (produce usable statistics faster).
Yeah, but it doesn't say that the initial stats target (1) is the worst
value you could possibly have.
I wonder if it isn't better to *remove* the second phrase from this
paragraph, and leave just this text:
This option is useful to analyze a database that was newly populated
from a restored dump or by <command>pg_upgrade</command>.
to avoid giving the impression that it'll improve things if you run it
in a database with existing statistics. Or we could proactively warn
more explicitly about the problem:
This option is useful to analyze a database that was newly populated
from a restored dump or by <command>pg_upgrade</command>.
Beware that running with this option in a database with existing
statistics may cause query optimizer choices to become
transiently worse, because of the very low statistics target
that is used in the early stages.
Or maybe just
This option is useful to analyze a database that was newly populated
from a restored dump or by <command>pg_upgrade</command>.
Running with this option in a database with existing statistics
is not recommended.
Given that the first stage uses statistic target=1, running this option
in a database with any stats at all is probably a bad idea.
--
Álvaro Herrera 39°49'30"S 73°17'W — https://www.EnterpriseDB.com/
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: vacuumdb --analyze-in-stages
2021-10-08 09:33 vacuumdb --analyze-in-stages nikolai.berkoff <nikolai.berkoff@pm.me>
2021-10-18 18:12 ` Re: vacuumdb --analyze-in-stages Euler Taveira <euler@eulerto.com>
2021-10-18 23:02 ` Re: vacuumdb --analyze-in-stages Alvaro Herrera <alvherre@alvh.no-ip.org>
@ 2021-10-18 23:34 ` David G. Johnston <david.g.johnston@gmail.com>
2021-10-18 23:53 ` Re: vacuumdb --analyze-in-stages Alvaro Herrera <alvherre@alvh.no-ip.org>
0 siblings, 1 reply; 8+ messages in thread
From: David G. Johnston @ 2021-10-18 23:34 UTC (permalink / raw)
To: Alvaro Herrera <alvherre@alvh.no-ip.org>; +Cc: Euler Taveira <euler@eulerto.com>; nikolai.berkoff <nikolai.berkoff@pm.me>; pgsql-docs@lists.postgresql.org <pgsql-docs@lists.postgresql.org>
On Mon, Oct 18, 2021 at 4:02 PM Alvaro Herrera <alvherre@alvh.no-ip.org>
wrote:
> On 2021-Oct-18, Euler Taveira wrote:
>
> > On Fri, Oct 8, 2021, at 6:33 AM, nikolai.berkoff wrote:
> > > Running --analyze-in-stages on a database with statistics causes the
> > > statistics to become significantly worse before they are improved.
> > > This could be made clearer in the documentation.
> >
> > I think the paragraph you modified *already* stated that usefulness of
> > --analyze-in-stages (newly populated from a restored dump or by
> pg_upgrade). I
> > don't think your change is an improvement for --analyze-in-stages
> description.
> > If you read the previous paragraph, it explains _how_ (3 stages of
> ANALYZE with
> > different settings) and _why_ (produce usable statistics faster).
>
> Yeah, but it doesn't say that the initial stats target (1) is the worst
> value you could possibly have.
> [...]
> Given that the first stage uses statistic target=1, running this option
> in a database with any stats at all is probably a bad idea.
>
>
Add the word "only"?
This option is only useful to analyze a database...
Beyond that maybe adjust the procedure description to include a comment
that we don't actually skip tables that already have a higher statistics
target than the current pass would apply. (can we do this?)
"Run several (currently three) stages of analyze with different
configuration settings, to produce usable statistics faster. The first of
these stages will remove any existing statistics even if they use a larger
statistic target configuration."
David J.
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: vacuumdb --analyze-in-stages
2021-10-08 09:33 vacuumdb --analyze-in-stages nikolai.berkoff <nikolai.berkoff@pm.me>
2021-10-18 18:12 ` Re: vacuumdb --analyze-in-stages Euler Taveira <euler@eulerto.com>
2021-10-18 23:02 ` Re: vacuumdb --analyze-in-stages Alvaro Herrera <alvherre@alvh.no-ip.org>
2021-10-18 23:34 ` Re: vacuumdb --analyze-in-stages David G. Johnston <david.g.johnston@gmail.com>
@ 2021-10-18 23:53 ` Alvaro Herrera <alvherre@alvh.no-ip.org>
2021-10-19 00:02 ` Re: vacuumdb --analyze-in-stages Euler Taveira <euler@eulerto.com>
0 siblings, 1 reply; 8+ messages in thread
From: Alvaro Herrera @ 2021-10-18 23:53 UTC (permalink / raw)
To: David G. Johnston <david.g.johnston@gmail.com>; +Cc: Euler Taveira <euler@eulerto.com>; nikolai.berkoff <nikolai.berkoff@pm.me>; pgsql-docs@lists.postgresql.org <pgsql-docs@lists.postgresql.org>
On 2021-Oct-18, David G. Johnston wrote:
> On Mon, Oct 18, 2021 at 4:02 PM Alvaro Herrera <alvherre@alvh.no-ip.org>
> wrote:
> > Given that the first stage uses statistic target=1, running this option
> > in a database with any stats at all is probably a bad idea.
>
> Add the word "only"?
>
> This option is only useful to analyze a database...
Maybe this is sufficient, since it would drive people away from trying
to do anything else than help upgrades with it.
> Beyond that maybe adjust the procedure description to include a comment
> that we don't actually skip tables that already have a higher statistics
> target than the current pass would apply. (can we do this?)
Hmm, vacuumdb can certainly query the catalogs to see what we have and
skip tables for which we have more than that number, perhaps with a
query based on this number
select tablename,
max(coalesce(cardinality(histogram_bounds), cardinality(most_common_freqs))) - 1
from pg_stats
group by tablename;
(and of course *don't* skip tables in the final stage, since the stored
stats could be obsolete.)
> "Run several (currently three) stages of analyze with different
> configuration settings, to produce usable statistics faster. The first of
> these stages will remove any existing statistics even if they use a larger
> statistic target configuration."
.. yeah, this is another option.
--
Álvaro Herrera PostgreSQL Developer — https://www.EnterpriseDB.com/
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: vacuumdb --analyze-in-stages
2021-10-08 09:33 vacuumdb --analyze-in-stages nikolai.berkoff <nikolai.berkoff@pm.me>
2021-10-18 18:12 ` Re: vacuumdb --analyze-in-stages Euler Taveira <euler@eulerto.com>
2021-10-18 23:02 ` Re: vacuumdb --analyze-in-stages Alvaro Herrera <alvherre@alvh.no-ip.org>
2021-10-18 23:34 ` Re: vacuumdb --analyze-in-stages David G. Johnston <david.g.johnston@gmail.com>
2021-10-18 23:53 ` Re: vacuumdb --analyze-in-stages Alvaro Herrera <alvherre@alvh.no-ip.org>
@ 2021-10-19 00:02 ` Euler Taveira <euler@eulerto.com>
2021-10-26 14:18 ` Re: vacuumdb --analyze-in-stages nikolai.berkoff <nikolai.berkoff@pm.me>
0 siblings, 1 reply; 8+ messages in thread
From: Euler Taveira @ 2021-10-19 00:02 UTC (permalink / raw)
To: Alvaro Herrera <alvherre@alvh.no-ip.org>; David G. Johnston <david.g.johnston@gmail.com>; +Cc: nikolai.berkoff <nikolai.berkoff@pm.me>; pgsql-docs@lists.postgresql.org <pgsql-docs@lists.postgresql.org>
On Mon, Oct 18, 2021, at 8:53 PM, Alvaro Herrera wrote:
> On 2021-Oct-18, David G. Johnston wrote:
>
> > On Mon, Oct 18, 2021 at 4:02 PM Alvaro Herrera <alvherre@alvh.no-ip.org>
> > wrote:
>
> > > Given that the first stage uses statistic target=1, running this option
> > > in a database with any stats at all is probably a bad idea.
> >
> > Add the word "only"?
> >
> > This option is only useful to analyze a database...
>
> Maybe this is sufficient, since it would drive people away from trying
> to do anything else than help upgrades with it.
+1. I like your 2nd suggestion.
"This option is only useful to analyze a database that was newly populated from
a restored dump or by <command>pg_upgrade</command>. Beware that running with
this option in a database with existing statistics may cause query optimizer
choices to become transiently worse, because of the very low statistics target
that is used in the early stages."
>
> > "Run several (currently three) stages of analyze with different
> > configuration settings, to produce usable statistics faster. The first of
> > these stages will remove any existing statistics even if they use a larger
> > statistic target configuration."
>
> .. yeah, this is another option.
We might include it too but I would suggest "replace" instead of "remove"
because it seems there won't be statistics after the first stage.
--
Euler Taveira
EDB https://www.enterprisedb.com/
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: vacuumdb --analyze-in-stages
2021-10-08 09:33 vacuumdb --analyze-in-stages nikolai.berkoff <nikolai.berkoff@pm.me>
2021-10-18 18:12 ` Re: vacuumdb --analyze-in-stages Euler Taveira <euler@eulerto.com>
2021-10-18 23:02 ` Re: vacuumdb --analyze-in-stages Alvaro Herrera <alvherre@alvh.no-ip.org>
2021-10-18 23:34 ` Re: vacuumdb --analyze-in-stages David G. Johnston <david.g.johnston@gmail.com>
2021-10-18 23:53 ` Re: vacuumdb --analyze-in-stages Alvaro Herrera <alvherre@alvh.no-ip.org>
2021-10-19 00:02 ` Re: vacuumdb --analyze-in-stages Euler Taveira <euler@eulerto.com>
@ 2021-10-26 14:18 ` nikolai.berkoff <nikolai.berkoff@pm.me>
2021-11-05 14:25 ` Re: vacuumdb --analyze-in-stages Alvaro Herrera <alvherre@alvh.no-ip.org>
0 siblings, 1 reply; 8+ messages in thread
From: nikolai.berkoff @ 2021-10-26 14:18 UTC (permalink / raw)
To: Euler Taveira <euler@eulerto.com>; +Cc: Alvaro Herrera <alvherre@alvh.no-ip.org>; David G. Johnston <david.g.johnston@gmail.com>; pgsql-docs@lists.postgresql.org <pgsql-docs@lists.postgresql.org>
‐‐‐‐‐‐‐ Original Message ‐‐‐‐‐‐‐
On Tuesday, October 19th, 2021 at 01:02, Euler Taveira <euler@eulerto.com> wrote:
> On Mon, Oct 18, 2021, at 8:53 PM, Alvaro Herrera wrote:
>
> > On 2021-Oct-18, David G. Johnston wrote:
> >
> > > On Mon, Oct 18, 2021 at 4:02 PM Alvaro Herrera <alvherre@alvh.no-ip.org>
> > > wrote:
> >
> > > > Given that the first stage uses statistic target=1, running this option
> > > > in a database with any stats at all is probably a bad idea.
> > >
> > > Add the word "only"?
> > >
> > > This option is only useful to analyze a database...
> >
> > Maybe this is sufficient, since it would drive people away from trying
> > to do anything else than help upgrades with it.
>
> +1. I like your 2nd suggestion.
>
> "This option is only useful to analyze a database that was newly populated from
> a restored dump or by <command>pg_upgrade</command>. Beware that running with
> this option in a database with existing statistics may cause query optimizer
> choices to become transiently worse, because of the very low statistics target
> that is used in the early stages."
>
> > > "Run several (currently three) stages of analyze with different
> > > configuration settings, to produce usable statistics faster. The first of
> > > these stages will remove any existing statistics even if they use a larger
> > > statistic target configuration."
> >
> > .. yeah, this is another option.
>
> We might include it too but I would suggest "replace" instead of "remove"
> because it seems there won't be statistics after the first stage.
Given all the suggestions I've tried to combine them into one patch again.
Regards,
Nikolai
Attachments:
[text/x-patch] analyze_in_stages.patch (1.6K, ../../czz9wn0n6dToi_HrmuLZm2fnjXX6m4QzagoBWILNd-0gGZVNn5aOnlDCXsuxQw1RQXs1tyR2PS_ux3axoJkcxH4OY8Srcp9Fe4jHmqCzRHM=@pm.me/2-analyze_in_stages.patch)
download | inline diff:
diff --git i/doc/src/sgml/ref/vacuumdb.sgml w/doc/src/sgml/ref/vacuumdb.sgml
index 223b986..05a288a 100644
--- i/doc/src/sgml/ref/vacuumdb.sgml
+++ w/doc/src/sgml/ref/vacuumdb.sgml
@@ -397,17 +397,19 @@ PostgreSQL documentation
<listitem>
<para>
Only calculate statistics for use by the optimizer (no vacuum),
- like <option>--analyze-only</option>. Run several (currently three)
- stages of analyze with different configuration settings, to produce
- usable statistics faster.
+ like <option>--analyze-only</option>. Run three stages of
+ analyze. The first stage uses a low statistics target
+ (see <xref linkend="guc-default-statistics-target"/>)
+ to produce statistics quickly, and subsequent stages build the
+ full statistics. Be aware running this option in a database
+ with statistics may cause the query optimizer choices to become transiently
+ worse due to the very low statistics targets of the early stages.
</para>
<para>
- This option is useful to analyze a database that was newly populated
- from a restored dump or by <command>pg_upgrade</command>. This option
- will try to create some statistics as fast as possible, to make the
- database usable, and then produce full statistics in the subsequent
- stages.
+ This option is only useful to analyze a database that current has no,
+ or wholly incorrect, statistics e.g. it was newly populated
+ from a restored dump or by <command>pg_upgrade</command>.
</para>
</listitem>
</varlistentry>
[application/pgp-signature] signature.asc (507B, ../../czz9wn0n6dToi_HrmuLZm2fnjXX6m4QzagoBWILNd-0gGZVNn5aOnlDCXsuxQw1RQXs1tyR2PS_ux3axoJkcxH4OY8Srcp9Fe4jHmqCzRHM=@pm.me/3-signature.asc)
download
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: vacuumdb --analyze-in-stages
2021-10-08 09:33 vacuumdb --analyze-in-stages nikolai.berkoff <nikolai.berkoff@pm.me>
2021-10-18 18:12 ` Re: vacuumdb --analyze-in-stages Euler Taveira <euler@eulerto.com>
2021-10-18 23:02 ` Re: vacuumdb --analyze-in-stages Alvaro Herrera <alvherre@alvh.no-ip.org>
2021-10-18 23:34 ` Re: vacuumdb --analyze-in-stages David G. Johnston <david.g.johnston@gmail.com>
2021-10-18 23:53 ` Re: vacuumdb --analyze-in-stages Alvaro Herrera <alvherre@alvh.no-ip.org>
2021-10-19 00:02 ` Re: vacuumdb --analyze-in-stages Euler Taveira <euler@eulerto.com>
2021-10-26 14:18 ` Re: vacuumdb --analyze-in-stages nikolai.berkoff <nikolai.berkoff@pm.me>
@ 2021-11-05 14:25 ` Alvaro Herrera <alvherre@alvh.no-ip.org>
0 siblings, 0 replies; 8+ messages in thread
From: Alvaro Herrera @ 2021-11-05 14:25 UTC (permalink / raw)
To: nikolai.berkoff <nikolai.berkoff@pm.me>; +Cc: Euler Taveira <euler@eulerto.com>; David G. Johnston <david.g.johnston@gmail.com>; pgsql-docs@lists.postgresql.org <pgsql-docs@lists.postgresql.org>
On 2021-Oct-26, nikolai.berkoff wrote:
> Given all the suggestions I've tried to combine them into one patch again.
OK, I pushed something very similar but not exactly your patch ...
please have a look if you care to suggest improvements.
... oh, I just noticed I made a typo -- omitted "with" in "running this
option". I'll wait before pushing a fix in case there are other
rewording suggestions.
--
Álvaro Herrera PostgreSQL Developer — https://www.EnterpriseDB.com/
^ permalink raw reply [nested|flat] 8+ messages in thread
end of thread, other threads:[~2021-11-05 14:25 UTC | newest]
Thread overview: 8+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2021-10-08 09:33 vacuumdb --analyze-in-stages nikolai.berkoff <nikolai.berkoff@pm.me>
2021-10-18 18:12 ` Euler Taveira <euler@eulerto.com>
2021-10-18 23:02 ` Alvaro Herrera <alvherre@alvh.no-ip.org>
2021-10-18 23:34 ` David G. Johnston <david.g.johnston@gmail.com>
2021-10-18 23:53 ` Alvaro Herrera <alvherre@alvh.no-ip.org>
2021-10-19 00:02 ` Euler Taveira <euler@eulerto.com>
2021-10-26 14:18 ` nikolai.berkoff <nikolai.berkoff@pm.me>
2021-11-05 14:25 ` Alvaro Herrera <alvherre@alvh.no-ip.org>
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