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

Commit5e09280

Browse files
committed
Make pg_statistic and related code account more honestly for collations.
When we first put in collations support, we basically punted on teachingpg_statistic, ANALYZE, and the planner selectivity functions about that.They've just used DEFAULT_COLLATION_OID independently of the actualcollation of the data. It's time to improve that, so:* Add columns to pg_statistic that record the specific collation associatedwith each statistics slot.* Teach ANALYZE to use the column's actual collation when comparing valuesfor statistical purposes, and record this in the appropriate slot. (Notethat type-specific typanalyze functions are now expected to fillstats->stacoll with the appropriate collation, too.)* Teach assorted selectivity functions to use the actual collation ofthe stats they are looking at, instead of just assuming it'sDEFAULT_COLLATION_OID.This should give noticeably better results in selectivity estimates forcolumns with nondefault collations, at least for query clauses that usethat same collation (which would be the default behavior in most cases).It's still true that comparisons with explicit COLLATE clauses differentfrom the stored data's collation won't be well-estimated, but that's noworse than before. Also, this patch does make the first step towardsdoing better with that, which is that it's now theoretically possible tocollect stats for a collation other than the column's own collation.Patch by me; thanks to Peter Eisentraut for review.Discussion:https://postgr.es/m/14706.1544630227@sss.pgh.pa.us
1 parent8fb569e commit5e09280

File tree

18 files changed

+189
-90
lines changed

18 files changed

+189
-90
lines changed

‎doc/src/sgml/catalogs.sgml

Lines changed: 12 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -6394,6 +6394,18 @@ SCRAM-SHA-256$<replaceable>&lt;iteration count&gt;</replaceable>:<replaceable>&l
63946394
</entry>
63956395
</row>
63966396

6397+
<row>
6398+
<entry><structfield>stacoll<replaceable>N</replaceable></structfield></entry>
6399+
<entry><type>oid</type></entry>
6400+
<entry><literal><link linkend="catalog-pg-collation"><structname>pg_collation</structname></link>.oid</literal></entry>
6401+
<entry>
6402+
The collation used to derive the statistics stored in the
6403+
<replaceable>N</replaceable>th <quote>slot</quote>. For example, a
6404+
histogram slot for a collatable column would show the collation that
6405+
defines the sort order of the data. Zero for noncollatable data.
6406+
</entry>
6407+
</row>
6408+
63976409
<row>
63986410
<entry><structfield>stanumbers<replaceable>N</replaceable></structfield></entry>
63996411
<entry><type>float4[]</type></entry>

‎src/backend/commands/analyze.c

Lines changed: 22 additions & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -904,11 +904,22 @@ examine_attribute(Relation onerel, int attnum, Node *index_expr)
904904
{
905905
stats->attrtypid=exprType(index_expr);
906906
stats->attrtypmod=exprTypmod(index_expr);
907+
908+
/*
909+
* If a collation has been specified for the index column, use that in
910+
* preference to anything else; but if not, fall back to whatever we
911+
* can get from the expression.
912+
*/
913+
if (OidIsValid(onerel->rd_indcollation[attnum-1]))
914+
stats->attrcollid=onerel->rd_indcollation[attnum-1];
915+
else
916+
stats->attrcollid=exprCollation(index_expr);
907917
}
908918
else
909919
{
910920
stats->attrtypid=attr->atttypid;
911921
stats->attrtypmod=attr->atttypmod;
922+
stats->attrcollid=attr->attcollation;
912923
}
913924

