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

Commit5f8eb25

Browse files
nathan-bossartcoreyhuinker
authored andcommitted
vacuumdb: Add option for analyzing only relations missing stats.
This commit adds a new --missing-only option that can be used inconjunction with --analyze-only and --analyze-in-stages. When thisoption is specified, vacuumdb will generate ANALYZE commands for arelation if it is missing any statistics it should ordinarily have.For example, if a table has statistics for one column but notanother, we will analyze the whole table. A similar principleapplies to extended statistics, expression indexes, and tableinheritance.Co-authored-by: Corey Huinker <corey.huinker@gmail.com>Reviewed-by: TODODiscussion:https://postgr.es/m/Z5O1bpcwDrMgyrYy%40nathan
1 parente208026 commit5f8eb25

File tree

4 files changed

+195
-0
lines changed

4 files changed

+195
-0
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-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> and <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/102_vacuumdb_stages.pl

Lines changed: 60 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -21,6 +21,66 @@
2121
.*statement:\ANALYZE/sx,
2222
'analyze three times');
2323

24+
$node->safe_psql('postgres',
25+
'CREATE TABLE regression_vacuumdb_test AS select generate_series(1, 10) a, generate_series(2, 11) b;');
26+
$node->issues_sql_like(
27+
['vacuumdb','--analyze-in-stages','--missing-only','-t','regression_vacuumdb_test','postgres' ],
28+
qr/statement:\ANALYZE/sx,
29+
'--missing-only with missing stats');
30+
$node->issues_sql_unlike(
31+
['vacuumdb','--analyze-in-stages','--missing-only','-t','regression_vacuumdb_test','postgres' ],
32+
qr/statement:\ANALYZE/sx,
33+
'--missing-only with no missing stats');
34+
35+
$node->safe_psql('postgres',
36+
'CREATE INDEX regression_vacuumdb_test_idx ON regression_vacuumdb_test (mod(a, 2));');
37+
$node->issues_sql_like(
38+
['vacuumdb','--analyze-in-stages','--missing-only','-t','regression_vacuumdb_test','postgres' ],
39+
qr/statement:\ANALYZE/sx,
40+
'--missing-only with missing index expression stats');
41+
$node->issues_sql_unlike(
42+
['vacuumdb','--analyze-in-stages','--missing-only','-t','regression_vacuumdb_test','postgres' ],
43+
qr/statement:\ANALYZE/sx,
44+
'--missing-only with no missing index expression stats');
45+
46+
$node->safe_psql('postgres',
47+
'CREATE STATISTICS regression_vacuumdb_test_stat ON a, b FROM regression_vacuumdb_test;');
48+
$node->issues_sql_like(
49+
['vacuumdb','--analyze-in-stages','--missing-only','-t','regression_vacuumdb_test','postgres' ],
50+
qr/statement:\ANALYZE/sx,
51+
'--missing-only with missing extended stats');
52+
$node->issues_sql_unlike(
53+
['vacuumdb','--analyze-in-stages','--missing-only','-t','regression_vacuumdb_test','postgres' ],
54+
qr/statement:\ANALYZE/sx,
55+
'--missing-only with no missing extended stats');
56+
57+
$node->safe_psql('postgres',
58+
"CREATE TABLE regression_vacuumdb_child (a INT) INHERITS (regression_vacuumdb_test);\n"
59+
."INSERT INTO regression_vacuumdb_child VALUES (1, 2);\n"
60+
."ANALYZE regression_vacuumdb_child;\n");
61+
$node->issues_sql_like(
62+
['vacuumdb','--analyze-in-stages','--missing-only','-t','regression_vacuumdb_test','postgres' ],
63+
qr/statement:\ANALYZE/sx,
64+
'--missing-only with missing inherited stats');
65+
$node->issues_sql_unlike(
66+
['vacuumdb','--analyze-in-stages','--missing-only','-t','regression_vacuumdb_test','postgres' ],
67+
qr/statement:\ANALYZE/sx,
68+
'--missing-only with no missing inherited stats');
69+
70+
$node->safe_psql('postgres',
71+
"CREATE TABLE regression_vacuumdb_parted (a INT) PARTITION BY LIST (a);\n"
72+
."CREATE TABLE regression_vacuumdb_part1 PARTITION OF regression_vacuumdb_parted FOR VALUES IN (1);\n"
73+
."INSERT INTO regression_vacuumdb_parted VALUES (1);\n"
74+
."ANALYZE regression_vacuumdb_part1;\n");
75+
$node->issues_sql_like(
76+
['vacuumdb','--analyze-in-stages','--missing-only','-t','regression_vacuumdb_parted','postgres' ],
77+
qr/statement:\ANALYZE/sx,
78+
'--missing-only with missing partition stats');
79+
$node->issues_sql_unlike(
80+
['vacuumdb','--analyze-in-stages','--missing-only','-t','regression_vacuumdb_parted','postgres' ],
81+
qr/statement:\ANALYZE/sx,
82+
'--missing-only with no missing partition stats');
83+
2484
$node->issues_sql_like(
2585
['vacuumdb','--analyze-in-stages','--all' ],
2686
qr/statement:\SET\default_statistics_target=1;\SET\vacuum_cost_delay=0;

‎src/bin/scripts/vacuumdb.c

Lines changed: 92 additions & 0 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_only;
5051
}vacuumingOptions;
5152

