Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commitedba754

Browse files
vacuumdb: Add option for analyzing only relations missing stats.
This commit adds a new --missing-stats-only option that can be usedwith --analyze-only or --analyze-in-stages. When this option isspecified, vacuumdb will analyze a relation if it lacks anystatistics for a column, expression index, or extended statisticsobject. This new option is primarily intended for use afterpg_upgrade (since it can now retain most optimizer statistics), butit 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 parent9c03c8d commitedba754

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
boolprocess_toast;
4848
boolskip_database_stats;
4949
char*buffer_usage_limit;
50+
boolmissing_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
case13:
282284
vacopts.buffer_usage_limit=escape_quotes(optarg);
283285
break;
286+
case14:
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
intstage;
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+
subissues_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)
[8]ページ先頭
©2009-2025 Movatter.jp