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

Commit6cbfb78

Browse files
committed
Rework the pg_statistic_ext catalog
Since extended statistic got introduced in PostgreSQL 10, there was asingle catalog pg_statistic_ext storing both the definitions and builtstatistic. That's however problematic when a user is supposed to haveaccess only to the definitions, but not to user data.Consider for example pg_dump on a database with RLS enabled - if thepg_statistic_ext catalog respects RLS (which it should, if it containsuser data), pg_dump would not see any records and the result would notdefine any extended statistics. That would be a surprising behavior.Until now this was not a pressing issue, because the existing types ofextended statistic (functional dependencies and ndistinct coefficients)do not include any user data directly. This changed with introductionof MCV lists, which do include most common combinations of values.The easiest way to fix this is to split the pg_statistic_ext cataloginto two - one for definitions, one for the built statistic values.The new catalog is called pg_statistic_ext_data, and we're maintaininga 1:1 relationship with the old catalog - either there are matchingrecords in both catalogs, or neither of them.Bumped CATVERSION due to changing system catalog definitions.Author: Dean Rasheed, with improvements by meReviewed-by: Dean Rasheed, John NaylorDiscussion:https://postgr.es/m/CAEZATCUhT9rt7Ui%3DVdx4N%3D%3DVV5XOK5dsXfnGgVOz_JhAicB%3DZA%40mail.gmail.com
1 parente3846a0 commit6cbfb78

File tree

25 files changed

+337
-118
lines changed

25 files changed

+337
-118
lines changed

‎doc/src/sgml/catalogs.sgml

Lines changed: 59 additions & 11 deletions
Original file line numberDiff line numberDiff line change
@@ -297,7 +297,12 @@
297297

298298
<row>
299299
<entry><link linkend="catalog-pg-statistic-ext"><structname>pg_statistic_ext</structname></link></entry>
300-
<entry>extended planner statistics</entry>
300+
<entry>extended planner statistics (definition)</entry>
301+
</row>
302+
303+
<row>
304+
<entry><link linkend="catalog-pg-statistic-ext-data"><structname>pg_statistic_ext_data</structname></link></entry>
305+
<entry>extended planner statistics (built statistics)</entry>
301306
</row>
302307

303308
<row>
@@ -6506,7 +6511,7 @@ SCRAM-SHA-256$<replaceable>&lt;iteration count&gt;</replaceable>:<replaceable>&l
65066511

65076512
<para>
65086513
The catalog <structname>pg_statistic_ext</structname>
6509-
holds extended planner statistics.
6514+
holdsdefinitions ofextended planner statistics.
65106515
Each row in this catalog corresponds to a <firstterm>statistics object</firstterm>
65116516
created with <xref linkend="sql-createstatistics"/>.
65126517
</para>
@@ -6581,8 +6586,57 @@ SCRAM-SHA-256$<replaceable>&lt;iteration count&gt;</replaceable>:<replaceable>&l
65816586
</entry>
65826587
</row>
65836588

6589+
</tbody>
6590+
</tgroup>
6591+
</table>
6592+
6593+
<para>
6594+
The <structfield>stxkind</structfield> field is filled at creation of the
6595+
statistics object, indicating which statistic type(s) are desired. The
6596+
statistics (once computed by <command>ANALYZE</command>) are stored in
6597+
<link linkend="catalog-pg-statistic-ext-data"><structname>pg_statistic_ext_data</structname></link>
6598+
catalog.
6599+
</para>
6600+
</sect1>
6601+
6602+
<sect1 id="catalog-pg-statistic-ext-data">
6603+
<title><structname>pg_statistic_ext_data</structname></title>
6604+
6605+
<indexterm zone="catalog-pg-statistic-ext">
6606+
<primary>pg_statistic_ext_data</primary>
6607+
</indexterm>
6608+
6609+
<para>
6610+
The catalog <structname>pg_statistic_ext_data</structname>
6611+
holds data for extended planner statistics defined in <structname>pg_statistic_ext</structname>.
6612+
Each row in this catalog corresponds to a <firstterm>statistics object</firstterm>
6613+
created with <xref linkend="sql-createstatistics"/>.
6614+
</para>
6615+
6616+
<table>
6617+
<title><structname>pg_statistic_ext_data</structname> Columns</title>
6618+
6619+
<tgroup cols="4">
6620+
<thead>
6621+
<row>
6622+
<entry>Name</entry>
6623+
<entry>Type</entry>
6624+
<entry>References</entry>
6625+
<entry>Description</entry>
6626+
</row>
6627+
</thead>
6628+
6629+
<tbody>
6630+
6631+
<row>
6632+
<entry><structfield>stxoid</structfield></entry>
6633+
<entry><type>oid</type></entry>
6634+
<entry><literal><link linkend="catalog-pg-statistic-ext"><structname>pg_statistic_ext</structname></link>.oid</literal></entry>
6635+
<entry>Extended statistic containing the definition for this data.</entry>
6636+
</row>
6637+
65846638
<row>
6585-
<entry><structfield>stxndistinct</structfield></entry>
6639+
<entry><structfield>stxdndistinct</structfield></entry>
65866640
<entry><type>pg_ndistinct</type></entry>
65876641
<entry></entry>
65886642
<entry>
@@ -6591,7 +6645,7 @@ SCRAM-SHA-256$<replaceable>&lt;iteration count&gt;</replaceable>:<replaceable>&l
65916645
</row>
65926646

65936647
<row>
6594-
<entry><structfield>stxdependencies</structfield></entry>
6648+
<entry><structfield>stxddependencies</structfield></entry>
65956649
<entry><type>pg_dependencies</type></entry>
65966650
<entry></entry>
65976651
<entry>
@@ -6601,7 +6655,7 @@ SCRAM-SHA-256$<replaceable>&lt;iteration count&gt;</replaceable>:<replaceable>&l
66016655
</row>
66026656

66036657
<row>
6604-
<entry><structfield>stxmcv</structfield></entry>
6658+
<entry><structfield>stxdmcv</structfield></entry>
66056659
<entry><type>pg_mcv_list</type></entry>
66066660
<entry></entry>
66076661
<entry>
@@ -6614,12 +6668,6 @@ SCRAM-SHA-256$<replaceable>&lt;iteration count&gt;</replaceable>:<replaceable>&l
66146668
</tgroup>
66156669
</table>
66166670

6617-
<para>
6618-
The <structfield>stxkind</structfield> field is filled at creation of the
6619-
statistics object, indicating which statistic type(s) are desired.
6620-
The fields after it are initially NULL and are filled only when the
6621-
corresponding statistic has been computed by <command>ANALYZE</command>.
6622-
</para>
66236671
</sect1>
66246672

66256673
<sect1 id="catalog-pg-subscription">

‎doc/src/sgml/func.sgml

Lines changed: 3 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -22427,12 +22427,12 @@ CREATE EVENT TRIGGER test_table_rewrite_oid
2242722427
The <function>pg_mcv_list_items</function> function can be used like this:
2242822428

2242922429
<programlisting>
22430-
SELECT m.* FROM pg_statistic_ext,
22431-
pg_mcv_list_items(stxmcv) m WHERE stxname = 'stts';
22430+
SELECT m.* FROM pg_statistic_ext join pg_statistic_ext_data on (oid = stxoid),
22431+
pg_mcv_list_items(stxdmcv) m WHERE stxname = 'stts';
2243222432
</programlisting>
2243322433

2243422434
Values of the <type>pg_mcv_list</type> can be obtained only from the
22435-
<literal>pg_statistic_ext.stxmcv</literal> column.
22435+
<literal>pg_statistic_ext_data.stxdmcv</literal> column.
2243622436
</para>
2243722437
</sect2>
2243822438

‎doc/src/sgml/perform.sgml

Lines changed: 12 additions & 8 deletions
Original file line numberDiff line numberDiff line change
@@ -1076,6 +1076,10 @@ WHERE tablename = 'road';
10761076
<primary>pg_statistic_ext</primary>
10771077
</indexterm>
10781078

1079+
<indexterm>
1080+
<primary>pg_statistic_ext_data</primary>
1081+
</indexterm>
1082+
10791083
<para>
10801084
It is common to see slow queries running bad execution plans because
10811085
multiple columns used in the query clauses are correlated.
@@ -1104,7 +1108,7 @@ WHERE tablename = 'road';
11041108
interest in the statistics. Actual data collection is performed
11051109
by <command>ANALYZE</command> (either a manual command, or background
11061110
auto-analyze). The collected values can be examined in the
1107-
<link linkend="catalog-pg-statistic-ext"><structname>pg_statistic_ext</structname></link>
1111+
<link linkend="catalog-pg-statistic-ext-data"><structname>pg_statistic_ext_data</structname></link>
11081112
catalog.
11091113
</para>
11101114

@@ -1172,10 +1176,10 @@ CREATE STATISTICS stts (dependencies) ON zip, city FROM zipcodes;
11721176

11731177
ANALYZE zipcodes;
11741178

1175-
SELECT stxname, stxkeys,stxdependencies
1176-
FROM pg_statistic_ext
1179+
SELECT stxname, stxkeys,stxddependencies
1180+
FROM pg_statistic_ext join pg_statistic_ext_data on (oid = stxoid)
11771181
WHERE stxname = 'stts';
1178-
stxname | stxkeys |stxdependencies
1182+
stxname | stxkeys |stxddependencies
11791183
---------+---------+------------------------------------------
11801184
stts | 1 5 | {"1 => 5": 1.000000, "5 => 1": 0.423130}
11811185
(1 row)
@@ -1262,8 +1266,8 @@ CREATE STATISTICS stts2 (ndistinct) ON zip, state, city FROM zipcodes;
12621266

12631267
ANALYZE zipcodes;
12641268

1265-
SELECT stxkeys AS k,stxndistinct AS nd
1266-
FROM pg_statistic_ext
1269+
SELECT stxkeys AS k,stxdndistinct AS nd
1270+
FROM pg_statistic_ext join pg_statistic_ext_data on (oid = stxoid)
12671271
WHERE stxname = 'stts2';
12681272
-[ RECORD 1 ]--------------------------------------------------------
12691273
k | 1 2 5
@@ -1317,8 +1321,8 @@ CREATE STATISTICS stts3 (mcv) ON state, city FROM zipcodes;
13171321

13181322
ANALYZE zipcodes;
13191323

1320-
SELECT m.* FROM pg_statistic_ext,
1321-
pg_mcv_list_items(stxmcv) m WHERE stxname = 'stts3';
1324+
SELECT m.* FROM pg_statistic_ext join pg_statistic_ext_data on (oid = stxoid),
1325+
pg_mcv_list_items(stxdmcv) m WHERE stxname = 'stts3';
13221326

13231327
index | values | nulls | frequency | base_frequency
13241328
-------+------------------------+-------+-----------+----------------

‎doc/src/sgml/planstats.sgml

Lines changed: 2 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -635,8 +635,8 @@ EXPLAIN (ANALYZE, TIMING OFF) SELECT * FROM t WHERE a = 1 AND b = 1;
635635
<function>pg_mcv_list_items</function> set-returning function.
636636

637637
<programlisting>
638-
SELECT m.* FROM pg_statistic_ext,
639-
pg_mcv_list_items(stxmcv) m WHERE stxname = 'stts2';
638+
SELECT m.* FROM pg_statistic_ext join pg_statistic_ext_data on (oid = stxoid),
639+
pg_mcv_list_items(stxdmcv) m WHERE stxname = 'stts2';
640640
index | values | nulls | frequency | base_frequency
641641
-------+----------+-------+-----------+----------------
642642
0 | {0, 0} | {f,f} | 0.01 | 0.0001

‎src/backend/catalog/Makefile

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -34,7 +34,7 @@ CATALOG_HEADERS := \
3434
pg_attrdef.h pg_constraint.h pg_inherits.h pg_index.h pg_operator.h\
3535
pg_opfamily.h pg_opclass.h pg_am.h pg_amop.h pg_amproc.h\
3636
pg_language.h pg_largeobject_metadata.h pg_largeobject.h pg_aggregate.h\
37-
pg_statistic_ext.h\
37+
pg_statistic_ext.hpg_statistic_ext_data.h\
3838
pg_statistic.h pg_rewrite.h pg_trigger.h pg_event_trigger.h pg_description.h\
3939
pg_cast.h pg_enum.h pg_namespace.h pg_conversion.h pg_depend.h\
4040
pg_database.h pg_db_role_setting.h pg_tablespace.h pg_pltemplate.h\

‎src/backend/commands/statscmds.c

Lines changed: 56 additions & 17 deletions
Original file line numberDiff line numberDiff line change
@@ -23,6 +23,7 @@
2323
#include"catalog/namespace.h"
2424
#include"catalog/pg_namespace.h"
2525
#include"catalog/pg_statistic_ext.h"
26+
#include"catalog/pg_statistic_ext_data.h"
2627
#include"commands/comment.h"
2728
#include"commands/defrem.h"
2829
#include"miscadmin.h"
@@ -67,8 +68,11 @@ CreateStatistics(CreateStatsStmt *stmt)
6768
HeapTuplehtup;
6869
Datumvalues[Natts_pg_statistic_ext];
6970
boolnulls[Natts_pg_statistic_ext];
71+
Datumdatavalues[Natts_pg_statistic_ext_data];
72+
booldatanulls[Natts_pg_statistic_ext_data];
7073
int2vector*stxkeys;
7174
Relationstatrel;
75+
Relationdatarel;
7276
Relationrel=NULL;
7377
Oidrelid;
7478
ObjectAddressparentobject,
@@ -336,18 +340,36 @@ CreateStatistics(CreateStatsStmt *stmt)
336340
values[Anum_pg_statistic_ext_stxkeys-1]=PointerGetDatum(stxkeys);
337341
values[Anum_pg_statistic_ext_stxkind-1]=PointerGetDatum(stxkind);
338342

339-
/* no statistics built yet */
340-
nulls[Anum_pg_statistic_ext_stxndistinct-1]= true;
341-
nulls[Anum_pg_statistic_ext_stxdependencies-1]= true;
342-
nulls[Anum_pg_statistic_ext_stxmcv-1]= true;
343-
344343
/* insert it into pg_statistic_ext */
345344
htup=heap_form_tuple(statrel->rd_att,values,nulls);
346345
CatalogTupleInsert(statrel,htup);
347346
heap_freetuple(htup);
348347

349348
relation_close(statrel,RowExclusiveLock);
350349

350+
/*
351+
* Also build the pg_statistic_ext_data tuple, to hold the actual
352+
* statistics data.
353+
*/
354+
datarel=table_open(StatisticExtDataRelationId,RowExclusiveLock);
355+
356+
memset(datavalues,0,sizeof(datavalues));
357+
memset(datanulls, false,sizeof(datanulls));
358+
359+
datavalues[Anum_pg_statistic_ext_data_stxoid-1]=ObjectIdGetDatum(statoid);
360+
361+
/* no statistics built yet */
362+
datanulls[Anum_pg_statistic_ext_data_stxdndistinct-1]= true;
363+
datanulls[Anum_pg_statistic_ext_data_stxddependencies-1]= true;
364+
datanulls[Anum_pg_statistic_ext_data_stxdmcv-1]= true;
365+
366+
/* insert it into pg_statistic_ext_data */
367+
htup=heap_form_tuple(datarel->rd_att,datavalues,datanulls);
368+
CatalogTupleInsert(datarel,htup);
369+
heap_freetuple(htup);
370+
371+
relation_close(datarel,RowExclusiveLock);
372+
351373
/*
352374
* Invalidate relcache so that others see the new statistics object.
353375
*/
@@ -403,6 +425,23 @@ RemoveStatisticsById(Oid statsOid)
403425
Form_pg_statistic_extstatext;
404426
Oidrelid;
405427

428+
/*
429+
* First delete the pg_statistic_ext_data tuple holding the actual
430+
* statistical data.
431+
*/
432+
relation=table_open(StatisticExtDataRelationId,RowExclusiveLock);
433+
434+
tup=SearchSysCache1(STATEXTDATASTXOID,ObjectIdGetDatum(statsOid));
435+
436+
if (!HeapTupleIsValid(tup))/* should not happen */
437+
elog(ERROR,"cache lookup failed for statistics data %u",statsOid);
438+
439+
CatalogTupleDelete(relation,&tup->t_self);
440+
441+
ReleaseSysCache(tup);
442+
443+
table_close(relation,RowExclusiveLock);
444+
406445
/*
407446
* Delete the pg_statistic_ext tuple. Also send out a cache inval on the
408447
* associated table, so that dependent plans will be rebuilt.
@@ -431,8 +470,8 @@ RemoveStatisticsById(Oid statsOid)
431470
*
432471
* This could throw an error if the type change can't be supported.
433472
* If it can be supported, but the stats must be recomputed, a likely choice
434-
* would be to set the relevant column(s) of thepg_statistic_ext tuple to
435-
* null until the next ANALYZE. (Note that the type change hasn't actually
473+
* would be to set the relevant column(s) of thepg_statistic_ext_data tuple
474+
*tonull until the next ANALYZE. (Note that the type change hasn't actually
436475
* happened yet, so one option that's *not* on the table is to recompute
437476
* immediately.)
438477
*
@@ -456,11 +495,11 @@ UpdateStatisticsForTypeChange(Oid statsOid, Oid relationOid, int attnum,
456495

457496
Relationrel;
458497

459-
Datumvalues[Natts_pg_statistic_ext];
460-
boolnulls[Natts_pg_statistic_ext];
461-
boolreplaces[Natts_pg_statistic_ext];
498+
Datumvalues[Natts_pg_statistic_ext_data];
499+
boolnulls[Natts_pg_statistic_ext_data];
500+
boolreplaces[Natts_pg_statistic_ext_data];
462501

463-
oldtup=SearchSysCache1(STATEXTOID,ObjectIdGetDatum(statsOid));
502+
oldtup=SearchSysCache1(STATEXTDATASTXOID,ObjectIdGetDatum(statsOid));
464503
if (!HeapTupleIsValid(oldtup))
465504
elog(ERROR,"cache lookup failed for statistics object %u",statsOid);
466505

@@ -479,14 +518,14 @@ UpdateStatisticsForTypeChange(Oid statsOid, Oid relationOid, int attnum,
479518
* OK, we need to reset some statistics. So let's build the new tuple,
480519
* replacing the affected statistics types with NULL.
481520
*/
482-
memset(nulls,0,Natts_pg_statistic_ext*sizeof(bool));
483-
memset(replaces,0,Natts_pg_statistic_ext*sizeof(bool));
484-
memset(values,0,Natts_pg_statistic_ext*sizeof(Datum));
521+
memset(nulls,0,Natts_pg_statistic_ext_data*sizeof(bool));
522+
memset(replaces,0,Natts_pg_statistic_ext_data*sizeof(bool));
523+
memset(values,0,Natts_pg_statistic_ext_data*sizeof(Datum));
485524

486-
replaces[Anum_pg_statistic_ext_stxmcv-1]= true;
487-
nulls[Anum_pg_statistic_ext_stxmcv-1]= true;
525+
replaces[Anum_pg_statistic_ext_data_stxdmcv-1]= true;
526+
nulls[Anum_pg_statistic_ext_data_stxdmcv-1]= true;
488527

489-
rel=heap_open(StatisticExtRelationId,RowExclusiveLock);
528+
rel=heap_open(StatisticExtDataRelationId,RowExclusiveLock);
490529

491530
/* replace the old tuple */
492531
stup=heap_modify_tuple(oldtup,

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

Lines changed: 9 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -1308,6 +1308,7 @@ get_relation_statistics(RelOptInfo *rel, Relation relation)
13081308
OidstatOid=lfirst_oid(l);
13091309
Form_pg_statistic_extstaForm;
13101310
HeapTuplehtup;
1311+
HeapTupledtup;
13111312
Bitmapset*keys=NULL;
13121313
inti;
13131314

@@ -1316,6 +1317,10 @@ get_relation_statistics(RelOptInfo *rel, Relation relation)
13161317
elog(ERROR,"cache lookup failed for statistics object %u",statOid);
13171318
staForm= (Form_pg_statistic_ext)GETSTRUCT(htup);
13181319

1320+
dtup=SearchSysCache1(STATEXTDATASTXOID,ObjectIdGetDatum(statOid));
1321+
if (!HeapTupleIsValid(dtup))
1322+
elog(ERROR,"cache lookup failed for statistics object %u",statOid);
1323+
13191324
/*
13201325
* First, build the array of columns covered. This is ultimately
13211326
* wasted if no stats within the object have actually been built, but
@@ -1325,7 +1330,7 @@ get_relation_statistics(RelOptInfo *rel, Relation relation)
13251330
keys=bms_add_member(keys,staForm->stxkeys.values[i]);
13261331

13271332
/* add one StatisticExtInfo for each kind built */
1328-
if (statext_is_kind_built(htup,STATS_EXT_NDISTINCT))
1333+
if (statext_is_kind_built(dtup,STATS_EXT_NDISTINCT))
13291334
{
13301335
StatisticExtInfo*info=makeNode(StatisticExtInfo);
13311336

@@ -1337,7 +1342,7 @@ get_relation_statistics(RelOptInfo *rel, Relation relation)
13371342
stainfos=lcons(info,stainfos);
13381343
}
13391344

1340-
if (statext_is_kind_built(htup,STATS_EXT_DEPENDENCIES))
1345+
if (statext_is_kind_built(dtup,STATS_EXT_DEPENDENCIES))
13411346
{
13421347
StatisticExtInfo*info=makeNode(StatisticExtInfo);
13431348

@@ -1349,7 +1354,7 @@ get_relation_statistics(RelOptInfo *rel, Relation relation)
13491354
stainfos=lcons(info,stainfos);
13501355
}
13511356

1352-
if (statext_is_kind_built(htup,STATS_EXT_MCV))
1357+
if (statext_is_kind_built(dtup,STATS_EXT_MCV))
13531358
{
13541359
StatisticExtInfo*info=makeNode(StatisticExtInfo);
13551360

@@ -1362,6 +1367,7 @@ get_relation_statistics(RelOptInfo *rel, Relation relation)
13621367
}
13631368

13641369
ReleaseSysCache(htup);
1370+
ReleaseSysCache(dtup);
13651371
bms_free(keys);
13661372
}
13671373

0 commit comments

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