5253
/* object filter options */
@@ -128,6 +129,7 @@ main(int argc, char *argv[])
128129
{"no-process-toast",no_argument,NULL,11},
129130
{"no-process-main",no_argument,NULL,12},
130131
{"buffer-usage-limit",required_argument,NULL,13},
132+
{"missing-only",no_argument,NULL,14},
131133
{NULL,0,NULL,0}
132134
};
133135

@@ -275,6 +277,9 @@ main(int argc, char *argv[])
275277
case13:
276278
vacopts.buffer_usage_limit=escape_quotes(optarg);
277279
break;
280+
case14:
281+
vacopts.missing_only= true;
282+
break;
278283
default:
279284
/* getopt_long already emitted a complaint */
280285
pg_log_error_hint("Try \"%s --help\" for more information.",progname);
@@ -360,6 +365,11 @@ main(int argc, char *argv[])
360365
pg_fatal("cannot use the \"%s\" option with the \"%s\" option",
361366
"buffer-usage-limit","full");
362367

368+
/* Prohibit --missing-only without --analyze-only or --analyze-in-stages */
369+
if (vacopts.missing_only&& !vacopts.analyze_only)
370+
pg_fatal("cannot use the \"%s\" option without \"%s\" or \"%s\"",
371+
"missing-only","analyze-only","analyze-in-stages");
372+
363373
/* fill cparams except for dbname, which is set below */
364374
cparams.pghost=host;
365375
cparams.pgport=port;
@@ -584,6 +594,13 @@ vacuum_one_database(ConnParams *cparams,
584594
"--buffer-usage-limit","16");
585595
}
586596

597+
if (vacopts->missing_only&&PQserverVersion(conn)<150000)
598+
{
599+
PQfinish(conn);
600+
pg_fatal("cannot use the \"%s\" option on server versions older than PostgreSQL %s",
601+
"--missing-only","15");
602+
}
603+
587604
/* skip_database_stats is used automatically if server supports it */
588605
vacopts->skip_database_stats= (PQserverVersion(conn) >=160000);
589606

@@ -672,6 +689,7 @@ vacuum_one_database(ConnParams *cparams,
672689
" FROM pg_catalog.pg_class c\n"
673690
" JOIN pg_catalog.pg_namespace ns"
674691
" ON c.relnamespace OPERATOR(pg_catalog.=) ns.oid\n"
692+
" CROSS JOIN LATERAL (SELECT c.relkind IN ('p', 'I')) as p (inherited)\n"
675693
" LEFT JOIN pg_catalog.pg_class t"
676694
" ON c.reltoastrelid OPERATOR(pg_catalog.=) t.oid\n");
677695

