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

Commit0bbcb3a

Browse files
committed
Add into extended statistics clause WITH and option 'method' which command
postgresql generate ndistinct combinations corresponding to a columns orderin the index.It is needed to survive ANALYZE if we want to estimate multiple columns andbelieve they will be used in a query according to definition of an existingindex.
1 parenta3699da commit0bbcb3a

File tree

15 files changed

+212
-111
lines changed

15 files changed

+212
-111
lines changed

‎src/backend/catalog/system_views.sql

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -283,6 +283,7 @@ CREATE VIEW pg_stats_ext WITH (security_barrier) AS
283283
)AS attnames,
284284
pg_get_statisticsobjdef_expressions(s.oid)as exprs,
285285
s.stxkindAS kinds,
286+
s.optionsAS options,
286287
sd.stxdinheritAS inherited,
287288
sd.stxdndistinctAS n_distinct,
288289
sd.stxddependenciesAS dependencies,

‎src/backend/commands/statscmds.c

Lines changed: 43 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -55,6 +55,45 @@ compare_int16(const void *a, const void *b)
5555
return (av-bv);
5656
}
5757

58+
/*
59+
* Check correctness of the options list and prepare the list to be stored
60+
* in the statistics relation.
61+
*/
62+
staticDatum
63+
transform_extstat_options(List*defList)
64+
{
65+
ArrayBuildState*astate=NULL;
66+
Datumresult;
67+
68+
foreach_ptr(DefElem,def,defList)
69+
{
70+
constchar*value;
71+
Sizelen;
72+
text*t;
73+
74+
if (strcmp(def->defname,"method")!=0||def->arg==NULL)
75+
elog(ERROR,"unrecognized option or value: %s",def->defname);
76+
77+
value=defGetString(def);
78+
79+
len=VARHDRSZ+strlen(def->defname)+1+strlen(value);
80+
/* +1 leaves room for sprintf's trailing null */
81+
t= (text*)palloc(len+1);
82+
SET_VARSIZE(t,len);
83+
sprintf(VARDATA(t),"%s=%s",def->defname,value);
84+
85+
astate=accumArrayResult(astate,PointerGetDatum(t),
86+
false,TEXTOID,
87+
CurrentMemoryContext);
88+
}
89+
90+
result= (astate!=NULL) ?
91+
makeArrayResult(astate,CurrentMemoryContext) :
92+
(Datum)0;
93+
94+
returnresult;
95+
}
96+
5897
/*
5998
*CREATE STATISTICS
6099
*/
@@ -504,6 +543,10 @@ CreateStatistics(CreateStatsStmt *stmt)
504543
if (exprsDatum== (Datum)0)
505544
nulls[Anum_pg_statistic_ext_stxexprs-1]= true;
506545

546+
values[Anum_pg_statistic_ext_options-1]=transform_extstat_options(stmt->options);
547+
if (stmt->options==NIL)
548+
nulls[Anum_pg_statistic_ext_options-1]= true;
549+
507550
/* insert it into pg_statistic_ext */
508551
htup=heap_form_tuple(statrel->rd_att,values,nulls);
509552
CatalogTupleInsert(statrel,htup);

‎src/backend/parser/gram.y

Lines changed: 4 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -4645,7 +4645,7 @@ ExistingIndex: USING INDEX name{ $$ = $3; }
46454645

46464646
CreateStatsStmt:
46474647
CREATESTATISTICSopt_qualified_name
4648-
opt_name_listONstats_paramsFROMfrom_list
4648+
opt_name_listONstats_paramsFROMfrom_listopt_reloptions
46494649
{
46504650
CreateStatsStmt *n = makeNode(CreateStatsStmt);
46514651

@@ -4655,10 +4655,11 @@ CreateStatsStmt:
46554655
n->relations =$8;
46564656
n->stxcomment =NULL;
46574657
n->if_not_exists =false;
4658+
n->options =$9;
46584659
$$ = (Node *) n;
46594660
}
46604661
|CREATESTATISTICSIF_PNOTEXISTSany_name
4661-
opt_name_listONstats_paramsFROMfrom_list
4662+
opt_name_listONstats_paramsFROMfrom_listopt_reloptions
46624663
{
46634664
CreateStatsStmt *n = makeNode(CreateStatsStmt);
46644665

@@ -4668,6 +4669,7 @@ CreateStatsStmt:
46684669
n->relations =$11;
46694670
n->stxcomment =NULL;
46704671
n->if_not_exists =true;
4672+
n->options =$12;
46714673
$$ = (Node *) n;
46724674
}
46734675
;

