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

Commit269b532

Browse files
committed
Add stxdinherit flag to pg_statistic_ext_data
Add pg_statistic_ext_data.stxdinherit flag, so that for each extendedstatistics definition we can store two versions of data - one for therelation alone, one for the whole inheritance tree. This is analogous topg_statistic.stainherit, but we failed to include such flag in catalogsfor extended statistics, and we had to work around it (see commits859b300,36c4bc6 and20b9fa3).This changes the relationship between the two catalogs storing extendedstatistics objects (pg_statistic_ext and pg_statistic_ext_data). Untilnow, there was a simple 1:1 mapping - for each definition there was onepg_statistic_ext_data row, and this row was inserted while creating thestatistics (and then updated during ANALYZE). With the stxdinherit flag,we don't know how many rows there will be (child relations may be addedafter the statistics object is defined), so there may be up to two rows.We could make CREATE STATISTICS to always create both rows, but thatseems wasteful - without partitioning we only need stxdinherit=falserows, and declaratively partitioned tables need only stxdinherit=true.So we no longer initialize pg_statistic_ext_data in CREATE STATISTICS,and instead make that a responsibility of ANALYZE. Which is what we dofor regular statistics too.Patch by me, with extensive improvements and fixes by Justin Pryzby.Author: Tomas Vondra, Justin PryzbyReviewed-by: Tomas Vondra, Justin PryzbyDiscussion:https://postgr.es/m/20210923212624.GI831%40telsasoft.com
1 parente701bdd commit269b532

File tree

19 files changed

+252
-237
lines changed

19 files changed

+252
-237
lines changed

‎doc/src/sgml/catalogs.sgml

Lines changed: 23 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -7521,6 +7521,19 @@ SCRAM-SHA-256$<replaceable>&lt;iteration count&gt;</replaceable>:<replaceable>&l
75217521
created with <link linkend="sql-createstatistics"><command>CREATE STATISTICS</command></link>.
75227522
</para>
75237523

7524+
<para>
7525+
Normally there is one entry, with <structfield>stxdinherit</structfield> =
7526+
<literal>false</literal>, for each statistics object that has been analyzed.
7527+
If the table has inheritance children, a second entry with
7528+
<structfield>stxdinherit</structfield> = <literal>true</literal> is also created.
7529+
This row represents the statistics object over the inheritance tree, i.e.,
7530+
statistics for the data you'd see with
7531+
<literal>SELECT * FROM <replaceable>table</replaceable>*</literal>,
7532+
whereas the <structfield>stxdinherit</structfield> = <literal>false</literal> row
7533+
represents the results of
7534+
<literal>SELECT * FROM ONLY <replaceable>table</replaceable></literal>.
7535+
</para>
7536+
75247537
<para>
75257538
Like <link linkend="catalog-pg-statistic"><structname>pg_statistic</structname></link>,
75267539
<structname>pg_statistic_ext_data</structname> should not be
@@ -7560,6 +7573,16 @@ SCRAM-SHA-256$<replaceable>&lt;iteration count&gt;</replaceable>:<replaceable>&l
75607573
</para></entry>
75617574
</row>
75627575

7576+
<row>
7577+
<entry role="catalog_table_entry"><para role="column_definition">
7578+
<structfield>stxdinherit</structfield> <type>bool</type>
7579+
</para>
7580+
<para>
7581+
If true, the stats include inheritance child columns, not just the
7582+
values in the specified relation
7583+
</para></entry>
7584+
</row>
7585+
75637586
<row>
75647587
<entry role="catalog_table_entry"><para role="column_definition">
75657588
<structfield>stxdndistinct</structfield> <type>pg_ndistinct</type>

‎src/backend/catalog/system_views.sql

