agora inbox for [email protected]help / color / mirror / Atom feed
[PATCH 5/5] WIP unify handling of attributes and expressions 9+ messages / 2 participants [nested] [flat]
* [PATCH 5/5] WIP unify handling of attributes and expressions @ 2021-03-04 03:53 Tomas Vondra <[email protected]> 0 siblings, 0 replies; 9+ messages in thread From: Tomas Vondra @ 2021-03-04 03:53 UTC (permalink / raw) --- src/backend/statistics/dependencies.c | 82 ++------ src/backend/statistics/extended_stats.c | 180 +++++++++++------- src/backend/statistics/mcv.c | 89 ++------- src/backend/statistics/mvdistinct.c | 138 +++----------- .../statistics/extended_stats_internal.h | 40 ++-- 5 files changed, 183 insertions(+), 346 deletions(-) diff --git a/src/backend/statistics/dependencies.c b/src/backend/statistics/dependencies.c index 602301b724..14d6503e1a 100644 --- a/src/backend/statistics/dependencies.c +++ b/src/backend/statistics/dependencies.c @@ -70,10 +70,7 @@ static void generate_dependencies(DependencyGenerator state); static DependencyGenerator DependencyGenerator_init(int n, int k); static void DependencyGenerator_free(DependencyGenerator state); static AttrNumber *DependencyGenerator_next(DependencyGenerator state); -static double dependency_degree(int numrows, HeapTuple *rows, - ExprInfo *exprs, int k, - AttrNumber *dependency, VacAttrStats **stats, - Bitmapset *attrs); +static double dependency_degree(StatBuildData *data, int k, AttrNumber *dependency); static bool dependency_is_fully_matched(MVDependency *dependency, Bitmapset *attnums); static bool dependency_is_compatible_clause(Node *clause, Index relid, @@ -222,17 +219,13 @@ DependencyGenerator_next(DependencyGenerator state) * the last one. */ static double -dependency_degree(int numrows, HeapTuple *rows, ExprInfo *exprs, int k, - AttrNumber *dependency, VacAttrStats **stats, - Bitmapset *attrs) +dependency_degree(StatBuildData *data, int k, AttrNumber *dependency) { int i, nitems; MultiSortSupport mss; SortItem *items; - AttrNumber *attnums; AttrNumber *attnums_dep; - int numattrs; /* counters valid within a group */ int group_size = 0; @@ -247,16 +240,9 @@ dependency_degree(int numrows, HeapTuple *rows, ExprInfo *exprs, int k, /* sort info for all attributes columns */ mss = multi_sort_init(k); - /* - * Transform the attrs from bitmap to an array to make accessing the i-th - * member easier, and then construct a filtered version with only attnums - * referenced by the dependency we validate. - */ - attnums = build_attnums_array(attrs, exprs->nexprs, &numattrs); - attnums_dep = (AttrNumber *) palloc(k * sizeof(AttrNumber)); for (i = 0; i < k; i++) - attnums_dep[i] = attnums[dependency[i]]; + attnums_dep[i] = data->attnums[dependency[i]]; /* * Verify the dependency (a,b,...)->z, using a rather simple algorithm: @@ -274,7 +260,7 @@ dependency_degree(int numrows, HeapTuple *rows, ExprInfo *exprs, int k, /* prepare the sort function for the dimensions */ for (i = 0; i < k; i++) { - VacAttrStats *colstat = stats[dependency[i]]; + VacAttrStats *colstat = data->stats[dependency[i]]; TypeCacheEntry *type; type = lookup_type_cache(colstat->attrtypid, TYPECACHE_LT_OPR); @@ -293,8 +279,7 @@ dependency_degree(int numrows, HeapTuple *rows, ExprInfo *exprs, int k, * descriptor. For now that assumption holds, but it might change in the * future for example if we support statistics on multiple tables. */ - items = build_sorted_items(numrows, &nitems, rows, exprs, - stats[0]->tupDesc, mss, k, attnums_dep); + items = build_sorted_items(data, &nitems, mss, k, attnums_dep); /* * Walk through the sorted array, split it into rows according to the @@ -340,11 +325,10 @@ dependency_degree(int numrows, HeapTuple *rows, ExprInfo *exprs, int k, pfree(items); pfree(mss); - pfree(attnums); pfree(attnums_dep); /* Compute the 'degree of validity' as (supporting/total). */ - return (n_supporting_rows * 1.0 / numrows); + return (n_supporting_rows * 1.0 / data->numrows); } /* @@ -364,54 +348,15 @@ dependency_degree(int numrows, HeapTuple *rows, ExprInfo *exprs, int k, * (c) -> b */ MVDependencies * -statext_dependencies_build(int numrows, HeapTuple *rows, - ExprInfo *exprs, Bitmapset *attrs, - VacAttrStats **stats) +statext_dependencies_build(StatBuildData *data) { int i, k; - int numattrs; - AttrNumber *attnums; - int nattnums; /* result */ MVDependencies *dependencies = NULL; - /* - * Transform the bms into an array of attnums, to make accessing i-th - * member easier. We add the expressions first, represented by negative - * attnums (this is OK, we don't allow statistics on system attributes), - * and then regular attributes. - */ - nattnums = bms_num_members(attrs) + exprs->nexprs; - attnums = (AttrNumber *) palloc(sizeof(AttrNumber) * nattnums); - - numattrs = 0; - - /* treat expressions as attributes with negative attnums */ - for (i = 0; i < exprs->nexprs; i++) - attnums[numattrs++] = -(i+1); - - /* - * and then regular attributes - * - * XXX Maybe add this in the opposite order, just like in MCV? first - * regular attnums, then exressions. - */ - k = -1; - while ((k = bms_next_member(attrs, k)) >= 0) - attnums[numattrs++] = k; - - Assert(numattrs >= 2); - Assert(numattrs == nattnums); - - /* - * Build a new bitmapset of attnums, offset by number of expressions (this - * is needed, because bitmaps can store only non-negative values). - */ - attrs = NULL; - for (i = 0; i < numattrs; i++) - attrs = bms_add_member(attrs, attnums[i] + exprs->nexprs); + Assert(data->nattnums >= 2); /* * We'll try build functional dependencies starting from the smallest ones @@ -419,12 +364,12 @@ statext_dependencies_build(int numrows, HeapTuple *rows, * included in the statistics object. We start from the smallest ones * because we want to be able to skip already implied ones. */ - for (k = 2; k <= numattrs; k++) + for (k = 2; k <= data->nattnums; k++) { AttrNumber *dependency; /* array with k elements */ /* prepare a DependencyGenerator of variation */ - DependencyGenerator DependencyGenerator = DependencyGenerator_init(numattrs, k); + DependencyGenerator DependencyGenerator = DependencyGenerator_init(data->nattnums, k); /* generate all possible variations of k values (out of n) */ while ((dependency = DependencyGenerator_next(DependencyGenerator))) @@ -433,8 +378,7 @@ statext_dependencies_build(int numrows, HeapTuple *rows, MVDependency *d; /* compute how valid the dependency seems */ - degree = dependency_degree(numrows, rows, exprs, k, dependency, - stats, attrs); + degree = dependency_degree(data, k, dependency); /* * if the dependency seems entirely invalid, don't store it @@ -449,7 +393,7 @@ statext_dependencies_build(int numrows, HeapTuple *rows, d->degree = degree; d->nattributes = k; for (i = 0; i < k; i++) - d->attributes[i] = attnums[dependency[i]]; + d->attributes[i] = data->attnums[dependency[i]]; /* initialize the list of dependencies */ if (dependencies == NULL) @@ -477,8 +421,6 @@ statext_dependencies_build(int numrows, HeapTuple *rows, DependencyGenerator_free(DependencyGenerator); } - pfree(attrs); - return dependencies; } diff --git a/src/backend/statistics/extended_stats.c b/src/backend/statistics/extended_stats.c index 95b2cc683e..5b3fa523e9 100644 --- a/src/backend/statistics/extended_stats.c +++ b/src/backend/statistics/extended_stats.c @@ -96,8 +96,11 @@ static Datum serialize_expr_stats(AnlExprData *exprdata, int nexprs); static Datum expr_fetch_func(VacAttrStatsP stats, int rownum, bool *isNull); static AnlExprData *build_expr_data(List *exprs); static VacAttrStats *examine_expression(Node *expr); -static ExprInfo *evaluate_expressions(Relation rel, List *exprs, - int numrows, HeapTuple *rows); + +static StatBuildData *make_build_data(Relation onerel, StatExtEntry *stat, + int numrows, HeapTuple *rows, + VacAttrStats **stats); + /* * Compute requested extended stats, using the rows sampled for the plain @@ -156,7 +159,7 @@ BuildRelationExtStatistics(Relation onerel, double totalrows, VacAttrStats **stats; ListCell *lc2; int stattarget; - ExprInfo *exprs; + StatBuildData *data; /* * Check if we can build these stats based on the column analyzed. If @@ -191,7 +194,7 @@ BuildRelationExtStatistics(Relation onerel, double totalrows, continue; /* evaluate expressions (if the statistics has any) */ - exprs = evaluate_expressions(onerel, stat->exprs, numrows, rows); + data = make_build_data(onerel, stat, numrows, rows, stats); /* compute statistic of each requested type */ foreach(lc2, stat->types) @@ -199,16 +202,11 @@ BuildRelationExtStatistics(Relation onerel, double totalrows, char t = (char) lfirst_int(lc2); if (t == STATS_EXT_NDISTINCT) - ndistinct = statext_ndistinct_build(totalrows, numrows, rows, - exprs, stat->columns, - stats); + ndistinct = statext_ndistinct_build(totalrows, data); else if (t == STATS_EXT_DEPENDENCIES) - dependencies = statext_dependencies_build(numrows, rows, - exprs, stat->columns, - stats); + dependencies = statext_dependencies_build(data); else if (t == STATS_EXT_MCV) - mcv = statext_mcv_build(numrows, rows, exprs, stat->columns, - stats, totalrows, stattarget); + mcv = statext_mcv_build(data, totalrows, stattarget); else if (t == STATS_EXT_EXPRESSIONS) { AnlExprData *exprdata; @@ -236,7 +234,8 @@ BuildRelationExtStatistics(Relation onerel, double totalrows, pgstat_progress_update_param(PROGRESS_ANALYZE_EXT_STATS_COMPUTED, ++ext_cnt); - pfree(exprs); + /* free the build data (allocated as a single chunk) */ + pfree(data); } table_close(pg_stext, RowExclusiveLock); @@ -937,30 +936,31 @@ build_attnums_array(Bitmapset *attrs, int nexprs, int *numattrs) * can simply pfree the return value to release all of it. */ SortItem * -build_sorted_items(int numrows, int *nitems, HeapTuple *rows, ExprInfo *exprs, - TupleDesc tdesc, MultiSortSupport mss, +build_sorted_items(StatBuildData *data, int *nitems, + MultiSortSupport mss, int numattrs, AttrNumber *attnums) { int i, j, len, - idx; - int nvalues = numrows * numattrs; + nrows; + int nvalues = data->numrows * numattrs; SortItem *items; Datum *values; bool *isnull; char *ptr; + int *typlen; /* Compute the total amount of memory we need (both items and values). */ - len = numrows * sizeof(SortItem) + nvalues * (sizeof(Datum) + sizeof(bool)); + len = data->numrows * sizeof(SortItem) + nvalues * (sizeof(Datum) + sizeof(bool)); /* Allocate the memory and split it into the pieces. */ ptr = palloc0(len); /* items to sort */ items = (SortItem *) ptr; - ptr += numrows * sizeof(SortItem); + ptr += data->numrows * sizeof(SortItem); /* values and null flags */ values = (Datum *) ptr; @@ -973,13 +973,24 @@ build_sorted_items(int numrows, int *nitems, HeapTuple *rows, ExprInfo *exprs, Assert((ptr - (char *) items) == len); /* fix the pointers to Datum and bool arrays */ - idx = 0; - for (i = 0; i < numrows; i++) + nrows = 0; + for (i = 0; i < data->numrows; i++) { - bool toowide = false; + items[nrows].values = &values[nrows * numattrs]; + items[nrows].isnull = &isnull[nrows * numattrs]; + + nrows++; + } + + /* build a local cache of typlen for all attributes */ + typlen = (int *) palloc(sizeof(int) * data->nattnums); + for (i = 0; i < data->nattnums; i++) + typlen[i] = get_typlen(data->stats[i]->attrtypid); - items[idx].values = &values[idx * numattrs]; - items[idx].isnull = &isnull[idx * numattrs]; + nrows = 0; + for (i = 0; i < data->numrows; i++) + { + bool toowide = false; /* load the values/null flags from sample rows */ for (j = 0; j < numattrs; j++) @@ -989,22 +1000,20 @@ build_sorted_items(int numrows, int *nitems, HeapTuple *rows, ExprInfo *exprs, int attlen; AttrNumber attnum = attnums[j]; - if (AttrNumberIsForUserDefinedAttr(attnum)) + int idx; + + /* match attnum to the pre-calculated data */ + for (idx = 0; idx < data->nattnums; idx++) { - value = heap_getattr(rows[i], attnum, tdesc, &isnull); - attlen = TupleDescAttr(tdesc, attnum - 1)->attlen; + if (attnum == data->attnums[idx]) + break; } - else - { - int idx = -(attnums[j] + 1); - - Assert((idx >= 0) && (idx < exprs->nexprs)); - value = exprs->values[idx][i]; - isnull = exprs->nulls[idx][i]; + Assert(idx < data->nattnums); - attlen = get_typlen(exprs->types[idx]); - } + value = data->values[idx][i]; + isnull = data->nulls[idx][i]; + attlen = typlen[idx]; /* * If this is a varlena value, check if it's too wide and if yes @@ -1026,21 +1035,21 @@ build_sorted_items(int numrows, int *nitems, HeapTuple *rows, ExprInfo *exprs, value = PointerGetDatum(PG_DETOAST_DATUM(value)); } - items[idx].values[j] = value; - items[idx].isnull[j] = isnull; + items[nrows].values[j] = value; + items[nrows].isnull[j] = isnull; } if (toowide) continue; - idx++; + nrows++; } /* store the actual number of items (ignoring the too-wide ones) */ - *nitems = idx; + *nitems = nrows; /* all items were too wide */ - if (idx == 0) + if (nrows == 0) { /* everything is allocated as a single chunk */ pfree(items); @@ -1048,7 +1057,7 @@ build_sorted_items(int numrows, int *nitems, HeapTuple *rows, ExprInfo *exprs, } /* do the sort, using the multi-sort */ - qsort_arg((void *) items, idx, sizeof(SortItem), + qsort_arg((void *) items, nrows, sizeof(SortItem), multi_sort_compare, mss); return items; @@ -2434,59 +2443,61 @@ statext_expressions_load(Oid stxoid, int idx) * all the requested statistics types. This matters especially for * expensive expressions, of course. */ -static ExprInfo * -evaluate_expressions(Relation rel, List *exprs, int numrows, HeapTuple *rows) +static StatBuildData * +make_build_data(Relation rel, StatExtEntry *stat, int numrows, HeapTuple *rows, + VacAttrStats **stats) { /* evaluated expressions */ - ExprInfo *result; + StatBuildData *result; char *ptr; Size len; int i; + int k; int idx; TupleTableSlot *slot; EState *estate; ExprContext *econtext; List *exprstates = NIL; - int nexprs = list_length(exprs); + int nkeys = bms_num_members(stat->columns) + list_length(stat->exprs); ListCell *lc; /* allocate everything as a single chunk, so we can free it easily */ - len = MAXALIGN(sizeof(ExprInfo)); - len += MAXALIGN(sizeof(Oid) * nexprs); /* types */ - len += MAXALIGN(sizeof(Oid) * nexprs); /* collations */ + len = MAXALIGN(sizeof(StatBuildData)); + len += MAXALIGN(sizeof(AttrNumber) * nkeys); /* attnums */ + len += MAXALIGN(sizeof(VacAttrStats *) * nkeys); /* stats */ /* values */ - len += MAXALIGN(sizeof(Datum *) * nexprs); - len += nexprs * MAXALIGN(sizeof(Datum) * numrows); + len += MAXALIGN(sizeof(Datum *) * nkeys); + len += nkeys * MAXALIGN(sizeof(Datum) * numrows); /* nulls */ - len += MAXALIGN(sizeof(bool *) * nexprs); - len += nexprs * MAXALIGN(sizeof(bool) * numrows); + len += MAXALIGN(sizeof(bool *) * nkeys); + len += nkeys * MAXALIGN(sizeof(bool) * numrows); ptr = palloc(len); /* set the pointers */ - result = (ExprInfo *) ptr; - ptr += MAXALIGN(sizeof(ExprInfo)); + result = (StatBuildData *) ptr; + ptr += MAXALIGN(sizeof(StatBuildData)); - /* types */ - result->types = (Oid *) ptr; - ptr += MAXALIGN(sizeof(Oid) * nexprs); + /* attnums */ + result->attnums = (AttrNumber *) ptr; + ptr += MAXALIGN(sizeof(AttrNumber) * nkeys); - /* collations */ - result->collations = (Oid *) ptr; - ptr += MAXALIGN(sizeof(Oid) * nexprs); + /* stats */ + result->stats = (VacAttrStats **) ptr; + ptr += MAXALIGN(sizeof(VacAttrStats *) * nkeys); /* values */ result->values = (Datum **) ptr; - ptr += MAXALIGN(sizeof(Datum *) * nexprs); + ptr += MAXALIGN(sizeof(Datum *) * nkeys); /* nulls */ result->nulls = (bool **) ptr; - ptr += MAXALIGN(sizeof(bool *) * nexprs); + ptr += MAXALIGN(sizeof(bool *) * nkeys); - for (i = 0; i < nexprs; i++) + for (i = 0; i < nkeys; i++) { result->values[i] = (Datum *) ptr; ptr += MAXALIGN(sizeof(Datum) * numrows); @@ -2497,17 +2508,46 @@ evaluate_expressions(Relation rel, List *exprs, int numrows, HeapTuple *rows) Assert((ptr - (char *) result) == len); - result->nexprs = list_length(exprs); + /* we have it allocated, so let's fill the values */ + result->nattnums = nkeys; + result->numrows = numrows; + /* fill the attribute info - first attributes, then expressions */ idx = 0; - foreach (lc, exprs) + k = -1; + while ((k = bms_next_member(stat->columns, k)) >= 0) + { + result->attnums[idx] = k; + result->stats[idx] = stats[idx]; + + idx++; + } + + k = -1; + foreach (lc, stat->exprs) { Node *expr = (Node *) lfirst(lc); - result->types[idx] = exprType(expr); - result->collations[idx] = exprCollation(expr); + result->attnums[idx] = k; + result->stats[idx] = examine_expression(expr); idx++; + k--; + } + + /* first extract values for all the regular attributes */ + for (i = 0; i < numrows; i++) + { + idx = 0; + k = -1; + while ((k = bms_next_member(stat->columns, k)) >= 0) + { + result->values[idx][i] = heap_getattr(rows[i], k, + result->stats[idx]->tupDesc, + &result->nulls[idx][i]); + + idx++; + } } /* @@ -2526,7 +2566,7 @@ evaluate_expressions(Relation rel, List *exprs, int numrows, HeapTuple *rows) econtext->ecxt_scantuple = slot; /* Set up expression evaluation state */ - exprstates = ExecPrepareExprList(exprs, estate); + exprstates = ExecPrepareExprList(stat->exprs, estate); for (i = 0; i < numrows; i++) { @@ -2539,7 +2579,7 @@ evaluate_expressions(Relation rel, List *exprs, int numrows, HeapTuple *rows) /* Set up for predicate or expression evaluation */ ExecStoreHeapTuple(rows[i], slot, false); - idx = 0; + idx = bms_num_members(stat->columns); foreach (lc, exprstates) { Datum datum; diff --git a/src/backend/statistics/mcv.c b/src/backend/statistics/mcv.c index 323d476814..844ba6f71f 100644 --- a/src/backend/statistics/mcv.c +++ b/src/backend/statistics/mcv.c @@ -74,8 +74,7 @@ ((ndims) * sizeof(DimensionInfo)) + \ ((nitems) * ITEM_SIZE(ndims))) -static MultiSortSupport build_mss(VacAttrStats **stats, int numattrs, - ExprInfo *exprs); +static MultiSortSupport build_mss(StatBuildData *data); static SortItem *build_distinct_groups(int numrows, SortItem *items, MultiSortSupport mss, int *ndistinct); @@ -182,16 +181,11 @@ get_mincount_for_mcv_list(int samplerows, double totalrows) * */ MCVList * -statext_mcv_build(int numrows, HeapTuple *rows, ExprInfo *exprs, - Bitmapset *attrs, VacAttrStats **stats, - double totalrows, int stattarget) +statext_mcv_build(StatBuildData *data, double totalrows, int stattarget) { int i, - k, - numattrs, ngroups, nitems; - AttrNumber *attnums; double mincount; SortItem *items; SortItem *groups; @@ -199,38 +193,11 @@ statext_mcv_build(int numrows, HeapTuple *rows, ExprInfo *exprs, MultiSortSupport mss; /* comparator for all the columns */ - mss = build_mss(stats, bms_num_members(attrs), exprs); - - /* - * treat expressions as special attributes with high attnums - * - * XXX We do this after build_mss, because that expects the bitmapset - * to only contain simple attributes (with a matching VacAttrStats) - */ - - /* - * Transform the bms into an array, to make accessing i-th member easier. - */ - attnums = (AttrNumber *) palloc(sizeof(AttrNumber) * (bms_num_members(attrs) + exprs->nexprs)); - - numattrs = 0; - - /* regular attributes */ - k = -1; - while ((k = bms_next_member(attrs, k)) >= 0) - attnums[numattrs++] = k; - - /* treat expressions as attributes with negative attnums */ - for (i = 0; i < exprs->nexprs; i++) - attnums[numattrs++] = -(i+1); - - Assert(numattrs >= 2); - Assert(numattrs == (bms_num_members(attrs) + exprs->nexprs)); - + mss = build_mss(data); /* sort the rows */ - items = build_sorted_items(numrows, &nitems, rows, exprs, - stats[0]->tupDesc, mss, numattrs, attnums); + items = build_sorted_items(data, &nitems, mss, + data->nattnums, data->attnums); if (!items) return NULL; @@ -265,7 +232,7 @@ statext_mcv_build(int numrows, HeapTuple *rows, ExprInfo *exprs, * using get_mincount_for_mcv_list() and then keep all items that seem to * be more common than that. */ - mincount = get_mincount_for_mcv_list(numrows, totalrows); + mincount = get_mincount_for_mcv_list(data->numrows, totalrows); /* * Walk the groups until we find the first group with a count below the @@ -301,7 +268,7 @@ statext_mcv_build(int numrows, HeapTuple *rows, ExprInfo *exprs, + sizeof(SortSupportData)); /* compute frequencies for values in each column */ - nfreqs = (int *) palloc0(sizeof(int) * numattrs); + nfreqs = (int *) palloc0(sizeof(int) * data->nattnums); freqs = build_column_frequencies(groups, ngroups, mss, nfreqs); /* @@ -312,12 +279,12 @@ statext_mcv_build(int numrows, HeapTuple *rows, ExprInfo *exprs, mcvlist->magic = STATS_MCV_MAGIC; mcvlist->type = STATS_MCV_TYPE_BASIC; - mcvlist->ndimensions = numattrs; + mcvlist->ndimensions = data->nattnums; mcvlist->nitems = nitems; /* store info about data type OIDs */ - for (i = 0; i < numattrs; i++) - mcvlist->types[i] = stats[i]->attrtypid; + for (i = 0; i < data->nattnums; i++) + mcvlist->types[i] = data->stats[i]->attrtypid; /* Copy the first chunk of groups into the result. */ for (i = 0; i < nitems; i++) @@ -325,22 +292,22 @@ statext_mcv_build(int numrows, HeapTuple *rows, ExprInfo *exprs, /* just pointer to the proper place in the list */ MCVItem *item = &mcvlist->items[i]; - item->values = (Datum *) palloc(sizeof(Datum) * numattrs); - item->isnull = (bool *) palloc(sizeof(bool) * numattrs); + item->values = (Datum *) palloc(sizeof(Datum) * data->nattnums); + item->isnull = (bool *) palloc(sizeof(bool) * data->nattnums); /* copy values for the group */ - memcpy(item->values, groups[i].values, sizeof(Datum) * numattrs); - memcpy(item->isnull, groups[i].isnull, sizeof(bool) * numattrs); + memcpy(item->values, groups[i].values, sizeof(Datum) * data->nattnums); + memcpy(item->isnull, groups[i].isnull, sizeof(bool) * data->nattnums); /* groups should be sorted by frequency in descending order */ Assert((i == 0) || (groups[i - 1].count >= groups[i].count)); /* group frequency */ - item->frequency = (double) groups[i].count / numrows; + item->frequency = (double) groups[i].count / data->numrows; /* base frequency, if the attributes were independent */ item->base_frequency = 1.0; - for (j = 0; j < numattrs; j++) + for (j = 0; j < data->nattnums; j++) { SortItem *freq; @@ -356,7 +323,7 @@ statext_mcv_build(int numrows, HeapTuple *rows, ExprInfo *exprs, sizeof(SortItem), multi_sort_compare, tmp); - item->base_frequency *= ((double) freq->count) / numrows; + item->base_frequency *= ((double) freq->count) / data->numrows; } } @@ -375,17 +342,17 @@ statext_mcv_build(int numrows, HeapTuple *rows, ExprInfo *exprs, * build MultiSortSupport for the attributes passed in attrs */ static MultiSortSupport -build_mss(VacAttrStats **stats, int numattrs, ExprInfo *exprs) +build_mss(StatBuildData *data) { int i; /* Sort by multiple columns (using array of SortSupport) */ - MultiSortSupport mss = multi_sort_init(numattrs + exprs->nexprs); + MultiSortSupport mss = multi_sort_init(data->nattnums); /* prepare the sort functions for all the attributes */ - for (i = 0; i < numattrs; i++) + for (i = 0; i < data->nattnums; i++) { - VacAttrStats *colstat = stats[i]; + VacAttrStats *colstat = data->stats[i]; TypeCacheEntry *type; type = lookup_type_cache(colstat->attrtypid, TYPECACHE_LT_OPR); @@ -396,20 +363,6 @@ build_mss(VacAttrStats **stats, int numattrs, ExprInfo *exprs) multi_sort_add_dimension(mss, i, type->lt_opr, colstat->attrcollid); } - /* prepare the sort functions for all the expressions */ - for (i = 0; i < exprs->nexprs; i++) - { - TypeCacheEntry *type; - - type = lookup_type_cache(exprs->types[i], TYPECACHE_LT_OPR); - if (type->lt_opr == InvalidOid) /* shouldn't happen */ - elog(ERROR, "cache lookup failed for ordering operator for type %u", - exprs->types[i]); - - multi_sort_add_dimension(mss, numattrs + i, type->lt_opr, - exprs->collations[i]); - } - return mss; } diff --git a/src/backend/statistics/mvdistinct.c b/src/backend/statistics/mvdistinct.c index 5e796e7123..7ca59d9785 100644 --- a/src/backend/statistics/mvdistinct.c +++ b/src/backend/statistics/mvdistinct.c @@ -36,9 +36,7 @@ #include "utils/syscache.h" #include "utils/typcache.h" -static double ndistinct_for_combination(double totalrows, int numrows, - HeapTuple *rows, ExprInfo *exprs, - int nattrs, VacAttrStats **stats, +static double ndistinct_for_combination(double totalrows, StatBuildData *data, int k, int *combination); static double estimate_ndistinct(double totalrows, int numrows, int d, int f1); static int n_choose_k(int n, int k); @@ -88,17 +86,12 @@ static void generate_combinations(CombinationGenerator *state); * allow using Bitmapsets. */ MVNDistinct * -statext_ndistinct_build(double totalrows, int numrows, HeapTuple *rows, - ExprInfo *exprs, Bitmapset *attrs, - VacAttrStats **stats) +statext_ndistinct_build(double totalrows, StatBuildData *data) { MVNDistinct *result; - int i; int k; int itemcnt; - int numattrs = bms_num_members(attrs); - int numcombs = num_combinations(numattrs + exprs->nexprs); - Bitmapset *tmp = NULL; + int numcombs = num_combinations(data->nattnums); result = palloc(offsetof(MVNDistinct, items) + numcombs * sizeof(MVNDistinctItem)); @@ -106,38 +99,14 @@ statext_ndistinct_build(double totalrows, int numrows, HeapTuple *rows, result->type = STATS_NDISTINCT_TYPE_BASIC; result->nitems = numcombs; - /* - * Treat expressions as system attributes with negative attnums, - * but offset everything by number of expressions. - */ - for (i = 0; i < exprs->nexprs; i++) - { - AttrNumber attnum = -(i + 1); - tmp = bms_add_member(tmp, attnum + exprs->nexprs); - } - - /* regular attributes */ - k = -1; - while ((k = bms_next_member(attrs, k)) >= 0) - { - AttrNumber attnum = k; - tmp = bms_add_member(tmp, attnum + exprs->nexprs); - } - - /* use the newly built bitmapset */ - attrs = tmp; - - /* make sure there were no clashes */ - Assert(bms_num_members(attrs) == numattrs + exprs->nexprs); - itemcnt = 0; - for (k = 2; k <= bms_num_members(attrs); k++) + for (k = 2; k <= data->nattnums; k++) { int *combination; CombinationGenerator *generator; /* generate combinations of K out of N elements */ - generator = generator_init(bms_num_members(attrs), k); + generator = generator_init(data->nattnums, k); while ((combination = generator_next(generator))) { @@ -147,36 +116,16 @@ statext_ndistinct_build(double totalrows, int numrows, HeapTuple *rows, item->attributes = palloc(sizeof(AttrNumber) * k); item->nattributes = k; + /* translate the indexes to attnums */ for (j = 0; j < k; j++) { - AttrNumber attnum = InvalidAttrNumber; - - /* - * The expressions have negative attnums, so even with the - * offset are before regular attributes. So the first chunk - * of indexes are for expressions. - */ - if (combination[j] >= exprs->nexprs) - attnum - = stats[combination[j] - exprs->nexprs]->attr->attnum; - else - { - /* make sure the expression index is valid */ - Assert(combination[j] >= 0); - Assert(combination[j] < exprs->nexprs); - - attnum = -(combination[j] + 1); - } - - Assert(attnum != InvalidAttrNumber); - - item->attributes[j] = attnum; + item->attributes[j] = data->attnums[combination[j]]; + + Assert(AttributeNumberIsValid(item->attributes[j])); } item->ndistinct = - ndistinct_for_combination(totalrows, numrows, rows, - exprs, numattrs, - stats, k, combination); + ndistinct_for_combination(totalrows, data, k, combination); itemcnt++; Assert(itemcnt <= result->nitems); @@ -471,9 +420,8 @@ pg_ndistinct_send(PG_FUNCTION_ARGS) * combination of multiple columns. */ static double -ndistinct_for_combination(double totalrows, int numrows, HeapTuple *rows, - ExprInfo *exprs, int nattrs, - VacAttrStats **stats, int k, int *combination) +ndistinct_for_combination(double totalrows, StatBuildData *data, + int k, int *combination) { int i, j; @@ -493,11 +441,11 @@ ndistinct_for_combination(double totalrows, int numrows, HeapTuple *rows, * using the specified column combination as dimensions. We could try to * sort in place, but it'd probably be more complex and bug-prone. */ - items = (SortItem *) palloc(numrows * sizeof(SortItem)); - values = (Datum *) palloc0(sizeof(Datum) * numrows * k); - isnull = (bool *) palloc0(sizeof(bool) * numrows * k); + items = (SortItem *) palloc(data->numrows * sizeof(SortItem)); + values = (Datum *) palloc0(sizeof(Datum) * data->numrows * k); + isnull = (bool *) palloc0(sizeof(bool) * data->numrows * k); - for (i = 0; i < numrows; i++) + for (i = 0; i < data->numrows; i++) { items[i].values = &values[i * k]; items[i].isnull = &isnull[i * k]; @@ -514,24 +462,11 @@ ndistinct_for_combination(double totalrows, int numrows, HeapTuple *rows, { Oid typid; TypeCacheEntry *type; - AttrNumber attnum = InvalidAttrNumber; - TupleDesc tdesc = NULL; Oid collid = InvalidOid; + VacAttrStats *colstat = data->stats[combination[i]]; - /* first nexprs indexes are for expressions, then regular attributes */ - if (combination[i] >= exprs->nexprs) - { - VacAttrStats *colstat = stats[combination[i] - exprs->nexprs]; - typid = colstat->attrtypid; - attnum = colstat->attr->attnum; - collid = colstat->attrcollid; - tdesc = colstat->tupDesc; - } - else - { - typid = exprs->types[combination[i]]; - collid = exprs->collations[combination[i]]; - } + typid = colstat->attrtypid; + collid = colstat->attrcollid; type = lookup_type_cache(typid, TYPECACHE_LT_OPR); if (type->lt_opr == InvalidOid) /* shouldn't happen */ @@ -542,38 +477,15 @@ ndistinct_for_combination(double totalrows, int numrows, HeapTuple *rows, multi_sort_add_dimension(mss, i, type->lt_opr, collid); /* accumulate all the data for this dimension into the arrays */ - for (j = 0; j < numrows; j++) + for (j = 0; j < data->numrows; j++) { - /* - * The first exprs indexes identify expressions, higher indexes - * are for plain attributes. - * - * XXX This seems a bit strange that we don't offset the (i) - * in any way? - */ - if (combination[i] >= exprs->nexprs) - items[j].values[i] = - heap_getattr(rows[j], - attnum, - tdesc, - &items[j].isnull[i]); - else - { - /* we know the first nexprs expressions are expressions, - * and the value is directly the expression index */ - int idx = combination[i]; - - /* make sure the expression index is valid */ - Assert((idx >= 0) && (idx < exprs->nexprs)); - - items[j].values[i] = exprs->values[idx][j]; - items[j].isnull[i] = exprs->nulls[idx][j]; - } + items[j].values[i] = data->values[combination[i]][j]; + items[j].isnull[i] = data->nulls[combination[i]][j]; } } /* We can sort the array now ... */ - qsort_arg((void *) items, numrows, sizeof(SortItem), + qsort_arg((void *) items, data->numrows, sizeof(SortItem), multi_sort_compare, mss); /* ... and count the number of distinct combinations */ @@ -581,7 +493,7 @@ ndistinct_for_combination(double totalrows, int numrows, HeapTuple *rows, f1 = 0; cnt = 1; d = 1; - for (i = 1; i < numrows; i++) + for (i = 1; i < data->numrows; i++) { if (multi_sort_compare(&items[i], &items[i - 1], mss) != 0) { @@ -598,7 +510,7 @@ ndistinct_for_combination(double totalrows, int numrows, HeapTuple *rows, if (cnt == 1) f1 += 1; - return estimate_ndistinct(totalrows, numrows, d, f1); + return estimate_ndistinct(totalrows, data->numrows, d, f1); } /* The Duj1 estimator (already used in analyze.c). */ diff --git a/src/include/statistics/extended_stats_internal.h b/src/include/statistics/extended_stats_internal.h index 1f09799deb..7acf82aa0e 100644 --- a/src/include/statistics/extended_stats_internal.h +++ b/src/include/statistics/extended_stats_internal.h @@ -57,35 +57,26 @@ typedef struct SortItem int count; } SortItem; -/* - * Used to pass pre-computed information about expressions the stats - * object is defined on. - */ -typedef struct ExprInfo -{ - int nexprs; /* number of expressions */ - Oid *collations; /* collation for each expression */ - Oid *types; /* type of each expression */ - Datum **values; /* values for each expression */ - bool **nulls; /* nulls for each expression */ -} ExprInfo; - -extern MVNDistinct *statext_ndistinct_build(double totalrows, - int numrows, HeapTuple *rows, - ExprInfo *exprs, Bitmapset *attrs, - VacAttrStats **stats); +/* a unified representation of the data the statistics is built on */ +typedef struct StatBuildData { + int numrows; + int nattnums; + AttrNumber *attnums; + VacAttrStats **stats; + Datum **values; + bool **nulls; +} StatBuildData; + + +extern MVNDistinct *statext_ndistinct_build(double totalrows, StatBuildData *data); extern bytea *statext_ndistinct_serialize(MVNDistinct *ndistinct); extern MVNDistinct *statext_ndistinct_deserialize(bytea *data); -extern MVDependencies *statext_dependencies_build(int numrows, HeapTuple *rows, - ExprInfo *exprs, Bitmapset *attrs, - VacAttrStats **stats); +extern MVDependencies *statext_dependencies_build(StatBuildData *data); extern bytea *statext_dependencies_serialize(MVDependencies *dependencies); extern MVDependencies *statext_dependencies_deserialize(bytea *data); -extern MCVList *statext_mcv_build(int numrows, HeapTuple *rows, - ExprInfo *exprs, Bitmapset *attrs, - VacAttrStats **stats, +extern MCVList *statext_mcv_build(StatBuildData *data, double totalrows, int stattarget); extern bytea *statext_mcv_serialize(MCVList *mcv, VacAttrStats **stats); extern MCVList *statext_mcv_deserialize(bytea *data); @@ -108,8 +99,7 @@ extern void *bsearch_arg(const void *key, const void *base, extern AttrNumber *build_attnums_array(Bitmapset *attrs, int nexprs, int *numattrs); -extern SortItem *build_sorted_items(int numrows, int *nitems, HeapTuple *rows, - ExprInfo *exprs, TupleDesc tdesc, +extern SortItem *build_sorted_items(StatBuildData *data, int *nitems, MultiSortSupport mss, int numattrs, AttrNumber *attnums); -- 2.26.2 --------------614DDB87AFFED893713AC0E9-- ^ permalink raw reply [nested|flat] 9+ messages in thread
* [PATCH v16 3/4] Merge constants in ArrayExpr into groups @ 2023-10-15 08:06 Dmitrii Dolgov <[email protected]> 0 siblings, 0 replies; 9+ messages in thread From: Dmitrii Dolgov @ 2023-10-15 08:06 UTC (permalink / raw) Using query_id_const_merge only first/last element in an ArrayExpr will be used to compute query id. Extend this to take into account number of elements, and merge constants into groups based on it. Resulting groups are powers of 10, i.e. 1 to 9, 10 to 99, etc. --- .../pg_stat_statements/expected/merging.out | 84 +++++++++++++++---- .../pg_stat_statements/pg_stat_statements.c | 17 +++- contrib/pg_stat_statements/sql/merging.sql | 13 +++ doc/src/sgml/pgstatstatements.sgml | 11 +-- src/backend/nodes/queryjumblefuncs.c | 52 ++++++++---- src/include/nodes/queryjumble.h | 7 +- 6 files changed, 142 insertions(+), 42 deletions(-) diff --git a/contrib/pg_stat_statements/expected/merging.out b/contrib/pg_stat_statements/expected/merging.out index f286c735a36..7400870f3f6 100644 --- a/contrib/pg_stat_statements/expected/merging.out +++ b/contrib/pg_stat_statements/expected/merging.out @@ -54,11 +54,11 @@ SELECT * FROM test_merge WHERE id IN (1, 2, 3); (0 rows) SELECT query, calls FROM pg_stat_statements ORDER BY query COLLATE "C"; - query | calls ---------------------------------------------+------- - SELECT * FROM test_merge WHERE id IN ($1) | 1 - SELECT * FROM test_merge WHERE id IN (...) | 1 - SELECT pg_stat_statements_reset() | 1 + query | calls +----------------------------------------------------------+------- + SELECT * FROM test_merge WHERE id IN ($1) | 1 + SELECT * FROM test_merge WHERE id IN (... [1-9 entries]) | 1 + SELECT pg_stat_statements_reset() | 1 (3 rows) SELECT * FROM test_merge WHERE id IN (1, 2, 3, 4, 5, 6, 7, 8, 9); @@ -80,7 +80,60 @@ SELECT query, calls FROM pg_stat_statements ORDER BY query COLLATE "C"; query | calls ------------------------------------------------------------------------+------- SELECT * FROM test_merge WHERE id IN ($1) | 1 - SELECT * FROM test_merge WHERE id IN (...) | 4 + SELECT * FROM test_merge WHERE id IN (... [1-9 entries]) | 2 + SELECT * FROM test_merge WHERE id IN (... [10-99 entries]) | 2 + SELECT pg_stat_statements_reset() | 1 + SELECT query, calls FROM pg_stat_statements ORDER BY query COLLATE "C" | 1 +(5 rows) + +-- Second order of magnitude, brace yourself +SELECT pg_stat_statements_reset(); + pg_stat_statements_reset +-------------------------- + +(1 row) + +SELECT * FROM test_merge WHERE id IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31, 32, 33, 34, 35, 36, 37, 38, 39, 40, 41, 42, 43, 44, 45, 46, 47, 48, 49, 50, 51, 52, 53, 54, 55, 56, 57, 58, 59, 60, 61, 62, 63, 64, 65, 66, 67, 68, 69, 70, 71, 72, 73, 74, 75, 76, 77, 78, 79, 80, 81, 82, 83, 84, 85, 86, 87, 88, 89, 90, 91, 92, 93, 94, 95, 96, 97, 98, 99, 100, 101, 102, 103, 104, 105, 106, 107, 108, 109, 110); + id | data +----+------ +(0 rows) + +SELECT query, calls FROM pg_stat_statements ORDER BY query COLLATE "C"; + query | calls +--------------------------------------------------------------+------- + SELECT * FROM test_merge WHERE id IN (... [100-999 entries]) | 1 + SELECT pg_stat_statements_reset() | 1 +(2 rows) + +-- With gaps on the threshold +SELECT pg_stat_statements_reset(); + pg_stat_statements_reset +-------------------------- + +(1 row) + +SELECT * FROM test_merge WHERE id IN (1, 2, 3, 4); + id | data +----+------ +(0 rows) + +SELECT query, calls FROM pg_stat_statements ORDER BY query COLLATE "C"; + query | calls +----------------------------------------------------------+------- + SELECT * FROM test_merge WHERE id IN (... [1-9 entries]) | 1 + SELECT pg_stat_statements_reset() | 1 +(2 rows) + +SELECT * FROM test_merge WHERE id IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15); + id | data +----+------ +(0 rows) + +SELECT query, calls FROM pg_stat_statements ORDER BY query COLLATE "C"; + query | calls +------------------------------------------------------------------------+------- + SELECT * FROM test_merge WHERE id IN (... [1-9 entries]) | 1 + SELECT * FROM test_merge WHERE id IN (... [10-99 entries]) | 1 SELECT pg_stat_statements_reset() | 1 SELECT query, calls FROM pg_stat_statements ORDER BY query COLLATE "C" | 1 (4 rows) @@ -108,11 +161,12 @@ SELECT * FROM test_merge WHERE id IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11) and dat (0 rows) SELECT query, calls FROM pg_stat_statements ORDER BY query COLLATE "C"; - query | calls -----------------------------------------------------------+------- - SELECT * FROM test_merge WHERE id IN (...) and data = $3 | 3 - SELECT pg_stat_statements_reset() | 1 -(2 rows) + query | calls +--------------------------------------------------------------------------+------- + SELECT * FROM test_merge WHERE id IN (... [1-9 entries]) and data = $3 | 1 + SELECT * FROM test_merge WHERE id IN (... [10-99 entries]) and data = $3 | 2 + SELECT pg_stat_statements_reset() | 1 +(3 rows) -- No constants simplification SELECT pg_stat_statements_reset(); @@ -147,10 +201,10 @@ SELECT * FROM test_merge_numeric WHERE id IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11) (0 rows) SELECT query, calls FROM pg_stat_statements ORDER BY query COLLATE "C"; - query | calls -----------------------------------------------------+------- - SELECT * FROM test_merge_numeric WHERE id IN (...) | 1 - SELECT pg_stat_statements_reset() | 1 + query | calls +--------------------------------------------------------------------+------- + SELECT * FROM test_merge_numeric WHERE id IN (... [10-99 entries]) | 1 + SELECT pg_stat_statements_reset() | 1 (2 rows) -- Test constants evaluation, verifies a tricky part to make sure there are no diff --git a/contrib/pg_stat_statements/pg_stat_statements.c b/contrib/pg_stat_statements/pg_stat_statements.c index 4d47a746670..a5702c3d749 100644 --- a/contrib/pg_stat_statements/pg_stat_statements.c +++ b/contrib/pg_stat_statements/pg_stat_statements.c @@ -2714,6 +2714,8 @@ generate_normalized_query(JumbleState *jstate, const char *query, last_tok_len = 0; /* Length (in bytes) of that tok */ bool skip = false; /* Signals that certain constants are merged together and have to be skipped */ + int magnitude; /* Order of magnitute for number of merged + constants */ /* @@ -2754,7 +2756,8 @@ generate_normalized_query(JumbleState *jstate, const char *query, Assert(len_to_wrt >= 0); /* Normal path, non merged constant */ - if (!jstate->clocations[i].merged) + magnitude = jstate->clocations[i].magnitude; + if (magnitude == 0) { memcpy(norm_query + n_quer_loc, query + quer_loc, len_to_wrt); n_quer_loc += len_to_wrt; @@ -2770,12 +2773,22 @@ generate_normalized_query(JumbleState *jstate, const char *query, /* The firsts merged constant */ else if (!skip) { + static const uint32 powers_of_ten[] = { + 1, 10, 100, + 1000, 10000, 100000, + 1000000, 10000000, 100000000, + 1000000000 + }; + int lower_merged = powers_of_ten[magnitude - 1]; + int upper_merged = powers_of_ten[magnitude]; + memcpy(norm_query + n_quer_loc, query + quer_loc, len_to_wrt); n_quer_loc += len_to_wrt; /* Skip the following until a non merged constant appear */ skip = true; - n_quer_loc += sprintf(norm_query + n_quer_loc, "..."); + n_quer_loc += sprintf(norm_query + n_quer_loc, "... [%d-%d entries]", + lower_merged, upper_merged - 1); } /* Otherwise the constant is merged away */ diff --git a/contrib/pg_stat_statements/sql/merging.sql b/contrib/pg_stat_statements/sql/merging.sql index 8b589135daa..c515e48d50c 100644 --- a/contrib/pg_stat_statements/sql/merging.sql +++ b/contrib/pg_stat_statements/sql/merging.sql @@ -27,6 +27,19 @@ SELECT * FROM test_merge WHERE id IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10); SELECT * FROM test_merge WHERE id IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11); SELECT query, calls FROM pg_stat_statements ORDER BY query COLLATE "C"; +-- Second order of magnitude, brace yourself +SELECT pg_stat_statements_reset(); +SELECT * FROM test_merge WHERE id IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31, 32, 33, 34, 35, 36, 37, 38, 39, 40, 41, 42, 43, 44, 45, 46, 47, 48, 49, 50, 51, 52, 53, 54, 55, 56, 57, 58, 59, 60, 61, 62, 63, 64, 65, 66, 67, 68, 69, 70, 71, 72, 73, 74, 75, 76, 77, 78, 79, 80, 81, 82, 83, 84, 85, 86, 87, 88, 89, 90, 91, 92, 93, 94, 95, 96, 97, 98, 99, 100, 101, 102, 103, 104, 105, 106, 107, 108, 109, 110); +SELECT query, calls FROM pg_stat_statements ORDER BY query COLLATE "C"; + +-- With gaps on the threshold +SELECT pg_stat_statements_reset(); +SELECT * FROM test_merge WHERE id IN (1, 2, 3, 4); +SELECT query, calls FROM pg_stat_statements ORDER BY query COLLATE "C"; + +SELECT * FROM test_merge WHERE id IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15); +SELECT query, calls FROM pg_stat_statements ORDER BY query COLLATE "C"; + -- More conditions in the query SELECT pg_stat_statements_reset(); diff --git a/doc/src/sgml/pgstatstatements.sgml b/doc/src/sgml/pgstatstatements.sgml index bba8e5e11ed..a919696abc2 100644 --- a/doc/src/sgml/pgstatstatements.sgml +++ b/doc/src/sgml/pgstatstatements.sgml @@ -559,7 +559,7 @@ =# SELECT * FROM test WHERE a IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12); =# SELECT query, calls FROM pg_stat_statements; -[ RECORD 1 ]------------------------------ -query | SELECT * FROM test WHERE a IN (...) +query | SELECT * FROM test WHERE a IN (... [10-99 entries]) calls | 2 -[ RECORD 2 ]------------------------------ query | SELECT pg_stat_statements_reset() @@ -896,10 +896,11 @@ calls | 1 with an array of different lenght. If this parameter is on, an array of constants will contribute only the - first and the last elements to the query identifier. It means two - occurences of the same query, where the only difference is number of - constants in the array, are going to get the same query identifier. - Such queries are represented in form <literal>'(...)'</literal>. + first element, the last element and the number of elements to the query + identifier. It means two occurences of the same query, where the only + difference is number of constants in the array, are going to get the + same query identifier if the arrays are of similar length. + Such queries are represented in form <literal>'(... [10-99 entries])'</literal>. The parameter could be used to reduce amount of repeating data stored via <structname>pg_stat_statements</structname>. The default value is off. diff --git a/src/backend/nodes/queryjumblefuncs.c b/src/backend/nodes/queryjumblefuncs.c index 4bc16dde6a0..a1d4567ca66 100644 --- a/src/backend/nodes/queryjumblefuncs.c +++ b/src/backend/nodes/queryjumblefuncs.c @@ -37,6 +37,8 @@ #include "nodes/queryjumble.h" #include "parser/scansup.h" +#include "utils/numutils.h" + #define JUMBLE_SIZE 1024 /* query serialization buffer size */ /* GUC parameters */ @@ -51,7 +53,7 @@ bool query_id_enabled = false; static void AppendJumble(JumbleState *jstate, const unsigned char *item, Size size); static void RecordConstLocation(JumbleState *jstate, - int location, bool merged); + int location, int magnitude); static void _jumbleNode(JumbleState *jstate, Node *node); static void _jumbleElements(JumbleState *jstate, List *elements); static void _jumbleA_Const(JumbleState *jstate, Node *node); @@ -206,12 +208,15 @@ AppendJumble(JumbleState *jstate, const unsigned char *item, Size size) * Record location of constant within query string of query tree that is * currently being walked. * - * Merged argument signals that the constant represents the first or the last - * element in a series of merged constants, and everything but the first/last - * element contributes nothing to the jumble hash. + * Magnitude argument larger than zero signals that the constant represents the + * first or the last element in a series of merged constants, and everything + * but such first/last element will contribute nothing to the jumble hash. The + * magnitute value specifies order of magnitute (i.e. how many digits it has) + * for the number of elements in the series, to represent the fact of merging + * later on. */ static void -RecordConstLocation(JumbleState *jstate, int location, bool merged) +RecordConstLocation(JumbleState *jstate, int location, int magnitude) { /* -1 indicates unknown or undefined location */ if (location >= 0) @@ -227,7 +232,7 @@ RecordConstLocation(JumbleState *jstate, int location, bool merged) } jstate->clocations[jstate->clocations_count].location = location; /* initialize lengths to -1 to simplify third-party module usage */ - jstate->clocations[jstate->clocations_count].merged = merged; + jstate->clocations[jstate->clocations_count].magnitude = magnitude; jstate->clocations[jstate->clocations_count].length = -1; jstate->clocations_count++; } @@ -237,24 +242,26 @@ RecordConstLocation(JumbleState *jstate, int location, bool merged) * Verify if the provided list contains could be merged down, which means it * contains only constant expressions. * - * Return value indicates if merging is possible. + * Return value is the order of magnitude (i.e. how many digits it has) for + * length of the list (to use for representation purposes later on) if merging + * is possible, otherwise zero. * * Note that this function searches only for explicit Const nodes and does not * try to simplify expressions. */ -static bool +static int IsMergeableConstList(List *elements, Const **firstConst, Const **lastConst) { ListCell *temp; Node *firstExpr = NULL; if (elements == NULL) - return false; + return 0; if (!query_id_const_merge) { /* Merging is disabled, process everything one by one */ - return false; + return 0; } firstExpr = linitial(elements); @@ -268,26 +275,26 @@ IsMergeableConstList(List *elements, Const **firstConst, Const **lastConst) { foreach(temp, elements) if (!IsA(lfirst(temp), Const)) - return false; + return 0; *firstConst = (Const *) firstExpr; *lastConst = llast_node(Const, elements); - return true; + return decimalLength32(elements->length); } /* * If we end up here, it means no constants merging is possible, process * the list as usual. */ - return false; + return 0; } #define JUMBLE_NODE(item) \ _jumbleNode(jstate, (Node *) expr->item) #define JUMBLE_ELEMENTS(list) \ _jumbleElements(jstate, (List *) expr->list) -#define JUMBLE_LOCATION(location, merged) \ - RecordConstLocation(jstate, expr->location, merged) +#define JUMBLE_LOCATION(location, magnitude) \ + RecordConstLocation(jstate, expr->location, magnitude) #define JUMBLE_FIELD(item) \ AppendJumble(jstate, (const unsigned char *) &(expr->item), sizeof(expr->item)) #define JUMBLE_FIELD_SINGLE(item) \ @@ -304,10 +311,19 @@ static void _jumbleElements(JumbleState *jstate, List *elements) { Const *first, *last; - if(IsMergeableConstList(elements, &first, &last)) + int magnitude = IsMergeableConstList(elements, &first, &last); + + if (magnitude) { - RecordConstLocation(jstate, first->location, true); - RecordConstLocation(jstate, last->location, true); + RecordConstLocation(jstate, first->location, magnitude); + RecordConstLocation(jstate, last->location, magnitude); + + /* + * After merging constants down we end up with only two constants, the + * first and the last one. To distinguish the order of magnitute behind + * merged constants, add its value into the jumble. + */ + JUMBLE_FIELD_SINGLE(magnitude); } else { diff --git a/src/include/nodes/queryjumble.h b/src/include/nodes/queryjumble.h index c64a007ad3f..8ee2e9afbb6 100644 --- a/src/include/nodes/queryjumble.h +++ b/src/include/nodes/queryjumble.h @@ -26,9 +26,12 @@ typedef struct LocationLen /* * Indicates the constant represents the beginning or the end of a merged - * constants interval. + * constants interval. The value shows how many constants were merged away + * (up to a power of 10), or in other words the order of manitude for + * number of merged constants (i.e. how many digits it has). Otherwise the + * value is 0, indicating that no merging was performed. */ - bool merged; + int magnitude; } LocationLen; /* -- 2.41.0 --pjkjqtbeiiimipyr Content-Type: text/x-diff; charset=us-ascii Content-Disposition: attachment; filename="v16-0004-Introduce-query_id_const_merge_threshold.patch" ^ permalink raw reply [nested|flat] 9+ messages in thread
* [PATCH v17 3/4] Merge constants in ArrayExpr into groups @ 2023-10-15 08:06 Dmitrii Dolgov <[email protected]> 0 siblings, 0 replies; 9+ messages in thread From: Dmitrii Dolgov @ 2023-10-15 08:06 UTC (permalink / raw) Using query_id_const_merge only first/last element in an ArrayExpr will be used to compute query id. Extend this to take into account number of elements, and merge constants into groups based on it. Resulting groups are powers of 10, i.e. 1 to 9, 10 to 99, etc. --- .../pg_stat_statements/expected/merging.out | 84 +++++++++++++++---- .../pg_stat_statements/pg_stat_statements.c | 17 +++- contrib/pg_stat_statements/sql/merging.sql | 13 +++ doc/src/sgml/pgstatstatements.sgml | 11 +-- src/backend/nodes/queryjumblefuncs.c | 52 ++++++++---- src/include/nodes/queryjumble.h | 7 +- 6 files changed, 142 insertions(+), 42 deletions(-) diff --git a/contrib/pg_stat_statements/expected/merging.out b/contrib/pg_stat_statements/expected/merging.out index f286c735a36..7400870f3f6 100644 --- a/contrib/pg_stat_statements/expected/merging.out +++ b/contrib/pg_stat_statements/expected/merging.out @@ -54,11 +54,11 @@ SELECT * FROM test_merge WHERE id IN (1, 2, 3); (0 rows) SELECT query, calls FROM pg_stat_statements ORDER BY query COLLATE "C"; - query | calls ---------------------------------------------+------- - SELECT * FROM test_merge WHERE id IN ($1) | 1 - SELECT * FROM test_merge WHERE id IN (...) | 1 - SELECT pg_stat_statements_reset() | 1 + query | calls +----------------------------------------------------------+------- + SELECT * FROM test_merge WHERE id IN ($1) | 1 + SELECT * FROM test_merge WHERE id IN (... [1-9 entries]) | 1 + SELECT pg_stat_statements_reset() | 1 (3 rows) SELECT * FROM test_merge WHERE id IN (1, 2, 3, 4, 5, 6, 7, 8, 9); @@ -80,7 +80,60 @@ SELECT query, calls FROM pg_stat_statements ORDER BY query COLLATE "C"; query | calls ------------------------------------------------------------------------+------- SELECT * FROM test_merge WHERE id IN ($1) | 1 - SELECT * FROM test_merge WHERE id IN (...) | 4 + SELECT * FROM test_merge WHERE id IN (... [1-9 entries]) | 2 + SELECT * FROM test_merge WHERE id IN (... [10-99 entries]) | 2 + SELECT pg_stat_statements_reset() | 1 + SELECT query, calls FROM pg_stat_statements ORDER BY query COLLATE "C" | 1 +(5 rows) + +-- Second order of magnitude, brace yourself +SELECT pg_stat_statements_reset(); + pg_stat_statements_reset +-------------------------- + +(1 row) + +SELECT * FROM test_merge WHERE id IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31, 32, 33, 34, 35, 36, 37, 38, 39, 40, 41, 42, 43, 44, 45, 46, 47, 48, 49, 50, 51, 52, 53, 54, 55, 56, 57, 58, 59, 60, 61, 62, 63, 64, 65, 66, 67, 68, 69, 70, 71, 72, 73, 74, 75, 76, 77, 78, 79, 80, 81, 82, 83, 84, 85, 86, 87, 88, 89, 90, 91, 92, 93, 94, 95, 96, 97, 98, 99, 100, 101, 102, 103, 104, 105, 106, 107, 108, 109, 110); + id | data +----+------ +(0 rows) + +SELECT query, calls FROM pg_stat_statements ORDER BY query COLLATE "C"; + query | calls +--------------------------------------------------------------+------- + SELECT * FROM test_merge WHERE id IN (... [100-999 entries]) | 1 + SELECT pg_stat_statements_reset() | 1 +(2 rows) + +-- With gaps on the threshold +SELECT pg_stat_statements_reset(); + pg_stat_statements_reset +-------------------------- + +(1 row) + +SELECT * FROM test_merge WHERE id IN (1, 2, 3, 4); + id | data +----+------ +(0 rows) + +SELECT query, calls FROM pg_stat_statements ORDER BY query COLLATE "C"; + query | calls +----------------------------------------------------------+------- + SELECT * FROM test_merge WHERE id IN (... [1-9 entries]) | 1 + SELECT pg_stat_statements_reset() | 1 +(2 rows) + +SELECT * FROM test_merge WHERE id IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15); + id | data +----+------ +(0 rows) + +SELECT query, calls FROM pg_stat_statements ORDER BY query COLLATE "C"; + query | calls +------------------------------------------------------------------------+------- + SELECT * FROM test_merge WHERE id IN (... [1-9 entries]) | 1 + SELECT * FROM test_merge WHERE id IN (... [10-99 entries]) | 1 SELECT pg_stat_statements_reset() | 1 SELECT query, calls FROM pg_stat_statements ORDER BY query COLLATE "C" | 1 (4 rows) @@ -108,11 +161,12 @@ SELECT * FROM test_merge WHERE id IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11) and dat (0 rows) SELECT query, calls FROM pg_stat_statements ORDER BY query COLLATE "C"; - query | calls -----------------------------------------------------------+------- - SELECT * FROM test_merge WHERE id IN (...) and data = $3 | 3 - SELECT pg_stat_statements_reset() | 1 -(2 rows) + query | calls +--------------------------------------------------------------------------+------- + SELECT * FROM test_merge WHERE id IN (... [1-9 entries]) and data = $3 | 1 + SELECT * FROM test_merge WHERE id IN (... [10-99 entries]) and data = $3 | 2 + SELECT pg_stat_statements_reset() | 1 +(3 rows) -- No constants simplification SELECT pg_stat_statements_reset(); @@ -147,10 +201,10 @@ SELECT * FROM test_merge_numeric WHERE id IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11) (0 rows) SELECT query, calls FROM pg_stat_statements ORDER BY query COLLATE "C"; - query | calls -----------------------------------------------------+------- - SELECT * FROM test_merge_numeric WHERE id IN (...) | 1 - SELECT pg_stat_statements_reset() | 1 + query | calls +--------------------------------------------------------------------+------- + SELECT * FROM test_merge_numeric WHERE id IN (... [10-99 entries]) | 1 + SELECT pg_stat_statements_reset() | 1 (2 rows) -- Test constants evaluation, verifies a tricky part to make sure there are no diff --git a/contrib/pg_stat_statements/pg_stat_statements.c b/contrib/pg_stat_statements/pg_stat_statements.c index 4d47a746670..a5702c3d749 100644 --- a/contrib/pg_stat_statements/pg_stat_statements.c +++ b/contrib/pg_stat_statements/pg_stat_statements.c @@ -2714,6 +2714,8 @@ generate_normalized_query(JumbleState *jstate, const char *query, last_tok_len = 0; /* Length (in bytes) of that tok */ bool skip = false; /* Signals that certain constants are merged together and have to be skipped */ + int magnitude; /* Order of magnitute for number of merged + constants */ /* @@ -2754,7 +2756,8 @@ generate_normalized_query(JumbleState *jstate, const char *query, Assert(len_to_wrt >= 0); /* Normal path, non merged constant */ - if (!jstate->clocations[i].merged) + magnitude = jstate->clocations[i].magnitude; + if (magnitude == 0) { memcpy(norm_query + n_quer_loc, query + quer_loc, len_to_wrt); n_quer_loc += len_to_wrt; @@ -2770,12 +2773,22 @@ generate_normalized_query(JumbleState *jstate, const char *query, /* The firsts merged constant */ else if (!skip) { + static const uint32 powers_of_ten[] = { + 1, 10, 100, + 1000, 10000, 100000, + 1000000, 10000000, 100000000, + 1000000000 + }; + int lower_merged = powers_of_ten[magnitude - 1]; + int upper_merged = powers_of_ten[magnitude]; + memcpy(norm_query + n_quer_loc, query + quer_loc, len_to_wrt); n_quer_loc += len_to_wrt; /* Skip the following until a non merged constant appear */ skip = true; - n_quer_loc += sprintf(norm_query + n_quer_loc, "..."); + n_quer_loc += sprintf(norm_query + n_quer_loc, "... [%d-%d entries]", + lower_merged, upper_merged - 1); } /* Otherwise the constant is merged away */ diff --git a/contrib/pg_stat_statements/sql/merging.sql b/contrib/pg_stat_statements/sql/merging.sql index 8b589135daa..c515e48d50c 100644 --- a/contrib/pg_stat_statements/sql/merging.sql +++ b/contrib/pg_stat_statements/sql/merging.sql @@ -27,6 +27,19 @@ SELECT * FROM test_merge WHERE id IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10); SELECT * FROM test_merge WHERE id IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11); SELECT query, calls FROM pg_stat_statements ORDER BY query COLLATE "C"; +-- Second order of magnitude, brace yourself +SELECT pg_stat_statements_reset(); +SELECT * FROM test_merge WHERE id IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31, 32, 33, 34, 35, 36, 37, 38, 39, 40, 41, 42, 43, 44, 45, 46, 47, 48, 49, 50, 51, 52, 53, 54, 55, 56, 57, 58, 59, 60, 61, 62, 63, 64, 65, 66, 67, 68, 69, 70, 71, 72, 73, 74, 75, 76, 77, 78, 79, 80, 81, 82, 83, 84, 85, 86, 87, 88, 89, 90, 91, 92, 93, 94, 95, 96, 97, 98, 99, 100, 101, 102, 103, 104, 105, 106, 107, 108, 109, 110); +SELECT query, calls FROM pg_stat_statements ORDER BY query COLLATE "C"; + +-- With gaps on the threshold +SELECT pg_stat_statements_reset(); +SELECT * FROM test_merge WHERE id IN (1, 2, 3, 4); +SELECT query, calls FROM pg_stat_statements ORDER BY query COLLATE "C"; + +SELECT * FROM test_merge WHERE id IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15); +SELECT query, calls FROM pg_stat_statements ORDER BY query COLLATE "C"; + -- More conditions in the query SELECT pg_stat_statements_reset(); diff --git a/doc/src/sgml/pgstatstatements.sgml b/doc/src/sgml/pgstatstatements.sgml index c78140f8858..ff24153c493 100644 --- a/doc/src/sgml/pgstatstatements.sgml +++ b/doc/src/sgml/pgstatstatements.sgml @@ -560,7 +560,7 @@ =# SELECT * FROM test WHERE a IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12); =# SELECT query, calls FROM pg_stat_statements; -[ RECORD 1 ]------------------------------ -query | SELECT * FROM test WHERE a IN (...) +query | SELECT * FROM test WHERE a IN (... [10-99 entries]) calls | 2 -[ RECORD 2 ]------------------------------ query | SELECT pg_stat_statements_reset() @@ -897,10 +897,11 @@ calls | 1 with an array of different lenght. If this parameter is on, an array of constants will contribute only the - first and the last elements to the query identifier. It means two - occurences of the same query, where the only difference is number of - constants in the array, are going to get the same query identifier. - Such queries are represented in form <literal>'(...)'</literal>. + first element, the last element and the number of elements to the query + identifier. It means two occurences of the same query, where the only + difference is number of constants in the array, are going to get the + same query identifier if the arrays are of similar length. + Such queries are represented in form <literal>'(... [10-99 entries])'</literal>. The parameter could be used to reduce amount of repeating data stored via <structname>pg_stat_statements</structname>. The default value is off. diff --git a/src/backend/nodes/queryjumblefuncs.c b/src/backend/nodes/queryjumblefuncs.c index 4bc16dde6a0..a1d4567ca66 100644 --- a/src/backend/nodes/queryjumblefuncs.c +++ b/src/backend/nodes/queryjumblefuncs.c @@ -37,6 +37,8 @@ #include "nodes/queryjumble.h" #include "parser/scansup.h" +#include "utils/numutils.h" + #define JUMBLE_SIZE 1024 /* query serialization buffer size */ /* GUC parameters */ @@ -51,7 +53,7 @@ bool query_id_enabled = false; static void AppendJumble(JumbleState *jstate, const unsigned char *item, Size size); static void RecordConstLocation(JumbleState *jstate, - int location, bool merged); + int location, int magnitude); static void _jumbleNode(JumbleState *jstate, Node *node); static void _jumbleElements(JumbleState *jstate, List *elements); static void _jumbleA_Const(JumbleState *jstate, Node *node); @@ -206,12 +208,15 @@ AppendJumble(JumbleState *jstate, const unsigned char *item, Size size) * Record location of constant within query string of query tree that is * currently being walked. * - * Merged argument signals that the constant represents the first or the last - * element in a series of merged constants, and everything but the first/last - * element contributes nothing to the jumble hash. + * Magnitude argument larger than zero signals that the constant represents the + * first or the last element in a series of merged constants, and everything + * but such first/last element will contribute nothing to the jumble hash. The + * magnitute value specifies order of magnitute (i.e. how many digits it has) + * for the number of elements in the series, to represent the fact of merging + * later on. */ static void -RecordConstLocation(JumbleState *jstate, int location, bool merged) +RecordConstLocation(JumbleState *jstate, int location, int magnitude) { /* -1 indicates unknown or undefined location */ if (location >= 0) @@ -227,7 +232,7 @@ RecordConstLocation(JumbleState *jstate, int location, bool merged) } jstate->clocations[jstate->clocations_count].location = location; /* initialize lengths to -1 to simplify third-party module usage */ - jstate->clocations[jstate->clocations_count].merged = merged; + jstate->clocations[jstate->clocations_count].magnitude = magnitude; jstate->clocations[jstate->clocations_count].length = -1; jstate->clocations_count++; } @@ -237,24 +242,26 @@ RecordConstLocation(JumbleState *jstate, int location, bool merged) * Verify if the provided list contains could be merged down, which means it * contains only constant expressions. * - * Return value indicates if merging is possible. + * Return value is the order of magnitude (i.e. how many digits it has) for + * length of the list (to use for representation purposes later on) if merging + * is possible, otherwise zero. * * Note that this function searches only for explicit Const nodes and does not * try to simplify expressions. */ -static bool +static int IsMergeableConstList(List *elements, Const **firstConst, Const **lastConst) { ListCell *temp; Node *firstExpr = NULL; if (elements == NULL) - return false; + return 0; if (!query_id_const_merge) { /* Merging is disabled, process everything one by one */ - return false; + return 0; } firstExpr = linitial(elements); @@ -268,26 +275,26 @@ IsMergeableConstList(List *elements, Const **firstConst, Const **lastConst) { foreach(temp, elements) if (!IsA(lfirst(temp), Const)) - return false; + return 0; *firstConst = (Const *) firstExpr; *lastConst = llast_node(Const, elements); - return true; + return decimalLength32(elements->length); } /* * If we end up here, it means no constants merging is possible, process * the list as usual. */ - return false; + return 0; } #define JUMBLE_NODE(item) \ _jumbleNode(jstate, (Node *) expr->item) #define JUMBLE_ELEMENTS(list) \ _jumbleElements(jstate, (List *) expr->list) -#define JUMBLE_LOCATION(location, merged) \ - RecordConstLocation(jstate, expr->location, merged) +#define JUMBLE_LOCATION(location, magnitude) \ + RecordConstLocation(jstate, expr->location, magnitude) #define JUMBLE_FIELD(item) \ AppendJumble(jstate, (const unsigned char *) &(expr->item), sizeof(expr->item)) #define JUMBLE_FIELD_SINGLE(item) \ @@ -304,10 +311,19 @@ static void _jumbleElements(JumbleState *jstate, List *elements) { Const *first, *last; - if(IsMergeableConstList(elements, &first, &last)) + int magnitude = IsMergeableConstList(elements, &first, &last); + + if (magnitude) { - RecordConstLocation(jstate, first->location, true); - RecordConstLocation(jstate, last->location, true); + RecordConstLocation(jstate, first->location, magnitude); + RecordConstLocation(jstate, last->location, magnitude); + + /* + * After merging constants down we end up with only two constants, the + * first and the last one. To distinguish the order of magnitute behind + * merged constants, add its value into the jumble. + */ + JUMBLE_FIELD_SINGLE(magnitude); } else { diff --git a/src/include/nodes/queryjumble.h b/src/include/nodes/queryjumble.h index c64a007ad3f..8ee2e9afbb6 100644 --- a/src/include/nodes/queryjumble.h +++ b/src/include/nodes/queryjumble.h @@ -26,9 +26,12 @@ typedef struct LocationLen /* * Indicates the constant represents the beginning or the end of a merged - * constants interval. + * constants interval. The value shows how many constants were merged away + * (up to a power of 10), or in other words the order of manitude for + * number of merged constants (i.e. how many digits it has). Otherwise the + * value is 0, indicating that no merging was performed. */ - bool merged; + int magnitude; } LocationLen; /* -- 2.41.0 --si27d3bgnnpsswlv Content-Type: text/x-diff; charset=us-ascii Content-Disposition: attachment; filename="v17-0004-Introduce-query_id_const_merge_threshold.patch" ^ permalink raw reply [nested|flat] 9+ messages in thread
* [PATCH v15 3/4] Merge constants in ArrayExpr into groups @ 2023-10-15 08:06 Dmitrii Dolgov <[email protected]> 0 siblings, 0 replies; 9+ messages in thread From: Dmitrii Dolgov @ 2023-10-15 08:06 UTC (permalink / raw) Using query_id_const_merge only first/last element in an ArrayExpr will be used to compute query id. Extend this to take into account number of elements, and merge constants into groups based on it. Resulting groups are powers of 10, i.e. 1 to 9, 10 to 99, etc. --- .../pg_stat_statements/expected/merging.out | 84 +++++++++++++++---- .../pg_stat_statements/pg_stat_statements.c | 17 +++- contrib/pg_stat_statements/sql/merging.sql | 13 +++ doc/src/sgml/config.sgml | 9 +- doc/src/sgml/pgstatstatements.sgml | 2 +- src/backend/nodes/queryjumblefuncs.c | 52 ++++++++---- src/include/nodes/queryjumble.h | 7 +- 7 files changed, 142 insertions(+), 42 deletions(-) diff --git a/contrib/pg_stat_statements/expected/merging.out b/contrib/pg_stat_statements/expected/merging.out index 7711572e0b7..1bb75a93045 100644 --- a/contrib/pg_stat_statements/expected/merging.out +++ b/contrib/pg_stat_statements/expected/merging.out @@ -54,11 +54,11 @@ SELECT * FROM test_merge WHERE id IN (1, 2, 3); (0 rows) SELECT query, calls FROM pg_stat_statements ORDER BY query COLLATE "C"; - query | calls ---------------------------------------------+------- - SELECT * FROM test_merge WHERE id IN ($1) | 1 - SELECT * FROM test_merge WHERE id IN (...) | 1 - SELECT pg_stat_statements_reset() | 1 + query | calls +----------------------------------------------------------+------- + SELECT * FROM test_merge WHERE id IN ($1) | 1 + SELECT * FROM test_merge WHERE id IN (... [1-9 entries]) | 1 + SELECT pg_stat_statements_reset() | 1 (3 rows) SELECT * FROM test_merge WHERE id IN (1, 2, 3, 4, 5, 6, 7, 8, 9); @@ -80,7 +80,60 @@ SELECT query, calls FROM pg_stat_statements ORDER BY query COLLATE "C"; query | calls ------------------------------------------------------------------------+------- SELECT * FROM test_merge WHERE id IN ($1) | 1 - SELECT * FROM test_merge WHERE id IN (...) | 4 + SELECT * FROM test_merge WHERE id IN (... [1-9 entries]) | 2 + SELECT * FROM test_merge WHERE id IN (... [10-99 entries]) | 2 + SELECT pg_stat_statements_reset() | 1 + SELECT query, calls FROM pg_stat_statements ORDER BY query COLLATE "C" | 1 +(5 rows) + +-- Second order of magnitude, brace yourself +SELECT pg_stat_statements_reset(); + pg_stat_statements_reset +-------------------------- + +(1 row) + +SELECT * FROM test_merge WHERE id IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31, 32, 33, 34, 35, 36, 37, 38, 39, 40, 41, 42, 43, 44, 45, 46, 47, 48, 49, 50, 51, 52, 53, 54, 55, 56, 57, 58, 59, 60, 61, 62, 63, 64, 65, 66, 67, 68, 69, 70, 71, 72, 73, 74, 75, 76, 77, 78, 79, 80, 81, 82, 83, 84, 85, 86, 87, 88, 89, 90, 91, 92, 93, 94, 95, 96, 97, 98, 99, 100, 101, 102, 103, 104, 105, 106, 107, 108, 109, 110); + id | data +----+------ +(0 rows) + +SELECT query, calls FROM pg_stat_statements ORDER BY query COLLATE "C"; + query | calls +--------------------------------------------------------------+------- + SELECT * FROM test_merge WHERE id IN (... [100-999 entries]) | 1 + SELECT pg_stat_statements_reset() | 1 +(2 rows) + +-- With gaps on the threshold +SELECT pg_stat_statements_reset(); + pg_stat_statements_reset +-------------------------- + +(1 row) + +SELECT * FROM test_merge WHERE id IN (1, 2, 3, 4); + id | data +----+------ +(0 rows) + +SELECT query, calls FROM pg_stat_statements ORDER BY query COLLATE "C"; + query | calls +----------------------------------------------------------+------- + SELECT * FROM test_merge WHERE id IN (... [1-9 entries]) | 1 + SELECT pg_stat_statements_reset() | 1 +(2 rows) + +SELECT * FROM test_merge WHERE id IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15); + id | data +----+------ +(0 rows) + +SELECT query, calls FROM pg_stat_statements ORDER BY query COLLATE "C"; + query | calls +------------------------------------------------------------------------+------- + SELECT * FROM test_merge WHERE id IN (... [1-9 entries]) | 1 + SELECT * FROM test_merge WHERE id IN (... [10-99 entries]) | 1 SELECT pg_stat_statements_reset() | 1 SELECT query, calls FROM pg_stat_statements ORDER BY query COLLATE "C" | 1 (4 rows) @@ -108,11 +161,12 @@ SELECT * FROM test_merge WHERE id IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11) and dat (0 rows) SELECT query, calls FROM pg_stat_statements ORDER BY query COLLATE "C"; - query | calls -----------------------------------------------------------+------- - SELECT * FROM test_merge WHERE id IN (...) and data = $3 | 3 - SELECT pg_stat_statements_reset() | 1 -(2 rows) + query | calls +--------------------------------------------------------------------------+------- + SELECT * FROM test_merge WHERE id IN (... [1-9 entries]) and data = $3 | 1 + SELECT * FROM test_merge WHERE id IN (... [10-99 entries]) and data = $3 | 2 + SELECT pg_stat_statements_reset() | 1 +(3 rows) -- No constants simplification SELECT pg_stat_statements_reset(); @@ -147,10 +201,10 @@ SELECT * FROM test_merge_numeric WHERE id IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11) (0 rows) SELECT query, calls FROM pg_stat_statements ORDER BY query COLLATE "C"; - query | calls -----------------------------------------------------+------- - SELECT * FROM test_merge_numeric WHERE id IN (...) | 1 - SELECT pg_stat_statements_reset() | 1 + query | calls +--------------------------------------------------------------------+------- + SELECT * FROM test_merge_numeric WHERE id IN (... [10-99 entries]) | 1 + SELECT pg_stat_statements_reset() | 1 (2 rows) -- Test constants evaluation, verifies a tricky part to make sure there are no diff --git a/contrib/pg_stat_statements/pg_stat_statements.c b/contrib/pg_stat_statements/pg_stat_statements.c index 0b03009a053..24d7401257d 100644 --- a/contrib/pg_stat_statements/pg_stat_statements.c +++ b/contrib/pg_stat_statements/pg_stat_statements.c @@ -2697,6 +2697,8 @@ generate_normalized_query(JumbleState *jstate, const char *query, last_tok_len = 0; /* Length (in bytes) of that tok */ bool skip = false; /* Signals that certain constants are merged together and have to be skipped */ + int magnitude; /* Order of magnitute for number of merged + constants */ /* @@ -2737,7 +2739,8 @@ generate_normalized_query(JumbleState *jstate, const char *query, Assert(len_to_wrt >= 0); /* Normal path, non merged constant */ - if (!jstate->clocations[i].merged) + magnitude = jstate->clocations[i].magnitude; + if (magnitude == 0) { memcpy(norm_query + n_quer_loc, query + quer_loc, len_to_wrt); n_quer_loc += len_to_wrt; @@ -2753,12 +2756,22 @@ generate_normalized_query(JumbleState *jstate, const char *query, /* The firsts merged constant */ else if (!skip) { + static const uint32 powers_of_ten[] = { + 1, 10, 100, + 1000, 10000, 100000, + 1000000, 10000000, 100000000, + 1000000000 + }; + int lower_merged = powers_of_ten[magnitude - 1]; + int upper_merged = powers_of_ten[magnitude]; + memcpy(norm_query + n_quer_loc, query + quer_loc, len_to_wrt); n_quer_loc += len_to_wrt; /* Skip the following until a non merged constant appear */ skip = true; - n_quer_loc += sprintf(norm_query + n_quer_loc, "..."); + n_quer_loc += sprintf(norm_query + n_quer_loc, "... [%d-%d entries]", + lower_merged, upper_merged - 1); } /* Otherwise the constant is merged away */ diff --git a/contrib/pg_stat_statements/sql/merging.sql b/contrib/pg_stat_statements/sql/merging.sql index 02266a92ce8..75960159a1d 100644 --- a/contrib/pg_stat_statements/sql/merging.sql +++ b/contrib/pg_stat_statements/sql/merging.sql @@ -27,6 +27,19 @@ SELECT * FROM test_merge WHERE id IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10); SELECT * FROM test_merge WHERE id IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11); SELECT query, calls FROM pg_stat_statements ORDER BY query COLLATE "C"; +-- Second order of magnitude, brace yourself +SELECT pg_stat_statements_reset(); +SELECT * FROM test_merge WHERE id IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31, 32, 33, 34, 35, 36, 37, 38, 39, 40, 41, 42, 43, 44, 45, 46, 47, 48, 49, 50, 51, 52, 53, 54, 55, 56, 57, 58, 59, 60, 61, 62, 63, 64, 65, 66, 67, 68, 69, 70, 71, 72, 73, 74, 75, 76, 77, 78, 79, 80, 81, 82, 83, 84, 85, 86, 87, 88, 89, 90, 91, 92, 93, 94, 95, 96, 97, 98, 99, 100, 101, 102, 103, 104, 105, 106, 107, 108, 109, 110); +SELECT query, calls FROM pg_stat_statements ORDER BY query COLLATE "C"; + +-- With gaps on the threshold +SELECT pg_stat_statements_reset(); +SELECT * FROM test_merge WHERE id IN (1, 2, 3, 4); +SELECT query, calls FROM pg_stat_statements ORDER BY query COLLATE "C"; + +SELECT * FROM test_merge WHERE id IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15); +SELECT query, calls FROM pg_stat_statements ORDER BY query COLLATE "C"; + -- More conditions in the query SELECT pg_stat_statements_reset(); diff --git a/doc/src/sgml/config.sgml b/doc/src/sgml/config.sgml index 55b0795bce1..4fd997284c9 100644 --- a/doc/src/sgml/config.sgml +++ b/doc/src/sgml/config.sgml @@ -8205,15 +8205,16 @@ COPY postgres_log FROM '/full/path/to/logfile.csv' WITH csv; with an array of different lenght. If this parameter is on, an array of constants will contribute only the - first and the last elements to the query identifier. It means two - occurences of the same query, where the only difference is number of - constants in the array, are going to get the same query identifier. + first element, the last element and the number of elements to the query + identifier. It means two occurences of the same query, where the only + difference is number of constants in the array, are going to get the + same query identifier if the arrays are of similar length. The parameter could be used to reduce amount of repeating data stored via <xref linkend="pgstatstatements"/> extension. The default value is off. The <xref linkend="pgstatstatements"/> extension will represent such - queries in form <literal>'(...)'</literal>. + queries in form <literal>'(... [10-99 entries])'</literal>. </para> </listitem> </varlistentry> diff --git a/doc/src/sgml/pgstatstatements.sgml b/doc/src/sgml/pgstatstatements.sgml index 8df4064cbf4..1f6e6a0e76b 100644 --- a/doc/src/sgml/pgstatstatements.sgml +++ b/doc/src/sgml/pgstatstatements.sgml @@ -559,7 +559,7 @@ =# SELECT * FROM test WHERE a IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12); =# SELECT query, calls FROM pg_stat_statements; -[ RECORD 1 ]------------------------------ -query | SELECT * FROM test WHERE a IN (...) +query | SELECT * FROM test WHERE a IN (... [10-99 entries]) calls | 2 -[ RECORD 2 ]------------------------------ query | SELECT pg_stat_statements_reset() diff --git a/src/backend/nodes/queryjumblefuncs.c b/src/backend/nodes/queryjumblefuncs.c index 52d45c2b093..b198fe2579e 100644 --- a/src/backend/nodes/queryjumblefuncs.c +++ b/src/backend/nodes/queryjumblefuncs.c @@ -37,6 +37,8 @@ #include "nodes/queryjumble.h" #include "parser/scansup.h" +#include "utils/numutils.h" + #define JUMBLE_SIZE 1024 /* query serialization buffer size */ /* GUC parameters */ @@ -51,7 +53,7 @@ bool query_id_enabled = false; static void AppendJumble(JumbleState *jstate, const unsigned char *item, Size size); static void RecordConstLocation(JumbleState *jstate, - int location, bool merged); + int location, int magnitude); static void _jumbleNode(JumbleState *jstate, Node *node); static void _jumbleA_Const(JumbleState *jstate, Node *node); static void _jumbleList(JumbleState *jstate, Node *node); @@ -193,12 +195,15 @@ AppendJumble(JumbleState *jstate, const unsigned char *item, Size size) * Record location of constant within query string of query tree that is * currently being walked. * - * Merged argument signals that the constant represents the first or the last - * element in a series of merged constants, and everything but the first/last - * element contributes nothing to the jumble hash. + * Magnitude argument larger than zero signals that the constant represents the + * first or the last element in a series of merged constants, and everything + * but such first/last element will contribute nothing to the jumble hash. The + * magnitute value specifies order of magnitute (i.e. how many digits it has) + * for the number of elements in the series, to represent the fact of merging + * later on. */ static void -RecordConstLocation(JumbleState *jstate, int location, bool merged) +RecordConstLocation(JumbleState *jstate, int location, int magnitude) { /* -1 indicates unknown or undefined location */ if (location >= 0) @@ -214,7 +219,7 @@ RecordConstLocation(JumbleState *jstate, int location, bool merged) } jstate->clocations[jstate->clocations_count].location = location; /* initialize lengths to -1 to simplify third-party module usage */ - jstate->clocations[jstate->clocations_count].merged = merged; + jstate->clocations[jstate->clocations_count].magnitude = magnitude; jstate->clocations[jstate->clocations_count].length = -1; jstate->clocations_count++; } @@ -224,24 +229,26 @@ RecordConstLocation(JumbleState *jstate, int location, bool merged) * Verify if the provided list contains could be merged down, which means it * contains only constant expressions. * - * Return value indicates if merging is possible. + * Return value is the order of magnitude (i.e. how many digits it has) for + * length of the list (to use for representation purposes later on) if merging + * is possible, otherwise zero. * * Note that this function searches only for explicit Const nodes and does not * try to simplify expressions. */ -static bool +static int IsMergeableConstList(List *elements, Const **firstConst, Const **lastConst) { ListCell *temp; Node *firstExpr = NULL; if (elements == NULL) - return false; + return 0; if (!query_id_const_merge) { /* Merging is disabled, process everything one by one */ - return false; + return 0; } firstExpr = linitial(elements); @@ -255,24 +262,24 @@ IsMergeableConstList(List *elements, Const **firstConst, Const **lastConst) { foreach(temp, elements) if (!IsA(lfirst(temp), Const)) - return false; + return 0; *firstConst = (Const *) firstExpr; *lastConst = llast_node(Const, elements); - return true; + return decimalLength32(elements->length); } /* * If we end up here, it means no constants merging is possible, process * the list as usual. */ - return false; + return 0; } #define JUMBLE_NODE(item) \ _jumbleNode(jstate, (Node *) expr->item) -#define JUMBLE_LOCATION(location, merged) \ - RecordConstLocation(jstate, expr->location, merged) +#define JUMBLE_LOCATION(location, magnitude) \ + RecordConstLocation(jstate, expr->location, magnitude) #define JUMBLE_FIELD(item) \ AppendJumble(jstate, (const unsigned char *) &(expr->item), sizeof(expr->item)) #define JUMBLE_FIELD_SINGLE(item) \ @@ -290,10 +297,19 @@ _jumbleArrayExpr(JumbleState *jstate, Node *node) { ArrayExpr *expr = (ArrayExpr *) node; Const *first, *last; - if(IsMergeableConstList(expr->elements, &first, &last)) + int magnitude = IsMergeableConstList(expr->elements, &first, &last); + + if (magnitude) { - RecordConstLocation(jstate, first->location, true); - RecordConstLocation(jstate, last->location, true); + RecordConstLocation(jstate, first->location, magnitude); + RecordConstLocation(jstate, last->location, magnitude); + + /* + * After merging constants down we end up with only two constants, the + * first and the last one. To distinguish the order of magnitute behind + * merged constants, add its value into the jumble. + */ + JUMBLE_FIELD_SINGLE(magnitude); } else { diff --git a/src/include/nodes/queryjumble.h b/src/include/nodes/queryjumble.h index 225957d7b3c..cfa99efc14e 100644 --- a/src/include/nodes/queryjumble.h +++ b/src/include/nodes/queryjumble.h @@ -27,9 +27,12 @@ typedef struct LocationLen /* * Indicates the constant represents the beginning or the end of a merged - * constants interval. + * constants interval. The value shows how many constants were merged away + * (up to a power of 10), or in other words the order of manitude for + * number of merged constants (i.e. how many digits it has). Otherwise the + * value is 0, indicating that no merging was performed. */ - bool merged; + int magnitude; } LocationLen; /* -- 2.41.0 --svnhsmawrtczycxj Content-Type: text/x-diff; charset=us-ascii Content-Disposition: attachment; filename="v15-0004-Add-query_id_const_merge_threshold.patch" ^ permalink raw reply [nested|flat] 9+ messages in thread
* [PATCH v19 3/4] Merge constants in ArrayExpr into groups @ 2024-01-22 20:20 Dmitrii Dolgov <[email protected]> 0 siblings, 0 replies; 9+ messages in thread From: Dmitrii Dolgov @ 2024-01-22 20:20 UTC (permalink / raw) Using query_id_const_merge only first/last element in an ArrayExpr will be used to compute query id. Extend this to take into account number of elements, and merge constants into groups based on it. Resulting groups are powers of 10, i.e. 1 to 9, 10 to 99, etc. --- .../pg_stat_statements/expected/merging.out | 84 +++++++++++++++---- .../pg_stat_statements/pg_stat_statements.c | 17 +++- contrib/pg_stat_statements/sql/merging.sql | 13 +++ doc/src/sgml/pgstatstatements.sgml | 11 +-- src/backend/nodes/queryjumblefuncs.c | 52 ++++++++---- src/include/nodes/queryjumble.h | 7 +- 6 files changed, 142 insertions(+), 42 deletions(-) diff --git a/contrib/pg_stat_statements/expected/merging.out b/contrib/pg_stat_statements/expected/merging.out index 1e58283afe8..0cb4f67b8b7 100644 --- a/contrib/pg_stat_statements/expected/merging.out +++ b/contrib/pg_stat_statements/expected/merging.out @@ -54,11 +54,11 @@ SELECT * FROM test_merge WHERE id IN (1, 2, 3); (0 rows) SELECT query, calls FROM pg_stat_statements ORDER BY query COLLATE "C"; - query | calls -----------------------------------------------------+------- - SELECT * FROM test_merge WHERE id IN ($1) | 1 - SELECT * FROM test_merge WHERE id IN (...) | 1 - SELECT pg_stat_statements_reset() IS NOT NULL AS t | 1 + query | calls +----------------------------------------------------------+------- + SELECT * FROM test_merge WHERE id IN ($1) | 1 + SELECT * FROM test_merge WHERE id IN (... [1-9 entries]) | 1 + SELECT pg_stat_statements_reset() IS NOT NULL AS t | 1 (3 rows) SELECT * FROM test_merge WHERE id IN (1, 2, 3, 4, 5, 6, 7, 8, 9); @@ -80,7 +80,60 @@ SELECT query, calls FROM pg_stat_statements ORDER BY query COLLATE "C"; query | calls ------------------------------------------------------------------------+------- SELECT * FROM test_merge WHERE id IN ($1) | 1 - SELECT * FROM test_merge WHERE id IN (...) | 4 + SELECT * FROM test_merge WHERE id IN (... [1-9 entries]) | 2 + SELECT * FROM test_merge WHERE id IN (... [10-99 entries]) | 2 + SELECT pg_stat_statements_reset() IS NOT NULL AS t | 1 + SELECT query, calls FROM pg_stat_statements ORDER BY query COLLATE "C" | 1 +(5 rows) + +-- Second order of magnitude, brace yourself +SELECT pg_stat_statements_reset() IS NOT NULL AS t; + t +--- + t +(1 row) + +SELECT * FROM test_merge WHERE id IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31, 32, 33, 34, 35, 36, 37, 38, 39, 40, 41, 42, 43, 44, 45, 46, 47, 48, 49, 50, 51, 52, 53, 54, 55, 56, 57, 58, 59, 60, 61, 62, 63, 64, 65, 66, 67, 68, 69, 70, 71, 72, 73, 74, 75, 76, 77, 78, 79, 80, 81, 82, 83, 84, 85, 86, 87, 88, 89, 90, 91, 92, 93, 94, 95, 96, 97, 98, 99, 100, 101, 102, 103, 104, 105, 106, 107, 108, 109, 110); + id | data +----+------ +(0 rows) + +SELECT query, calls FROM pg_stat_statements ORDER BY query COLLATE "C"; + query | calls +--------------------------------------------------------------+------- + SELECT * FROM test_merge WHERE id IN (... [100-999 entries]) | 1 + SELECT pg_stat_statements_reset() IS NOT NULL AS t | 1 +(2 rows) + +-- With gaps on the threshold +SELECT pg_stat_statements_reset() IS NOT NULL AS t; + t +--- + t +(1 row) + +SELECT * FROM test_merge WHERE id IN (1, 2, 3, 4); + id | data +----+------ +(0 rows) + +SELECT query, calls FROM pg_stat_statements ORDER BY query COLLATE "C"; + query | calls +----------------------------------------------------------+------- + SELECT * FROM test_merge WHERE id IN (... [1-9 entries]) | 1 + SELECT pg_stat_statements_reset() IS NOT NULL AS t | 1 +(2 rows) + +SELECT * FROM test_merge WHERE id IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15); + id | data +----+------ +(0 rows) + +SELECT query, calls FROM pg_stat_statements ORDER BY query COLLATE "C"; + query | calls +------------------------------------------------------------------------+------- + SELECT * FROM test_merge WHERE id IN (... [1-9 entries]) | 1 + SELECT * FROM test_merge WHERE id IN (... [10-99 entries]) | 1 SELECT pg_stat_statements_reset() IS NOT NULL AS t | 1 SELECT query, calls FROM pg_stat_statements ORDER BY query COLLATE "C" | 1 (4 rows) @@ -108,11 +161,12 @@ SELECT * FROM test_merge WHERE id IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11) and dat (0 rows) SELECT query, calls FROM pg_stat_statements ORDER BY query COLLATE "C"; - query | calls -----------------------------------------------------------+------- - SELECT * FROM test_merge WHERE id IN (...) and data = $3 | 3 - SELECT pg_stat_statements_reset() IS NOT NULL AS t | 1 -(2 rows) + query | calls +--------------------------------------------------------------------------+------- + SELECT * FROM test_merge WHERE id IN (... [1-9 entries]) and data = $3 | 1 + SELECT * FROM test_merge WHERE id IN (... [10-99 entries]) and data = $3 | 2 + SELECT pg_stat_statements_reset() IS NOT NULL AS t | 1 +(3 rows) -- No constants simplification SELECT pg_stat_statements_reset() IS NOT NULL AS t; @@ -147,10 +201,10 @@ SELECT * FROM test_merge_numeric WHERE id IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11) (0 rows) SELECT query, calls FROM pg_stat_statements ORDER BY query COLLATE "C"; - query | calls -----------------------------------------------------+------- - SELECT * FROM test_merge_numeric WHERE id IN (...) | 1 - SELECT pg_stat_statements_reset() IS NOT NULL AS t | 1 + query | calls +--------------------------------------------------------------------+------- + SELECT * FROM test_merge_numeric WHERE id IN (... [10-99 entries]) | 1 + SELECT pg_stat_statements_reset() IS NOT NULL AS t | 1 (2 rows) -- Test constants evaluation, verifies a tricky part to make sure there are no diff --git a/contrib/pg_stat_statements/pg_stat_statements.c b/contrib/pg_stat_statements/pg_stat_statements.c index d7841b51cc9..00eec30feb1 100644 --- a/contrib/pg_stat_statements/pg_stat_statements.c +++ b/contrib/pg_stat_statements/pg_stat_statements.c @@ -2827,6 +2827,8 @@ generate_normalized_query(JumbleState *jstate, const char *query, last_tok_len = 0; /* Length (in bytes) of that tok */ bool skip = false; /* Signals that certain constants are merged together and have to be skipped */ + int magnitude; /* Order of magnitute for number of merged + constants */ /* @@ -2867,7 +2869,8 @@ generate_normalized_query(JumbleState *jstate, const char *query, Assert(len_to_wrt >= 0); /* Normal path, non merged constant */ - if (!jstate->clocations[i].merged) + magnitude = jstate->clocations[i].magnitude; + if (magnitude == 0) { memcpy(norm_query + n_quer_loc, query + quer_loc, len_to_wrt); n_quer_loc += len_to_wrt; @@ -2883,12 +2886,22 @@ generate_normalized_query(JumbleState *jstate, const char *query, /* The firsts merged constant */ else if (!skip) { + static const uint32 powers_of_ten[] = { + 1, 10, 100, + 1000, 10000, 100000, + 1000000, 10000000, 100000000, + 1000000000 + }; + int lower_merged = powers_of_ten[magnitude - 1]; + int upper_merged = powers_of_ten[magnitude]; + memcpy(norm_query + n_quer_loc, query + quer_loc, len_to_wrt); n_quer_loc += len_to_wrt; /* Skip the following until a non merged constant appear */ skip = true; - n_quer_loc += sprintf(norm_query + n_quer_loc, "..."); + n_quer_loc += sprintf(norm_query + n_quer_loc, "... [%d-%d entries]", + lower_merged, upper_merged - 1); } /* Otherwise the constant is merged away */ diff --git a/contrib/pg_stat_statements/sql/merging.sql b/contrib/pg_stat_statements/sql/merging.sql index 71985bb1cd9..657044faded 100644 --- a/contrib/pg_stat_statements/sql/merging.sql +++ b/contrib/pg_stat_statements/sql/merging.sql @@ -27,6 +27,19 @@ SELECT * FROM test_merge WHERE id IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10); SELECT * FROM test_merge WHERE id IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11); SELECT query, calls FROM pg_stat_statements ORDER BY query COLLATE "C"; +-- Second order of magnitude, brace yourself +SELECT pg_stat_statements_reset() IS NOT NULL AS t; +SELECT * FROM test_merge WHERE id IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31, 32, 33, 34, 35, 36, 37, 38, 39, 40, 41, 42, 43, 44, 45, 46, 47, 48, 49, 50, 51, 52, 53, 54, 55, 56, 57, 58, 59, 60, 61, 62, 63, 64, 65, 66, 67, 68, 69, 70, 71, 72, 73, 74, 75, 76, 77, 78, 79, 80, 81, 82, 83, 84, 85, 86, 87, 88, 89, 90, 91, 92, 93, 94, 95, 96, 97, 98, 99, 100, 101, 102, 103, 104, 105, 106, 107, 108, 109, 110); +SELECT query, calls FROM pg_stat_statements ORDER BY query COLLATE "C"; + +-- With gaps on the threshold +SELECT pg_stat_statements_reset() IS NOT NULL AS t; +SELECT * FROM test_merge WHERE id IN (1, 2, 3, 4); +SELECT query, calls FROM pg_stat_statements ORDER BY query COLLATE "C"; + +SELECT * FROM test_merge WHERE id IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15); +SELECT query, calls FROM pg_stat_statements ORDER BY query COLLATE "C"; + -- More conditions in the query SELECT pg_stat_statements_reset() IS NOT NULL AS t; diff --git a/doc/src/sgml/pgstatstatements.sgml b/doc/src/sgml/pgstatstatements.sgml index 2b4c142f647..bac029430be 100644 --- a/doc/src/sgml/pgstatstatements.sgml +++ b/doc/src/sgml/pgstatstatements.sgml @@ -616,7 +616,7 @@ =# SELECT * FROM test WHERE a IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12); =# SELECT query, calls FROM pg_stat_statements; -[ RECORD 1 ]------------------------------ -query | SELECT * FROM test WHERE a IN (...) +query | SELECT * FROM test WHERE a IN (... [10-99 entries]) calls | 2 -[ RECORD 2 ]------------------------------ query | SELECT pg_stat_statements_reset() @@ -968,10 +968,11 @@ calls | 1 with an array of different lenght. If this parameter is on, an array of constants will contribute only the - first and the last elements to the query identifier. It means two - occurences of the same query, where the only difference is number of - constants in the array, are going to get the same query identifier. - Such queries are represented in form <literal>'(...)'</literal>. + first element, the last element and the number of elements to the query + identifier. It means two occurences of the same query, where the only + difference is number of constants in the array, are going to get the + same query identifier if the arrays are of similar length. + Such queries are represented in form <literal>'(... [10-99 entries])'</literal>. The parameter could be used to reduce amount of repeating data stored via <structname>pg_stat_statements</structname>. The default value is off. diff --git a/src/backend/nodes/queryjumblefuncs.c b/src/backend/nodes/queryjumblefuncs.c index e9473def361..a27202cb279 100644 --- a/src/backend/nodes/queryjumblefuncs.c +++ b/src/backend/nodes/queryjumblefuncs.c @@ -37,6 +37,8 @@ #include "nodes/queryjumble.h" #include "parser/scansup.h" +#include "utils/numutils.h" + #define JUMBLE_SIZE 1024 /* query serialization buffer size */ /* GUC parameters */ @@ -57,7 +59,7 @@ bool query_id_enabled = false; static void AppendJumble(JumbleState *jstate, const unsigned char *item, Size size); static void RecordConstLocation(JumbleState *jstate, - int location, bool merged); + int location, int magnitude); static void _jumbleNode(JumbleState *jstate, Node *node); static void _jumbleElements(JumbleState *jstate, List *elements); static void _jumbleA_Const(JumbleState *jstate, Node *node); @@ -212,12 +214,15 @@ AppendJumble(JumbleState *jstate, const unsigned char *item, Size size) * Record location of constant within query string of query tree that is * currently being walked. * - * Merged argument signals that the constant represents the first or the last - * element in a series of merged constants, and everything but the first/last - * element contributes nothing to the jumble hash. + * Magnitude argument larger than zero signals that the constant represents the + * first or the last element in a series of merged constants, and everything + * but such first/last element will contribute nothing to the jumble hash. The + * magnitute value specifies order of magnitute (i.e. how many digits it has) + * for the number of elements in the series, to represent the fact of merging + * later on. */ static void -RecordConstLocation(JumbleState *jstate, int location, bool merged) +RecordConstLocation(JumbleState *jstate, int location, int magnitude) { /* -1 indicates unknown or undefined location */ if (location >= 0) @@ -233,7 +238,7 @@ RecordConstLocation(JumbleState *jstate, int location, bool merged) } jstate->clocations[jstate->clocations_count].location = location; /* initialize lengths to -1 to simplify third-party module usage */ - jstate->clocations[jstate->clocations_count].merged = merged; + jstate->clocations[jstate->clocations_count].magnitude = magnitude; jstate->clocations[jstate->clocations_count].length = -1; jstate->clocations_count++; } @@ -243,24 +248,26 @@ RecordConstLocation(JumbleState *jstate, int location, bool merged) * Verify if the provided list contains could be merged down, which means it * contains only constant expressions. * - * Return value indicates if merging is possible. + * Return value is the order of magnitude (i.e. how many digits it has) for + * length of the list (to use for representation purposes later on) if merging + * is possible, otherwise zero. * * Note that this function searches only for explicit Const nodes and does not * try to simplify expressions. */ -static bool +static int IsMergeableConstList(List *elements, Const **firstConst, Const **lastConst) { ListCell *temp; Node *firstExpr = NULL; if (elements == NULL) - return false; + return 0; if (!query_id_const_merge) { /* Merging is disabled, process everything one by one */ - return false; + return 0; } firstExpr = linitial(elements); @@ -274,26 +281,26 @@ IsMergeableConstList(List *elements, Const **firstConst, Const **lastConst) { foreach(temp, elements) if (!IsA(lfirst(temp), Const)) - return false; + return 0; *firstConst = (Const *) firstExpr; *lastConst = llast_node(Const, elements); - return true; + return decimalLength32(elements->length); } /* * If we end up here, it means no constants merging is possible, process * the list as usual. */ - return false; + return 0; } #define JUMBLE_NODE(item) \ _jumbleNode(jstate, (Node *) expr->item) #define JUMBLE_ELEMENTS(list) \ _jumbleElements(jstate, (List *) expr->list) -#define JUMBLE_LOCATION(location, merged) \ - RecordConstLocation(jstate, expr->location, merged) +#define JUMBLE_LOCATION(location, magnitude) \ + RecordConstLocation(jstate, expr->location, magnitude) #define JUMBLE_FIELD(item) \ AppendJumble(jstate, (const unsigned char *) &(expr->item), sizeof(expr->item)) #define JUMBLE_FIELD_SINGLE(item) \ @@ -310,10 +317,19 @@ static void _jumbleElements(JumbleState *jstate, List *elements) { Const *first, *last; - if(IsMergeableConstList(elements, &first, &last)) + int magnitude = IsMergeableConstList(elements, &first, &last); + + if (magnitude) { - RecordConstLocation(jstate, first->location, true); - RecordConstLocation(jstate, last->location, true); + RecordConstLocation(jstate, first->location, magnitude); + RecordConstLocation(jstate, last->location, magnitude); + + /* + * After merging constants down we end up with only two constants, the + * first and the last one. To distinguish the order of magnitute behind + * merged constants, add its value into the jumble. + */ + JUMBLE_FIELD_SINGLE(magnitude); } else { diff --git a/src/include/nodes/queryjumble.h b/src/include/nodes/queryjumble.h index 8daf0725d7f..551555494e0 100644 --- a/src/include/nodes/queryjumble.h +++ b/src/include/nodes/queryjumble.h @@ -26,9 +26,12 @@ typedef struct LocationLen /* * Indicates the constant represents the beginning or the end of a merged - * constants interval. + * constants interval. The value shows how many constants were merged away + * (up to a power of 10), or in other words the order of manitude for + * number of merged constants (i.e. how many digits it has). Otherwise the + * value is 0, indicating that no merging was performed. */ - bool merged; + int magnitude; } LocationLen; /* -- 2.41.0 --yzrwxjftwx5vp4wd Content-Type: text/x-diff; charset=us-ascii Content-Disposition: attachment; filename="v19-0004-Introduce-query_id_const_merge_threshold.patch" ^ permalink raw reply [nested|flat] 9+ messages in thread
* [PATCH v18 3/4] Merge constants in ArrayExpr into groups @ 2024-01-22 20:20 Dmitrii Dolgov <[email protected]> 0 siblings, 0 replies; 9+ messages in thread From: Dmitrii Dolgov @ 2024-01-22 20:20 UTC (permalink / raw) Using query_id_const_merge only first/last element in an ArrayExpr will be used to compute query id. Extend this to take into account number of elements, and merge constants into groups based on it. Resulting groups are powers of 10, i.e. 1 to 9, 10 to 99, etc. --- .../pg_stat_statements/expected/merging.out | 84 +++++++++++++++---- .../pg_stat_statements/pg_stat_statements.c | 17 +++- contrib/pg_stat_statements/sql/merging.sql | 13 +++ doc/src/sgml/pgstatstatements.sgml | 11 +-- src/backend/nodes/queryjumblefuncs.c | 52 ++++++++---- src/include/nodes/queryjumble.h | 7 +- 6 files changed, 142 insertions(+), 42 deletions(-) diff --git a/contrib/pg_stat_statements/expected/merging.out b/contrib/pg_stat_statements/expected/merging.out index 1e58283afe8..0cb4f67b8b7 100644 --- a/contrib/pg_stat_statements/expected/merging.out +++ b/contrib/pg_stat_statements/expected/merging.out @@ -54,11 +54,11 @@ SELECT * FROM test_merge WHERE id IN (1, 2, 3); (0 rows) SELECT query, calls FROM pg_stat_statements ORDER BY query COLLATE "C"; - query | calls -----------------------------------------------------+------- - SELECT * FROM test_merge WHERE id IN ($1) | 1 - SELECT * FROM test_merge WHERE id IN (...) | 1 - SELECT pg_stat_statements_reset() IS NOT NULL AS t | 1 + query | calls +----------------------------------------------------------+------- + SELECT * FROM test_merge WHERE id IN ($1) | 1 + SELECT * FROM test_merge WHERE id IN (... [1-9 entries]) | 1 + SELECT pg_stat_statements_reset() IS NOT NULL AS t | 1 (3 rows) SELECT * FROM test_merge WHERE id IN (1, 2, 3, 4, 5, 6, 7, 8, 9); @@ -80,7 +80,60 @@ SELECT query, calls FROM pg_stat_statements ORDER BY query COLLATE "C"; query | calls ------------------------------------------------------------------------+------- SELECT * FROM test_merge WHERE id IN ($1) | 1 - SELECT * FROM test_merge WHERE id IN (...) | 4 + SELECT * FROM test_merge WHERE id IN (... [1-9 entries]) | 2 + SELECT * FROM test_merge WHERE id IN (... [10-99 entries]) | 2 + SELECT pg_stat_statements_reset() IS NOT NULL AS t | 1 + SELECT query, calls FROM pg_stat_statements ORDER BY query COLLATE "C" | 1 +(5 rows) + +-- Second order of magnitude, brace yourself +SELECT pg_stat_statements_reset() IS NOT NULL AS t; + t +--- + t +(1 row) + +SELECT * FROM test_merge WHERE id IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31, 32, 33, 34, 35, 36, 37, 38, 39, 40, 41, 42, 43, 44, 45, 46, 47, 48, 49, 50, 51, 52, 53, 54, 55, 56, 57, 58, 59, 60, 61, 62, 63, 64, 65, 66, 67, 68, 69, 70, 71, 72, 73, 74, 75, 76, 77, 78, 79, 80, 81, 82, 83, 84, 85, 86, 87, 88, 89, 90, 91, 92, 93, 94, 95, 96, 97, 98, 99, 100, 101, 102, 103, 104, 105, 106, 107, 108, 109, 110); + id | data +----+------ +(0 rows) + +SELECT query, calls FROM pg_stat_statements ORDER BY query COLLATE "C"; + query | calls +--------------------------------------------------------------+------- + SELECT * FROM test_merge WHERE id IN (... [100-999 entries]) | 1 + SELECT pg_stat_statements_reset() IS NOT NULL AS t | 1 +(2 rows) + +-- With gaps on the threshold +SELECT pg_stat_statements_reset() IS NOT NULL AS t; + t +--- + t +(1 row) + +SELECT * FROM test_merge WHERE id IN (1, 2, 3, 4); + id | data +----+------ +(0 rows) + +SELECT query, calls FROM pg_stat_statements ORDER BY query COLLATE "C"; + query | calls +----------------------------------------------------------+------- + SELECT * FROM test_merge WHERE id IN (... [1-9 entries]) | 1 + SELECT pg_stat_statements_reset() IS NOT NULL AS t | 1 +(2 rows) + +SELECT * FROM test_merge WHERE id IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15); + id | data +----+------ +(0 rows) + +SELECT query, calls FROM pg_stat_statements ORDER BY query COLLATE "C"; + query | calls +------------------------------------------------------------------------+------- + SELECT * FROM test_merge WHERE id IN (... [1-9 entries]) | 1 + SELECT * FROM test_merge WHERE id IN (... [10-99 entries]) | 1 SELECT pg_stat_statements_reset() IS NOT NULL AS t | 1 SELECT query, calls FROM pg_stat_statements ORDER BY query COLLATE "C" | 1 (4 rows) @@ -108,11 +161,12 @@ SELECT * FROM test_merge WHERE id IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11) and dat (0 rows) SELECT query, calls FROM pg_stat_statements ORDER BY query COLLATE "C"; - query | calls -----------------------------------------------------------+------- - SELECT * FROM test_merge WHERE id IN (...) and data = $3 | 3 - SELECT pg_stat_statements_reset() IS NOT NULL AS t | 1 -(2 rows) + query | calls +--------------------------------------------------------------------------+------- + SELECT * FROM test_merge WHERE id IN (... [1-9 entries]) and data = $3 | 1 + SELECT * FROM test_merge WHERE id IN (... [10-99 entries]) and data = $3 | 2 + SELECT pg_stat_statements_reset() IS NOT NULL AS t | 1 +(3 rows) -- No constants simplification SELECT pg_stat_statements_reset() IS NOT NULL AS t; @@ -147,10 +201,10 @@ SELECT * FROM test_merge_numeric WHERE id IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11) (0 rows) SELECT query, calls FROM pg_stat_statements ORDER BY query COLLATE "C"; - query | calls -----------------------------------------------------+------- - SELECT * FROM test_merge_numeric WHERE id IN (...) | 1 - SELECT pg_stat_statements_reset() IS NOT NULL AS t | 1 + query | calls +--------------------------------------------------------------------+------- + SELECT * FROM test_merge_numeric WHERE id IN (... [10-99 entries]) | 1 + SELECT pg_stat_statements_reset() IS NOT NULL AS t | 1 (2 rows) -- Test constants evaluation, verifies a tricky part to make sure there are no diff --git a/contrib/pg_stat_statements/pg_stat_statements.c b/contrib/pg_stat_statements/pg_stat_statements.c index 09912f61b7d..bb8c1894e05 100644 --- a/contrib/pg_stat_statements/pg_stat_statements.c +++ b/contrib/pg_stat_statements/pg_stat_statements.c @@ -2826,6 +2826,8 @@ generate_normalized_query(JumbleState *jstate, const char *query, last_tok_len = 0; /* Length (in bytes) of that tok */ bool skip = false; /* Signals that certain constants are merged together and have to be skipped */ + int magnitude; /* Order of magnitute for number of merged + constants */ /* @@ -2866,7 +2868,8 @@ generate_normalized_query(JumbleState *jstate, const char *query, Assert(len_to_wrt >= 0); /* Normal path, non merged constant */ - if (!jstate->clocations[i].merged) + magnitude = jstate->clocations[i].magnitude; + if (magnitude == 0) { memcpy(norm_query + n_quer_loc, query + quer_loc, len_to_wrt); n_quer_loc += len_to_wrt; @@ -2882,12 +2885,22 @@ generate_normalized_query(JumbleState *jstate, const char *query, /* The firsts merged constant */ else if (!skip) { + static const uint32 powers_of_ten[] = { + 1, 10, 100, + 1000, 10000, 100000, + 1000000, 10000000, 100000000, + 1000000000 + }; + int lower_merged = powers_of_ten[magnitude - 1]; + int upper_merged = powers_of_ten[magnitude]; + memcpy(norm_query + n_quer_loc, query + quer_loc, len_to_wrt); n_quer_loc += len_to_wrt; /* Skip the following until a non merged constant appear */ skip = true; - n_quer_loc += sprintf(norm_query + n_quer_loc, "..."); + n_quer_loc += sprintf(norm_query + n_quer_loc, "... [%d-%d entries]", + lower_merged, upper_merged - 1); } /* Otherwise the constant is merged away */ diff --git a/contrib/pg_stat_statements/sql/merging.sql b/contrib/pg_stat_statements/sql/merging.sql index 71985bb1cd9..657044faded 100644 --- a/contrib/pg_stat_statements/sql/merging.sql +++ b/contrib/pg_stat_statements/sql/merging.sql @@ -27,6 +27,19 @@ SELECT * FROM test_merge WHERE id IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10); SELECT * FROM test_merge WHERE id IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11); SELECT query, calls FROM pg_stat_statements ORDER BY query COLLATE "C"; +-- Second order of magnitude, brace yourself +SELECT pg_stat_statements_reset() IS NOT NULL AS t; +SELECT * FROM test_merge WHERE id IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31, 32, 33, 34, 35, 36, 37, 38, 39, 40, 41, 42, 43, 44, 45, 46, 47, 48, 49, 50, 51, 52, 53, 54, 55, 56, 57, 58, 59, 60, 61, 62, 63, 64, 65, 66, 67, 68, 69, 70, 71, 72, 73, 74, 75, 76, 77, 78, 79, 80, 81, 82, 83, 84, 85, 86, 87, 88, 89, 90, 91, 92, 93, 94, 95, 96, 97, 98, 99, 100, 101, 102, 103, 104, 105, 106, 107, 108, 109, 110); +SELECT query, calls FROM pg_stat_statements ORDER BY query COLLATE "C"; + +-- With gaps on the threshold +SELECT pg_stat_statements_reset() IS NOT NULL AS t; +SELECT * FROM test_merge WHERE id IN (1, 2, 3, 4); +SELECT query, calls FROM pg_stat_statements ORDER BY query COLLATE "C"; + +SELECT * FROM test_merge WHERE id IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15); +SELECT query, calls FROM pg_stat_statements ORDER BY query COLLATE "C"; + -- More conditions in the query SELECT pg_stat_statements_reset() IS NOT NULL AS t; diff --git a/doc/src/sgml/pgstatstatements.sgml b/doc/src/sgml/pgstatstatements.sgml index 2b4c142f647..bac029430be 100644 --- a/doc/src/sgml/pgstatstatements.sgml +++ b/doc/src/sgml/pgstatstatements.sgml @@ -616,7 +616,7 @@ =# SELECT * FROM test WHERE a IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12); =# SELECT query, calls FROM pg_stat_statements; -[ RECORD 1 ]------------------------------ -query | SELECT * FROM test WHERE a IN (...) +query | SELECT * FROM test WHERE a IN (... [10-99 entries]) calls | 2 -[ RECORD 2 ]------------------------------ query | SELECT pg_stat_statements_reset() @@ -968,10 +968,11 @@ calls | 1 with an array of different lenght. If this parameter is on, an array of constants will contribute only the - first and the last elements to the query identifier. It means two - occurences of the same query, where the only difference is number of - constants in the array, are going to get the same query identifier. - Such queries are represented in form <literal>'(...)'</literal>. + first element, the last element and the number of elements to the query + identifier. It means two occurences of the same query, where the only + difference is number of constants in the array, are going to get the + same query identifier if the arrays are of similar length. + Such queries are represented in form <literal>'(... [10-99 entries])'</literal>. The parameter could be used to reduce amount of repeating data stored via <structname>pg_stat_statements</structname>. The default value is off. diff --git a/src/backend/nodes/queryjumblefuncs.c b/src/backend/nodes/queryjumblefuncs.c index 45717553b5b..7db7ae7f2ee 100644 --- a/src/backend/nodes/queryjumblefuncs.c +++ b/src/backend/nodes/queryjumblefuncs.c @@ -37,6 +37,8 @@ #include "nodes/queryjumble.h" #include "parser/scansup.h" +#include "utils/numutils.h" + #define JUMBLE_SIZE 1024 /* query serialization buffer size */ /* GUC parameters */ @@ -51,7 +53,7 @@ bool query_id_enabled = false; static void AppendJumble(JumbleState *jstate, const unsigned char *item, Size size); static void RecordConstLocation(JumbleState *jstate, - int location, bool merged); + int location, int magnitude); static void _jumbleNode(JumbleState *jstate, Node *node); static void _jumbleElements(JumbleState *jstate, List *elements); static void _jumbleA_Const(JumbleState *jstate, Node *node); @@ -206,12 +208,15 @@ AppendJumble(JumbleState *jstate, const unsigned char *item, Size size) * Record location of constant within query string of query tree that is * currently being walked. * - * Merged argument signals that the constant represents the first or the last - * element in a series of merged constants, and everything but the first/last - * element contributes nothing to the jumble hash. + * Magnitude argument larger than zero signals that the constant represents the + * first or the last element in a series of merged constants, and everything + * but such first/last element will contribute nothing to the jumble hash. The + * magnitute value specifies order of magnitute (i.e. how many digits it has) + * for the number of elements in the series, to represent the fact of merging + * later on. */ static void -RecordConstLocation(JumbleState *jstate, int location, bool merged) +RecordConstLocation(JumbleState *jstate, int location, int magnitude) { /* -1 indicates unknown or undefined location */ if (location >= 0) @@ -227,7 +232,7 @@ RecordConstLocation(JumbleState *jstate, int location, bool merged) } jstate->clocations[jstate->clocations_count].location = location; /* initialize lengths to -1 to simplify third-party module usage */ - jstate->clocations[jstate->clocations_count].merged = merged; + jstate->clocations[jstate->clocations_count].magnitude = magnitude; jstate->clocations[jstate->clocations_count].length = -1; jstate->clocations_count++; } @@ -237,24 +242,26 @@ RecordConstLocation(JumbleState *jstate, int location, bool merged) * Verify if the provided list contains could be merged down, which means it * contains only constant expressions. * - * Return value indicates if merging is possible. + * Return value is the order of magnitude (i.e. how many digits it has) for + * length of the list (to use for representation purposes later on) if merging + * is possible, otherwise zero. * * Note that this function searches only for explicit Const nodes and does not * try to simplify expressions. */ -static bool +static int IsMergeableConstList(List *elements, Const **firstConst, Const **lastConst) { ListCell *temp; Node *firstExpr = NULL; if (elements == NULL) - return false; + return 0; if (!query_id_const_merge) { /* Merging is disabled, process everything one by one */ - return false; + return 0; } firstExpr = linitial(elements); @@ -268,26 +275,26 @@ IsMergeableConstList(List *elements, Const **firstConst, Const **lastConst) { foreach(temp, elements) if (!IsA(lfirst(temp), Const)) - return false; + return 0; *firstConst = (Const *) firstExpr; *lastConst = llast_node(Const, elements); - return true; + return decimalLength32(elements->length); } /* * If we end up here, it means no constants merging is possible, process * the list as usual. */ - return false; + return 0; } #define JUMBLE_NODE(item) \ _jumbleNode(jstate, (Node *) expr->item) #define JUMBLE_ELEMENTS(list) \ _jumbleElements(jstate, (List *) expr->list) -#define JUMBLE_LOCATION(location, merged) \ - RecordConstLocation(jstate, expr->location, merged) +#define JUMBLE_LOCATION(location, magnitude) \ + RecordConstLocation(jstate, expr->location, magnitude) #define JUMBLE_FIELD(item) \ AppendJumble(jstate, (const unsigned char *) &(expr->item), sizeof(expr->item)) #define JUMBLE_FIELD_SINGLE(item) \ @@ -304,10 +311,19 @@ static void _jumbleElements(JumbleState *jstate, List *elements) { Const *first, *last; - if(IsMergeableConstList(elements, &first, &last)) + int magnitude = IsMergeableConstList(elements, &first, &last); + + if (magnitude) { - RecordConstLocation(jstate, first->location, true); - RecordConstLocation(jstate, last->location, true); + RecordConstLocation(jstate, first->location, magnitude); + RecordConstLocation(jstate, last->location, magnitude); + + /* + * After merging constants down we end up with only two constants, the + * first and the last one. To distinguish the order of magnitute behind + * merged constants, add its value into the jumble. + */ + JUMBLE_FIELD_SINGLE(magnitude); } else { diff --git a/src/include/nodes/queryjumble.h b/src/include/nodes/queryjumble.h index 8daf0725d7f..551555494e0 100644 --- a/src/include/nodes/queryjumble.h +++ b/src/include/nodes/queryjumble.h @@ -26,9 +26,12 @@ typedef struct LocationLen /* * Indicates the constant represents the beginning or the end of a merged - * constants interval. + * constants interval. The value shows how many constants were merged away + * (up to a power of 10), or in other words the order of manitude for + * number of merged constants (i.e. how many digits it has). Otherwise the + * value is 0, indicating that no merging was performed. */ - bool merged; + int magnitude; } LocationLen; /* -- 2.41.0 --tp75mykizkxh6a6a Content-Type: text/x-diff; charset=us-ascii Content-Disposition: attachment; filename="v18-0004-Introduce-query_id_const_merge_threshold.patch" ^ permalink raw reply [nested|flat] 9+ messages in thread
* [PATCH v22 3/4] Merge constants in ArrayExpr into groups @ 2024-05-12 09:51 Dmitrii Dolgov <[email protected]> 0 siblings, 0 replies; 9+ messages in thread From: Dmitrii Dolgov @ 2024-05-12 09:51 UTC (permalink / raw) Using query_id_const_merge only first/last element in an ArrayExpr will be used to compute query id. Extend this to take into account number of elements, and merge constants into groups based on it. Resulting groups are powers of 10, i.e. 1 to 9, 10 to 99, etc. Reviewed-by: Sutou Kouhei Tested-by: Yasuo Honda --- .../pg_stat_statements/expected/merging.out | 84 +++++++++++++++---- .../pg_stat_statements/pg_stat_statements.c | 29 +++++-- contrib/pg_stat_statements/sql/merging.sql | 13 +++ doc/src/sgml/pgstatstatements.sgml | 11 +-- src/backend/nodes/gen_node_support.pl | 2 +- src/backend/nodes/queryjumblefuncs.c | 55 ++++++++---- src/include/nodes/queryjumble.h | 10 ++- 7 files changed, 158 insertions(+), 46 deletions(-) diff --git a/contrib/pg_stat_statements/expected/merging.out b/contrib/pg_stat_statements/expected/merging.out index 1e58283afe..0cb4f67b8b 100644 --- a/contrib/pg_stat_statements/expected/merging.out +++ b/contrib/pg_stat_statements/expected/merging.out @@ -54,11 +54,11 @@ SELECT * FROM test_merge WHERE id IN (1, 2, 3); (0 rows) SELECT query, calls FROM pg_stat_statements ORDER BY query COLLATE "C"; - query | calls -----------------------------------------------------+------- - SELECT * FROM test_merge WHERE id IN ($1) | 1 - SELECT * FROM test_merge WHERE id IN (...) | 1 - SELECT pg_stat_statements_reset() IS NOT NULL AS t | 1 + query | calls +----------------------------------------------------------+------- + SELECT * FROM test_merge WHERE id IN ($1) | 1 + SELECT * FROM test_merge WHERE id IN (... [1-9 entries]) | 1 + SELECT pg_stat_statements_reset() IS NOT NULL AS t | 1 (3 rows) SELECT * FROM test_merge WHERE id IN (1, 2, 3, 4, 5, 6, 7, 8, 9); @@ -80,7 +80,60 @@ SELECT query, calls FROM pg_stat_statements ORDER BY query COLLATE "C"; query | calls ------------------------------------------------------------------------+------- SELECT * FROM test_merge WHERE id IN ($1) | 1 - SELECT * FROM test_merge WHERE id IN (...) | 4 + SELECT * FROM test_merge WHERE id IN (... [1-9 entries]) | 2 + SELECT * FROM test_merge WHERE id IN (... [10-99 entries]) | 2 + SELECT pg_stat_statements_reset() IS NOT NULL AS t | 1 + SELECT query, calls FROM pg_stat_statements ORDER BY query COLLATE "C" | 1 +(5 rows) + +-- Second order of magnitude, brace yourself +SELECT pg_stat_statements_reset() IS NOT NULL AS t; + t +--- + t +(1 row) + +SELECT * FROM test_merge WHERE id IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31, 32, 33, 34, 35, 36, 37, 38, 39, 40, 41, 42, 43, 44, 45, 46, 47, 48, 49, 50, 51, 52, 53, 54, 55, 56, 57, 58, 59, 60, 61, 62, 63, 64, 65, 66, 67, 68, 69, 70, 71, 72, 73, 74, 75, 76, 77, 78, 79, 80, 81, 82, 83, 84, 85, 86, 87, 88, 89, 90, 91, 92, 93, 94, 95, 96, 97, 98, 99, 100, 101, 102, 103, 104, 105, 106, 107, 108, 109, 110); + id | data +----+------ +(0 rows) + +SELECT query, calls FROM pg_stat_statements ORDER BY query COLLATE "C"; + query | calls +--------------------------------------------------------------+------- + SELECT * FROM test_merge WHERE id IN (... [100-999 entries]) | 1 + SELECT pg_stat_statements_reset() IS NOT NULL AS t | 1 +(2 rows) + +-- With gaps on the threshold +SELECT pg_stat_statements_reset() IS NOT NULL AS t; + t +--- + t +(1 row) + +SELECT * FROM test_merge WHERE id IN (1, 2, 3, 4); + id | data +----+------ +(0 rows) + +SELECT query, calls FROM pg_stat_statements ORDER BY query COLLATE "C"; + query | calls +----------------------------------------------------------+------- + SELECT * FROM test_merge WHERE id IN (... [1-9 entries]) | 1 + SELECT pg_stat_statements_reset() IS NOT NULL AS t | 1 +(2 rows) + +SELECT * FROM test_merge WHERE id IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15); + id | data +----+------ +(0 rows) + +SELECT query, calls FROM pg_stat_statements ORDER BY query COLLATE "C"; + query | calls +------------------------------------------------------------------------+------- + SELECT * FROM test_merge WHERE id IN (... [1-9 entries]) | 1 + SELECT * FROM test_merge WHERE id IN (... [10-99 entries]) | 1 SELECT pg_stat_statements_reset() IS NOT NULL AS t | 1 SELECT query, calls FROM pg_stat_statements ORDER BY query COLLATE "C" | 1 (4 rows) @@ -108,11 +161,12 @@ SELECT * FROM test_merge WHERE id IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11) and dat (0 rows) SELECT query, calls FROM pg_stat_statements ORDER BY query COLLATE "C"; - query | calls -----------------------------------------------------------+------- - SELECT * FROM test_merge WHERE id IN (...) and data = $3 | 3 - SELECT pg_stat_statements_reset() IS NOT NULL AS t | 1 -(2 rows) + query | calls +--------------------------------------------------------------------------+------- + SELECT * FROM test_merge WHERE id IN (... [1-9 entries]) and data = $3 | 1 + SELECT * FROM test_merge WHERE id IN (... [10-99 entries]) and data = $3 | 2 + SELECT pg_stat_statements_reset() IS NOT NULL AS t | 1 +(3 rows) -- No constants simplification SELECT pg_stat_statements_reset() IS NOT NULL AS t; @@ -147,10 +201,10 @@ SELECT * FROM test_merge_numeric WHERE id IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11) (0 rows) SELECT query, calls FROM pg_stat_statements ORDER BY query COLLATE "C"; - query | calls -----------------------------------------------------+------- - SELECT * FROM test_merge_numeric WHERE id IN (...) | 1 - SELECT pg_stat_statements_reset() IS NOT NULL AS t | 1 + query | calls +--------------------------------------------------------------------+------- + SELECT * FROM test_merge_numeric WHERE id IN (... [10-99 entries]) | 1 + SELECT pg_stat_statements_reset() IS NOT NULL AS t | 1 (2 rows) -- Test constants evaluation, verifies a tricky part to make sure there are no diff --git a/contrib/pg_stat_statements/pg_stat_statements.c b/contrib/pg_stat_statements/pg_stat_statements.c index bcbf1164c1..24ab2a45ff 100644 --- a/contrib/pg_stat_statements/pg_stat_statements.c +++ b/contrib/pg_stat_statements/pg_stat_statements.c @@ -2830,6 +2830,8 @@ generate_normalized_query(JumbleState *jstate, const char *query, bool merged_interval = false; /* Currently processed constants belong to a merged constants interval. */ + int magnitude; /* Order of magnitute for number of merged + constants */ /* @@ -2844,8 +2846,13 @@ generate_normalized_query(JumbleState *jstate, const char *query, * certainly isn't more than 11 bytes, even if n reaches INT_MAX. We * could refine that limit based on the max value of n for the current * query, but it hardly seems worth any extra effort to do so. + * + * On top of that, each pair of $n symbols representing a merged constants + * interval will be decorated with the explanationary text, adding 14 + * bytes. */ - norm_query_buflen = query_len + jstate->clocations_count * 10; + norm_query_buflen = query_len + jstate->clocations_count * 10 + + jstate->clocations_merged_count * 14; /* Allocate result buffer */ norm_query = palloc(norm_query_buflen + 1); @@ -2870,7 +2877,8 @@ generate_normalized_query(JumbleState *jstate, const char *query, Assert(len_to_wrt >= 0); /* Normal path, non merged constant */ - if (!jstate->clocations[i].merged) + magnitude = jstate->clocations[i].magnitude; + if (magnitude == 0) { memcpy(norm_query + n_quer_loc, query + quer_loc, len_to_wrt); n_quer_loc += len_to_wrt; @@ -2887,13 +2895,23 @@ generate_normalized_query(JumbleState *jstate, const char *query, /* * We are not inside a merged interval yet, which means it is the * the first merged constant. - * + */ + static const uint32 powers_of_ten[] = { + 1, 10, 100, + 1000, 10000, 100000, + 1000000, 10000000, 100000000, + 1000000000 + }; + int lower_merged = powers_of_ten[magnitude - 1]; + int upper_merged = powers_of_ten[magnitude]; + + /* * A merged constants interval must be represented via two * constants with the merged flag. Currently we are at the first, * verify there is another one. */ Assert(i + 1 < jstate->clocations_count); - Assert(jstate->clocations[i + 1].merged); + Assert(jstate->clocations[i + 1].magnitude > 0); memcpy(norm_query + n_quer_loc, query + quer_loc, len_to_wrt); n_quer_loc += len_to_wrt; @@ -2902,7 +2920,8 @@ generate_normalized_query(JumbleState *jstate, const char *query, merged_interval = true; /* Mark the interval in the normalized query */ - n_quer_loc += sprintf(norm_query + n_quer_loc, "..."); + n_quer_loc += sprintf(norm_query + n_quer_loc, "... [%d-%d entries]", + lower_merged, upper_merged - 1); } /* Otherwise the constant is merged away, move forward */ diff --git a/contrib/pg_stat_statements/sql/merging.sql b/contrib/pg_stat_statements/sql/merging.sql index 71985bb1cd..657044fade 100644 --- a/contrib/pg_stat_statements/sql/merging.sql +++ b/contrib/pg_stat_statements/sql/merging.sql @@ -27,6 +27,19 @@ SELECT * FROM test_merge WHERE id IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10); SELECT * FROM test_merge WHERE id IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11); SELECT query, calls FROM pg_stat_statements ORDER BY query COLLATE "C"; +-- Second order of magnitude, brace yourself +SELECT pg_stat_statements_reset() IS NOT NULL AS t; +SELECT * FROM test_merge WHERE id IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31, 32, 33, 34, 35, 36, 37, 38, 39, 40, 41, 42, 43, 44, 45, 46, 47, 48, 49, 50, 51, 52, 53, 54, 55, 56, 57, 58, 59, 60, 61, 62, 63, 64, 65, 66, 67, 68, 69, 70, 71, 72, 73, 74, 75, 76, 77, 78, 79, 80, 81, 82, 83, 84, 85, 86, 87, 88, 89, 90, 91, 92, 93, 94, 95, 96, 97, 98, 99, 100, 101, 102, 103, 104, 105, 106, 107, 108, 109, 110); +SELECT query, calls FROM pg_stat_statements ORDER BY query COLLATE "C"; + +-- With gaps on the threshold +SELECT pg_stat_statements_reset() IS NOT NULL AS t; +SELECT * FROM test_merge WHERE id IN (1, 2, 3, 4); +SELECT query, calls FROM pg_stat_statements ORDER BY query COLLATE "C"; + +SELECT * FROM test_merge WHERE id IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15); +SELECT query, calls FROM pg_stat_statements ORDER BY query COLLATE "C"; + -- More conditions in the query SELECT pg_stat_statements_reset() IS NOT NULL AS t; diff --git a/doc/src/sgml/pgstatstatements.sgml b/doc/src/sgml/pgstatstatements.sgml index 2276783786..55dfa5b50f 100644 --- a/doc/src/sgml/pgstatstatements.sgml +++ b/doc/src/sgml/pgstatstatements.sgml @@ -634,7 +634,7 @@ =# SELECT * FROM test WHERE a IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12); =# SELECT query, calls FROM pg_stat_statements; -[ RECORD 1 ]------------------------------ -query | SELECT * FROM test WHERE a IN (...) +query | SELECT * FROM test WHERE a IN (... [10-99 entries]) calls | 2 -[ RECORD 2 ]------------------------------ query | SELECT pg_stat_statements_reset() @@ -992,10 +992,11 @@ calls | 1 with an array of different lenght. If this parameter is on, an array of constants will contribute only the - first and the last elements to the query identifier. It means two - occurences of the same query, where the only difference is number of - constants in the array, are going to get the same query identifier. - Such queries are represented in form <literal>'(...)'</literal>. + first element, the last element and the number of elements to the query + identifier. It means two occurences of the same query, where the only + difference is number of constants in the array, are going to get the + same query identifier if the arrays are of similar length. + Such queries are represented in form <literal>'(... [10-99 entries])'</literal>. The parameter could be used to reduce amount of repeating data stored via <structname>pg_stat_statements</structname>. The default value is off. diff --git a/src/backend/nodes/gen_node_support.pl b/src/backend/nodes/gen_node_support.pl index d2a276c303..c98d121dba 100644 --- a/src/backend/nodes/gen_node_support.pl +++ b/src/backend/nodes/gen_node_support.pl @@ -1323,7 +1323,7 @@ _jumble${n}(JumbleState *jstate, Node *node) # Track the node's location only if directly requested. if ($query_jumble_location) { - print $jff "\tJUMBLE_LOCATION($f, false);\n" + print $jff "\tJUMBLE_LOCATION($f, 0);\n" unless $query_jumble_ignore; } } diff --git a/src/backend/nodes/queryjumblefuncs.c b/src/backend/nodes/queryjumblefuncs.c index 2953073872..8ab3261d66 100644 --- a/src/backend/nodes/queryjumblefuncs.c +++ b/src/backend/nodes/queryjumblefuncs.c @@ -37,6 +37,8 @@ #include "nodes/queryjumble.h" #include "parser/scansup.h" +#include "utils/numutils.h" + #define JUMBLE_SIZE 1024 /* query serialization buffer size */ /* GUC parameters */ @@ -57,7 +59,7 @@ bool query_id_enabled = false; static void AppendJumble(JumbleState *jstate, const unsigned char *item, Size size); static void RecordConstLocation(JumbleState *jstate, - int location, bool merged); + int location, int magnitude); static void _jumbleNode(JumbleState *jstate, Node *node); static void _jumbleElements(JumbleState *jstate, List *elements); static void _jumbleA_Const(JumbleState *jstate, Node *node); @@ -129,6 +131,7 @@ JumbleQuery(Query *query) jstate->clocations = (LocationLen *) palloc(jstate->clocations_buf_size * sizeof(LocationLen)); jstate->clocations_count = 0; + jstate->clocations_merged_count = 0; jstate->highest_extern_param_id = 0; /* Compute query ID and mark the Query node with it */ @@ -218,12 +221,15 @@ AppendJumble(JumbleState *jstate, const unsigned char *item, Size size) * Record location of constant within query string of query tree that is * currently being walked. * - * Merged argument signals that the constant represents the first or the last - * element in a series of merged constants, and everything but the first/last - * element contributes nothing to the jumble hash. + * Magnitude argument larger than zero signals that the constant represents the + * first or the last element in a series of merged constants, and everything + * but such first/last element will contribute nothing to the jumble hash. The + * magnitute value specifies order of magnitute (i.e. how many digits it has) + * for the number of elements in the series, to represent the fact of merging + * later on. */ static void -RecordConstLocation(JumbleState *jstate, int location, bool merged) +RecordConstLocation(JumbleState *jstate, int location, int magnitude) { /* -1 indicates unknown or undefined location */ if (location >= 0) @@ -238,10 +244,12 @@ RecordConstLocation(JumbleState *jstate, int location, bool merged) sizeof(LocationLen)); } jstate->clocations[jstate->clocations_count].location = location; + jstate->clocations[jstate->clocations_count].magnitude = magnitude; /* initialize lengths to -1 to simplify third-party module usage */ - jstate->clocations[jstate->clocations_count].merged = merged; jstate->clocations[jstate->clocations_count].length = -1; jstate->clocations_count++; + if (magnitude > 0) + jstate->clocations_merged_count++; } } @@ -249,24 +257,26 @@ RecordConstLocation(JumbleState *jstate, int location, bool merged) * Verify if the provided list contains could be merged down, which means it * contains only constant expressions. * - * Return value indicates if merging is possible. + * Return value is the order of magnitude (i.e. how many digits it has) for + * length of the list (to use for representation purposes later on) if merging + * is possible, otherwise zero. * * Note that this function searches only for explicit Const nodes and does not * try to simplify expressions. */ -static bool +static int IsMergeableConstList(List *elements, Const **firstConst, Const **lastConst) { ListCell *temp; Node *firstExpr = NULL; if (elements == NIL) - return false; + return 0; if (!query_id_const_merge) { /* Merging is disabled, process everything one by one */ - return false; + return 0; } firstExpr = linitial(elements); @@ -280,26 +290,26 @@ IsMergeableConstList(List *elements, Const **firstConst, Const **lastConst) { foreach(temp, elements) if (!IsA(lfirst(temp), Const)) - return false; + return 0; *firstConst = (Const *) firstExpr; *lastConst = llast_node(Const, elements); - return true; + return decimalLength32(elements->length); } /* * If we end up here, it means no constants merging is possible, process * the list as usual. */ - return false; + return 0; } #define JUMBLE_NODE(item) \ _jumbleNode(jstate, (Node *) expr->item) #define JUMBLE_ELEMENTS(list) \ _jumbleElements(jstate, (List *) expr->list) -#define JUMBLE_LOCATION(location, merged) \ - RecordConstLocation(jstate, expr->location, merged) +#define JUMBLE_LOCATION(location, magnitude) \ + RecordConstLocation(jstate, expr->location, magnitude) #define JUMBLE_FIELD(item) \ AppendJumble(jstate, (const unsigned char *) &(expr->item), sizeof(expr->item)) #define JUMBLE_FIELD_SINGLE(item) \ @@ -316,15 +326,24 @@ static void _jumbleElements(JumbleState *jstate, List *elements) { Const *first, *last; - if (IsMergeableConstList(elements, &first, &last)) + int magnitude = IsMergeableConstList(elements, &first, &last); + + if (magnitude) { /* * Both first and last constants have to be recorded. The first one * will indicate the merged interval, the last one will tell us the * length of the interval within the query text. */ - RecordConstLocation(jstate, first->location, true); - RecordConstLocation(jstate, last->location, true); + RecordConstLocation(jstate, first->location, magnitude); + RecordConstLocation(jstate, last->location, magnitude); + + /* + * After merging constants down we end up with only two constants, the + * first and the last one. To distinguish the order of magnitute behind + * merged constants, add its value into the jumble. + */ + JUMBLE_FIELD_SINGLE(magnitude); } else { diff --git a/src/include/nodes/queryjumble.h b/src/include/nodes/queryjumble.h index 8daf0725d7..0e69e420b7 100644 --- a/src/include/nodes/queryjumble.h +++ b/src/include/nodes/queryjumble.h @@ -26,9 +26,12 @@ typedef struct LocationLen /* * Indicates the constant represents the beginning or the end of a merged - * constants interval. + * constants interval. The value shows how many constants were merged away + * (up to a power of 10), or in other words the order of manitude for + * number of merged constants (i.e. how many digits it has). Otherwise the + * value is 0, indicating that no merging was performed. */ - bool merged; + int magnitude; } LocationLen; /* @@ -52,6 +55,9 @@ typedef struct JumbleState /* Current number of valid entries in clocations array */ int clocations_count; + /* Current number of entries with merged constants interval */ + int clocations_merged_count; + /* highest Param id we've seen, in order to start normalization correctly */ int highest_extern_param_id; } JumbleState; -- 2.45.1 --nse6ci3hmyxhp7l3 Content-Type: text/plain; charset=us-ascii Content-Disposition: attachment; filename="v22-0004-Introduce-query_id_const_merge_threshold.patch" ^ permalink raw reply [nested|flat] 9+ messages in thread
* [PATCH v21 3/4] Merge constants in ArrayExpr into groups @ 2024-05-12 09:51 Dmitrii Dolgov <[email protected]> 0 siblings, 0 replies; 9+ messages in thread From: Dmitrii Dolgov @ 2024-05-12 09:51 UTC (permalink / raw) Using query_id_const_merge only first/last element in an ArrayExpr will be used to compute query id. Extend this to take into account number of elements, and merge constants into groups based on it. Resulting groups are powers of 10, i.e. 1 to 9, 10 to 99, etc. Reviewed-by: Sutou Kouhei Tested-by: Yasuo Honda --- .../pg_stat_statements/expected/merging.out | 84 +++++++++++++++---- .../pg_stat_statements/pg_stat_statements.c | 29 +++++-- contrib/pg_stat_statements/sql/merging.sql | 13 +++ doc/src/sgml/pgstatstatements.sgml | 11 +-- src/backend/nodes/queryjumblefuncs.c | 55 ++++++++---- src/include/nodes/queryjumble.h | 10 ++- 6 files changed, 157 insertions(+), 45 deletions(-) diff --git a/contrib/pg_stat_statements/expected/merging.out b/contrib/pg_stat_statements/expected/merging.out index 1e58283afe..0cb4f67b8b 100644 --- a/contrib/pg_stat_statements/expected/merging.out +++ b/contrib/pg_stat_statements/expected/merging.out @@ -54,11 +54,11 @@ SELECT * FROM test_merge WHERE id IN (1, 2, 3); (0 rows) SELECT query, calls FROM pg_stat_statements ORDER BY query COLLATE "C"; - query | calls -----------------------------------------------------+------- - SELECT * FROM test_merge WHERE id IN ($1) | 1 - SELECT * FROM test_merge WHERE id IN (...) | 1 - SELECT pg_stat_statements_reset() IS NOT NULL AS t | 1 + query | calls +----------------------------------------------------------+------- + SELECT * FROM test_merge WHERE id IN ($1) | 1 + SELECT * FROM test_merge WHERE id IN (... [1-9 entries]) | 1 + SELECT pg_stat_statements_reset() IS NOT NULL AS t | 1 (3 rows) SELECT * FROM test_merge WHERE id IN (1, 2, 3, 4, 5, 6, 7, 8, 9); @@ -80,7 +80,60 @@ SELECT query, calls FROM pg_stat_statements ORDER BY query COLLATE "C"; query | calls ------------------------------------------------------------------------+------- SELECT * FROM test_merge WHERE id IN ($1) | 1 - SELECT * FROM test_merge WHERE id IN (...) | 4 + SELECT * FROM test_merge WHERE id IN (... [1-9 entries]) | 2 + SELECT * FROM test_merge WHERE id IN (... [10-99 entries]) | 2 + SELECT pg_stat_statements_reset() IS NOT NULL AS t | 1 + SELECT query, calls FROM pg_stat_statements ORDER BY query COLLATE "C" | 1 +(5 rows) + +-- Second order of magnitude, brace yourself +SELECT pg_stat_statements_reset() IS NOT NULL AS t; + t +--- + t +(1 row) + +SELECT * FROM test_merge WHERE id IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31, 32, 33, 34, 35, 36, 37, 38, 39, 40, 41, 42, 43, 44, 45, 46, 47, 48, 49, 50, 51, 52, 53, 54, 55, 56, 57, 58, 59, 60, 61, 62, 63, 64, 65, 66, 67, 68, 69, 70, 71, 72, 73, 74, 75, 76, 77, 78, 79, 80, 81, 82, 83, 84, 85, 86, 87, 88, 89, 90, 91, 92, 93, 94, 95, 96, 97, 98, 99, 100, 101, 102, 103, 104, 105, 106, 107, 108, 109, 110); + id | data +----+------ +(0 rows) + +SELECT query, calls FROM pg_stat_statements ORDER BY query COLLATE "C"; + query | calls +--------------------------------------------------------------+------- + SELECT * FROM test_merge WHERE id IN (... [100-999 entries]) | 1 + SELECT pg_stat_statements_reset() IS NOT NULL AS t | 1 +(2 rows) + +-- With gaps on the threshold +SELECT pg_stat_statements_reset() IS NOT NULL AS t; + t +--- + t +(1 row) + +SELECT * FROM test_merge WHERE id IN (1, 2, 3, 4); + id | data +----+------ +(0 rows) + +SELECT query, calls FROM pg_stat_statements ORDER BY query COLLATE "C"; + query | calls +----------------------------------------------------------+------- + SELECT * FROM test_merge WHERE id IN (... [1-9 entries]) | 1 + SELECT pg_stat_statements_reset() IS NOT NULL AS t | 1 +(2 rows) + +SELECT * FROM test_merge WHERE id IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15); + id | data +----+------ +(0 rows) + +SELECT query, calls FROM pg_stat_statements ORDER BY query COLLATE "C"; + query | calls +------------------------------------------------------------------------+------- + SELECT * FROM test_merge WHERE id IN (... [1-9 entries]) | 1 + SELECT * FROM test_merge WHERE id IN (... [10-99 entries]) | 1 SELECT pg_stat_statements_reset() IS NOT NULL AS t | 1 SELECT query, calls FROM pg_stat_statements ORDER BY query COLLATE "C" | 1 (4 rows) @@ -108,11 +161,12 @@ SELECT * FROM test_merge WHERE id IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11) and dat (0 rows) SELECT query, calls FROM pg_stat_statements ORDER BY query COLLATE "C"; - query | calls -----------------------------------------------------------+------- - SELECT * FROM test_merge WHERE id IN (...) and data = $3 | 3 - SELECT pg_stat_statements_reset() IS NOT NULL AS t | 1 -(2 rows) + query | calls +--------------------------------------------------------------------------+------- + SELECT * FROM test_merge WHERE id IN (... [1-9 entries]) and data = $3 | 1 + SELECT * FROM test_merge WHERE id IN (... [10-99 entries]) and data = $3 | 2 + SELECT pg_stat_statements_reset() IS NOT NULL AS t | 1 +(3 rows) -- No constants simplification SELECT pg_stat_statements_reset() IS NOT NULL AS t; @@ -147,10 +201,10 @@ SELECT * FROM test_merge_numeric WHERE id IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11) (0 rows) SELECT query, calls FROM pg_stat_statements ORDER BY query COLLATE "C"; - query | calls -----------------------------------------------------+------- - SELECT * FROM test_merge_numeric WHERE id IN (...) | 1 - SELECT pg_stat_statements_reset() IS NOT NULL AS t | 1 + query | calls +--------------------------------------------------------------------+------- + SELECT * FROM test_merge_numeric WHERE id IN (... [10-99 entries]) | 1 + SELECT pg_stat_statements_reset() IS NOT NULL AS t | 1 (2 rows) -- Test constants evaluation, verifies a tricky part to make sure there are no diff --git a/contrib/pg_stat_statements/pg_stat_statements.c b/contrib/pg_stat_statements/pg_stat_statements.c index d267a72e0a..1c35e10117 100644 --- a/contrib/pg_stat_statements/pg_stat_statements.c +++ b/contrib/pg_stat_statements/pg_stat_statements.c @@ -2828,6 +2828,8 @@ generate_normalized_query(JumbleState *jstate, const char *query, bool merged_interval = false; /* Currently processed constants belong to a merged constants interval. */ + int magnitude; /* Order of magnitute for number of merged + constants */ /* @@ -2842,8 +2844,13 @@ generate_normalized_query(JumbleState *jstate, const char *query, * certainly isn't more than 11 bytes, even if n reaches INT_MAX. We * could refine that limit based on the max value of n for the current * query, but it hardly seems worth any extra effort to do so. + * + * On top of that, each pair of $n symbols representing a merged constants + * interval will be decorated with the explanationary text, adding 14 + * bytes. */ - norm_query_buflen = query_len + jstate->clocations_count * 10; + norm_query_buflen = query_len + jstate->clocations_count * 10 + + jstate->clocations_merged_count * 14; /* Allocate result buffer */ norm_query = palloc(norm_query_buflen + 1); @@ -2868,7 +2875,8 @@ generate_normalized_query(JumbleState *jstate, const char *query, Assert(len_to_wrt >= 0); /* Normal path, non merged constant */ - if (!jstate->clocations[i].merged) + magnitude = jstate->clocations[i].magnitude; + if (magnitude == 0) { memcpy(norm_query + n_quer_loc, query + quer_loc, len_to_wrt); n_quer_loc += len_to_wrt; @@ -2885,13 +2893,23 @@ generate_normalized_query(JumbleState *jstate, const char *query, /* * We are not inside a merged interval yet, which means it is the * the first merged constant. - * + */ + static const uint32 powers_of_ten[] = { + 1, 10, 100, + 1000, 10000, 100000, + 1000000, 10000000, 100000000, + 1000000000 + }; + int lower_merged = powers_of_ten[magnitude - 1]; + int upper_merged = powers_of_ten[magnitude]; + + /* * A merged constants interval must be represented via two * constants with the merged flag. Currently we are at the first, * verify there is another one. */ Assert(i + 1 < jstate->clocations_count); - Assert(jstate->clocations[i + 1].merged); + Assert(jstate->clocations[i + 1].magnitude > 0); memcpy(norm_query + n_quer_loc, query + quer_loc, len_to_wrt); n_quer_loc += len_to_wrt; @@ -2900,7 +2918,8 @@ generate_normalized_query(JumbleState *jstate, const char *query, merged_interval = true; /* Mark the interval in the normalized query */ - n_quer_loc += sprintf(norm_query + n_quer_loc, "..."); + n_quer_loc += sprintf(norm_query + n_quer_loc, "... [%d-%d entries]", + lower_merged, upper_merged - 1); } /* Otherwise the constant is merged away, move forward */ diff --git a/contrib/pg_stat_statements/sql/merging.sql b/contrib/pg_stat_statements/sql/merging.sql index 71985bb1cd..657044fade 100644 --- a/contrib/pg_stat_statements/sql/merging.sql +++ b/contrib/pg_stat_statements/sql/merging.sql @@ -27,6 +27,19 @@ SELECT * FROM test_merge WHERE id IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10); SELECT * FROM test_merge WHERE id IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11); SELECT query, calls FROM pg_stat_statements ORDER BY query COLLATE "C"; +-- Second order of magnitude, brace yourself +SELECT pg_stat_statements_reset() IS NOT NULL AS t; +SELECT * FROM test_merge WHERE id IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31, 32, 33, 34, 35, 36, 37, 38, 39, 40, 41, 42, 43, 44, 45, 46, 47, 48, 49, 50, 51, 52, 53, 54, 55, 56, 57, 58, 59, 60, 61, 62, 63, 64, 65, 66, 67, 68, 69, 70, 71, 72, 73, 74, 75, 76, 77, 78, 79, 80, 81, 82, 83, 84, 85, 86, 87, 88, 89, 90, 91, 92, 93, 94, 95, 96, 97, 98, 99, 100, 101, 102, 103, 104, 105, 106, 107, 108, 109, 110); +SELECT query, calls FROM pg_stat_statements ORDER BY query COLLATE "C"; + +-- With gaps on the threshold +SELECT pg_stat_statements_reset() IS NOT NULL AS t; +SELECT * FROM test_merge WHERE id IN (1, 2, 3, 4); +SELECT query, calls FROM pg_stat_statements ORDER BY query COLLATE "C"; + +SELECT * FROM test_merge WHERE id IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15); +SELECT query, calls FROM pg_stat_statements ORDER BY query COLLATE "C"; + -- More conditions in the query SELECT pg_stat_statements_reset() IS NOT NULL AS t; diff --git a/doc/src/sgml/pgstatstatements.sgml b/doc/src/sgml/pgstatstatements.sgml index cbe1f3e171..12ffd02190 100644 --- a/doc/src/sgml/pgstatstatements.sgml +++ b/doc/src/sgml/pgstatstatements.sgml @@ -616,7 +616,7 @@ =# SELECT * FROM test WHERE a IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12); =# SELECT query, calls FROM pg_stat_statements; -[ RECORD 1 ]------------------------------ -query | SELECT * FROM test WHERE a IN (...) +query | SELECT * FROM test WHERE a IN (... [10-99 entries]) calls | 2 -[ RECORD 2 ]------------------------------ query | SELECT pg_stat_statements_reset() @@ -974,10 +974,11 @@ calls | 1 with an array of different lenght. If this parameter is on, an array of constants will contribute only the - first and the last elements to the query identifier. It means two - occurences of the same query, where the only difference is number of - constants in the array, are going to get the same query identifier. - Such queries are represented in form <literal>'(...)'</literal>. + first element, the last element and the number of elements to the query + identifier. It means two occurences of the same query, where the only + difference is number of constants in the array, are going to get the + same query identifier if the arrays are of similar length. + Such queries are represented in form <literal>'(... [10-99 entries])'</literal>. The parameter could be used to reduce amount of repeating data stored via <structname>pg_stat_statements</structname>. The default value is off. diff --git a/src/backend/nodes/queryjumblefuncs.c b/src/backend/nodes/queryjumblefuncs.c index 722b064873..1d3f36ca64 100644 --- a/src/backend/nodes/queryjumblefuncs.c +++ b/src/backend/nodes/queryjumblefuncs.c @@ -37,6 +37,8 @@ #include "nodes/queryjumble.h" #include "parser/scansup.h" +#include "utils/numutils.h" + #define JUMBLE_SIZE 1024 /* query serialization buffer size */ /* GUC parameters */ @@ -57,7 +59,7 @@ bool query_id_enabled = false; static void AppendJumble(JumbleState *jstate, const unsigned char *item, Size size); static void RecordConstLocation(JumbleState *jstate, - int location, bool merged); + int location, int magnitude); static void _jumbleNode(JumbleState *jstate, Node *node); static void _jumbleElements(JumbleState *jstate, List *elements); static void _jumbleA_Const(JumbleState *jstate, Node *node); @@ -122,6 +124,7 @@ JumbleQuery(Query *query) jstate->clocations = (LocationLen *) palloc(jstate->clocations_buf_size * sizeof(LocationLen)); jstate->clocations_count = 0; + jstate->clocations_merged_count = 0; jstate->highest_extern_param_id = 0; /* Compute query ID and mark the Query node with it */ @@ -211,12 +214,15 @@ AppendJumble(JumbleState *jstate, const unsigned char *item, Size size) * Record location of constant within query string of query tree that is * currently being walked. * - * Merged argument signals that the constant represents the first or the last - * element in a series of merged constants, and everything but the first/last - * element contributes nothing to the jumble hash. + * Magnitude argument larger than zero signals that the constant represents the + * first or the last element in a series of merged constants, and everything + * but such first/last element will contribute nothing to the jumble hash. The + * magnitute value specifies order of magnitute (i.e. how many digits it has) + * for the number of elements in the series, to represent the fact of merging + * later on. */ static void -RecordConstLocation(JumbleState *jstate, int location, bool merged) +RecordConstLocation(JumbleState *jstate, int location, int magnitude) { /* -1 indicates unknown or undefined location */ if (location >= 0) @@ -231,10 +237,12 @@ RecordConstLocation(JumbleState *jstate, int location, bool merged) sizeof(LocationLen)); } jstate->clocations[jstate->clocations_count].location = location; + jstate->clocations[jstate->clocations_count].magnitude = magnitude; /* initialize lengths to -1 to simplify third-party module usage */ - jstate->clocations[jstate->clocations_count].merged = merged; jstate->clocations[jstate->clocations_count].length = -1; jstate->clocations_count++; + if (magnitude > 0) + jstate->clocations_merged_count++; } } @@ -242,24 +250,26 @@ RecordConstLocation(JumbleState *jstate, int location, bool merged) * Verify if the provided list contains could be merged down, which means it * contains only constant expressions. * - * Return value indicates if merging is possible. + * Return value is the order of magnitude (i.e. how many digits it has) for + * length of the list (to use for representation purposes later on) if merging + * is possible, otherwise zero. * * Note that this function searches only for explicit Const nodes and does not * try to simplify expressions. */ -static bool +static int IsMergeableConstList(List *elements, Const **firstConst, Const **lastConst) { ListCell *temp; Node *firstExpr = NULL; if (elements == NIL) - return false; + return 0; if (!query_id_const_merge) { /* Merging is disabled, process everything one by one */ - return false; + return 0; } firstExpr = linitial(elements); @@ -273,26 +283,26 @@ IsMergeableConstList(List *elements, Const **firstConst, Const **lastConst) { foreach(temp, elements) if (!IsA(lfirst(temp), Const)) - return false; + return 0; *firstConst = (Const *) firstExpr; *lastConst = llast_node(Const, elements); - return true; + return decimalLength32(elements->length); } /* * If we end up here, it means no constants merging is possible, process * the list as usual. */ - return false; + return 0; } #define JUMBLE_NODE(item) \ _jumbleNode(jstate, (Node *) expr->item) #define JUMBLE_ELEMENTS(list) \ _jumbleElements(jstate, (List *) expr->list) -#define JUMBLE_LOCATION(location, merged) \ - RecordConstLocation(jstate, expr->location, merged) +#define JUMBLE_LOCATION(location, magnitude) \ + RecordConstLocation(jstate, expr->location, magnitude) #define JUMBLE_FIELD(item) \ AppendJumble(jstate, (const unsigned char *) &(expr->item), sizeof(expr->item)) #define JUMBLE_FIELD_SINGLE(item) \ @@ -309,15 +319,24 @@ static void _jumbleElements(JumbleState *jstate, List *elements) { Const *first, *last; - if (IsMergeableConstList(elements, &first, &last)) + int magnitude = IsMergeableConstList(elements, &first, &last); + + if (magnitude) { /* * Both first and last constants have to be recorded. The first one * will indicate the merged interval, the last one will tell us the * length of the interval within the query text. */ - RecordConstLocation(jstate, first->location, true); - RecordConstLocation(jstate, last->location, true); + RecordConstLocation(jstate, first->location, magnitude); + RecordConstLocation(jstate, last->location, magnitude); + + /* + * After merging constants down we end up with only two constants, the + * first and the last one. To distinguish the order of magnitute behind + * merged constants, add its value into the jumble. + */ + JUMBLE_FIELD_SINGLE(magnitude); } else { diff --git a/src/include/nodes/queryjumble.h b/src/include/nodes/queryjumble.h index 8daf0725d7..0e69e420b7 100644 --- a/src/include/nodes/queryjumble.h +++ b/src/include/nodes/queryjumble.h @@ -26,9 +26,12 @@ typedef struct LocationLen /* * Indicates the constant represents the beginning or the end of a merged - * constants interval. + * constants interval. The value shows how many constants were merged away + * (up to a power of 10), or in other words the order of manitude for + * number of merged constants (i.e. how many digits it has). Otherwise the + * value is 0, indicating that no merging was performed. */ - bool merged; + int magnitude; } LocationLen; /* @@ -52,6 +55,9 @@ typedef struct JumbleState /* Current number of valid entries in clocations array */ int clocations_count; + /* Current number of entries with merged constants interval */ + int clocations_merged_count; + /* highest Param id we've seen, in order to start normalization correctly */ int highest_extern_param_id; } JumbleState; -- 2.45.1 --7t7bac4aexischdh Content-Type: text/plain; charset=us-ascii Content-Disposition: attachment; filename="v21-0004-Introduce-query_id_const_merge_threshold.patch" ^ permalink raw reply [nested|flat] 9+ messages in thread
* [PATCH v20 3/4] Merge constants in ArrayExpr into groups @ 2024-05-12 09:51 Dmitrii Dolgov <[email protected]> 0 siblings, 0 replies; 9+ messages in thread From: Dmitrii Dolgov @ 2024-05-12 09:51 UTC (permalink / raw) Using query_id_const_merge only first/last element in an ArrayExpr will be used to compute query id. Extend this to take into account number of elements, and merge constants into groups based on it. Resulting groups are powers of 10, i.e. 1 to 9, 10 to 99, etc. Reviewed-by: Sutou Kouhei Tested-by: Yasuo Honda --- .../pg_stat_statements/expected/merging.out | 84 +++++++++++++++---- .../pg_stat_statements/pg_stat_statements.c | 29 +++++-- contrib/pg_stat_statements/sql/merging.sql | 13 +++ doc/src/sgml/pgstatstatements.sgml | 11 +-- src/backend/nodes/queryjumblefuncs.c | 55 ++++++++---- src/include/nodes/queryjumble.h | 10 ++- 6 files changed, 157 insertions(+), 45 deletions(-) diff --git a/contrib/pg_stat_statements/expected/merging.out b/contrib/pg_stat_statements/expected/merging.out index 1e58283afe8..0cb4f67b8b7 100644 --- a/contrib/pg_stat_statements/expected/merging.out +++ b/contrib/pg_stat_statements/expected/merging.out @@ -54,11 +54,11 @@ SELECT * FROM test_merge WHERE id IN (1, 2, 3); (0 rows) SELECT query, calls FROM pg_stat_statements ORDER BY query COLLATE "C"; - query | calls -----------------------------------------------------+------- - SELECT * FROM test_merge WHERE id IN ($1) | 1 - SELECT * FROM test_merge WHERE id IN (...) | 1 - SELECT pg_stat_statements_reset() IS NOT NULL AS t | 1 + query | calls +----------------------------------------------------------+------- + SELECT * FROM test_merge WHERE id IN ($1) | 1 + SELECT * FROM test_merge WHERE id IN (... [1-9 entries]) | 1 + SELECT pg_stat_statements_reset() IS NOT NULL AS t | 1 (3 rows) SELECT * FROM test_merge WHERE id IN (1, 2, 3, 4, 5, 6, 7, 8, 9); @@ -80,7 +80,60 @@ SELECT query, calls FROM pg_stat_statements ORDER BY query COLLATE "C"; query | calls ------------------------------------------------------------------------+------- SELECT * FROM test_merge WHERE id IN ($1) | 1 - SELECT * FROM test_merge WHERE id IN (...) | 4 + SELECT * FROM test_merge WHERE id IN (... [1-9 entries]) | 2 + SELECT * FROM test_merge WHERE id IN (... [10-99 entries]) | 2 + SELECT pg_stat_statements_reset() IS NOT NULL AS t | 1 + SELECT query, calls FROM pg_stat_statements ORDER BY query COLLATE "C" | 1 +(5 rows) + +-- Second order of magnitude, brace yourself +SELECT pg_stat_statements_reset() IS NOT NULL AS t; + t +--- + t +(1 row) + +SELECT * FROM test_merge WHERE id IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31, 32, 33, 34, 35, 36, 37, 38, 39, 40, 41, 42, 43, 44, 45, 46, 47, 48, 49, 50, 51, 52, 53, 54, 55, 56, 57, 58, 59, 60, 61, 62, 63, 64, 65, 66, 67, 68, 69, 70, 71, 72, 73, 74, 75, 76, 77, 78, 79, 80, 81, 82, 83, 84, 85, 86, 87, 88, 89, 90, 91, 92, 93, 94, 95, 96, 97, 98, 99, 100, 101, 102, 103, 104, 105, 106, 107, 108, 109, 110); + id | data +----+------ +(0 rows) + +SELECT query, calls FROM pg_stat_statements ORDER BY query COLLATE "C"; + query | calls +--------------------------------------------------------------+------- + SELECT * FROM test_merge WHERE id IN (... [100-999 entries]) | 1 + SELECT pg_stat_statements_reset() IS NOT NULL AS t | 1 +(2 rows) + +-- With gaps on the threshold +SELECT pg_stat_statements_reset() IS NOT NULL AS t; + t +--- + t +(1 row) + +SELECT * FROM test_merge WHERE id IN (1, 2, 3, 4); + id | data +----+------ +(0 rows) + +SELECT query, calls FROM pg_stat_statements ORDER BY query COLLATE "C"; + query | calls +----------------------------------------------------------+------- + SELECT * FROM test_merge WHERE id IN (... [1-9 entries]) | 1 + SELECT pg_stat_statements_reset() IS NOT NULL AS t | 1 +(2 rows) + +SELECT * FROM test_merge WHERE id IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15); + id | data +----+------ +(0 rows) + +SELECT query, calls FROM pg_stat_statements ORDER BY query COLLATE "C"; + query | calls +------------------------------------------------------------------------+------- + SELECT * FROM test_merge WHERE id IN (... [1-9 entries]) | 1 + SELECT * FROM test_merge WHERE id IN (... [10-99 entries]) | 1 SELECT pg_stat_statements_reset() IS NOT NULL AS t | 1 SELECT query, calls FROM pg_stat_statements ORDER BY query COLLATE "C" | 1 (4 rows) @@ -108,11 +161,12 @@ SELECT * FROM test_merge WHERE id IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11) and dat (0 rows) SELECT query, calls FROM pg_stat_statements ORDER BY query COLLATE "C"; - query | calls -----------------------------------------------------------+------- - SELECT * FROM test_merge WHERE id IN (...) and data = $3 | 3 - SELECT pg_stat_statements_reset() IS NOT NULL AS t | 1 -(2 rows) + query | calls +--------------------------------------------------------------------------+------- + SELECT * FROM test_merge WHERE id IN (... [1-9 entries]) and data = $3 | 1 + SELECT * FROM test_merge WHERE id IN (... [10-99 entries]) and data = $3 | 2 + SELECT pg_stat_statements_reset() IS NOT NULL AS t | 1 +(3 rows) -- No constants simplification SELECT pg_stat_statements_reset() IS NOT NULL AS t; @@ -147,10 +201,10 @@ SELECT * FROM test_merge_numeric WHERE id IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11) (0 rows) SELECT query, calls FROM pg_stat_statements ORDER BY query COLLATE "C"; - query | calls -----------------------------------------------------+------- - SELECT * FROM test_merge_numeric WHERE id IN (...) | 1 - SELECT pg_stat_statements_reset() IS NOT NULL AS t | 1 + query | calls +--------------------------------------------------------------------+------- + SELECT * FROM test_merge_numeric WHERE id IN (... [10-99 entries]) | 1 + SELECT pg_stat_statements_reset() IS NOT NULL AS t | 1 (2 rows) -- Test constants evaluation, verifies a tricky part to make sure there are no diff --git a/contrib/pg_stat_statements/pg_stat_statements.c b/contrib/pg_stat_statements/pg_stat_statements.c index 0e8d42e2054..0ade4cf515f 100644 --- a/contrib/pg_stat_statements/pg_stat_statements.c +++ b/contrib/pg_stat_statements/pg_stat_statements.c @@ -2828,6 +2828,8 @@ generate_normalized_query(JumbleState *jstate, const char *query, bool merged_interval = false; /* Currently processed constants belong to a merged constants interval. */ + int magnitude; /* Order of magnitute for number of merged + constants */ /* @@ -2842,8 +2844,13 @@ generate_normalized_query(JumbleState *jstate, const char *query, * certainly isn't more than 11 bytes, even if n reaches INT_MAX. We * could refine that limit based on the max value of n for the current * query, but it hardly seems worth any extra effort to do so. + * + * On top of that, each pair of $n symbols representing a merged constants + * interval will be decorated with the explanationary text, adding 14 + * bytes. */ - norm_query_buflen = query_len + jstate->clocations_count * 10; + norm_query_buflen = query_len + jstate->clocations_count * 10 + + jstate->clocations_merged_count * 14; /* Allocate result buffer */ norm_query = palloc(norm_query_buflen + 1); @@ -2868,7 +2875,8 @@ generate_normalized_query(JumbleState *jstate, const char *query, Assert(len_to_wrt >= 0); /* Normal path, non merged constant */ - if (!jstate->clocations[i].merged) + magnitude = jstate->clocations[i].magnitude; + if (magnitude == 0) { memcpy(norm_query + n_quer_loc, query + quer_loc, len_to_wrt); n_quer_loc += len_to_wrt; @@ -2885,13 +2893,23 @@ generate_normalized_query(JumbleState *jstate, const char *query, /* * We are not inside a merged interval yet, which means it is the * the first merged constant. - * + */ + static const uint32 powers_of_ten[] = { + 1, 10, 100, + 1000, 10000, 100000, + 1000000, 10000000, 100000000, + 1000000000 + }; + int lower_merged = powers_of_ten[magnitude - 1]; + int upper_merged = powers_of_ten[magnitude]; + + /* * A merged constants interval must be represented via two * constants with the merged flag. Currently we are at the first, * verify there is another one. */ Assert(i + 1 < jstate->clocations_count); - Assert(jstate->clocations[i + 1].merged); + Assert(jstate->clocations[i + 1].magnitude > 0); memcpy(norm_query + n_quer_loc, query + quer_loc, len_to_wrt); n_quer_loc += len_to_wrt; @@ -2900,7 +2918,8 @@ generate_normalized_query(JumbleState *jstate, const char *query, merged_interval = true; /* Mark the interval in the normalized query */ - n_quer_loc += sprintf(norm_query + n_quer_loc, "..."); + n_quer_loc += sprintf(norm_query + n_quer_loc, "... [%d-%d entries]", + lower_merged, upper_merged - 1); } /* Otherwise the constant is merged away, move forward */ diff --git a/contrib/pg_stat_statements/sql/merging.sql b/contrib/pg_stat_statements/sql/merging.sql index 71985bb1cd9..657044faded 100644 --- a/contrib/pg_stat_statements/sql/merging.sql +++ b/contrib/pg_stat_statements/sql/merging.sql @@ -27,6 +27,19 @@ SELECT * FROM test_merge WHERE id IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10); SELECT * FROM test_merge WHERE id IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11); SELECT query, calls FROM pg_stat_statements ORDER BY query COLLATE "C"; +-- Second order of magnitude, brace yourself +SELECT pg_stat_statements_reset() IS NOT NULL AS t; +SELECT * FROM test_merge WHERE id IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31, 32, 33, 34, 35, 36, 37, 38, 39, 40, 41, 42, 43, 44, 45, 46, 47, 48, 49, 50, 51, 52, 53, 54, 55, 56, 57, 58, 59, 60, 61, 62, 63, 64, 65, 66, 67, 68, 69, 70, 71, 72, 73, 74, 75, 76, 77, 78, 79, 80, 81, 82, 83, 84, 85, 86, 87, 88, 89, 90, 91, 92, 93, 94, 95, 96, 97, 98, 99, 100, 101, 102, 103, 104, 105, 106, 107, 108, 109, 110); +SELECT query, calls FROM pg_stat_statements ORDER BY query COLLATE "C"; + +-- With gaps on the threshold +SELECT pg_stat_statements_reset() IS NOT NULL AS t; +SELECT * FROM test_merge WHERE id IN (1, 2, 3, 4); +SELECT query, calls FROM pg_stat_statements ORDER BY query COLLATE "C"; + +SELECT * FROM test_merge WHERE id IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15); +SELECT query, calls FROM pg_stat_statements ORDER BY query COLLATE "C"; + -- More conditions in the query SELECT pg_stat_statements_reset() IS NOT NULL AS t; diff --git a/doc/src/sgml/pgstatstatements.sgml b/doc/src/sgml/pgstatstatements.sgml index cbe1f3e1715..12ffd021909 100644 --- a/doc/src/sgml/pgstatstatements.sgml +++ b/doc/src/sgml/pgstatstatements.sgml @@ -616,7 +616,7 @@ =# SELECT * FROM test WHERE a IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12); =# SELECT query, calls FROM pg_stat_statements; -[ RECORD 1 ]------------------------------ -query | SELECT * FROM test WHERE a IN (...) +query | SELECT * FROM test WHERE a IN (... [10-99 entries]) calls | 2 -[ RECORD 2 ]------------------------------ query | SELECT pg_stat_statements_reset() @@ -974,10 +974,11 @@ calls | 1 with an array of different lenght. If this parameter is on, an array of constants will contribute only the - first and the last elements to the query identifier. It means two - occurences of the same query, where the only difference is number of - constants in the array, are going to get the same query identifier. - Such queries are represented in form <literal>'(...)'</literal>. + first element, the last element and the number of elements to the query + identifier. It means two occurences of the same query, where the only + difference is number of constants in the array, are going to get the + same query identifier if the arrays are of similar length. + Such queries are represented in form <literal>'(... [10-99 entries])'</literal>. The parameter could be used to reduce amount of repeating data stored via <structname>pg_stat_statements</structname>. The default value is off. diff --git a/src/backend/nodes/queryjumblefuncs.c b/src/backend/nodes/queryjumblefuncs.c index 9e17767100d..e9ede0acb70 100644 --- a/src/backend/nodes/queryjumblefuncs.c +++ b/src/backend/nodes/queryjumblefuncs.c @@ -37,6 +37,8 @@ #include "nodes/queryjumble.h" #include "parser/scansup.h" +#include "utils/numutils.h" + #define JUMBLE_SIZE 1024 /* query serialization buffer size */ /* GUC parameters */ @@ -57,7 +59,7 @@ bool query_id_enabled = false; static void AppendJumble(JumbleState *jstate, const unsigned char *item, Size size); static void RecordConstLocation(JumbleState *jstate, - int location, bool merged); + int location, int magnitude); static void _jumbleNode(JumbleState *jstate, Node *node); static void _jumbleElements(JumbleState *jstate, List *elements); static void _jumbleA_Const(JumbleState *jstate, Node *node); @@ -123,6 +125,7 @@ JumbleQuery(Query *query) jstate->clocations = (LocationLen *) palloc(jstate->clocations_buf_size * sizeof(LocationLen)); jstate->clocations_count = 0; + jstate->clocations_merged_count = 0; jstate->highest_extern_param_id = 0; /* Compute query ID and mark the Query node with it */ @@ -212,12 +215,15 @@ AppendJumble(JumbleState *jstate, const unsigned char *item, Size size) * Record location of constant within query string of query tree that is * currently being walked. * - * Merged argument signals that the constant represents the first or the last - * element in a series of merged constants, and everything but the first/last - * element contributes nothing to the jumble hash. + * Magnitude argument larger than zero signals that the constant represents the + * first or the last element in a series of merged constants, and everything + * but such first/last element will contribute nothing to the jumble hash. The + * magnitute value specifies order of magnitute (i.e. how many digits it has) + * for the number of elements in the series, to represent the fact of merging + * later on. */ static void -RecordConstLocation(JumbleState *jstate, int location, bool merged) +RecordConstLocation(JumbleState *jstate, int location, int magnitude) { /* -1 indicates unknown or undefined location */ if (location >= 0) @@ -232,10 +238,12 @@ RecordConstLocation(JumbleState *jstate, int location, bool merged) sizeof(LocationLen)); } jstate->clocations[jstate->clocations_count].location = location; + jstate->clocations[jstate->clocations_count].magnitude = magnitude; /* initialize lengths to -1 to simplify third-party module usage */ - jstate->clocations[jstate->clocations_count].merged = merged; jstate->clocations[jstate->clocations_count].length = -1; jstate->clocations_count++; + if (magnitude > 0) + jstate->clocations_merged_count++; } } @@ -243,24 +251,26 @@ RecordConstLocation(JumbleState *jstate, int location, bool merged) * Verify if the provided list contains could be merged down, which means it * contains only constant expressions. * - * Return value indicates if merging is possible. + * Return value is the order of magnitude (i.e. how many digits it has) for + * length of the list (to use for representation purposes later on) if merging + * is possible, otherwise zero. * * Note that this function searches only for explicit Const nodes and does not * try to simplify expressions. */ -static bool +static int IsMergeableConstList(List *elements, Const **firstConst, Const **lastConst) { ListCell *temp; Node *firstExpr = NULL; if (elements == NIL) - return false; + return 0; if (!query_id_const_merge) { /* Merging is disabled, process everything one by one */ - return false; + return 0; } firstExpr = linitial(elements); @@ -274,26 +284,26 @@ IsMergeableConstList(List *elements, Const **firstConst, Const **lastConst) { foreach(temp, elements) if (!IsA(lfirst(temp), Const)) - return false; + return 0; *firstConst = (Const *) firstExpr; *lastConst = llast_node(Const, elements); - return true; + return decimalLength32(elements->length); } /* * If we end up here, it means no constants merging is possible, process * the list as usual. */ - return false; + return 0; } #define JUMBLE_NODE(item) \ _jumbleNode(jstate, (Node *) expr->item) #define JUMBLE_ELEMENTS(list) \ _jumbleElements(jstate, (List *) expr->list) -#define JUMBLE_LOCATION(location, merged) \ - RecordConstLocation(jstate, expr->location, merged) +#define JUMBLE_LOCATION(location, magnitude) \ + RecordConstLocation(jstate, expr->location, magnitude) #define JUMBLE_FIELD(item) \ AppendJumble(jstate, (const unsigned char *) &(expr->item), sizeof(expr->item)) #define JUMBLE_FIELD_SINGLE(item) \ @@ -310,15 +320,24 @@ static void _jumbleElements(JumbleState *jstate, List *elements) { Const *first, *last; - if (IsMergeableConstList(elements, &first, &last)) + int magnitude = IsMergeableConstList(elements, &first, &last); + + if (magnitude) { /* * Both first and last constants have to be recorded. The first one * will indicate the merged interval, the last one will tell us the * length of the interval within the query text. */ - RecordConstLocation(jstate, first->location, true); - RecordConstLocation(jstate, last->location, true); + RecordConstLocation(jstate, first->location, magnitude); + RecordConstLocation(jstate, last->location, magnitude); + + /* + * After merging constants down we end up with only two constants, the + * first and the last one. To distinguish the order of magnitute behind + * merged constants, add its value into the jumble. + */ + JUMBLE_FIELD_SINGLE(magnitude); } else { diff --git a/src/include/nodes/queryjumble.h b/src/include/nodes/queryjumble.h index 8daf0725d7f..0e69e420b7f 100644 --- a/src/include/nodes/queryjumble.h +++ b/src/include/nodes/queryjumble.h @@ -26,9 +26,12 @@ typedef struct LocationLen /* * Indicates the constant represents the beginning or the end of a merged - * constants interval. + * constants interval. The value shows how many constants were merged away + * (up to a power of 10), or in other words the order of manitude for + * number of merged constants (i.e. how many digits it has). Otherwise the + * value is 0, indicating that no merging was performed. */ - bool merged; + int magnitude; } LocationLen; /* @@ -52,6 +55,9 @@ typedef struct JumbleState /* Current number of valid entries in clocations array */ int clocations_count; + /* Current number of entries with merged constants interval */ + int clocations_merged_count; + /* highest Param id we've seen, in order to start normalization correctly */ int highest_extern_param_id; } JumbleState; -- 2.41.0 --eqaczcvvorx3zguj Content-Type: text/x-diff; charset=us-ascii Content-Disposition: attachment; filename="v20-0004-Introduce-query_id_const_merge_threshold.patch" ^ permalink raw reply [nested|flat] 9+ messages in thread
end of thread, other threads:[~2024-05-12 09:51 UTC | newest] Thread overview: 9+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2021-03-04 03:53 [PATCH 5/5] WIP unify handling of attributes and expressions Tomas Vondra <[email protected]> 2023-10-15 08:06 [PATCH v16 3/4] Merge constants in ArrayExpr into groups Dmitrii Dolgov <[email protected]> 2023-10-15 08:06 [PATCH v17 3/4] Merge constants in ArrayExpr into groups Dmitrii Dolgov <[email protected]> 2023-10-15 08:06 [PATCH v15 3/4] Merge constants in ArrayExpr into groups Dmitrii Dolgov <[email protected]> 2024-01-22 20:20 [PATCH v19 3/4] Merge constants in ArrayExpr into groups Dmitrii Dolgov <[email protected]> 2024-01-22 20:20 [PATCH v18 3/4] Merge constants in ArrayExpr into groups Dmitrii Dolgov <[email protected]> 2024-05-12 09:51 [PATCH v22 3/4] Merge constants in ArrayExpr into groups Dmitrii Dolgov <[email protected]> 2024-05-12 09:51 [PATCH v20 3/4] Merge constants in ArrayExpr into groups Dmitrii Dolgov <[email protected]> 2024-05-12 09:51 [PATCH v21 3/4] Merge constants in ArrayExpr into groups Dmitrii Dolgov <[email protected]>
This inbox is served by agora; see mirroring instructions for how to clone and mirror all data and code used for this inbox