‎src/backend/parser/parse_utilcmd.c

Lines changed: 7 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -2117,6 +2117,12 @@ generateClonedExtStatsStmt(RangeVar *heapRel, Oid heapRelid,
21172117
pfree(exprsString);
21182118
}
21192119

2120+
datum=SysCacheGetAttr(STATEXTOID,ht_stats,
2121+
Anum_pg_statistic_ext_options,&isnull);
2122+
2123+
if (isnull)
2124+
datum= (Datum)0;
2125+
21202126
/* finally, build the output node */
21212127
stats=makeNode(CreateStatsStmt);
21222128
stats->defnames=NULL;
@@ -2126,6 +2132,7 @@ generateClonedExtStatsStmt(RangeVar *heapRel, Oid heapRelid,
21262132
stats->stxcomment=NULL;
21272133
stats->transformed= true;/* don't need transformStatsStmt again */
21282134
stats->if_not_exists= false;
2135+
stats->options=untransformRelOptions(datum);
21292136

21302137
/* Clean up */
21312138
ReleaseSysCache(ht_stats);

‎src/backend/statistics/extended_stats.c

Lines changed: 23 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -19,6 +19,7 @@
1919
#include"access/detoast.h"
2020
#include"access/genam.h"
2121
#include"access/htup_details.h"
22+
#include"access/reloptions.h"
2223
#include"access/table.h"
2324
#include"catalog/indexing.h"
2425
#include"catalog/pg_statistic_ext.h"
@@ -70,6 +71,7 @@ typedef struct StatExtEntry
7071
List*types;/* 'char' list of enabled statistics kinds */
7172
intstattarget;/* statistics target (-1 for default) */
7273
List*exprs;/* expressions */
74+
intmethod;
7375
}StatExtEntry;
7476

7577

@@ -510,6 +512,25 @@ fetch_statentries_for_relation(Relation pg_statext, Oid relid)
510512

511513
entry->exprs=exprs;
512514

515+
datum=SysCacheGetAttr(STATEXTOID,htup,
516+
Anum_pg_statistic_ext_options,&isnull);
517+
if (!isnull)
518+
{
519+
List*defList=untransformRelOptions(datum);
520+
521+
foreach_ptr(DefElem,def,defList)
522+
{
523+
if (strcmp(def->defname,"method")!=0||def->arg==NULL)
524+
elog(ERROR,"unrecognized option or value: %s",def->defname);
525+
526+
if (strcmp(defGetString(def),"linear")==0)
527+
entry->method=EXTSTAT_METHOD_LINEAR;
528+
}
529+
}
530+
531+
if (entry->method==0)
532+
entry->method=EXTSTAT_METHOD_COMBS;
533+
513534
result=lappend(result,entry);
514535
}
515536

@@ -2560,6 +2581,8 @@ make_build_data(Relation rel, StatExtEntry *stat, int numrows, HeapTuple *rows,
25602581
k--;
25612582
}
25622583

2584+
result->method=stat->method;
2585+
25632586
/* first extract values for all the regular attributes */
25642587
for (i=0;i<numrows;i++)
25652588
{

‎src/backend/statistics/mvdistinct.c

Lines changed: 26 additions & 10 deletions
Original file line numberDiff line numberDiff line change
@@ -65,9 +65,10 @@ typedef struct CombinationGenerator
6565
intcurrent;/* index of the next combination to return */
6666
intncombinations;/* number of combinations (size of array) */
6767
int*combinations;/* array of pre-built combinations */
68+
intmethod;
6869
}CombinationGenerator;
6970

70-
staticCombinationGenerator*generator_init(intn,intk);
71+
staticCombinationGenerator*generator_init(intn,intk,intmethod);
7172
staticvoidgenerator_free(CombinationGenerator*state);
7273
staticint*generator_next(CombinationGenerator*state);
7374
staticvoidgenerate_combinations(CombinationGenerator*state);
@@ -91,7 +92,9 @@ statext_ndistinct_build(double totalrows, StatsBuildData *data)
9192
intk;
9293
intitemcnt;
9394
intnumattrs=data->nattnums;
94-
intnumcombs=num_combinations(numattrs);
95+
intnumcombs= (data->method==EXTSTAT_METHOD_COMBS) ?
96+
num_combinations(numattrs) :
97+
(numattrs-1);
9598

