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

Commit6ee3b91

Browse files
jeff-daviscoreyhuinkerjeff-davis-aster
committed
pg_dump: prepare attribute stats query.
Follow precedent in pg_dump for preparing queries to improveperformance. Also, simplify the query by removing unnecessary joins.Reviewed-by: Tom Lane <tgl@sss.pgh.pa.us>Reported-by: Andres Freund <andres@anarazel.de>Co-authored-by: Corey Huinker <corey.huinker@gmail.com>Co-authored-by: Jeff Davis <pgsql@j-davis.com>Discussion:https://postgr.es/m/CADkLM=dRMC6t8gp9GVf6y6E_r5EChQjMAAh_vPyih_zMiq0zvA@mail.gmail.com
1 parent8f42718 commit6ee3b91

File tree

2 files changed

+58
-66
lines changed

2 files changed

+58
-66
lines changed

‎src/bin/pg_dump/pg_backup.h

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -72,6 +72,7 @@ enum _dumpPreparedQueries
7272
PREPQUERY_DUMPOPR,
7373
PREPQUERY_DUMPRANGETYPE,
7474
PREPQUERY_DUMPTABLEATTACH,
75+
PREPQUERY_GETATTRIBUTESTATS,
7576
PREPQUERY_GETCOLUMNACLS,
7677
PREPQUERY_GETDOMAINCONSTRAINTS,
7778
};

‎src/bin/pg_dump/pg_dump.c

