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
Appearance settings

Commit 5e09280

Browse filesBrowse files
tglsfdctglsfdc
committed
Make pg_statistic and related code account more honestly for collations.
When we first put in collations support, we basically punted on teaching pg_statistic, ANALYZE, and the planner selectivity functions about that. They've just used DEFAULT_COLLATION_OID independently of the actual collation of the data. It's time to improve that, so: * Add columns to pg_statistic that record the specific collation associated with each statistics slot. * Teach ANALYZE to use the column's actual collation when comparing values for statistical purposes, and record this in the appropriate slot. (Note that type-specific typanalyze functions are now expected to fill stats->stacoll with the appropriate collation, too.) * Teach assorted selectivity functions to use the actual collation of the stats they are looking at, instead of just assuming it's DEFAULT_COLLATION_OID. This should give noticeably better results in selectivity estimates for columns with nondefault collations, at least for query clauses that use that same collation (which would be the default behavior in most cases). It's still true that comparisons with explicit COLLATE clauses different from the stored data's collation won't be well-estimated, but that's no worse than before. Also, this patch does make the first step towards doing better with that, which is that it's now theoretically possible to collect 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 parent 8fb569e commit 5e09280
Copy full SHA for 5e09280

18 files changed

+189
-90
lines changed

‎doc/src/sgml/catalogs.sgml

Copy file name to clipboardExpand all lines: doc/src/sgml/catalogs.sgml
+12Lines 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

Copy file name to clipboardExpand all lines: src/backend/commands/analyze.c
+22-4Lines 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

Copy file name to clipboardExpand all lines: src/backend/statistics/dependencies.c
+4-1Lines 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

Copy file name to clipboardExpand all lines: src/backend/statistics/extended_stats.c
+4-4Lines 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(MultiSortSupport mss, int sortdim, Oid oper)
370+
multi_sort_add_dimension(MultiSortSupport mss, int sortdim,
371+
Oid oper, Oid collation)
371372
{
372373
SortSupport ssup = &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

Copy file name to clipboardExpand all lines: src/backend/statistics/mvdistinct.c
+4-1Lines 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

Copy file name to clipboardExpand all lines: src/backend/tsearch/ts_typanalyze.c
+2Lines 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 (0)
Morty Proxy This is a proxified and sanitized view of the page, visit original site.