@@ -755,6 +773,79 @@ vacuum_one_database(ConnParams *cparams,
755773
vacopts->min_mxid_age);
756774
}
757775

776+
if (vacopts->missing_only)
777+
{
778+
appendPQExpBufferStr(&catalog_query," AND (\n");
779+
780+
/* regular stats */
781+
appendPQExpBufferStr(&catalog_query,
782+
" EXISTS (SELECT NULL FROM pg_catalog.pg_attribute a\n"
783+
" WHERE a.attrelid OPERATOR(pg_catalog.=) c.oid\n"
784+
" AND c.reltuples OPERATOR(pg_catalog.!=) 0::pg_catalog.float4\n"
785+
" AND a.attnum OPERATOR(pg_catalog.>) 0::pg_catalog.int2\n"
786+
" AND NOT a.attisdropped\n"
787+
" AND a.attstattarget IS DISTINCT FROM 0::pg_catalog.int2\n"
788+
" AND NOT EXISTS (SELECT NULL FROM pg_catalog.pg_statistic s\n"
789+
" WHERE s.starelid OPERATOR(pg_catalog.=) a.attrelid\n"
790+
" AND s.staattnum OPERATOR(pg_catalog.=) a.attnum\n"
791+
" AND s.stainherit OPERATOR(pg_catalog.=) p.inherited))\n");
792+
793+
/* extended stats */
794+
appendPQExpBufferStr(&catalog_query,
795+
" OR EXISTS (SELECT NULL FROM pg_catalog.pg_statistic_ext e\n"
796+
" WHERE e.stxrelid OPERATOR(pg_catalog.=) c.oid\n"
797+
" AND c.reltuples OPERATOR(pg_catalog.!=) 0::pg_catalog.float4\n"
798+
" AND e.stxstattarget IS DISTINCT FROM 0::pg_catalog.int2\n"
799+
" AND NOT EXISTS (SELECT NULL FROM pg_catalog.pg_statistic_ext_data d\n"
800+
" WHERE d.stxoid OPERATOR(pg_catalog.=) e.oid\n"
801+
" AND d.stxdinherit OPERATOR(pg_catalog.=) p.inherited))\n");
802+
803+
/* expression indexes */
804+
appendPQExpBufferStr(&catalog_query,
805+
" OR EXISTS (SELECT NULL FROM pg_catalog.pg_index i\n"
806+
" CROSS JOIN LATERAL pg_catalog.unnest(i.indkey) WITH ORDINALITY u (attnum, ord)\n"
807+
" WHERE i.indrelid OPERATOR(pg_catalog.=) c.oid\n"
808+
" AND c.reltuples OPERATOR(pg_catalog.!=) 0::pg_catalog.float4\n"
809+
" AND i.indexprs IS NOT NULL\n"
810+
" AND NOT EXISTS (SELECT NULL FROM pg_catalog.pg_statistic s\n"
811+
" WHERE s.starelid OPERATOR(pg_catalog.=) i.indexrelid\n"
812+
" AND s.staattnum OPERATOR(pg_catalog.=) u.ord\n"
813+
" AND s.stainherit OPERATOR(pg_catalog.=) p.inherited))\n");
814+
815+
/* table inheritance and regular stats */
816+
appendPQExpBufferStr(&catalog_query,
817+
" OR EXISTS (SELECT NULL FROM pg_catalog.pg_attribute a\n"
818+
" WHERE a.attrelid OPERATOR(pg_catalog.=) c.oid\n"
819+
" AND c.reltuples OPERATOR(pg_catalog.!=) 0::pg_catalog.float4\n"
820+
" AND a.attnum OPERATOR(pg_catalog.>) 0::pg_catalog.int2\n"
821+
" AND NOT a.attisdropped\n"
822+
" AND a.attstattarget IS DISTINCT FROM 0::pg_catalog.int2\n"
823+
" AND c.relhassubclass\n"
824+
" AND NOT p.inherited\n"
825+
" AND EXISTS (SELECT NULL FROM pg_catalog.pg_inherits h\n"
826+
" WHERE h.inhparent OPERATOR(pg_catalog.=) c.oid)\n"
827+
" AND NOT EXISTS (SELECT NULL FROM pg_catalog.pg_statistic s\n"
828+
" WHERE s.starelid OPERATOR(pg_catalog.=) a.attrelid\n"
829+
" AND s.staattnum OPERATOR(pg_catalog.=) a.attnum\n"
830+
" AND s.stainherit))\n");
831+
832+
/* table inheritance and extended stats */
833+
appendPQExpBufferStr(&catalog_query,
834+
" OR EXISTS (SELECT NULL FROM pg_catalog.pg_statistic_ext e\n"
835+
" WHERE e.stxrelid OPERATOR(pg_catalog.=) c.oid\n"
836+
" AND c.reltuples OPERATOR(pg_catalog.!=) 0::pg_catalog.float4\n"
837+
" AND e.stxstattarget IS DISTINCT FROM 0::pg_catalog.int2\n"
838+
" AND c.relhassubclass\n"
839+
" AND NOT p.inherited\n"
840+
" AND EXISTS (SELECT NULL FROM pg_catalog.pg_inherits h\n"
841+
" WHERE h.inhparent OPERATOR(pg_catalog.=) c.oid)\n"
842+
" AND NOT EXISTS (SELECT NULL FROM pg_catalog.pg_statistic_ext_data d\n"
843+
" WHERE d.stxoid OPERATOR(pg_catalog.=) e.oid\n"
844+
" AND d.stxdinherit))\n");
845+
846+
appendPQExpBufferStr(&catalog_query," )\n");
847+
}
848+
758849
/*
759850
* Execute the catalog query. We use the default search_path for this
760851
* query for consistency with table lookups done elsewhere by the user.
@@ -1181,6 +1272,7 @@ help(const char *progname)
11811272
printf(_(" -j, --jobs=NUM use this many concurrent connections to vacuum\n"));
11821273
printf(_(" --min-mxid-age=MXID_AGE minimum multixact ID age of tables to vacuum\n"));
11831274
printf(_(" --min-xid-age=XID_AGE minimum transaction ID age of tables to vacuum\n"));
1275+
printf(_(" --missing-only only analyze relations with missing statistics\n"));
11841276
printf(_(" --no-index-cleanup don't remove index entries that point to dead tuples\n"));
11851277
printf(_(" --no-process-main skip the main relation\n"));
11861278
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
@@ -2820,6 +2820,33 @@ sub issues_sql_like
28202820

28212821
=pod
28222822
2823+
=item$node->issues_sql_unlike(cmd, unexpected_sql, test_name)
2824+
2825+
Run a command on the node, then verify that $unexpected_sql does not appear in
2826+
the server log file.
2827+
2828+
=cut
2829+
2830+
subissues_sql_unlike
2831+
{
2832+
local$Test::Builder::Level =$Test::Builder::Level + 1;
2833+
2834+
my ($self,$cmd,$unexpected_sql,$test_name) =@_;
2835+
2836+
local%ENV =$self->_get_env();
2837+
2838+
my$log_location =-s$self->logfile;
2839+
2840+
my$result = PostgreSQL::Test::Utils::run_log($cmd);
2841+
ok($result,"@$cmd exit code 0");
2842+
my$log =
2843+
PostgreSQL::Test::Utils::slurp_file($self->logfile,$log_location);
2844+
unlike($log,$unexpected_sql,"$test_name: SQL not found in server log");
2845+
return;
2846+
}
2847+
2848+
=pod
2849+
28232850
=item$node->log_content()
28242851
28252852
Returns the contents of log of the node

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp