Hi,
On Fri, Nov 24, 2023 at 12:17:56PM +0100, Magnus Hagander wrote:
> On Fri, Nov 24, 2023 at 11:21 AM Michael Banck <mba...@gmx.net> wrote:
> > On Wed, Nov 22, 2023 at 11:23:34PM -0500, Bruce Momjian wrote:
> > > + Non-zero values of
> > > + <varname>vacuum_cost_delay</varname> will delay statistics
> > > generation.
> >
> > Now I wonder wheter vacuumdb maybe should have an option to explicitly
> > force vacuum_cost_delay to 0 (I don't think it has?)?
>
> That's exactly what I proposed, isn't it? :)
You're right, I somehow only saw your mail after I had already sent
mine.
To make up for this, I created a patch that implements our propoals, see
attached.
Michael
>From b4a6e23f297994a237e35b9f96d2b806a17ba7a8 Mon Sep 17 00:00:00 2001
From: Michael Banck <michael.ba...@credativ.de>
Date: Fri, 24 Nov 2023 13:00:31 +0100
Subject: [PATCH] Add --no-cost-delay option to vacuumdb.
This sets the vacuum_cost_delay parameter to 0 and overrides the system
setting (which is 0 by default as well). This can be useful in scripts where
the actual value of vacuum_cost_delay is not known and vacuumdb should operate
as fast as possible.
If --no-cost-delay is selected in addition to --analze-in-stages, all stages
are always analyzed without cost-based vacuum. Otherwise, the previous
behaviour of only overriding the system settting of vacuum_cost_delay for the
first stage is retained.
---
src/bin/scripts/vacuumdb.c | 29 +++++++++++++++++++++++++++--
1 file changed, 27 insertions(+), 2 deletions(-)
diff --git a/src/bin/scripts/vacuumdb.c b/src/bin/scripts/vacuumdb.c
index dd0d51659b..67373c6506 100644
--- a/src/bin/scripts/vacuumdb.c
+++ b/src/bin/scripts/vacuumdb.c
@@ -47,6 +47,7 @@ typedef struct vacuumingOptions
bool process_toast;
bool skip_database_stats;
char *buffer_usage_limit;
+ bool no_cost_delay;
} vacuumingOptions;
/* object filter options */
@@ -127,6 +128,7 @@ main(int argc, char *argv[])
{"no-process-toast", no_argument, NULL, 11},
{"no-process-main", no_argument, NULL, 12},
{"buffer-usage-limit", required_argument, NULL, 13},
+ {"no-cost-delay", no_argument, NULL, 14},
{NULL, 0, NULL, 0}
};
@@ -157,6 +159,7 @@ main(int argc, char *argv[])
vacopts.do_truncate = true;
vacopts.process_main = true;
vacopts.process_toast = true;
+ vacopts.no_cost_delay = false;
pg_logging_init(argv[0]);
progname = get_progname(argv[0]);
@@ -274,6 +277,9 @@ main(int argc, char *argv[])
case 13:
vacopts.buffer_usage_limit = escape_quotes(optarg);
break;
+ case 14:
+ vacopts.no_cost_delay = true;
+ break;
default:
/* getopt_long already emitted a complaint */
pg_log_error_hint("Try \"%s --help\" for more information.", progname);
@@ -508,6 +514,11 @@ vacuum_one_database(ConnParams *cparams,
"SET default_statistics_target=10; RESET vacuum_cost_delay;",
"RESET default_statistics_target;"
};
+ const char *stage_commands_no_cost_delay[] = {
+ "SET default_statistics_target=1; SET vacuum_cost_delay=0;",
+ "SET default_statistics_target=10; SET vacuum_cost_delay=0",
+ "RESET default_statistics_target; SET vacuum_cost_delay=0"
+ };
const char *stage_messages[] = {
gettext_noop("Generating minimal optimizer statistics (1 target)"),
gettext_noop("Generating medium optimizer statistics (10 targets)"),
@@ -799,10 +810,22 @@ vacuum_one_database(ConnParams *cparams,
* ourselves before setting up the slots.
*/
if (stage == ANALYZE_NO_STAGE)
- initcmd = NULL;
+ {
+ /* Switch off vacuum_cost_delay, if requested */
+ if (vacopts->no_cost_delay)
+ {
+ initcmd = "SET vacuum_cost_delay=0;";
+ executeCommand(conn, initcmd, echo);
+ }
+ else
+ initcmd = NULL;
+ }
else
{
- initcmd = stage_commands[stage];
+ if (vacopts->no_cost_delay)
+ initcmd = stage_commands_no_cost_delay[stage];
+ else
+ initcmd = stage_commands[stage];
executeCommand(conn, initcmd, echo);
}
@@ -1171,6 +1194,8 @@ help(const char *progname)
printf(_(" --no-process-main skip the main relation\n"));
printf(_(" --no-process-toast skip the TOAST table associated with the table to vacuum\n"));
printf(_(" --no-truncate don't truncate empty pages at the end of the table\n"));
+ printf(_(" --no-cost-delay force cost-based vacuum to off, overriding the\n"
+ " vacuum_cost_delay configuration parameter\n"));
printf(_(" -n, --schema=SCHEMA vacuum tables in the specified schema(s) only\n"));
printf(_(" -N, --exclude-schema=SCHEMA do not vacuum tables in the specified schema(s)\n"));
printf(_(" -P, --parallel=PARALLEL_WORKERS use this many background workers for vacuum, if available\n"));
--
2.39.2