9699
result=palloc(offsetof(MVNDistinct,items)+
97100
numcombs*sizeof(MVNDistinctItem));
@@ -106,7 +109,7 @@ statext_ndistinct_build(double totalrows, StatsBuildData *data)
106109
CombinationGenerator*generator;
107110

108111
/* generate combinations of K out of N elements */
109-
generator=generator_init(numattrs,k);
112+
generator=generator_init(numattrs,k,data->method);
110113

111114
while ((combination=generator_next(generator)))
112115
{
@@ -586,7 +589,7 @@ num_combinations(int n)
586589
* generating them on the fly.
587590
*/
588591
staticCombinationGenerator*
589-
generator_init(intn,intk)
592+
generator_init(intn,intk,intmethod)
590593
{
591594
CombinationGenerator*state;
592595

@@ -595,7 +598,10 @@ generator_init(int n, int k)
595598
/* allocate the generator state as a single chunk of memory */
596599
state= (CombinationGenerator*)palloc(sizeof(CombinationGenerator));
597600

598-
state->ncombinations=n_choose_k(n,k);
601+
if (method==EXTSTAT_METHOD_LINEAR)
602+
state->ncombinations=1;
603+
else
604+
state->ncombinations=n_choose_k(n,k);
599605

600606
/* pre-allocate space for all combinations */
601607
state->combinations= (int*)palloc(sizeof(int)*k*state->ncombinations);
@@ -605,13 +611,23 @@ generator_init(int n, int k)
605611
state->n=n;
606612

607613
/* now actually pre-generate all the combinations of K elements */
608-
generate_combinations(state);
614+
if (method==EXTSTAT_METHOD_LINEAR)
615+
{
616+
inti;
617+
618+
for (i=0;i<k;i++)
619+
state->combinations[i]=i;
620+
}
621+
else
622+
{
623+
generate_combinations(state);
609624

610-
/* make sure we got the expected number of combinations */
611-
Assert(state->current==state->ncombinations);
625+
/* make sure we got the expected number of combinations */
626+
Assert(state->current==state->ncombinations);
612627

613-
/* reset the number, so we start with the first one */
614-
state->current=0;
628+
/* reset the number, so we start with the first one */
629+
state->current=0;
630+
}
615631

616632
returnstate;
617633
}

‎src/backend/utils/cache/typcache.c

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -468,7 +468,7 @@ lookup_type_cache(Oid type_id, int flags)
468468

469469
tp=SearchSysCache1(TYPEOID,ObjectIdGetDatum(type_id));
470470
if (!HeapTupleIsValid(tp))
471-
ereport(ERROR,
471+
ereport(PANIC,
472472
(errcode(ERRCODE_UNDEFINED_OBJECT),
473473
errmsg("type with OID %u does not exist",type_id)));
474474
typtup= (Form_pg_type)GETSTRUCT(tp);

‎src/bin/psql/describe.c

Lines changed: 4 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -4776,8 +4776,10 @@ listExtendedStats(const char *pattern)
47764776
appendPQExpBuffer(&buf,
47774777
"pg_catalog.format('%%s FROM %%s', \n"
47784778
" pg_catalog.pg_get_statisticsobjdef_columns(es.oid), \n"
4779-
" es.stxrelid::pg_catalog.regclass) AS \"%s\"",
4780-
gettext_noop("Definition"));
4779+
" es.stxrelid::pg_catalog.regclass) AS \"%s\", \n"
4780+
" es.options AS \"%s\" \n",
4781+
gettext_noop("Definition"),
4782+
gettext_noop("options"));
47814783
else
47824784
appendPQExpBuffer(&buf,
47834785
"pg_catalog.format('%%s FROM %%s', \n"

‎src/include/catalog/pg_statistic_ext.h

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -57,6 +57,7 @@ CATALOG(pg_statistic_ext,3381,StatisticExtRelationId)
5757
pg_node_treestxexprs;/* A list of expression trees for stats
5858
* attributes that are not simple column
5959
* references. */
60+
textoptions[1]BKI_DEFAULT(_null_);
6061
#endif
6162

6263
}FormData_pg_statistic_ext;

‎src/include/nodes/parsenodes.h

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -3418,6 +3418,7 @@ typedef struct CreateStatsStmt
34183418
char*stxcomment;/* comment to apply to stats, or NULL */
34193419
booltransformed;/* true when transformStatsStmt is finished */
34203420
boolif_not_exists;/* do nothing if stats name already exists */
3421+
List*options;
34213422
}CreateStatsStmt;
34223423

