Skip to content

Commit edba754

Browse files
nathan-bossartnathan-bossart
committed
vacuumdb: Add option for analyzing only relations missing stats.
This commit adds a new --missing-stats-only option that can be used with --analyze-only or --analyze-in-stages. When this option is specified, vacuumdb will analyze a relation if it lacks any statistics for a column, expression index, or extended statistics object. This new option is primarily intended for use after pg_upgrade (since it can now retain most optimizer statistics), but it might be useful in other situations, too. Author: Corey Huinker <corey.huinker@gmail.com> Co-authored-by: Nathan Bossart <nathandbossart@gmail.com> Reviewed-by: John Naylor <johncnaylorls@gmail.com> Discussion: https://postgr.es/m/Z5O1bpcwDrMgyrYy%40nathan
1 parent 9c03c8d commit edba754

File tree

4 files changed

+215
-2
lines changed

4 files changed

+215
-2
lines changed

‎doc/src/sgml/ref/vacuumdb.sgml

Lines changed: 16 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -277,6 +277,22 @@ PostgreSQL documentation
277277
</listitem>
278278
</varlistentry>
279279

280+
<varlistentry>
281+
<term><option>--missing-stats-only</option></term>
282+
<listitem>
283+
<para>
284+
Only analyze relations that are missing statistics for a column, index
285+
expression, or extended statistics object. This option prevents
286+
<application>vacuumdb</application> from deleting existing statistics
287+
so that the query optimizer's choices do not become transiently worse.
288+
</para>
289+
<para>
290+
This option can only be used in conjunction with
291+
<option>--analyze-only</option> or <option>--analyze-in-stages</option>.
292+
</para>
293+
</listitem>
294+
</varlistentry>
295+
280296
<varlistentry>
281297
<term><option>-n <replaceable class="parameter">schema</replaceable></option></term>
282298
<term><option>--schema=<replaceable class="parameter">schema</replaceable></option></term>

‎src/bin/scripts/t/100_vacuumdb.pl

Lines changed: 60 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -237,4 +237,64 @@
237237
qr/cannot vacuum all databases and a specific one at the same time/,
238238
'cannot use option --all and a dbname as argument at the same time');
239239

