diff options
Diffstat (limited to 'src')
| -rw-r--r-- | src/backend/utils/adt/selfuncs.c | 160 | ||||
| -rw-r--r-- | src/include/catalog/catversion.h | 2 | ||||
| -rw-r--r-- | src/include/catalog/pg_operator.dat | 35 | ||||
| -rw-r--r-- | src/include/catalog/pg_proc.dat | 9 | ||||
| -rw-r--r-- | src/include/utils/selfuncs.h | 6 |
5 files changed, 196 insertions, 16 deletions
diff --git a/src/backend/utils/adt/selfuncs.c b/src/backend/utils/adt/selfuncs.c index e62b69d6f26..4fdcb07d97b 100644 --- a/src/backend/utils/adt/selfuncs.c +++ b/src/backend/utils/adt/selfuncs.c @@ -830,6 +830,132 @@ histogram_selectivity(VariableStatData *vardata, FmgrInfo *opproc, } /* + * generic_restriction_selectivity - Selectivity for almost anything + * + * This function estimates selectivity for operators that we don't have any + * special knowledge about, but are on data types that we collect standard + * MCV and/or histogram statistics for. (Additional assumptions are that + * the operator is strict and immutable, or at least stable.) + * + * If we have "VAR OP CONST" or "CONST OP VAR", selectivity is estimated by + * applying the operator to each element of the column's MCV and/or histogram + * stats, and merging the results using the assumption that the histogram is + * a reasonable random sample of the column's non-MCV population. Note that + * if the operator's semantics are related to the histogram ordering, this + * might not be such a great assumption; other functions such as + * scalarineqsel() are probably a better match in such cases. + * + * Otherwise, fall back to the default selectivity provided by the caller. + */ +double +generic_restriction_selectivity(PlannerInfo *root, Oid operator, + List *args, int varRelid, + double default_selectivity) +{ + double selec; + VariableStatData vardata; + Node *other; + bool varonleft; + + /* + * If expression is not variable OP something or something OP variable, + * then punt and return the default estimate. + */ + if (!get_restriction_variable(root, args, varRelid, + &vardata, &other, &varonleft)) + return default_selectivity; + + /* + * If the something is a NULL constant, assume operator is strict and + * return zero, ie, operator will never return TRUE. + */ + if (IsA(other, Const) && + ((Const *) other)->constisnull) + { + ReleaseVariableStats(vardata); + return 0.0; + } + + if (IsA(other, Const)) + { + /* Variable is being compared to a known non-null constant */ + Datum constval = ((Const *) other)->constvalue; + FmgrInfo opproc; + double mcvsum; + double mcvsel; + double nullfrac; + int hist_size; + + fmgr_info(get_opcode(operator), &opproc); + + /* + * Calculate the selectivity for the column's most common values. + */ + mcvsel = mcv_selectivity(&vardata, &opproc, constval, varonleft, + &mcvsum); + + /* + * If the histogram is large enough, see what fraction of it matches + * the query, and assume that's representative of the non-MCV + * population. Otherwise use the default selectivity for the non-MCV + * population. + */ + selec = histogram_selectivity(&vardata, &opproc, + constval, varonleft, + 10, 1, &hist_size); + if (selec < 0) + { + /* Nope, fall back on default */ + selec = default_selectivity; + } + else if (hist_size < 100) + { + /* + * For histogram sizes from 10 to 100, we combine the histogram + * and default selectivities, putting increasingly more trust in + * the histogram for larger sizes. + */ + double hist_weight = hist_size / 100.0; + + selec = selec * hist_weight + + default_selectivity * (1.0 - hist_weight); + } + + /* In any case, don't believe extremely small or large estimates. */ + if (selec < 0.0001) + selec = 0.0001; + else if (selec > 0.9999) + selec = 0.9999; + + /* Don't forget to account for nulls. */ + if (HeapTupleIsValid(vardata.statsTuple)) + nullfrac = ((Form_pg_statistic) GETSTRUCT(vardata.statsTuple))->stanullfrac; + else + nullfrac = 0.0; + + /* + * Now merge the results from the MCV and histogram calculations, + * realizing that the histogram covers only the non-null values that + * are not listed in MCV. + */ + selec *= 1.0 - nullfrac - mcvsum; + selec += mcvsel; + } + else + { + /* Comparison value is not constant, so we can't do anything */ + selec = default_selectivity; + } + + ReleaseVariableStats(vardata); + + /* result should be in range, but make sure... */ + CLAMP_PROBABILITY(selec); + + return selec; +} + +/* * ineq_histogram_selectivity - Examine the histogram for scalarineqsel * * Determine the fraction of the variable's histogram population that @@ -2917,6 +3043,40 @@ fail: /* + * matchingsel -- generic matching-operator selectivity support + * + * Use these for any operators that (a) are on data types for which we collect + * standard statistics, and (b) have behavior for which the default estimate + * (twice DEFAULT_EQ_SEL) is sane. Typically that is good for match-like + * operators. + */ + +Datum +matchingsel(PG_FUNCTION_ARGS) +{ + PlannerInfo *root = (PlannerInfo *) PG_GETARG_POINTER(0); + Oid operator = PG_GETARG_OID(1); + List *args = (List *) PG_GETARG_POINTER(2); + int varRelid = PG_GETARG_INT32(3); + double selec; + + /* Use generic restriction selectivity logic. */ + selec = generic_restriction_selectivity(root, operator, + args, varRelid, + DEFAULT_MATCHING_SEL); + + PG_RETURN_FLOAT8((float8) selec); +} + +Datum +matchingjoinsel(PG_FUNCTION_ARGS) +{ + /* Just punt, for the moment. */ + PG_RETURN_FLOAT8(DEFAULT_MATCHING_SEL); +} + + +/* * Helper routine for estimate_num_groups: add an item to a list of * GroupVarInfos, but only if it's not known equal to any of the existing * entries. diff --git a/src/include/catalog/catversion.h b/src/include/catalog/catversion.h index eaca0570fdd..bb07aebdbed 100644 --- a/src/include/catalog/catversion.h +++ b/src/include/catalog/catversion.h @@ -53,6 +53,6 @@ */ /* yyyymmddN */ -#define CATALOG_VERSION_NO 202003301 +#define CATALOG_VERSION_NO 202004011 #endif diff --git a/src/include/catalog/pg_operator.dat b/src/include/catalog/pg_operator.dat index 7c135da3b1f..65c7fedf237 100644 --- a/src/include/catalog/pg_operator.dat +++ b/src/include/catalog/pg_operator.dat @@ -3016,18 +3016,21 @@ { oid => '3693', descr => 'contains', oprname => '@>', oprleft => 'tsquery', oprright => 'tsquery', oprresult => 'bool', oprcom => '<@(tsquery,tsquery)', - oprcode => 'tsq_mcontains', oprrest => 'contsel', oprjoin => 'contjoinsel' }, + oprcode => 'tsq_mcontains', oprrest => 'matchingsel', + oprjoin => 'matchingjoinsel' }, { oid => '3694', descr => 'is contained by', oprname => '<@', oprleft => 'tsquery', oprright => 'tsquery', oprresult => 'bool', oprcom => '@>(tsquery,tsquery)', - oprcode => 'tsq_mcontained', oprrest => 'contsel', oprjoin => 'contjoinsel' }, + oprcode => 'tsq_mcontained', oprrest => 'matchingsel', + oprjoin => 'matchingjoinsel' }, { oid => '3762', descr => 'text search match', oprname => '@@', oprleft => 'text', oprright => 'text', oprresult => 'bool', - oprcode => 'ts_match_tt', oprrest => 'contsel', oprjoin => 'contjoinsel' }, + oprcode => 'ts_match_tt', oprrest => 'matchingsel', + oprjoin => 'matchingjoinsel' }, { oid => '3763', descr => 'text search match', oprname => '@@', oprleft => 'text', oprright => 'tsquery', - oprresult => 'bool', oprcode => 'ts_match_tq', oprrest => 'contsel', - oprjoin => 'contjoinsel' }, + oprresult => 'bool', oprcode => 'ts_match_tq', oprrest => 'matchingsel', + oprjoin => 'matchingjoinsel' }, # generic record comparison operators { oid => '2988', oid_symbol => 'RECORD_EQ_OP', descr => 'equal', @@ -3178,7 +3181,8 @@ { oid => '3897', descr => 'is adjacent to', oprname => '-|-', oprleft => 'anyrange', oprright => 'anyrange', oprresult => 'bool', oprcom => '-|-(anyrange,anyrange)', - oprcode => 'range_adjacent', oprrest => 'contsel', oprjoin => 'contjoinsel' }, + oprcode => 'range_adjacent', oprrest => 'matchingsel', + oprjoin => 'matchingjoinsel' }, { oid => '3898', descr => 'range union', oprname => '+', oprleft => 'anyrange', oprright => 'anyrange', oprresult => 'anyrange', oprcom => '+(anyrange,anyrange)', @@ -3258,22 +3262,23 @@ { oid => '3246', descr => 'contains', oprname => '@>', oprleft => 'jsonb', oprright => 'jsonb', oprresult => 'bool', oprcom => '<@(jsonb,jsonb)', oprcode => 'jsonb_contains', - oprrest => 'contsel', oprjoin => 'contjoinsel' }, + oprrest => 'matchingsel', oprjoin => 'matchingjoinsel' }, { oid => '3247', descr => 'key exists', oprname => '?', oprleft => 'jsonb', oprright => 'text', oprresult => 'bool', - oprcode => 'jsonb_exists', oprrest => 'contsel', oprjoin => 'contjoinsel' }, + oprcode => 'jsonb_exists', oprrest => 'matchingsel', + oprjoin => 'matchingjoinsel' }, { oid => '3248', descr => 'any key exists', oprname => '?|', oprleft => 'jsonb', oprright => '_text', oprresult => 'bool', - oprcode => 'jsonb_exists_any', oprrest => 'contsel', - oprjoin => 'contjoinsel' }, + oprcode => 'jsonb_exists_any', oprrest => 'matchingsel', + oprjoin => 'matchingjoinsel' }, { oid => '3249', descr => 'all keys exist', oprname => '?&', oprleft => 'jsonb', oprright => '_text', oprresult => 'bool', - oprcode => 'jsonb_exists_all', oprrest => 'contsel', - oprjoin => 'contjoinsel' }, + oprcode => 'jsonb_exists_all', oprrest => 'matchingsel', + oprjoin => 'matchingjoinsel' }, { oid => '3250', descr => 'is contained by', oprname => '<@', oprleft => 'jsonb', oprright => 'jsonb', oprresult => 'bool', oprcom => '@>(jsonb,jsonb)', oprcode => 'jsonb_contained', - oprrest => 'contsel', oprjoin => 'contjoinsel' }, + oprrest => 'matchingsel', oprjoin => 'matchingjoinsel' }, { oid => '3284', descr => 'concatenate', oprname => '||', oprleft => 'jsonb', oprright => 'jsonb', oprresult => 'jsonb', oprcode => 'jsonb_concat' }, @@ -3292,10 +3297,10 @@ { oid => '4012', descr => 'jsonpath exists', oprname => '@?', oprleft => 'jsonb', oprright => 'jsonpath', oprresult => 'bool', oprcode => 'jsonb_path_exists_opr(jsonb,jsonpath)', - oprrest => 'contsel', oprjoin => 'contjoinsel' }, + oprrest => 'matchingsel', oprjoin => 'matchingjoinsel' }, { oid => '4013', descr => 'jsonpath match', oprname => '@@', oprleft => 'jsonb', oprright => 'jsonpath', oprresult => 'bool', oprcode => 'jsonb_path_match_opr(jsonb,jsonpath)', - oprrest => 'contsel', oprjoin => 'contjoinsel' }, + oprrest => 'matchingsel', oprjoin => 'matchingjoinsel' }, ] diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat index a6a708cca92..fe3df4436d7 100644 --- a/src/include/catalog/pg_proc.dat +++ b/src/include/catalog/pg_proc.dat @@ -10628,6 +10628,15 @@ prosrc => 'shift_jis_2004_to_euc_jis_2004', probin => '$libdir/euc2004_sjis2004' }, +{ oid => '8387', + descr => 'restriction selectivity for generic matching operators', + proname => 'matchingsel', provolatile => 's', prorettype => 'float8', + proargtypes => 'internal oid internal int4', prosrc => 'matchingsel' }, +{ oid => '8388', descr => 'join selectivity for generic matching operators', + proname => 'matchingjoinsel', provolatile => 's', prorettype => 'float8', + proargtypes => 'internal oid internal int2 internal', + prosrc => 'matchingjoinsel' }, + # replication/origin.h { oid => '6003', descr => 'create a replication origin', proname => 'pg_replication_origin_create', provolatile => 'v', diff --git a/src/include/utils/selfuncs.h b/src/include/utils/selfuncs.h index 1c9570f1951..1dd3ac12f8b 100644 --- a/src/include/utils/selfuncs.h +++ b/src/include/utils/selfuncs.h @@ -42,6 +42,9 @@ /* default selectivity estimate for pattern-match operators such as LIKE */ #define DEFAULT_MATCH_SEL 0.005 +/* default selectivity estimate for other matching operators */ +#define DEFAULT_MATCHING_SEL 0.010 + /* default number of distinct values in a table */ #define DEFAULT_NUM_DISTINCT 200 @@ -148,6 +151,9 @@ extern double histogram_selectivity(VariableStatData *vardata, FmgrInfo *opproc, Datum constval, bool varonleft, int min_hist_size, int n_skip, int *hist_size); +extern double generic_restriction_selectivity(PlannerInfo *root, Oid operator, + List *args, int varRelid, + double default_selectivity); extern double ineq_histogram_selectivity(PlannerInfo *root, VariableStatData *vardata, FmgrInfo *opproc, bool isgt, bool iseq, |