Lines changed: 57 additions & 66 deletions
Original file line numberDiff line numberDiff line change
@@ -10415,10 +10415,8 @@ dumpComment(Archive *fout, const char *type,
1041510415
* param_name, param_type
1041610416
*/
1041710417
static const char *att_stats_arginfo[][2] = {
10418-
{"relation", "regclass"},
1041910418
{"attname", "name"},
1042010419
{"inherited", "boolean"},
10421-
{"version", "integer"},
1042210420
{"null_frac", "float4"},
1042310421
{"avg_width", "integer"},
1042410422
{"n_distinct", "float4"},
@@ -10434,60 +10432,6 @@ static const char *att_stats_arginfo[][2] = {
1043410432
{"range_bounds_histogram", "text"},
1043510433
};
1043610434

10437-
/*
10438-
* getAttStatsExportQuery --
10439-
*
10440-
* Generate a query that will fetch all attribute (e.g. pg_statistic)
10441-
* stats for a given relation.
10442-
*/
10443-
static void
10444-
getAttStatsExportQuery(PQExpBuffer query, Archive *fout,
10445-
const char *schemaname, const char *relname)
10446-
{
10447-
resetPQExpBuffer(query);
10448-
appendPQExpBuffer(query,
10449-
"SELECT c.oid::regclass AS relation, "
10450-
"s.attname,"
10451-
"s.inherited,"
10452-
"'%u'::integer AS version, "
10453-
"s.null_frac,"
10454-
"s.avg_width,"
10455-
"s.n_distinct,"
10456-
"s.most_common_vals,"
10457-
"s.most_common_freqs,"
10458-
"s.histogram_bounds,"
10459-
"s.correlation,"
10460-
"s.most_common_elems,"
10461-
"s.most_common_elem_freqs,"
10462-
"s.elem_count_histogram,",
10463-
fout->remoteVersion);
10464-
10465-
if (fout->remoteVersion >= 170000)
10466-
appendPQExpBufferStr(query,
10467-
"s.range_length_histogram,"
10468-
"s.range_empty_frac,"
10469-
"s.range_bounds_histogram ");
10470-
else
10471-
appendPQExpBufferStr(query,
10472-
"NULL AS range_length_histogram,"
10473-
"NULL AS range_empty_frac,"
10474-
"NULL AS range_bounds_histogram ");
10475-
10476-
appendPQExpBufferStr(query,
10477-
"FROM pg_stats s "
10478-
"JOIN pg_namespace n "
10479-
"ON n.nspname = s.schemaname "
10480-
"JOIN pg_class c "
10481-
"ON c.relname = s.tablename "
10482-
"AND c.relnamespace = n.oid "
10483-
"WHERE s.schemaname = ");
10484-
appendStringLiteralAH(query, schemaname, fout);
10485-
appendPQExpBufferStr(query, " AND s.tablename = ");
10486-
appendStringLiteralAH(query, relname, fout);
10487-
appendPQExpBufferStr(query, " ORDER BY s.attname, s.inherited");
10488-
}
10489-
10490-
1049110435
/*
1049210436
* appendNamedArgument --
1049310437
*
@@ -10513,17 +10457,17 @@ appendNamedArgument(PQExpBuffer out, Archive *fout, const char *argname,
1051310457
* Append a formatted pg_restore_relation_stats statement.
1051410458
*/
1051510459
static void
10516-
appendRelStatsImport(PQExpBuffer out, Archive *fout, const RelStatsInfo *rsinfo)
10460+
appendRelStatsImport(PQExpBuffer out, Archive *fout, const RelStatsInfo *rsinfo,
10461+
const char *qualified_name)
1051710462
{
10518-
const char *qualname = fmtQualifiedId(rsinfo->dobj.namespace->dobj.name, rsinfo->dobj.name);
1051910463
charreltuples_str[FLOAT_SHORTEST_DECIMAL_LEN];
1052010464

1052110465
float_to_shortest_decimal_buf(rsinfo->reltuples, reltuples_str);
1052210466

1052310467
appendPQExpBufferStr(out, "SELECT * FROM pg_catalog.pg_restore_relation_stats(\n");
10524-
appendPQExpBuffer(out, "\t'relation', '%s'::regclass,\n", qualname);
1052510468
appendPQExpBuffer(out, "\t'version', '%u'::integer,\n",
1052610469
fout->remoteVersion);
10470+
appendPQExpBuffer(out, "\t'relation', '%s'::regclass,\n", qualified_name);
1052710471
appendPQExpBuffer(out, "\t'relpages', '%d'::integer,\n", rsinfo->relpages);
1052810472
appendPQExpBuffer(out, "\t'reltuples', '%s'::real,\n", reltuples_str);
1052910473
appendPQExpBuffer(out, "\t'relallvisible', '%d'::integer\n);\n",
@@ -10536,13 +10480,18 @@ appendRelStatsImport(PQExpBuffer out, Archive *fout, const RelStatsInfo *rsinfo)
1053610480
* Append a series of formatted pg_restore_attribute_stats statements.
1053710481
*/
1053810482
static void
10539-
appendAttStatsImport(PQExpBuffer out, Archive *fout, PGresult *res)
10483+
appendAttStatsImport(PQExpBuffer out, Archive *fout, PGresult *res,
10484+
const char *qualified_name)
1054010485
{
1054110486
for (int rownum = 0; rownum < PQntuples(res); rownum++)
1054210487
{
1054310488
const char *sep = "";
1054410489

1054510490
appendPQExpBufferStr(out, "SELECT * FROM pg_catalog.pg_restore_attribute_stats(\n");
10491+
appendPQExpBuffer(out, "\t'version', '%u'::integer,\n",
10492+
fout->remoteVersion);
10493+
appendPQExpBuffer(out, "\t'relation', '%s'::regclass,\n",
10494+
qualified_name);
1054610495
for (int argno = 0; argno < lengthof(att_stats_arginfo); argno++)
1054710496
{
1054810497
const char *argname = att_stats_arginfo[argno][0];
@@ -10607,6 +10556,7 @@ dumpRelationStats(Archive *fout, const RelStatsInfo *rsinfo)
1060710556
DumpableObject *dobj = (DumpableObject *) &rsinfo->dobj;
1060810557
DumpId *deps = NULL;
1060910558
intndeps = 0;
10559+
const char *qualified_name;
1061010560

1061110561
/* nothing to do if we are not dumping statistics */
1061210562
if (!fout->dopt->dumpStatistics)
@@ -10622,15 +10572,56 @@ dumpRelationStats(Archive *fout, const RelStatsInfo *rsinfo)
1062210572
tag = createPQExpBuffer();
1062310573
appendPQExpBufferStr(tag, fmtId(dobj->name));
1062410574

10625-
out = createPQExpBuffer();
10575+
query = createPQExpBuffer();
10576+
if (!fout->is_prepared[PREPQUERY_GETATTRIBUTESTATS])
10577+
{
10578+
appendPQExpBufferStr(query,
10579+
"PREPARE getAttributeStats(pg_catalog.name, pg_catalog.name) AS\n"
10580+
"SELECT s.attname, s.inherited, "
10581+
"s.null_frac, s.avg_width, s.n_distinct, "
10582+
"s.most_common_vals, s.most_common_freqs, "
10583+
"s.histogram_bounds, s.correlation, "
10584+
"s.most_common_elems, s.most_common_elem_freqs, "
10585+
"s.elem_count_histogram, ");
10586+
10587+
if (fout->remoteVersion >= 170000)
10588+
appendPQExpBufferStr(query,
10589+
"s.range_length_histogram, s.range_empty_frac, "
10590+
"s.range_bounds_histogram ");
10591+
else
10592+
appendPQExpBufferStr(query,
10593+
"NULL AS range_length_histogram,"
10594+
"NULL AS range_empty_frac,"
10595+
"NULL AS range_bounds_histogram ");
1062610596

10627-
appendRelStatsImport(out, fout, rsinfo);
10597+
appendPQExpBufferStr(query,
10598+
"FROM pg_stats s "
10599+
"WHERE s.schemaname = $1 "
10600+
"AND s.tablename = $2 "
10601+
"ORDER BY s.attname, s.inherited");
10602+
10603+
ExecuteSqlStatement(fout, query->data);
10604+
10605+
fout->is_prepared[PREPQUERY_GETATTRIBUTESTATS] = true;
10606+
resetPQExpBuffer(query);
10607+
}
10608+
10609+
appendPQExpBufferStr(query, "EXECUTE getAttributeStats(");
10610+
appendStringLiteralAH(query, dobj->namespace->dobj.name, fout);
10611+
appendPQExpBufferStr(query, ", ");
10612+
appendStringLiteralAH(query, dobj->name, fout);
10613+
appendPQExpBufferStr(query, "); ");
1062810614

10629-
query = createPQExpBuffer();
10630-
getAttStatsExportQuery(query, fout, dobj->namespace->dobj.name,
10631-
dobj->name);
1063210615
res = ExecuteSqlQuery(fout, query->data, PGRES_TUPLES_OK);
10633-
appendAttStatsImport(out, fout, res);
10616+
10617+
out = createPQExpBuffer();
10618+
10619+
qualified_name = fmtQualifiedId(rsinfo->dobj.namespace->dobj.name,
10620+
rsinfo->dobj.name);
10621+
10622+
appendRelStatsImport(out, fout, rsinfo, qualified_name);
10623+
appendAttStatsImport(out, fout, res, qualified_name);
10624+
1063410625
PQclear(res);
1063510626

1063610627
ArchiveEntry(fout, nilCatalogId, createDumpId(),

0 commit comments

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