240+
$node->safe_psql('postgres',
241+
'CREATE TABLE regression_vacuumdb_test AS select generate_series(1, 10) a, generate_series(2, 11) b;');
242+
$node->issues_sql_like(
243+
[ 'vacuumdb', '--analyze-only', '--missing-stats-only', '-t', 'regression_vacuumdb_test', 'postgres' ],
244+
qr/statement:\ ANALYZE/sx,
245+
'--missing-stats-only with missing stats');
246+
$node->issues_sql_unlike(
247+
[ 'vacuumdb', '--analyze-only', '--missing-stats-only', '-t', 'regression_vacuumdb_test', 'postgres' ],
248+
qr/statement:\ ANALYZE/sx,
249+
'--missing-stats-only with no missing stats');
250+
251+
$node->safe_psql('postgres',
252+
'CREATE INDEX regression_vacuumdb_test_idx ON regression_vacuumdb_test (mod(a, 2));');
253+
$node->issues_sql_like(
254+
[ 'vacuumdb', '--analyze-in-stages', '--missing-stats-only', '-t', 'regression_vacuumdb_test', 'postgres' ],
255+
qr/statement:\ ANALYZE/sx,
256+
'--missing-stats-only with missing index expression stats');
257+
$node->issues_sql_unlike(
258+
[ 'vacuumdb', '--analyze-in-stages', '--missing-stats-only', '-t', 'regression_vacuumdb_test', 'postgres' ],
259+
qr/statement:\ ANALYZE/sx,
260+
'--missing-stats-only with no missing index expression stats');
261+
262+
$node->safe_psql('postgres',
263+
'CREATE STATISTICS regression_vacuumdb_test_stat ON a, b FROM regression_vacuumdb_test;');
264+
$node->issues_sql_like(
265+
[ 'vacuumdb', '--analyze-only', '--missing-stats-only', '-t', 'regression_vacuumdb_test', 'postgres' ],
266+
qr/statement:\ ANALYZE/sx,
267+
'--missing-stats-only with missing extended stats');
268+
$node->issues_sql_unlike(
269+
[ 'vacuumdb', '--analyze-only', '--missing-stats-only', '-t', 'regression_vacuumdb_test', 'postgres' ],
270+
qr/statement:\ ANALYZE/sx,
271+
'--missing-stats-only with no missing extended stats');
272+
273+
$node->safe_psql('postgres',
274+
"CREATE TABLE regression_vacuumdb_child (a INT) INHERITS (regression_vacuumdb_test);\n"
275+
. "INSERT INTO regression_vacuumdb_child VALUES (1, 2);\n"
276+
. "ANALYZE regression_vacuumdb_child;\n");
277+
$node->issues_sql_like(
278+
[ 'vacuumdb', '--analyze-in-stages', '--missing-stats-only', '-t', 'regression_vacuumdb_test', 'postgres' ],
279+
qr/statement:\ ANALYZE/sx,
280+
'--missing-stats-only with missing inherited stats');
281+
$node->issues_sql_unlike(
282+
[ 'vacuumdb', '--analyze-in-stages', '--missing-stats-only', '-t', 'regression_vacuumdb_test', 'postgres' ],
283+
qr/statement:\ ANALYZE/sx,
284+
'--missing-stats-only with no missing inherited stats');
285+
286+
$node->safe_psql('postgres',
287+
"CREATE TABLE regression_vacuumdb_parted (a INT) PARTITION BY LIST (a);\n"
288+
. "CREATE TABLE regression_vacuumdb_part1 PARTITION OF regression_vacuumdb_parted FOR VALUES IN (1);\n"
289+
. "INSERT INTO regression_vacuumdb_parted VALUES (1);\n"
290+
. "ANALYZE regression_vacuumdb_part1;\n");
291+
$node->issues_sql_like(
292+
[ 'vacuumdb', '--analyze-only', '--missing-stats-only', '-t', 'regression_vacuumdb_parted', 'postgres' ],
293+
qr/statement:\ ANALYZE/sx,
294+
'--missing-stats-only with missing partition stats');
295+
$node->issues_sql_unlike(
296+
[ 'vacuumdb', '--analyze-only', '--missing-stats-only', '-t', 'regression_vacuumdb_parted', 'postgres' ],
297+
qr/statement:\ ANALYZE/sx,
298+
'--missing-stats-only with no missing partition stats');
299+
240300
done_testing();

‎src/bin/scripts/vacuumdb.c

Lines changed: 112 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -47,6 +47,7 @@ typedef struct vacuumingOptions
4747
bool process_toast;
4848
bool skip_database_stats;
4949
char *buffer_usage_limit;
50+
bool missing_stats_only;
5051
} vacuumingOptions;
5152

5253
/* object filter options */
@@ -134,6 +135,7 @@ main(int argc, char *argv[])
134135
{"no-process-toast", no_argument, NULL, 11},
135136
{"no-process-main", no_argument, NULL, 12},
136137
{"buffer-usage-limit", required_argument, NULL, 13},
138+
{"missing-stats-only", no_argument, NULL, 14},
137139
{NULL, 0, NULL, 0}
138140
};
139141

@@ -281,6 +283,9 @@ main(int argc, char *argv[])
281283
case 13:
282284
vacopts.buffer_usage_limit = escape_quotes(optarg);
283285
break;
286+
case 14:
287+
vacopts.missing_stats_only = true;
288+
break;
284289
default:
285290
/* getopt_long already emitted a complaint */
286291
pg_log_error_hint("Try \"%s --help\" for more information.", progname);
@@ -366,6 +371,14 @@ main(int argc, char *argv[])
366371
pg_fatal("cannot use the \"%s\" option with the \"%s\" option",
367372
"buffer-usage-limit", "full");
368373

374+
/*
375+
* Prohibit --missing-stats-only without --analyze-only or
376+
* --analyze-in-stages.
377+
*/
378+
if (vacopts.missing_stats_only && !vacopts.analyze_only)
379+
pg_fatal("cannot use the \"%s\" option without \"%s\" or \"%s\"",
380+
"missing-stats-only", "analyze-only", "analyze-in-stages");
381+
369382
/* fill cparams except for dbname, which is set below */
370383
cparams.pghost = host;
371384
cparams.pgport = port;
@@ -406,12 +419,14 @@ main(int argc, char *argv[])
406419
if (analyze_in_stages)
407420
{
408421
int stage;
422+
SimpleStringList *found_objs = NULL;
409423

410424
for (stage = 0; stage < ANALYZE_NUM_STAGES; stage++)
411425
{
412426
vacuum_one_database(&cparams, &vacopts,
413427
stage,
414-
&objects, NULL,
428+
&objects,
429+
vacopts.missing_stats_only ? &found_objs : NULL,
415430
concurrentCons,
416431
progname, echo, quiet);
417432
}
@@ -614,6 +629,13 @@ vacuum_one_database(ConnParams *cparams,
614629
"--buffer-usage-limit", "16");
615630
}
616631

632+
if (vacopts->missing_stats_only && PQserverVersion(conn) < 150000)
633+
{
634+
PQfinish(conn);
635+
pg_fatal("cannot use the \"%s\" option on server versions older than PostgreSQL %s",
636+
"--missing-stats-only", "15");
637+
}
638+
617639
/* skip_database_stats is used automatically if server supports it */
618640
vacopts->skip_database_stats = (PQserverVersion(conn) >= 160000);
619641

@@ -838,6 +860,9 @@ retrieve_objects(PGconn *conn, vacuumingOptions *vacopts,
838860
" FROM pg_catalog.pg_class c\n"
839861
" JOIN pg_catalog.pg_namespace ns"
840862
" ON c.relnamespace OPERATOR(pg_catalog.=) ns.oid\n"
863+
" CROSS JOIN LATERAL (SELECT c.relkind IN ("
864+
CppAsString2(RELKIND_PARTITIONED_TABLE) ", "
865+
CppAsString2(RELKIND_PARTITIONED_INDEX) ")) as p (inherited)\n"
841866
" LEFT JOIN pg_catalog.pg_class t"
842867
" ON c.reltoastrelid OPERATOR(pg_catalog.=) t.oid\n");
843868

@@ -921,6 +946,84 @@ retrieve_objects(PGconn *conn, vacuumingOptions *vacopts,
921946
vacopts->min_mxid_age);
922947
}
923948

949+
if (vacopts->missing_stats_only)
950+
{
951+
appendPQExpBufferStr(&catalog_query, " AND (\n");
952+
953+
/* regular stats */
954+
appendPQExpBufferStr(&catalog_query,
955+
" EXISTS (SELECT NULL FROM pg_catalog.pg_attribute a\n"
956+
" WHERE a.attrelid OPERATOR(pg_catalog.=) c.oid\n"
957+
" AND c.reltuples OPERATOR(pg_catalog.!=) 0::pg_catalog.float4\n"
958+
" AND a.attnum OPERATOR(pg_catalog.>) 0::pg_catalog.int2\n"
959+
" AND NOT a.attisdropped\n"
960+
" AND a.attstattarget IS DISTINCT FROM 0::pg_catalog.int2\n"
961+
" AND NOT EXISTS (SELECT NULL FROM pg_catalog.pg_statistic s\n"
962+
" WHERE s.starelid OPERATOR(pg_catalog.=) a.attrelid\n"
963+
" AND s.staattnum OPERATOR(pg_catalog.=) a.attnum\n"
964+
" AND s.stainherit OPERATOR(pg_catalog.=) p.inherited))\n");
965+
966+
/* extended stats */
967+
appendPQExpBufferStr(&catalog_query,
968+
" OR EXISTS (SELECT NULL FROM pg_catalog.pg_statistic_ext e\n"
969+
" WHERE e.stxrelid OPERATOR(pg_catalog.=) c.oid\n"
970+
" AND c.reltuples OPERATOR(pg_catalog.!=) 0::pg_catalog.float4\n"
971+
" AND e.stxstattarget IS DISTINCT FROM 0::pg_catalog.int2\n"
972+
" AND NOT EXISTS (SELECT NULL FROM pg_catalog.pg_statistic_ext_data d\n"
973+
" WHERE d.stxoid OPERATOR(pg_catalog.=) e.oid\n"
974+
" AND d.stxdinherit OPERATOR(pg_catalog.=) p.inherited))\n");
975+
976+
/* expression indexes */
977+
appendPQExpBufferStr(&catalog_query,
978+
" OR EXISTS (SELECT NULL FROM pg_catalog.pg_attribute a\n"
979+
" JOIN pg_catalog.pg_index i"
980+
" ON i.indexrelid OPERATOR(pg_catalog.=) a.attrelid\n"
981+
" WHERE i.indrelid OPERATOR(pg_catalog.=) c.oid\n"
982+
" AND c.reltuples OPERATOR(pg_catalog.!=) 0::pg_catalog.float4\n"
983+
" AND i.indkey[a.attnum OPERATOR(pg_catalog.-) 1::pg_catalog.int2]"
984+
" OPERATOR(pg_catalog.=) 0::pg_catalog.int2\n"
985+
" AND a.attnum OPERATOR(pg_catalog.>) 0::pg_catalog.int2\n"
986+
" AND NOT a.attisdropped\n"
987+
" AND a.attstattarget IS DISTINCT FROM 0::pg_catalog.int2\n"
988+
" AND NOT EXISTS (SELECT NULL FROM pg_catalog.pg_statistic s\n"
989+
" WHERE s.starelid OPERATOR(pg_catalog.=) a.attrelid\n"
990+
" AND s.staattnum OPERATOR(pg_catalog.=) a.attnum\n"
991+
" AND s.stainherit OPERATOR(pg_catalog.=) p.inherited))\n");
992+
993+
/* inheritance and regular stats */
994+
appendPQExpBufferStr(&catalog_query,
995+
" OR EXISTS (SELECT NULL FROM pg_catalog.pg_attribute a\n"
996+
" WHERE a.attrelid OPERATOR(pg_catalog.=) c.oid\n"
997+
" AND c.reltuples OPERATOR(pg_catalog.!=) 0::pg_catalog.float4\n"
998+
" AND a.attnum OPERATOR(pg_catalog.>) 0::pg_catalog.int2\n"
999+
" AND NOT a.attisdropped\n"
1000+
" AND a.attstattarget IS DISTINCT FROM 0::pg_catalog.int2\n"
1001+
" AND c.relhassubclass\n"
1002+
" AND NOT p.inherited\n"
1003+
" AND EXISTS (SELECT NULL FROM pg_catalog.pg_inherits h\n"
1004+
" WHERE h.inhparent OPERATOR(pg_catalog.=) c.oid)\n"
1005+
" AND NOT EXISTS (SELECT NULL FROM pg_catalog.pg_statistic s\n"
1006+
" WHERE s.starelid OPERATOR(pg_catalog.=) a.attrelid\n"
1007+
" AND s.staattnum OPERATOR(pg_catalog.=) a.attnum\n"
1008+
" AND s.stainherit))\n");
1009+
1010+
/* inheritance and extended stats */
1011+
appendPQExpBufferStr(&catalog_query,
1012+
" OR EXISTS (SELECT NULL FROM pg_catalog.pg_statistic_ext e\n"
1013+
" WHERE e.stxrelid OPERATOR(pg_catalog.=) c.oid\n"
1014+
" AND c.reltuples OPERATOR(pg_catalog.!=) 0::pg_catalog.float4\n"
1015+
" AND e.stxstattarget IS DISTINCT FROM 0::pg_catalog.int2\n"
1016+
" AND c.relhassubclass\n"
1017+
" AND NOT p.inherited\n"
1018+
" AND EXISTS (SELECT NULL FROM pg_catalog.pg_inherits h\n"
1019+
" WHERE h.inhparent OPERATOR(pg_catalog.=) c.oid)\n"
1020+
" AND NOT EXISTS (SELECT NULL FROM pg_catalog.pg_statistic_ext_data d\n"
1021+
" WHERE d.stxoid OPERATOR(pg_catalog.=) e.oid\n"
1022+
" AND d.stxdinherit))\n");
1023+
1024+
appendPQExpBufferStr(&catalog_query, " )\n");
1025+
}
1026+
9241027
/*
9251028
* Execute the catalog query. We use the default search_path for this
9261029
* query for consistency with table lookups done elsewhere by the user.
@@ -983,6 +1086,11 @@ vacuum_all_databases(ConnParams *cparams,
9831086

9841087
if (analyze_in_stages)
9851088
{
1089+
SimpleStringList **found_objs = NULL;
1090+
1091+
if (vacopts->missing_stats_only)
1092+
found_objs = palloc0(PQntuples(result) * sizeof(SimpleStringList *));
1093+
9861094
/*
9871095
* When analyzing all databases in stages, we analyze them all in the
9881096
* fastest stage first, so that initial statistics become available
@@ -999,7 +1107,8 @@ vacuum_all_databases(ConnParams *cparams,
9991107

10001108
vacuum_one_database(cparams, vacopts,
10011109
stage,
1002-
objects, NULL,
1110+
objects,
1111+
vacopts->missing_stats_only ? &found_objs[i] : NULL,
10031112
concurrentCons,
10041113
progname, echo, quiet);
10051114
}
@@ -1239,6 +1348,7 @@ help(const char *progname)
12391348
printf(_(" -j, --jobs=NUM use this many concurrent connections to vacuum\n"));
12401349
printf(_(" --min-mxid-age=MXID_AGE minimum multixact ID age of tables to vacuum\n"));
12411350
printf(_(" --min-xid-age=XID_AGE minimum transaction ID age of tables to vacuum\n"));
1351+
printf(_(" --missing-stats-only only analyze relations with missing statistics\n"));
12421352
printf(_(" --no-index-cleanup don't remove index entries that point to dead tuples\n"));
12431353
printf(_(" --no-process-main skip the main relation\n"));
12441354
printf(_(" --no-process-toast skip the TOAST table associated with the table to vacuum\n"));

‎src/test/perl/PostgreSQL/Test/Cluster.pm

Lines changed: 27 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -2849,6 +2849,33 @@ sub issues_sql_like
28492849

28502850
=pod
28512851
2852+
=item $node->issues_sql_unlike(cmd, unexpected_sql, test_name)
2853+
2854+
Run a command on the node, then verify that $unexpected_sql does not appear in
2855+
the server log file.
2856+
2857+
=cut
2858+
2859+
sub issues_sql_unlike
2860+
{
2861+
local $Test::Builder::Level = $Test::Builder::Level + 1;
2862+
2863+
my ($self, $cmd, $unexpected_sql, $test_name) = @_;
2864+
2865+
local %ENV = $self->_get_env();
2866+
2867+
my $log_location = -s $self->logfile;
2868+
2869+
my $result = PostgreSQL::Test::Utils::run_log($cmd);
2870+
ok($result, "@$cmd exit code 0");
2871+
my $log =
2872+
PostgreSQL::Test::Utils::slurp_file($self->logfile, $log_location);
2873+
unlike($log, $unexpected_sql, "$test_name: SQL not found in server log");
2874+
return;
2875+
}
2876+
2877+
=pod
2878+
28522879
=item $node->log_content()
28532880
28542881
Returns the contents of log of the node

0 commit comments

Comments
 (0)