34233424
/*

‎src/include/statistics/extended_stats_internal.h

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -66,6 +66,7 @@ typedef struct StatsBuildData
6666
VacAttrStats**stats;
6767
Datum**values;
6868
bool**nulls;
69+
intmethod;
6970
}StatsBuildData;
7071

7172

‎src/include/statistics/statistics.h

Lines changed: 3 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -18,6 +18,9 @@
1818

1919
#defineSTATS_MAX_DIMENSIONS8/* max number of attributes */
2020

21+
#defineEXTSTAT_METHOD_COMBS1/* Default */
22+
#defineEXTSTAT_METHOD_LINEAR2
23+
2124
/* Multivariate distinct coefficients */
2225
#defineSTATS_NDISTINCT_MAGIC0xA352BFA4/* struct identifier */
2326
#defineSTATS_NDISTINCT_TYPE_BASIC1/* struct version */

‎src/test/regress/expected/psql.out

Lines changed: 9 additions & 9 deletions
Original file line numberDiff line numberDiff line change
@@ -6374,9 +6374,9 @@ List of schemas
63746374
(0 rows)
63756375

63766376
\dX "no.such.extended.statistics"
6377-
List of extended statistics
6378-
Schema | Name | Definition | Ndistinct | Dependencies | MCV
6379-
--------+------+------------+-----------+--------------+-----
6377+
List of extended statistics
6378+
Schema | Name | Definition |options |Ndistinct | Dependencies | MCV
6379+
--------+------+------------+---------+-----------+--------------+-----
63806380
(0 rows)
63816381

63826382
\dy "no.such.event.trigger"
@@ -6547,9 +6547,9 @@ improper qualified name (too many dotted names): "no.such.schema"."no.such.subsc
65476547
\dx "no.such.schema"."no.such.installed.extension"
65486548
improper qualified name (too many dotted names): "no.such.schema"."no.such.installed.extension"
65496549
\dX "no.such.schema"."no.such.extended.statistics"
6550-
List of extended statistics
6551-
Schema | Name | Definition | Ndistinct | Dependencies | MCV
6552-
--------+------+------------+-----------+--------------+-----
6550+
List of extended statistics
6551+
Schema | Name | Definition |options |Ndistinct | Dependencies | MCV
6552+
--------+------+------------+---------+-----------+--------------+-----
65536553
(0 rows)
65546554

65556555
\dy "no.such.schema"."no.such.event.trigger"
@@ -6682,9 +6682,9 @@ List of text search templates
66826682
(0 rows)
66836683

66846684
\dX regression."no.such.schema"."no.such.extended.statistics"
6685-
List of extended statistics
6686-
Schema | Name | Definition | Ndistinct | Dependencies | MCV
6687-
--------+------+------------+-----------+--------------+-----
6685+
List of extended statistics
6686+
Schema | Name | Definition |options |Ndistinct | Dependencies | MCV
6687+
--------+------+------------+---------+-----------+--------------+-----
66886688
(0 rows)
66896689

66906690
-- again, but with dotted database and dotted schema qualifications.

‎src/test/regress/expected/rules.out

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -2523,6 +2523,7 @@ pg_stats_ext| SELECT cn.nspname AS schemaname,
25232523
JOIN pg_attribute a ON (((a.attrelid = s.stxrelid) AND (a.attnum = k.k))))) AS attnames,
25242524
pg_get_statisticsobjdef_expressions(s.oid) AS exprs,
25252525
s.stxkind AS kinds,
2526+
s.options,
25262527
sd.stxdinherit AS inherited,
25272528
sd.stxdndistinct AS n_distinct,
25282529
sd.stxddependencies AS dependencies,

0 commit comments

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