914925
typtuple=SearchSysCacheCopy1(TYPEOID,
@@ -1553,6 +1564,11 @@ update_attstats(Oid relid, bool inh, int natts, VacAttrStats **vacattrstats)
15531564
{
15541565
values[i++]=ObjectIdGetDatum(stats->staop[k]);/* staopN */
15551566
}
1567+
i=Anum_pg_statistic_stacoll1-1;
1568+
for (k=0;k<STATISTIC_NUM_SLOTS;k++)
1569+
{
1570+
values[i++]=ObjectIdGetDatum(stats->stacoll[k]);/* stacollN */
1571+
}
15561572
i=Anum_pg_statistic_stanumbers1-1;
15571573
for (k=0;k<STATISTIC_NUM_SLOTS;k++)
15581574
{
@@ -1993,9 +2009,8 @@ compute_distinct_stats(VacAttrStatsP stats,
19932009
firstcount1=track_cnt;
19942010
for (j=0;j<track_cnt;j++)
19952011
{
1996-
/* We always use the default collation for statistics */
19972012
if (DatumGetBool(FunctionCall2Coll(&f_cmpeq,
1998-
DEFAULT_COLLATION_OID,
2013+
stats->attrcollid,
19992014
value,track[j].value)))
20002015
{
20012016
match= true;
@@ -2202,6 +2217,7 @@ compute_distinct_stats(VacAttrStatsP stats,
22022217

22032218
stats->stakind[0]=STATISTIC_KIND_MCV;
22042219
stats->staop[0]=mystats->eqopr;
2220+
stats->stacoll[0]=stats->attrcollid;
22052221
stats->stanumbers[0]=mcv_freqs;
22062222
stats->numnumbers[0]=num_mcv;
22072223
stats->stavalues[0]=mcv_values;
@@ -2273,8 +2289,7 @@ compute_scalar_stats(VacAttrStatsP stats,
22732289

22742290
memset(&ssup,0,sizeof(ssup));
22752291
ssup.ssup_cxt=CurrentMemoryContext;
2276-
/* We always use the default collation for statistics */
2277-
ssup.ssup_collation=DEFAULT_COLLATION_OID;
2292+
ssup.ssup_collation=stats->attrcollid;
22782293
ssup.ssup_nulls_first= false;
22792294

22802295
/*
@@ -2567,6 +2582,7 @@ compute_scalar_stats(VacAttrStatsP stats,
25672582

25682583
stats->stakind[slot_idx]=STATISTIC_KIND_MCV;
25692584
stats->staop[slot_idx]=mystats->eqopr;
2585+
stats->stacoll[slot_idx]=stats->attrcollid;
25702586
stats->stanumbers[slot_idx]=mcv_freqs;
25712587
stats->numnumbers[slot_idx]=num_mcv;
25722588
stats->stavalues[slot_idx]=mcv_values;
@@ -2682,6 +2698,7 @@ compute_scalar_stats(VacAttrStatsP stats,
26822698

26832699
stats->stakind[slot_idx]=STATISTIC_KIND_HISTOGRAM;
26842700
stats->staop[slot_idx]=mystats->ltopr;
2701+
stats->stacoll[slot_idx]=stats->attrcollid;
26852702
stats->stavalues[slot_idx]=hist_values;
26862703
stats->numvalues[slot_idx]=num_hist;
26872704

@@ -2725,6 +2742,7 @@ compute_scalar_stats(VacAttrStatsP stats,
27252742

27262743
stats->stakind[slot_idx]=STATISTIC_KIND_CORRELATION;
27272744
stats->staop[slot_idx]=mystats->ltopr;
2745+
stats->stacoll[slot_idx]=stats->attrcollid;
27282746
stats->stanumbers[slot_idx]=corrs;
27292747
stats->numnumbers[slot_idx]=1;
27302748
slot_idx++;

‎src/backend/statistics/dependencies.c

Lines changed: 4 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -252,6 +252,9 @@ dependency_degree(int numrows, HeapTuple *rows, int k, AttrNumber *dependency,
252252
* (b) split the data into groups by first (k-1) columns
253253
*
254254
* (c) for each group count different values in the last column
255+
*
256+
* We use the column data types' default sort operators and collations;
257+
* perhaps at some point it'd be worth using column-specific collations?
255258
*/
256259

257260
/* prepare the sort function for the first dimension, and SortItem array */
@@ -266,7 +269,7 @@ dependency_degree(int numrows, HeapTuple *rows, int k, AttrNumber *dependency,
266269
colstat->attrtypid);
267270

268271
/* prepare the sort function for this dimension */
269-
multi_sort_add_dimension(mss,i,type->lt_opr);
272+
multi_sort_add_dimension(mss,i,type->lt_opr,type->typcollation);
270273

271274
/* accumulate all the data for both columns into an array and sort it */
272275
for (j=0;j<numrows;j++)

‎src/backend/statistics/extended_stats.c

Lines changed: 4 additions & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -363,18 +363,18 @@ multi_sort_init(int ndims)
363363
}
364364

365365
/*
366-
* Prepare sort support info using the given sort operator
366+
* Prepare sort support info using the given sort operator and collation
367367
* at the position 'sortdim'
368368
*/
369369
void
370-
multi_sort_add_dimension(MultiSortSupportmss,intsortdim,Oidoper)
370+
multi_sort_add_dimension(MultiSortSupportmss,intsortdim,
371+
Oidoper,Oidcollation)
371372
{
372373
SortSupportssup=&mss->ssup[sortdim];
373374

374375
ssup->ssup_cxt=CurrentMemoryContext;
375-
ssup->ssup_collation=DEFAULT_COLLATION_OID;
376+
ssup->ssup_collation=collation;
376377
ssup->ssup_nulls_first= false;
377-
ssup->ssup_cxt=CurrentMemoryContext;
378378

379379
PrepareSortSupportFromOrderingOp(oper,ssup);
380380
}

‎src/backend/statistics/mvdistinct.c

Lines changed: 4 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -454,6 +454,9 @@ ndistinct_for_combination(double totalrows, int numrows, HeapTuple *rows,
454454
/*
455455
* For each dimension, set up sort-support and fill in the values from the
456456
* sample data.
457+
*
458+
* We use the column data types' default sort operators and collations;
459+
* perhaps at some point it'd be worth using column-specific collations?
457460
*/
458461
for (i=0;i<k;i++)
459462
{
@@ -466,7 +469,7 @@ ndistinct_for_combination(double totalrows, int numrows, HeapTuple *rows,
466469
colstat->attrtypid);
467470

468471
/* prepare the sort function for this dimension */
469-
multi_sort_add_dimension(mss,i,type->lt_opr);
472+
multi_sort_add_dimension(mss,i,type->lt_opr,type->typcollation);
470473

471474
/* accumulate all the data for this dimension into the arrays */
472475
for (j=0;j<numrows;j++)

‎src/backend/tsearch/ts_typanalyze.c

Lines changed: 2 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -14,6 +14,7 @@
1414
#include"postgres.h"
1515

1616
#include"access/hash.h"
17+
#include"catalog/pg_collation.h"
1718
#include"catalog/pg_operator.h"
1819
#include"commands/vacuum.h"
1920
#include"tsearch/ts_type.h"
@@ -415,6 +416,7 @@ compute_tsvector_stats(VacAttrStats *stats,
415416

416417
stats->stakind[0]=STATISTIC_KIND_MCELEM;
417418
stats->staop[0]=TextEqualOperator;
419+
stats->stacoll[0]=DEFAULT_COLLATION_OID;
418420
stats->stanumbers[0]=mcelem_freqs;
419421
/* See above comment about two extra frequency fields */
420422
stats->numnumbers[0]=num_mcelem+2;

0 commit comments

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