Lines changed: 2 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -266,6 +266,7 @@ CREATE VIEW pg_stats_ext WITH (security_barrier) AS
266266
)AS attnames,
267267
pg_get_statisticsobjdef_expressions(s.oid)as exprs,
268268
s.stxkindAS kinds,
269+
sd.stxdinheritAS inherited,
269270
sd.stxdndistinctAS n_distinct,
270271
sd.stxddependenciesAS dependencies,
271272
m.most_common_vals,
@@ -298,6 +299,7 @@ CREATE VIEW pg_stats_ext_exprs WITH (security_barrier) AS
298299
s.stxnameAS statistics_name,
299300
pg_get_userbyid(s.stxowner)AS statistics_owner,
300301
stat.expr,
302+
sd.stxdinheritAS inherited,
301303
(stat.a).stanullfracAS null_frac,
302304
(stat.a).stawidthAS avg_width,
303305
(stat.a).stadistinctAS n_distinct,

‎src/backend/commands/analyze.c

Lines changed: 3 additions & 25 deletions
Original file line numberDiff line numberDiff line change
@@ -549,7 +549,6 @@ do_analyze_rel(Relation onerel, VacuumParams *params,
549549
{
550550
MemoryContextcol_context,
551551
old_context;
552-
boolbuild_ext_stats;
553552

554553
pgstat_progress_update_param(PROGRESS_ANALYZE_PHASE,
555554
PROGRESS_ANALYZE_PHASE_COMPUTE_STATS);
@@ -613,30 +612,9 @@ do_analyze_rel(Relation onerel, VacuumParams *params,
613612
thisdata->attr_cnt,thisdata->vacattrstats);
614613
}
615614

616-
/*
617-
* Should we build extended statistics for this relation?
618-
*
619-
* The extended statistics catalog does not include an inheritance
620-
* flag, so we can't store statistics built both with and without
621-
* data from child relations. We can store just one set of statistics
622-
* per relation. For plain relations that's fine, but for inheritance
623-
* trees we have to pick whether to store statistics for just the
624-
* one relation or the whole tree. For plain inheritance we store
625-
* the (!inh) version, mostly for backwards compatibility reasons.
626-
* For partitioned tables that's pointless (the non-leaf tables are
627-
* always empty), so we store stats representing the whole tree.
628-
*/
629-
build_ext_stats= (onerel->rd_rel->relkind==RELKIND_PARTITIONED_TABLE) ?inh : (!inh);
630-
631-
/*
632-
* Build extended statistics (if there are any).
633-
*
634-
* For now we only build extended statistics on individual relations,
635-
* not for relations representing inheritance trees.
636-
*/
637-
if (build_ext_stats)
638-
BuildRelationExtStatistics(onerel,totalrows,numrows,rows,
639-
attr_cnt,vacattrstats);
615+
/* Build extended statistics (if there are any). */
616+
BuildRelationExtStatistics(onerel,inh,totalrows,numrows,rows,
617+
attr_cnt,vacattrstats);
640618
}
641619

642620
pgstat_progress_update_param(PROGRESS_ANALYZE_PHASE,

‎src/backend/commands/statscmds.c

Lines changed: 34 additions & 38 deletions
Original file line numberDiff line numberDiff line change
@@ -75,13 +75,10 @@ CreateStatistics(CreateStatsStmt *stmt)
7575
HeapTuplehtup;
7676
Datumvalues[Natts_pg_statistic_ext];
7777
boolnulls[Natts_pg_statistic_ext];
78-
Datumdatavalues[Natts_pg_statistic_ext_data];
79-
booldatanulls[Natts_pg_statistic_ext_data];
8078
int2vector*stxkeys;
8179
List*stxexprs=NIL;
8280
DatumexprsDatum;
8381
Relationstatrel;
84-
Relationdatarel;
8582
Relationrel=NULL;
8683
Oidrelid;
8784
ObjectAddressparentobject,
@@ -514,28 +511,10 @@ CreateStatistics(CreateStatsStmt *stmt)
514511
relation_close(statrel,RowExclusiveLock);
515512

516513
/*
517-
* Also build the pg_statistic_ext_data tuple, to hold the actual
518-
* statistics data.
514+
* We used to create the pg_statistic_ext_data tuple too, but it's not clear
515+
* what value should the stxdinherit flag have (it depends on whether the rel
516+
* is partitioned, contains data, etc.)
519517
*/
520-
datarel=table_open(StatisticExtDataRelationId,RowExclusiveLock);
521-
522-
memset(datavalues,0,sizeof(datavalues));
523-
memset(datanulls, false,sizeof(datanulls));
524-
525-
datavalues[Anum_pg_statistic_ext_data_stxoid-1]=ObjectIdGetDatum(statoid);
526-
527-
/* no statistics built yet */
528-
datanulls[Anum_pg_statistic_ext_data_stxdndistinct-1]= true;
529-
datanulls[Anum_pg_statistic_ext_data_stxddependencies-1]= true;
530-
datanulls[Anum_pg_statistic_ext_data_stxdmcv-1]= true;
531-
datanulls[Anum_pg_statistic_ext_data_stxdexpr-1]= true;
532-
533-
/* insert it into pg_statistic_ext_data */
534-
htup=heap_form_tuple(datarel->rd_att,datavalues,datanulls);
535-
CatalogTupleInsert(datarel,htup);
536-
heap_freetuple(htup);
537-
538-
relation_close(datarel,RowExclusiveLock);
539518

540519
InvokeObjectPostCreateHook(StatisticExtRelationId,statoid,0);
541520

@@ -717,32 +696,49 @@ AlterStatistics(AlterStatsStmt *stmt)
717696
}
718697

