Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1jJL8E-0006NA-55 for pgsql-hackers@arkaria.postgresql.org; Tue, 31 Mar 2020 17:56:58 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1jJL8D-0007Wc-3H for pgsql-hackers@arkaria.postgresql.org; Tue, 31 Mar 2020 17:56:57 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1jJL5A-00031g-4J for pgsql-hackers@lists.postgresql.org; Tue, 31 Mar 2020 17:53:48 +0000 Received: from sss.pgh.pa.us ([66.207.139.130]) by makus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1jJL52-0003VG-SK for pgsql-hackers@lists.postgresql.org; Tue, 31 Mar 2020 17:53:47 +0000 Received: from sss1.sss.pgh.pa.us (localhost [127.0.0.1]) by sss.pgh.pa.us (8.14.4/8.14.4) with ESMTP id 02VHrbNu018338; Tue, 31 Mar 2020 13:53:37 -0400 From: Tom Lane To: Alexey Bashtanov cc: pgsql-hackers@lists.postgresql.org Subject: Re: Less-silly selectivity for JSONB matching operators In-reply-to: <14445.1585673093@sss.pgh.pa.us> References: <12237.1582833074@sss.pgh.pa.us> <14469.1582927774@sss.pgh.pa.us> <8fcbdcc5-cbed-f96f-7ed4-f34d1bd1a205@imap.cc> <9233.1585671632@sss.pgh.pa.us> <7e87d82d-326a-a8df-ee4f-ec708c701dce@imap.cc> <9662.1585672165@sss.pgh.pa.us> <7822136b-c4ab-7d5f-f7bc-8d59f03e4bbc@imap.cc> <14445.1585673093@sss.pgh.pa.us> Comments: In-reply-to Tom Lane message dated "Tue, 31 Mar 2020 12:44:53 -0400" MIME-Version: 1.0 Content-Type: multipart/mixed; boundary="----- =_aaaaaaaaaa0" Content-ID: <18089.1585677176.0@sss.pgh.pa.us> Date: Tue, 31 Mar 2020 13:53:37 -0400 Message-ID: <18337.1585677217@sss.pgh.pa.us> List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk ------- =_aaaaaaaaaa0 Content-Type: text/plain; charset="us-ascii" Content-ID: <18089.1585677176.1@sss.pgh.pa.us> Renamed "matchsel" to "matchingsel" etc, added DEFAULT_MATCHING_SEL, rebased over commit 911e70207. Since that commit already created new versions of the relevant contrib modules, I think we can just redefine what those versions contain, rather than making yet-newer versions. (Of course, that assumes we're going to include this in v13.) regards, tom lane ------- =_aaaaaaaaaa0 Content-Type: text/x-diff; name="0001-better-jsonb-selectivity-2.patch"; charset="us-ascii" Content-ID: <18089.1585677176.2@sss.pgh.pa.us> Content-Description: 0001-better-jsonb-selectivity-2.patch Content-Transfer-Encoding: quoted-printable diff --git a/contrib/ltree/ltree_op.c b/contrib/ltree/ltree_op.c index 34e6e4b..f606761 100644 --- a/contrib/ltree/ltree_op.c +++ b/contrib/ltree/ltree_op.c @@ -566,8 +566,6 @@ ltree2text(PG_FUNCTION_ARGS) } = = -#define DEFAULT_PARENT_SEL 0.001 - /* * ltreeparentsel - Selectivity of parent relationship for ltree data typ= es. */ @@ -578,101 +576,12 @@ ltreeparentsel(PG_FUNCTION_ARGS) Oid operator =3D PG_GETARG_OID(1); List *args =3D (List *) PG_GETARG_POINTER(2); int varRelid =3D PG_GETARG_INT32(3); - VariableStatData vardata; - Node *other; - bool varonleft; double selec; = - /* - * If expression is not variable <@ something or something <@ variable, - * then punt and return a default estimate. - */ - if (!get_restriction_variable(root, args, varRelid, - &vardata, &other, &varonleft)) - PG_RETURN_FLOAT8(DEFAULT_PARENT_SEL); - - /* - * 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); - PG_RETURN_FLOAT8(0.0); - } - - if (IsA(other, Const)) - { - /* Variable is being compared to a known non-null constant */ - Datum constval =3D ((Const *) other)->constvalue; - FmgrInfo contproc; - double mcvsum; - double mcvsel; - double nullfrac; - int hist_size; - - fmgr_info(get_opcode(operator), &contproc); - - /* - * Is the constant "<@" to any of the column's most common values? - */ - mcvsel =3D mcv_selectivity(&vardata, &contproc, constval, varonleft, - &mcvsum); - - /* - * If the histogram is large enough, see what fraction of it the - * constant is "<@" to, and assume that's representative of the - * non-MCV population. Otherwise use the default selectivity for the - * non-MCV population. - */ - selec =3D histogram_selectivity(&vardata, &contproc, - constval, varonleft, - 10, 1, &hist_size); - if (selec < 0) - { - /* Nope, fall back on default */ - selec =3D DEFAULT_PARENT_SEL; - } - 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 =3D hist_size / 100.0; - - selec =3D selec * hist_weight + - DEFAULT_PARENT_SEL * (1.0 - hist_weight); - } - - /* In any case, don't believe extremely small or large estimates. */ - if (selec < 0.0001) - selec =3D 0.0001; - else if (selec > 0.9999) - selec =3D 0.9999; - - if (HeapTupleIsValid(vardata.statsTuple)) - nullfrac =3D ((Form_pg_statistic) GETSTRUCT(vardata.statsTuple))->stan= ullfrac; - else - nullfrac =3D 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 *=3D 1.0 - nullfrac - mcvsum; - selec +=3D mcvsel; - } - else - selec =3D DEFAULT_PARENT_SEL; - - ReleaseVariableStats(vardata); - - /* result should be in range, but make sure... */ - CLAMP_PROBABILITY(selec); + /* Use generic restriction selectivity logic, with default 0.001. */ + selec =3D generic_restriction_selectivity(root, operator, + args, varRelid, + 0.001); = PG_RETURN_FLOAT8((float8) selec); } diff --git a/doc/src/sgml/xoper.sgml b/doc/src/sgml/xoper.sgml index 132056f..56b0849 100644 --- a/doc/src/sgml/xoper.sgml +++ b/doc/src/sgml/xoper.sgml @@ -283,6 +283,18 @@ column OP constant = + Another useful built-in selectivity estimation function + is matchingsel, which will work for almost any + binary operator, if standard MCV and/or histogram statistics are + collected for the input data type(s). Its default estimate is set to + twice the default estimate used in eqsel, making + it most suitable for comparison operators that are somewhat less + strict than equality. (Or you could call the + underlying generic_restriction_selectivity + function, providing a different default estimate.) + + + There are additional selectivity estimation functions designed for ge= ometric operators in src/backend/utils/adt/geo_selfuncs.c: areasel, positionsel, and contsel. At this writing these are just stu= bs, but you might want @@ -319,6 +331,7 @@ table1.column1 OP table2.column2 scalarlejoinsel for <=3D scalargtjoinsel for > scalargejoinsel for >=3D + matchingjoinsel for generic matching o= perators areajoinsel for 2D area-based comparis= ons positionjoinsel for 2D position-based = comparisons contjoinsel for 2D containment-based c= omparisons diff --git a/src/backend/utils/adt/selfuncs.c b/src/backend/utils/adt/self= uncs.c index e62b69d..4fdcb07 100644 --- a/src/backend/utils/adt/selfuncs.c +++ b/src/backend/utils/adt/selfuncs.c @@ -830,6 +830,132 @@ histogram_selectivity(VariableStatData *vardata, Fmg= rInfo *opproc, } = /* + * generic_restriction_selectivity - Selectivity for almost anything + * + * This function estimates selectivity for operators that we don't have a= ny + * special knowledge about, but are on data types that we collect standar= d + * 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 histo= gram + * stats, and merging the results using the assumption that the histogram= is + * a reasonable random sample of the column's non-MCV population. Note t= hat + * if the operator's semantics are related to the histogram ordering, thi= s + * 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 =3D ((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 =3D 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 =3D histogram_selectivity(&vardata, &opproc, + constval, varonleft, + 10, 1, &hist_size); + if (selec < 0) + { + /* Nope, fall back on default */ + selec =3D 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 =3D hist_size / 100.0; + + selec =3D 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 =3D 0.0001; + else if (selec > 0.9999) + selec =3D 0.9999; + + /* Don't forget to account for nulls. */ + if (HeapTupleIsValid(vardata.statsTuple)) + nullfrac =3D ((Form_pg_statistic) GETSTRUCT(vardata.statsTuple))->stan= ullfrac; + else + nullfrac =3D 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 *=3D 1.0 - nullfrac - mcvsum; + selec +=3D mcvsel; + } + else + { + /* Comparison value is not constant, so we can't do anything */ + selec =3D 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 co= llect + * standard statistics, and (b) have behavior for which the default estim= ate + * (twice DEFAULT_EQ_SEL) is sane. Typically that is good for match-like + * operators. + */ + +Datum +matchingsel(PG_FUNCTION_ARGS) +{ + PlannerInfo *root =3D (PlannerInfo *) PG_GETARG_POINTER(0); + Oid operator =3D PG_GETARG_OID(1); + List *args =3D (List *) PG_GETARG_POINTER(2); + int varRelid =3D PG_GETARG_INT32(3); + double selec; + + /* Use generic restriction selectivity logic. */ + selec =3D 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/pg_operator.dat b/src/include/catalog/pg_= operator.dat index 7c135da..65c7fed 100644 --- a/src/include/catalog/pg_operator.dat +++ b/src/include/catalog/pg_operator.dat @@ -3016,18 +3016,21 @@ { oid =3D> '3693', descr =3D> 'contains', oprname =3D> '@>', oprleft =3D> 'tsquery', oprright =3D> 'tsquery', oprresult =3D> 'bool', oprcom =3D> '<@(tsquery,tsquery)', - oprcode =3D> 'tsq_mcontains', oprrest =3D> 'contsel', oprjoin =3D> 'con= tjoinsel' }, + oprcode =3D> 'tsq_mcontains', oprrest =3D> 'matchingsel', + oprjoin =3D> 'matchingjoinsel' }, { oid =3D> '3694', descr =3D> 'is contained by', oprname =3D> '<@', oprleft =3D> 'tsquery', oprright =3D> 'tsquery', oprresult =3D> 'bool', oprcom =3D> '@>(tsquery,tsquery)', - oprcode =3D> 'tsq_mcontained', oprrest =3D> 'contsel', oprjoin =3D> 'co= ntjoinsel' }, + oprcode =3D> 'tsq_mcontained', oprrest =3D> 'matchingsel', + oprjoin =3D> 'matchingjoinsel' }, { oid =3D> '3762', descr =3D> 'text search match', oprname =3D> '@@', oprleft =3D> 'text', oprright =3D> 'text', oprresult= =3D> 'bool', - oprcode =3D> 'ts_match_tt', oprrest =3D> 'contsel', oprjoin =3D> 'contj= oinsel' }, + oprcode =3D> 'ts_match_tt', oprrest =3D> 'matchingsel', + oprjoin =3D> 'matchingjoinsel' }, { oid =3D> '3763', descr =3D> 'text search match', oprname =3D> '@@', oprleft =3D> 'text', oprright =3D> 'tsquery', - oprresult =3D> 'bool', oprcode =3D> 'ts_match_tq', oprrest =3D> 'contse= l', - oprjoin =3D> 'contjoinsel' }, + oprresult =3D> 'bool', oprcode =3D> 'ts_match_tq', oprrest =3D> 'matchi= ngsel', + oprjoin =3D> 'matchingjoinsel' }, = # generic record comparison operators { oid =3D> '2988', oid_symbol =3D> 'RECORD_EQ_OP', descr =3D> 'equal', @@ -3178,7 +3181,8 @@ { oid =3D> '3897', descr =3D> 'is adjacent to', oprname =3D> '-|-', oprleft =3D> 'anyrange', oprright =3D> 'anyrange', oprresult =3D> 'bool', oprcom =3D> '-|-(anyrange,anyrange)', - oprcode =3D> 'range_adjacent', oprrest =3D> 'contsel', oprjoin =3D> 'co= ntjoinsel' }, + oprcode =3D> 'range_adjacent', oprrest =3D> 'matchingsel', + oprjoin =3D> 'matchingjoinsel' }, { oid =3D> '3898', descr =3D> 'range union', oprname =3D> '+', oprleft =3D> 'anyrange', oprright =3D> 'anyrange', oprresult =3D> 'anyrange', oprcom =3D> '+(anyrange,anyrange)', @@ -3258,22 +3262,23 @@ { oid =3D> '3246', descr =3D> 'contains', oprname =3D> '@>', oprleft =3D> 'jsonb', oprright =3D> 'jsonb', oprresu= lt =3D> 'bool', oprcom =3D> '<@(jsonb,jsonb)', oprcode =3D> 'jsonb_contains', - oprrest =3D> 'contsel', oprjoin =3D> 'contjoinsel' }, + oprrest =3D> 'matchingsel', oprjoin =3D> 'matchingjoinsel' }, { oid =3D> '3247', descr =3D> 'key exists', oprname =3D> '?', oprleft =3D> 'jsonb', oprright =3D> 'text', oprresult= =3D> 'bool', - oprcode =3D> 'jsonb_exists', oprrest =3D> 'contsel', oprjoin =3D> 'cont= joinsel' }, + oprcode =3D> 'jsonb_exists', oprrest =3D> 'matchingsel', + oprjoin =3D> 'matchingjoinsel' }, { oid =3D> '3248', descr =3D> 'any key exists', oprname =3D> '?|', oprleft =3D> 'jsonb', oprright =3D> '_text', oprresu= lt =3D> 'bool', - oprcode =3D> 'jsonb_exists_any', oprrest =3D> 'contsel', - oprjoin =3D> 'contjoinsel' }, + oprcode =3D> 'jsonb_exists_any', oprrest =3D> 'matchingsel', + oprjoin =3D> 'matchingjoinsel' }, { oid =3D> '3249', descr =3D> 'all keys exist', oprname =3D> '?&', oprleft =3D> 'jsonb', oprright =3D> '_text', oprresu= lt =3D> 'bool', - oprcode =3D> 'jsonb_exists_all', oprrest =3D> 'contsel', - oprjoin =3D> 'contjoinsel' }, + oprcode =3D> 'jsonb_exists_all', oprrest =3D> 'matchingsel', + oprjoin =3D> 'matchingjoinsel' }, { oid =3D> '3250', descr =3D> 'is contained by', oprname =3D> '<@', oprleft =3D> 'jsonb', oprright =3D> 'jsonb', oprresu= lt =3D> 'bool', oprcom =3D> '@>(jsonb,jsonb)', oprcode =3D> 'jsonb_contained', - oprrest =3D> 'contsel', oprjoin =3D> 'contjoinsel' }, + oprrest =3D> 'matchingsel', oprjoin =3D> 'matchingjoinsel' }, { oid =3D> '3284', descr =3D> 'concatenate', oprname =3D> '||', oprleft =3D> 'jsonb', oprright =3D> 'jsonb', oprresult =3D> 'jsonb', oprcode =3D> 'jsonb_concat' }, @@ -3292,10 +3297,10 @@ { oid =3D> '4012', descr =3D> 'jsonpath exists', oprname =3D> '@?', oprleft =3D> 'jsonb', oprright =3D> 'jsonpath', oprresult =3D> 'bool', oprcode =3D> 'jsonb_path_exists_opr(jsonb,jsonpa= th)', - oprrest =3D> 'contsel', oprjoin =3D> 'contjoinsel' }, + oprrest =3D> 'matchingsel', oprjoin =3D> 'matchingjoinsel' }, { oid =3D> '4013', descr =3D> 'jsonpath match', oprname =3D> '@@', oprleft =3D> 'jsonb', oprright =3D> 'jsonpath', oprresult =3D> 'bool', oprcode =3D> 'jsonb_path_match_opr(jsonb,jsonpat= h)', - oprrest =3D> 'contsel', oprjoin =3D> 'contjoinsel' }, + oprrest =3D> 'matchingsel', oprjoin =3D> 'matchingjoinsel' }, = ] diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc= .dat index a6a708c..fe3df44 100644 --- a/src/include/catalog/pg_proc.dat +++ b/src/include/catalog/pg_proc.dat @@ -10628,6 +10628,15 @@ prosrc =3D> 'shift_jis_2004_to_euc_jis_2004', probin =3D> '$libdir/euc2004_sjis2004' }, = +{ oid =3D> '8387', + descr =3D> 'restriction selectivity for generic matching operators', + proname =3D> 'matchingsel', provolatile =3D> 's', prorettype =3D> 'floa= t8', + proargtypes =3D> 'internal oid internal int4', prosrc =3D> 'matchingsel= ' }, +{ oid =3D> '8388', descr =3D> 'join selectivity for generic matching oper= ators', + proname =3D> 'matchingjoinsel', provolatile =3D> 's', prorettype =3D> '= float8', + proargtypes =3D> 'internal oid internal int2 internal', + prosrc =3D> 'matchingjoinsel' }, + # replication/origin.h { oid =3D> '6003', descr =3D> 'create a replication origin', proname =3D> 'pg_replication_origin_create', provolatile =3D> 'v', diff --git a/src/include/utils/selfuncs.h b/src/include/utils/selfuncs.h index 1c9570f..1dd3ac1 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 oper= ator, + List *args, int varRelid, + double default_selectivity); extern double ineq_histogram_selectivity(PlannerInfo *root, VariableStatData *vardata, FmgrInfo *opproc, bool isgt, bool iseq, ------- =_aaaaaaaaaa0 Content-Type: text/x-diff; name="0002-better-contrib-selectivity-2.patch"; charset="us-ascii" Content-ID: <18089.1585677176.3@sss.pgh.pa.us> Content-Description: 0002-better-contrib-selectivity-2.patch Content-Transfer-Encoding: quoted-printable diff --git a/contrib/hstore/hstore--1.6--1.7.sql b/contrib/hstore/hstore--= 1.6--1.7.sql index 0d126ef..3e5cb67 100644 --- a/contrib/hstore/hstore--1.6--1.7.sql +++ b/contrib/hstore/hstore--1.6--1.7.sql @@ -10,3 +10,18 @@ LANGUAGE C IMMUTABLE PARALLEL SAFE; = ALTER OPERATOR FAMILY gist_hstore_ops USING gist ADD FUNCTION 10 (hstore) ghstore_options (internal); + +ALTER OPERATOR ? (hstore, text) + SET (RESTRICT =3D matchingsel, JOIN =3D matchingjoinsel); +ALTER OPERATOR ?| (hstore, text[]) + SET (RESTRICT =3D matchingsel, JOIN =3D matchingjoinsel); +ALTER OPERATOR ?& (hstore, text[]) + SET (RESTRICT =3D matchingsel, JOIN =3D matchingjoinsel); +ALTER OPERATOR @> (hstore, hstore) + SET (RESTRICT =3D matchingsel, JOIN =3D matchingjoinsel); +ALTER OPERATOR <@ (hstore, hstore) + SET (RESTRICT =3D matchingsel, JOIN =3D matchingjoinsel); +ALTER OPERATOR @ (hstore, hstore) + SET (RESTRICT =3D matchingsel, JOIN =3D matchingjoinsel); +ALTER OPERATOR ~ (hstore, hstore) + SET (RESTRICT =3D matchingsel, JOIN =3D matchingjoinsel); diff --git a/contrib/ltree/ltree--1.1--1.2.sql b/contrib/ltree/ltree--1.1-= -1.2.sql index 7b4ea99..186381e 100644 --- a/contrib/ltree/ltree--1.1--1.2.sql +++ b/contrib/ltree/ltree--1.1--1.2.sql @@ -19,3 +19,84 @@ ADD FUNCTION 10 (ltree) ltree_gist_options (internal); ALTER OPERATOR FAMILY gist__ltree_ops USING gist ADD FUNCTION 10 (_ltree) _ltree_gist_options (internal); = +ALTER OPERATOR < (ltree, ltree) + SET (RESTRICT =3D scalarltsel, JOIN =3D scalarltjoinsel); +ALTER OPERATOR <=3D (ltree, ltree) + SET (RESTRICT =3D scalarlesel, JOIN =3D scalarlejoinsel); +ALTER OPERATOR >=3D (ltree, ltree) + SET (RESTRICT =3D scalargesel, JOIN =3D scalargejoinsel); +ALTER OPERATOR > (ltree, ltree) + SET (RESTRICT =3D scalargtsel, JOIN =3D scalargtjoinsel); + +ALTER OPERATOR @> (ltree, ltree) + SET (RESTRICT =3D matchingsel, JOIN =3D matchingjoinsel); +ALTER OPERATOR ^@> (ltree, ltree) + SET (RESTRICT =3D matchingsel, JOIN =3D matchingjoinsel); +ALTER OPERATOR <@ (ltree, ltree) + SET (RESTRICT =3D matchingsel, JOIN =3D matchingjoinsel); +ALTER OPERATOR ^<@ (ltree, ltree) + SET (RESTRICT =3D matchingsel, JOIN =3D matchingjoinsel); +ALTER OPERATOR ~ (ltree, lquery) + SET (RESTRICT =3D matchingsel, JOIN =3D matchingjoinsel); +ALTER OPERATOR ~ (lquery, ltree) + SET (RESTRICT =3D matchingsel, JOIN =3D matchingjoinsel); +ALTER OPERATOR ^~ (ltree, lquery) + SET (RESTRICT =3D matchingsel, JOIN =3D matchingjoinsel); +ALTER OPERATOR ^~ (lquery, ltree) + SET (RESTRICT =3D matchingsel, JOIN =3D matchingjoinsel); +ALTER OPERATOR ? (ltree, _lquery) + SET (RESTRICT =3D matchingsel, JOIN =3D matchingjoinsel); +ALTER OPERATOR ? (_lquery, ltree) + SET (RESTRICT =3D matchingsel, JOIN =3D matchingjoinsel); +ALTER OPERATOR ^? (ltree, _lquery) + SET (RESTRICT =3D matchingsel, JOIN =3D matchingjoinsel); +ALTER OPERATOR ^? (_lquery, ltree) + SET (RESTRICT =3D matchingsel, JOIN =3D matchingjoinsel); +ALTER OPERATOR @ (ltree, ltxtquery) + SET (RESTRICT =3D matchingsel, JOIN =3D matchingjoinsel); +ALTER OPERATOR @ (ltxtquery, ltree) + SET (RESTRICT =3D matchingsel, JOIN =3D matchingjoinsel); +ALTER OPERATOR ^@ (ltree, ltxtquery) + SET (RESTRICT =3D matchingsel, JOIN =3D matchingjoinsel); +ALTER OPERATOR ^@ (ltxtquery, ltree) + SET (RESTRICT =3D matchingsel, JOIN =3D matchingjoinsel); +ALTER OPERATOR @> (_ltree, ltree) + SET (RESTRICT =3D matchingsel, JOIN =3D matchingjoinsel); +ALTER OPERATOR <@ (ltree, _ltree) + SET (RESTRICT =3D matchingsel, JOIN =3D matchingjoinsel); +ALTER OPERATOR <@ (_ltree, ltree) + SET (RESTRICT =3D matchingsel, JOIN =3D matchingjoinsel); +ALTER OPERATOR @> (ltree, _ltree) + SET (RESTRICT =3D matchingsel, JOIN =3D matchingjoinsel); +ALTER OPERATOR ~ (_ltree, lquery) + SET (RESTRICT =3D matchingsel, JOIN =3D matchingjoinsel); +ALTER OPERATOR ~ (lquery, _ltree) + SET (RESTRICT =3D matchingsel, JOIN =3D matchingjoinsel); +ALTER OPERATOR ? (_ltree, _lquery) + SET (RESTRICT =3D matchingsel, JOIN =3D matchingjoinsel); +ALTER OPERATOR ? (_lquery, _ltree) + SET (RESTRICT =3D matchingsel, JOIN =3D matchingjoinsel); +ALTER OPERATOR @ (_ltree, ltxtquery) + SET (RESTRICT =3D matchingsel, JOIN =3D matchingjoinsel); +ALTER OPERATOR @ (ltxtquery, _ltree) + SET (RESTRICT =3D matchingsel, JOIN =3D matchingjoinsel); +ALTER OPERATOR ^@> (_ltree, ltree) + SET (RESTRICT =3D matchingsel, JOIN =3D matchingjoinsel); +ALTER OPERATOR ^<@ (ltree, _ltree) + SET (RESTRICT =3D matchingsel, JOIN =3D matchingjoinsel); +ALTER OPERATOR ^<@ (_ltree, ltree) + SET (RESTRICT =3D matchingsel, JOIN =3D matchingjoinsel); +ALTER OPERATOR ^@> (ltree, _ltree) + SET (RESTRICT =3D matchingsel, JOIN =3D matchingjoinsel); +ALTER OPERATOR ^~ (_ltree, lquery) + SET (RESTRICT =3D matchingsel, JOIN =3D matchingjoinsel); +ALTER OPERATOR ^~ (lquery, _ltree) + SET (RESTRICT =3D matchingsel, JOIN =3D matchingjoinsel); +ALTER OPERATOR ^? (_ltree, _lquery) + SET (RESTRICT =3D matchingsel, JOIN =3D matchingjoinsel); +ALTER OPERATOR ^? (_lquery, _ltree) + SET (RESTRICT =3D matchingsel, JOIN =3D matchingjoinsel); +ALTER OPERATOR ^@ (_ltree, ltxtquery) + SET (RESTRICT =3D matchingsel, JOIN =3D matchingjoinsel); +ALTER OPERATOR ^@ (ltxtquery, _ltree) + SET (RESTRICT =3D matchingsel, JOIN =3D matchingjoinsel); diff --git a/contrib/ltree/ltree_op.c b/contrib/ltree/ltree_op.c index f606761..4ac2ed5 100644 --- a/contrib/ltree/ltree_op.c +++ b/contrib/ltree/ltree_op.c @@ -568,6 +568,9 @@ ltree2text(PG_FUNCTION_ARGS) = /* * ltreeparentsel - Selectivity of parent relationship for ltree data typ= es. + * + * This function is not used anymore, if the ltree extension has been + * updated to 1.2 or later. */ Datum ltreeparentsel(PG_FUNCTION_ARGS) diff --git a/contrib/pg_trgm/expected/pg_trgm.out b/contrib/pg_trgm/expect= ed/pg_trgm.out index 5746be0..923c326 100644 --- a/contrib/pg_trgm/expected/pg_trgm.out +++ b/contrib/pg_trgm/expected/pg_trgm.out @@ -5170,16 +5170,15 @@ SELECT similarity('Szczecin', 'Warsaw'); EXPLAIN (COSTS OFF) SELECT DISTINCT city, similarity(city, 'Warsaw'), show_limit() FROM restaurants WHERE city % 'Warsaw'; - QUERY PLAN = -------------------------------------------------------------- - Unique - -> Sort - Sort Key: city, (similarity(city, 'Warsaw'::text)) - -> Bitmap Heap Scan on restaurants - Recheck Cond: (city % 'Warsaw'::text) - -> Bitmap Index Scan on restaurants_city_idx - Index Cond: (city % 'Warsaw'::text) -(7 rows) + QUERY PLAN = +------------------------------------------------------------------- + HashAggregate + Group Key: city, similarity(city, 'Warsaw'::text), show_limit() + -> Bitmap Heap Scan on restaurants + Recheck Cond: (city % 'Warsaw'::text) + -> Bitmap Index Scan on restaurants_city_idx + Index Cond: (city % 'Warsaw'::text) +(6 rows) = SELECT set_limit(0.3); set_limit = diff --git a/contrib/pg_trgm/pg_trgm--1.4--1.5.sql b/contrib/pg_trgm/pg_tr= gm--1.4--1.5.sql index 3804c3b..284f88d 100644 --- a/contrib/pg_trgm/pg_trgm--1.4--1.5.sql +++ b/contrib/pg_trgm/pg_trgm--1.4--1.5.sql @@ -10,3 +10,14 @@ LANGUAGE C IMMUTABLE PARALLEL SAFE; = ALTER OPERATOR FAMILY gist_trgm_ops USING gist ADD FUNCTION 10 (text) gtrgm_options (internal); + +ALTER OPERATOR % (text, text) + SET (RESTRICT =3D matchingsel, JOIN =3D matchingjoinsel); +ALTER OPERATOR <% (text, text) + SET (RESTRICT =3D matchingsel, JOIN =3D matchingjoinsel); +ALTER OPERATOR %> (text, text) + SET (RESTRICT =3D matchingsel, JOIN =3D matchingjoinsel); +ALTER OPERATOR <<% (text, text) + SET (RESTRICT =3D matchingsel, JOIN =3D matchingjoinsel); +ALTER OPERATOR %>> (text, text) + SET (RESTRICT =3D matchingsel, JOIN =3D matchingjoinsel); ------- =_aaaaaaaaaa0--