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

Commitd06215d

Browse files
committed
Allow setting statistics target for extended statistics
When building statistics, we need to decide how many rows to sample andhow accurate the resulting statistics should be. Until now, it was notpossible to explicitly define statistics target for extended statisticsobjects, the value was always computed from the per-attribute targetswith a fallback to the system-wide default statistics target.That's a bit inconvenient, as it ties together the statistics target setfor per-column and extended statistics. In some cases it may be usefulto require larger sample / higher accuracy for extended statics (or theother way around), but with this approach that's not possible.So this commit introduces a new command, allowing to specify statisticstarget for individual extended statistics objects, overriding the valuederived from per-attribute targets (and the system default). ALTER STATISTICS stat_name SET STATISTICS target_value;When determining statistics target for an extended statistics object wefirst look at this explicitly set value. When this value is -1, we fallback to the old formula, looking at the per-attribute targets first andthen the system default. This means the behavior is backwards compatiblewith older PostgreSQL releases.Author: Tomas VondraDiscussion:https://postgr.es/m/20190618213357.vli3i23vpkset2xd@developmentReviewed-by: Kirk Jamison, Dean Rasheed
1 parentbca6e64 commitd06215d

File tree

22 files changed

+458
-19
lines changed

22 files changed

+458
-19
lines changed

‎doc/src/sgml/ref/alter_statistics.sgml

Lines changed: 17 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -26,6 +26,7 @@ PostgreSQL documentation
2626
ALTER STATISTICS <replaceable class="parameter">name</replaceable> OWNER TO { <replaceable class="parameter">new_owner</replaceable> | CURRENT_USER | SESSION_USER }
2727
ALTER STATISTICS <replaceable class="parameter">name</replaceable> RENAME TO <replaceable class="parameter">new_name</replaceable>
2828
ALTER STATISTICS <replaceable class="parameter">name</replaceable> SET SCHEMA <replaceable class="parameter">new_schema</replaceable>
29+
ALTER STATISTICS <replaceable class="parameter">name</replaceable> SET STATISTICS <replaceable class="parameter">new_target</replaceable>
2930
</synopsis>
3031
</refsynopsisdiv>
3132

@@ -93,6 +94,22 @@ ALTER STATISTICS <replaceable class="parameter">name</replaceable> SET SCHEMA <r
9394
</listitem>
9495
</varlistentry>
9596

97+
<varlistentry>
98+
<term><replaceable class="parameter">new_target</replaceable></term>
99+
<listitem>
100+
<para>
101+
The statistic-gathering target for this statistics object for subsequent
102+
<xref linkend="sql-analyze"/> operations.
103+
The target can be set in the range 0 to 10000; alternatively, set it
104+
to -1 to revert to using the system default statistics
105+
target (<xref linkend="guc-default-statistics-target"/>).
106+
For more information on the use of statistics by the
107+
<productname>PostgreSQL</productname> query planner, refer to
108+
<xref linkend="planner-stats"/>.
109+
</para>
110+
</listitem>
111+
</varlistentry>
112+
96113
</variablelist>
97114
</para>
98115
</refsect1>

‎src/backend/commands/analyze.c

Lines changed: 12 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -307,7 +307,8 @@ do_analyze_rel(Relation onerel, VacuumParams *params,
307307
VacAttrStats**vacattrstats;
308308
AnlIndexData*indexdata;
309309
inttargrows,
310-
numrows;
310+
numrows,
311+
minrows;
311312
doubletotalrows,
312313
totaldeadrows;
313314
HeapTuple*rows;
@@ -491,6 +492,16 @@ do_analyze_rel(Relation onerel, VacuumParams *params,
491492
}
492493
}
493494

495+
/*
496+
* Look at extended statistics objects too, as those may define custom
497+
* statistics target. So we may need to sample more rows and then build
498+
* the statistics with enough detail.
499+
*/
500+
minrows=ComputeExtStatisticsRows(onerel,attr_cnt,vacattrstats);
501+
502+
if (targrows<minrows)
503+
targrows=minrows;
504+
494505
/*
495506
* Acquire the sample rows
496507
*/

‎src/backend/commands/statscmds.c

Lines changed: 108 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -14,13 +14,15 @@
1414
*/
1515
#include"postgres.h"
1616

17+
#include"access/heapam.h"
1718
#include"access/relation.h"
1819
#include"access/relscan.h"
1920
#include"access/table.h"
2021
#include"catalog/catalog.h"
2122
#include"catalog/dependency.h"
2223
#include"catalog/indexing.h"
2324
#include"catalog/namespace.h"
25+
#include"catalog/objectaccess.h"
2426
#include"catalog/pg_namespace.h"
2527
#include"catalog/pg_statistic_ext.h"
2628
#include"catalog/pg_statistic_ext_data.h"
@@ -29,6 +31,7 @@
2931
#include"miscadmin.h"
3032
#include"statistics/statistics.h"
3133
#include"utils/builtins.h"
34+
#include"utils/fmgroids.h"
3235
#include"utils/inval.h"
3336
#include"utils/memutils.h"
3437
#include"utils/rel.h"
@@ -336,6 +339,7 @@ CreateStatistics(CreateStatsStmt *stmt)
336339
values[Anum_pg_statistic_ext_stxrelid-1]=ObjectIdGetDatum(relid);
337340
values[Anum_pg_statistic_ext_stxname-1]=NameGetDatum(&stxname);
338341
values[Anum_pg_statistic_ext_stxnamespace-1]=ObjectIdGetDatum(namespaceId);
342+
values[Anum_pg_statistic_ext_stxstattarget-1]=Int32GetDatum(-1);
339343
values[Anum_pg_statistic_ext_stxowner-1]=ObjectIdGetDatum(stxowner);
340344
values[Anum_pg_statistic_ext_stxkeys-1]=PointerGetDatum(stxkeys);
341345
values[Anum_pg_statistic_ext_stxkind-1]=PointerGetDatum(stxkind);
@@ -414,6 +418,110 @@ CreateStatistics(CreateStatsStmt *stmt)
414418
returnmyself;
415419
}
416420

421+
/*
422+
*ALTER STATISTICS
423+
*/
424+
ObjectAddress
425+
AlterStatistics(AlterStatsStmt*stmt)
426+
{
427+
Relationrel;
428+
Oidstxoid;
429+
HeapTupleoldtup;
430+
HeapTuplenewtup;
431+
Datumrepl_val[Natts_pg_statistic_ext];
432+
boolrepl_null[Natts_pg_statistic_ext];
433+
boolrepl_repl[Natts_pg_statistic_ext];
434+
ObjectAddressaddress;
435+
intnewtarget=stmt->stxstattarget;
436+
437+
/* Limit statistics target to a sane range */
438+
if (newtarget<-1)
439+
{
440+
ereport(ERROR,
441+
(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
442+
errmsg("statistics target %d is too low",
443+
newtarget)));
444+
}
445+
elseif (newtarget>10000)
446+
{
447+
newtarget=10000;
448+
ereport(WARNING,
449+
(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
450+
errmsg("lowering statistics target to %d",
451+
newtarget)));
452+
}
453+
454+
/* lookup OID of the statistics object */
455+
stxoid=get_statistics_object_oid(stmt->defnames,stmt->missing_ok);
456+
457+
/*
458+
* If we got here and the OID is not valid, it means the statistics
459+
* does not exist, but the command specified IF EXISTS. So report
460+
* this as a simple NOTICE and we're done.
461+
*/
462+
if (!OidIsValid(stxoid))
463+
{
464+
char*schemaname;
465+
char*statname;
466+
467+
Assert(stmt->missing_ok);
468+
469+
DeconstructQualifiedName(stmt->defnames,&schemaname,&statname);
470+
471+
if (schemaname)
472+
ereport(NOTICE,
473+
(errmsg("statistics object \"%s.%s\" does not exist, skipping",
474+
schemaname,statname)));
475+
else
476+
ereport(NOTICE,
477+
(errmsg("statistics object \"%s\" does not exist, skipping",
478+
statname)));
479+
480+
returnInvalidObjectAddress;
481+
}
482+
483+
/* Search pg_statistic_ext */
484+
rel=table_open(StatisticExtRelationId,RowExclusiveLock);
485+
486+
oldtup=SearchSysCache1(STATEXTOID,ObjectIdGetDatum(stxoid));
487+
488+
/* Must be owner of the existing statistics object */
489+
if (!pg_statistics_object_ownercheck(stxoid,GetUserId()))
490+
aclcheck_error(ACLCHECK_NOT_OWNER,OBJECT_STATISTIC_EXT,
491+
NameListToString(stmt->defnames));
492+
493+
/* Build new tuple. */
494+
memset(repl_val,0,sizeof(repl_val));
495+
memset(repl_null, false,sizeof(repl_null));
496+
memset(repl_repl, false,sizeof(repl_repl));
497+
498+
/* replace the stxstattarget column */
499+
repl_repl[Anum_pg_statistic_ext_stxstattarget-1]= true;
500+
repl_val[Anum_pg_statistic_ext_stxstattarget-1]=Int32GetDatum(newtarget);
501+
502+
newtup=heap_modify_tuple(oldtup,RelationGetDescr(rel),
503+
repl_val,repl_null,repl_repl);
504+
505+
/* Update system catalog. */
506+
CatalogTupleUpdate(rel,&newtup->t_self,newtup);
507+
508+
InvokeObjectPostAlterHook(StatisticExtRelationId,stxoid,0);
509+
510+
ObjectAddressSet(address,StatisticExtRelationId,stxoid);
511+
512+
/*
513+
* NOTE: because we only support altering the statistics target, not the
514+
* other fields, there is no need to update dependencies.
515+
*/
516+
517+
heap_freetuple(newtup);
518+
ReleaseSysCache(oldtup);
519+
520+
table_close(rel,RowExclusiveLock);
521+
522+
returnaddress;
523+
}
524+
417525
/*
418526
* Guts of statistics object deletion.
419527
*/

‎src/backend/nodes/copyfuncs.c

Lines changed: 15 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -3497,6 +3497,18 @@ _copyCreateStatsStmt(const CreateStatsStmt *from)
34973497
returnnewnode;
34983498
}
34993499

3500+
staticAlterStatsStmt*
3501+
_copyAlterStatsStmt(constAlterStatsStmt*from)
3502+
{
3503+
AlterStatsStmt*newnode=makeNode(AlterStatsStmt);
3504+
3505+
COPY_NODE_FIELD(defnames);
3506+
COPY_SCALAR_FIELD(stxstattarget);
3507+
COPY_SCALAR_FIELD(missing_ok);
3508+
3509+
returnnewnode;
3510+
}
3511+
35003512
staticCreateFunctionStmt*
35013513
_copyCreateFunctionStmt(constCreateFunctionStmt*from)
35023514
{
@@ -5211,6 +5223,9 @@ copyObjectImpl(const void *from)
52115223
caseT_CreateStatsStmt:
52125224
retval=_copyCreateStatsStmt(from);
52135225
break;
5226+
caseT_AlterStatsStmt:
5227+
retval=_copyAlterStatsStmt(from);
5228+
break;
52145229
caseT_CreateFunctionStmt:
52155230
retval=_copyCreateFunctionStmt(from);
52165231
break;

‎src/backend/nodes/equalfuncs.c

Lines changed: 13 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1365,6 +1365,16 @@ _equalCreateStatsStmt(const CreateStatsStmt *a, const CreateStatsStmt *b)
13651365
return true;
13661366
}
13671367

1368+
staticbool
1369+
_equalAlterStatsStmt(constAlterStatsStmt*a,constAlterStatsStmt*b)
1370+
{
1371+
COMPARE_NODE_FIELD(defnames);
1372+
COMPARE_SCALAR_FIELD(stxstattarget);
1373+
COMPARE_SCALAR_FIELD(missing_ok);
1374+
1375+
return true;
1376+
}
1377+
13681378
staticbool
13691379
_equalCreateFunctionStmt(constCreateFunctionStmt*a,constCreateFunctionStmt*b)
13701380
{
@@ -3309,6 +3319,9 @@ equal(const void *a, const void *b)
33093319
caseT_CreateStatsStmt:
33103320
retval=_equalCreateStatsStmt(a,b);
33113321
break;
3322+
caseT_AlterStatsStmt:
3323+
retval=_equalAlterStatsStmt(a,b);
3324+
break;
33123325
caseT_CreateFunctionStmt:
33133326
retval=_equalCreateFunctionStmt(a,b);
33143327
break;

‎src/backend/nodes/outfuncs.c

Lines changed: 13 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -2668,6 +2668,16 @@ _outCreateStatsStmt(StringInfo str, const CreateStatsStmt *node)
26682668
WRITE_BOOL_FIELD(if_not_exists);
26692669
}
26702670

2671+
staticvoid
2672+
_outAlterStatsStmt(StringInfostr,constAlterStatsStmt*node)
2673+
{
2674+
WRITE_NODE_TYPE("ALTERSTATSSTMT");
2675+
2676+
WRITE_NODE_FIELD(defnames);
2677+
WRITE_INT_FIELD(stxstattarget);
2678+
WRITE_BOOL_FIELD(missing_ok);
2679+
}
2680+
26712681
staticvoid
26722682
_outNotifyStmt(StringInfostr,constNotifyStmt*node)
26732683
{
@@ -4130,6 +4140,9 @@ outNode(StringInfo str, const void *obj)
41304140
caseT_CreateStatsStmt:
41314141
_outCreateStatsStmt(str,obj);
41324142
break;
4143+
caseT_AlterStatsStmt:
4144+
_outAlterStatsStmt(str,obj);
4145+
break;
41334146
caseT_NotifyStmt:
41344147
_outNotifyStmt(str,obj);
41354148
break;

‎src/backend/parser/gram.y

Lines changed: 30 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -252,7 +252,7 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query);
252252
AlterOperatorStmtAlterSeqStmtAlterSystemStmtAlterTableStmt
253253
AlterTblSpcStmtAlterExtensionStmtAlterExtensionContentsStmtAlterForeignTableStmt
254254
AlterCompositeTypeStmtAlterUserMappingStmt
255-
AlterRoleStmtAlterRoleSetStmtAlterPolicyStmt
255+
AlterRoleStmtAlterRoleSetStmtAlterPolicyStmtAlterStatsStmt
256256
AlterDefaultPrivilegesStmtDefACLAction
257257
AnalyzeStmtCallStmtClosePortalStmtClusterStmtCommentStmt
258258
ConstraintsSetStmtCopyStmtCreateAsStmtCreateCastStmt
@@ -852,6 +852,7 @@ stmt :
852852
|AlterRoleSetStmt
853853
|AlterRoleStmt
854854
|AlterSubscriptionStmt
855+
|AlterStatsStmt
855856
|AlterTSConfigurationStmt
856857
|AlterTSDictionaryStmt
857858
|AlterUserMappingStmt
@@ -3984,6 +3985,34 @@ CreateStatsStmt:
39843985
}
39853986
;
39863987

3988+
3989+
/*****************************************************************************
3990+
*
3991+
*QUERY :
3992+
*ALTER STATISTICS [IF EXISTS] stats_name
3993+
*SET STATISTICS <SignedIconst>
3994+
*
3995+
*****************************************************************************/
3996+
3997+
AlterStatsStmt:
3998+
ALTERSTATISTICSany_nameSETSTATISTICSSignedIconst
3999+
{
4000+
AlterStatsStmt *n = makeNode(AlterStatsStmt);
4001+
n->defnames =$3;
4002+
n->missing_ok =false;
4003+
n->stxstattarget =$6;
4004+
$$ = (Node *)n;
4005+
}
4006+
|ALTERSTATISTICSIF_PEXISTSany_nameSETSTATISTICSSignedIconst
4007+
{
4008+
AlterStatsStmt *n = makeNode(AlterStatsStmt);
4009+
n->defnames =$5;
4010+
n->missing_ok =true;
4011+
n->stxstattarget =$8;
4012+
$$ = (Node *)n;
4013+
}
4014+
;
4015+
39874016
/*****************************************************************************
39884017
*
39894018
*QUERY :

0 commit comments

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