719698
/*
720-
* Guts of statistics object deletion.
699+
* Delete entry in pg_statistic_ext_data catalog. We don't know if the row
700+
* exists, so don't error out.
721701
*/
722702
void
723-
RemoveStatisticsById(OidstatsOid)
703+
RemoveStatisticsDataById(OidstatsOid,boolinh)
724704
{
725705
Relationrelation;
726706
HeapTupletup;
727-
Form_pg_statistic_extstatext;
728-
Oidrelid;
729707

730-
/*
731-
* First delete the pg_statistic_ext_data tuple holding the actual
732-
* statistical data.
733-
*/
734708
relation=table_open(StatisticExtDataRelationId,RowExclusiveLock);
735709

736-
tup=SearchSysCache1(STATEXTDATASTXOID,ObjectIdGetDatum(statsOid));
737-
738-
if (!HeapTupleIsValid(tup))/* should not happen */
739-
elog(ERROR,"cache lookup failed for statistics data %u",statsOid);
710+
tup=SearchSysCache2(STATEXTDATASTXOID,ObjectIdGetDatum(statsOid),
711+
BoolGetDatum(inh));
740712

741-
CatalogTupleDelete(relation,&tup->t_self);
713+
/* We don't know if the data row for inh value exists. */
714+
if (HeapTupleIsValid(tup))
715+
{
716+
CatalogTupleDelete(relation,&tup->t_self);
742717

743-
ReleaseSysCache(tup);
718+
ReleaseSysCache(tup);
719+
}
744720

745721
table_close(relation,RowExclusiveLock);
722+
}
723+
724+
/*
725+
* Guts of statistics object deletion.
726+
*/
727+
void
728+
RemoveStatisticsById(OidstatsOid)
729+
{
730+
Relationrelation;
731+
HeapTupletup;
732+
Form_pg_statistic_extstatext;
733+
Oidrelid;
734+
735+
/*
736+
* First delete the pg_statistic_ext_data tuples holding the actual
737+
* statistical data. There might be data with/without inheritance, so
738+
* attempt deleting both.
739+
*/
740+
RemoveStatisticsDataById(statsOid, true);
741+
RemoveStatisticsDataById(statsOid, false);
746742

747743
/*
748744
* Delete the pg_statistic_ext tuple. Also send out a cache inval on the

‎src/backend/optimizer/util/plancat.c

Lines changed: 90 additions & 56 deletions
Original file line numberDiff line numberDiff line change
@@ -30,6 +30,7 @@
3030
#include"catalog/pg_am.h"
3131
#include"catalog/pg_proc.h"
3232
#include"catalog/pg_statistic_ext.h"
33+
#include"catalog/pg_statistic_ext_data.h"
3334
#include"foreign/fdwapi.h"
3435
#include"miscadmin.h"
3536
#include"nodes/makefuncs.h"
@@ -1276,6 +1277,87 @@ get_relation_constraints(PlannerInfo *root,
12761277
returnresult;
12771278
}
12781279

1280+
/*
1281+
* Try loading data for the statistics object.
1282+
*
1283+
* We don't know if the data (specified by statOid and inh value) exist.
1284+
* The result is stored in stainfos list.
1285+
*/
1286+
staticvoid
1287+
get_relation_statistics_worker(List**stainfos,RelOptInfo*rel,
1288+
OidstatOid,boolinh,
1289+
Bitmapset*keys,List*exprs)
1290+
{
1291+
Form_pg_statistic_ext_datadataForm;
1292+
HeapTupledtup;
1293+
1294+
dtup=SearchSysCache2(STATEXTDATASTXOID,
1295+
ObjectIdGetDatum(statOid),BoolGetDatum(inh));
1296+
if (!HeapTupleIsValid(dtup))
1297+
return;
1298+
1299+
dataForm= (Form_pg_statistic_ext_data)GETSTRUCT(dtup);
1300+
1301+
/* add one StatisticExtInfo for each kind built */
1302+
if (statext_is_kind_built(dtup,STATS_EXT_NDISTINCT))
1303+
{
1304+
StatisticExtInfo*info=makeNode(StatisticExtInfo);
1305+
1306+
info->statOid=statOid;
1307+
info->inherit=dataForm->stxdinherit;
1308+
info->rel=rel;
1309+
info->kind=STATS_EXT_NDISTINCT;
1310+
info->keys=bms_copy(keys);
1311+
info->exprs=exprs;
1312+
1313+
*stainfos=lappend(*stainfos,info);
1314+
}
1315+
1316+
if (statext_is_kind_built(dtup,STATS_EXT_DEPENDENCIES))
1317+
{
1318+
StatisticExtInfo*info=makeNode(StatisticExtInfo);
1319+
1320+
info->statOid=statOid;
1321+
info->inherit=dataForm->stxdinherit;
1322+
info->rel=rel;
1323+
info->kind=STATS_EXT_DEPENDENCIES;
1324+
info->keys=bms_copy(keys);
1325+
info->exprs=exprs;
1326+
1327+
*stainfos=lappend(*stainfos,info);
1328+
}
1329+
1330+
if (statext_is_kind_built(dtup,STATS_EXT_MCV))
1331+
{
1332+
StatisticExtInfo*info=makeNode(StatisticExtInfo);
1333+
1334+
info->statOid=statOid;
1335+
info->inherit=dataForm->stxdinherit;
1336+
info->rel=rel;
1337+
info->kind=STATS_EXT_MCV;
1338+
info->keys=bms_copy(keys);
1339+
info->exprs=exprs;
1340+
1341+
*stainfos=lappend(*stainfos,info);
1342+
}
1343+
1344+
if (statext_is_kind_built(dtup,STATS_EXT_EXPRESSIONS))
1345+
{
1346+
StatisticExtInfo*info=makeNode(StatisticExtInfo);
1347+
1348+
info->statOid=statOid;
1349+
info->inherit=dataForm->stxdinherit;
1350+
info->rel=rel;
1351+
info->kind=STATS_EXT_EXPRESSIONS;
1352+
info->keys=bms_copy(keys);
1353+
info->exprs=exprs;
1354+
1355+
*stainfos=lappend(*stainfos,info);
1356+
}
1357+
1358+
ReleaseSysCache(dtup);
1359+
}
1360+
12791361
/*
12801362
* get_relation_statistics
12811363
*Retrieve extended statistics defined on the table.
@@ -1299,7 +1381,6 @@ get_relation_statistics(RelOptInfo *rel, Relation relation)
12991381
OidstatOid=lfirst_oid(l);
13001382
Form_pg_statistic_extstaForm;
13011383
HeapTuplehtup;
1302-
HeapTupledtup;
13031384
Bitmapset*keys=NULL;
13041385
List*exprs=NIL;
13051386
inti;
@@ -1309,10 +1390,6 @@ get_relation_statistics(RelOptInfo *rel, Relation relation)
13091390
elog(ERROR,"cache lookup failed for statistics object %u",statOid);
13101391
staForm= (Form_pg_statistic_ext)GETSTRUCT(htup);
13111392

1312-
dtup=SearchSysCache1(STATEXTDATASTXOID,ObjectIdGetDatum(statOid));
1313-
if (!HeapTupleIsValid(dtup))
1314-
elog(ERROR,"cache lookup failed for statistics object %u",statOid);
1315-
13161393
/*
13171394
* First, build the array of columns covered. This is ultimately
13181395
* wasted if no stats within the object have actually been built, but
@@ -1324,6 +1401,11 @@ get_relation_statistics(RelOptInfo *rel, Relation relation)
13241401
/*
13251402
* Preprocess expressions (if any). We read the expressions, run them
13261403
* through eval_const_expressions, and fix the varnos.
1404+
*
1405+
* XXX We don't know yet if there are any data for this stats object,
1406+
* with either stxdinherit value. But it's reasonable to assume there
1407+
* is at least one of those, possibly both. So it's better to process
1408+
* keys and expressions here.
13271409
*/
13281410
{
13291411
boolisnull;
@@ -1364,61 +1446,13 @@ get_relation_statistics(RelOptInfo *rel, Relation relation)
13641446
}
13651447
}
13661448

1367-
/* add one StatisticExtInfo for each kind built */
1368-
if (statext_is_kind_built(dtup,STATS_EXT_NDISTINCT))
1369-
{
1370-
StatisticExtInfo*info=makeNode(StatisticExtInfo);
1371-
1372-
info->statOid=statOid;
1373-
info->rel=rel;
1374-
info->kind=STATS_EXT_NDISTINCT;
1375-
info->keys=bms_copy(keys);
1376-
info->exprs=exprs;
1377-
1378-
stainfos=lappend(stainfos,info);
1379-
}
1380-
1381-
if (statext_is_kind_built(dtup,STATS_EXT_DEPENDENCIES))
1382-
{
1383-
StatisticExtInfo*info=makeNode(StatisticExtInfo);
1449+
/* extract statistics for possible values of stxdinherit flag */
13841450

1385-
info->statOid=statOid;
1386-
info->rel=rel;
1387-
info->kind=STATS_EXT_DEPENDENCIES;
1388-
info->keys=bms_copy(keys);
1389-
info->exprs=exprs;
1451+
get_relation_statistics_worker(&stainfos,rel,statOid, true,keys,exprs);
13901452

1391-
stainfos=lappend(stainfos,info);
1392-
}
1393-
1394-
if (statext_is_kind_built(dtup,STATS_EXT_MCV))
1395-
{
1396-
StatisticExtInfo*info=makeNode(StatisticExtInfo);
1397-
1398-
info->statOid=statOid;
1399-
info->rel=rel;
1400-
info->kind=STATS_EXT_MCV;
1401-
info->keys=bms_copy(keys);
1402-
info->exprs=exprs;
1403-
1404-
stainfos=lappend(stainfos,info);
1405-
}
1406-
1407-
if (statext_is_kind_built(dtup,STATS_EXT_EXPRESSIONS))
1408-
{
1409-
StatisticExtInfo*info=makeNode(StatisticExtInfo);
1410-
1411-
info->statOid=statOid;
1412-
info->rel=rel;
1413-
info->kind=STATS_EXT_EXPRESSIONS;
1414-
info->keys=bms_copy(keys);
1415-
info->exprs=exprs;
1416-
1417-
stainfos=lappend(stainfos,info);
1418-
}
1453+
get_relation_statistics_worker(&stainfos,rel,statOid, false,keys,exprs);
14191454

14201455
ReleaseSysCache(htup);
1421-
ReleaseSysCache(dtup);
14221456
bms_free(keys);
14231457
}
14241458

0 commit comments

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