Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commita808186

Browse files
committed
Improve selectivity estimation for assorted match-style operators.
Quite a few matching operators such as JSONB's @> used "contsel" and"contjoinsel" as their selectivity estimators. That was a bad idea,because (a) contsel is only a stub, yielding a fixed default estimate,and (b) that default is 0.001, meaning we estimate these operators asfive times more selective than equality, which is surely pretty silly.There's a good model for improving this in ltree's ltreeparentsel():for any "var OP constant" query, we can try applying the operatorto all of the column's MCV and histogram values, taking the latteras being a random sample of the non-MCV values. That code isactually 100% generic, except for the question of exactly whatdefault selectivity ought to be plugged in when we don't have stats.Hence, migrate the guts of ltreeparentsel() into the core code, providewrappers "matchingsel" and "matchingjoinsel" with a more-appropriatedefault estimate, and use those for the non-geometric operators thatformerly used contsel (mostly JSONB containment operators and tsquerymatching).Also apply this code to some match-like operators in hstore, ltree, andpg_trgm, including the former users of ltreeparentsel as well as onesthat improperly used contsel. Since commit911e702 just created newversions of those extensions that we haven't released yet, we can sneakthis change into those new versions instead of having to create anadditional generation of update scripts.Patch by me, reviewed by Alexey BashtanovDiscussion:https://postgr.es/m/12237.1582833074@sss.pgh.pa.us
1 parentd8653f4 commita808186

File tree

11 files changed

+332
-121
lines changed

11 files changed

+332
-121
lines changed

‎contrib/hstore/hstore--1.6--1.7.sql

Lines changed: 15 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -10,3 +10,18 @@ LANGUAGE C IMMUTABLE PARALLEL SAFE;
1010

1111
ALTEROPERATOR FAMILY gist_hstore_ops USING gist
1212
ADD FUNCTION10 (hstore) ghstore_options (internal);
13+
14+
ALTEROPERATOR ? (hstore,text)
15+
SET (RESTRICT= matchingsel,JOIN= matchingjoinsel);
16+
ALTEROPERATOR ?| (hstore,text[])
17+
SET (RESTRICT= matchingsel,JOIN= matchingjoinsel);
18+
ALTEROPERATOR ?& (hstore,text[])
19+
SET (RESTRICT= matchingsel,JOIN= matchingjoinsel);
20+
ALTEROPERATOR @> (hstore, hstore)
21+
SET (RESTRICT= matchingsel,JOIN= matchingjoinsel);
22+
ALTEROPERATOR<@ (hstore, hstore)
23+
SET (RESTRICT= matchingsel,JOIN= matchingjoinsel);
24+
ALTEROPERATOR @ (hstore, hstore)
25+
SET (RESTRICT= matchingsel,JOIN= matchingjoinsel);
26+
ALTEROPERATOR ~ (hstore, hstore)
27+
SET (RESTRICT= matchingsel,JOIN= matchingjoinsel);

‎contrib/ltree/ltree--1.1--1.2.sql

Lines changed: 81 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -19,3 +19,84 @@ ADD FUNCTION 10 (ltree) ltree_gist_options (internal);
1919
ALTEROPERATOR FAMILY gist__ltree_ops USING gist
2020
ADD FUNCTION10 (_ltree) _ltree_gist_options (internal);
2121

22+
ALTEROPERATOR< (ltree, ltree)
23+
SET (RESTRICT= scalarltsel,JOIN= scalarltjoinsel);
24+
ALTEROPERATOR<= (ltree, ltree)
25+
SET (RESTRICT= scalarlesel,JOIN= scalarlejoinsel);
26+
ALTEROPERATOR>= (ltree, ltree)
27+
SET (RESTRICT= scalargesel,JOIN= scalargejoinsel);
28+
ALTEROPERATOR> (ltree, ltree)
29+
SET (RESTRICT= scalargtsel,JOIN= scalargtjoinsel);
30+
31+
ALTEROPERATOR @> (ltree, ltree)
32+
SET (RESTRICT= matchingsel,JOIN= matchingjoinsel);
33+
ALTEROPERATOR ^@> (ltree, ltree)
34+
SET (RESTRICT= matchingsel,JOIN= matchingjoinsel);
35+
ALTEROPERATOR<@ (ltree, ltree)
36+
SET (RESTRICT= matchingsel,JOIN= matchingjoinsel);
37+
ALTEROPERATOR ^<@ (ltree, ltree)
38+
SET (RESTRICT= matchingsel,JOIN= matchingjoinsel);
39+
ALTEROPERATOR ~ (ltree, lquery)
40+
SET (RESTRICT= matchingsel,JOIN= matchingjoinsel);
41+
ALTEROPERATOR ~ (lquery, ltree)
42+
SET (RESTRICT= matchingsel,JOIN= matchingjoinsel);
43+
ALTEROPERATOR ^~ (ltree, lquery)
44+
SET (RESTRICT= matchingsel,JOIN= matchingjoinsel);
45+
ALTEROPERATOR ^~ (lquery, ltree)
46+
SET (RESTRICT= matchingsel,JOIN= matchingjoinsel);
47+
ALTEROPERATOR ? (ltree, _lquery)
48+
SET (RESTRICT= matchingsel,JOIN= matchingjoinsel);
49+
ALTEROPERATOR ? (_lquery, ltree)
50+
SET (RESTRICT= matchingsel,JOIN= matchingjoinsel);
51+
ALTEROPERATOR ^? (ltree, _lquery)
52+
SET (RESTRICT= matchingsel,JOIN= matchingjoinsel);
53+
ALTEROPERATOR ^? (_lquery, ltree)
54+
SET (RESTRICT= matchingsel,JOIN= matchingjoinsel);
55+
ALTEROPERATOR @ (ltree, ltxtquery)
56+
SET (RESTRICT= matchingsel,JOIN= matchingjoinsel);
57+
ALTEROPERATOR @ (ltxtquery, ltree)
58+
SET (RESTRICT= matchingsel,JOIN= matchingjoinsel);
59+
ALTEROPERATOR ^@ (ltree, ltxtquery)
60+
SET (RESTRICT= matchingsel,JOIN= matchingjoinsel);
61+
ALTEROPERATOR ^@ (ltxtquery, ltree)
62+
SET (RESTRICT= matchingsel,JOIN= matchingjoinsel);
63+
ALTEROPERATOR @> (_ltree, ltree)
64+
SET (RESTRICT= matchingsel,JOIN= matchingjoinsel);
65+
ALTEROPERATOR<@ (ltree, _ltree)
66+
SET (RESTRICT= matchingsel,JOIN= matchingjoinsel);
67+
ALTEROPERATOR<@ (_ltree, ltree)
68+
SET (RESTRICT= matchingsel,JOIN= matchingjoinsel);
69+
ALTEROPERATOR @> (ltree, _ltree)
70+
SET (RESTRICT= matchingsel,JOIN= matchingjoinsel);
71+
ALTEROPERATOR ~ (_ltree, lquery)
72+
SET (RESTRICT= matchingsel,JOIN= matchingjoinsel);
73+
ALTEROPERATOR ~ (lquery, _ltree)
74+
SET (RESTRICT= matchingsel,JOIN= matchingjoinsel);
75+
ALTEROPERATOR ? (_ltree, _lquery)
76+
SET (RESTRICT= matchingsel,JOIN= matchingjoinsel);
77+
ALTEROPERATOR ? (_lquery, _ltree)
78+
SET (RESTRICT= matchingsel,JOIN= matchingjoinsel);
79+
ALTEROPERATOR @ (_ltree, ltxtquery)
80+
SET (RESTRICT= matchingsel,JOIN= matchingjoinsel);
81+
ALTEROPERATOR @ (ltxtquery, _ltree)
82+
SET (RESTRICT= matchingsel,JOIN= matchingjoinsel);
83+
ALTEROPERATOR ^@> (_ltree, ltree)
84+
SET (RESTRICT= matchingsel,JOIN= matchingjoinsel);
85+
ALTEROPERATOR ^<@ (ltree, _ltree)
86+
SET (RESTRICT= matchingsel,JOIN= matchingjoinsel);
87+
ALTEROPERATOR ^<@ (_ltree, ltree)
88+
SET (RESTRICT= matchingsel,JOIN= matchingjoinsel);
89+
ALTEROPERATOR ^@> (ltree, _ltree)
90+
SET (RESTRICT= matchingsel,JOIN= matchingjoinsel);
91+
ALTEROPERATOR ^~ (_ltree, lquery)
92+
SET (RESTRICT= matchingsel,JOIN= matchingjoinsel);
93+
ALTEROPERATOR ^~ (lquery, _ltree)
94+
SET (RESTRICT= matchingsel,JOIN= matchingjoinsel);
95+
ALTEROPERATOR ^? (_ltree, _lquery)
96+
SET (RESTRICT= matchingsel,JOIN= matchingjoinsel);
97+
ALTEROPERATOR ^? (_lquery, _ltree)
98+
SET (RESTRICT= matchingsel,JOIN= matchingjoinsel);
99+
ALTEROPERATOR ^@ (_ltree, ltxtquery)
100+
SET (RESTRICT= matchingsel,JOIN= matchingjoinsel);
101+
ALTEROPERATOR ^@ (ltxtquery, _ltree)
102+
SET (RESTRICT= matchingsel,JOIN= matchingjoinsel);

‎contrib/ltree/ltree_op.c

Lines changed: 7 additions & 95 deletions
Original file line numberDiff line numberDiff line change
@@ -566,10 +566,11 @@ ltree2text(PG_FUNCTION_ARGS)
566566
}
567567

568568

569-
#defineDEFAULT_PARENT_SEL 0.001
570-
571569
/*
572570
*ltreeparentsel - Selectivity of parent relationship for ltree data types.
571+
*
572+
* This function is not used anymore, if the ltree extension has been
573+
* updated to 1.2 or later.
573574
*/
574575
Datum
575576
ltreeparentsel(PG_FUNCTION_ARGS)
@@ -578,101 +579,12 @@ ltreeparentsel(PG_FUNCTION_ARGS)
578579
Oidoperator=PG_GETARG_OID(1);
579580
List*args= (List*)PG_GETARG_POINTER(2);
580581
intvarRelid=PG_GETARG_INT32(3);
581-
VariableStatDatavardata;
582-
Node*other;
583-
boolvaronleft;
584582
doubleselec;
585583

586-
/*
587-
* If expression is not variable <@ something or something <@ variable,
588-
* then punt and return a default estimate.
589-
*/
590-
if (!get_restriction_variable(root,args,varRelid,
591-
&vardata,&other,&varonleft))
592-
PG_RETURN_FLOAT8(DEFAULT_PARENT_SEL);
593-
594-
/*
595-
* If the something is a NULL constant, assume operator is strict and
596-
* return zero, ie, operator will never return TRUE.
597-
*/
598-
if (IsA(other,Const)&&
599-
((Const*)other)->constisnull)
600-
{
601-
ReleaseVariableStats(vardata);
602-
PG_RETURN_FLOAT8(0.0);
603-
}
604-
605-
if (IsA(other,Const))
606-
{
607-
/* Variable is being compared to a known non-null constant */
608-
Datumconstval= ((Const*)other)->constvalue;
609-
FmgrInfocontproc;
610-
doublemcvsum;
611-
doublemcvsel;
612-
doublenullfrac;
613-
inthist_size;
614-
615-
fmgr_info(get_opcode(operator),&contproc);
616-
617-
/*
618-
* Is the constant "<@" to any of the column's most common values?
619-
*/
620-
mcvsel=mcv_selectivity(&vardata,&contproc,constval,varonleft,
621-
&mcvsum);
622-
623-
/*
624-
* If the histogram is large enough, see what fraction of it the
625-
* constant is "<@" to, and assume that's representative of the
626-
* non-MCV population. Otherwise use the default selectivity for the
627-
* non-MCV population.
628-
*/
629-
selec=histogram_selectivity(&vardata,&contproc,
630-
constval,varonleft,
631-
10,1,&hist_size);
632-
if (selec<0)
633-
{
634-
/* Nope, fall back on default */
635-
selec=DEFAULT_PARENT_SEL;
636-
}
637-
elseif (hist_size<100)
638-
{
639-
/*
640-
* For histogram sizes from 10 to 100, we combine the histogram
641-
* and default selectivities, putting increasingly more trust in
642-
* the histogram for larger sizes.
643-
*/
644-
doublehist_weight=hist_size /100.0;
645-
646-
selec=selec*hist_weight+
647-
DEFAULT_PARENT_SEL* (1.0-hist_weight);
648-
}
649-
650-
/* In any case, don't believe extremely small or large estimates. */
651-
if (selec<0.0001)
652-
selec=0.0001;
653-
elseif (selec>0.9999)
654-
selec=0.9999;
655-
656-
if (HeapTupleIsValid(vardata.statsTuple))
657-
nullfrac= ((Form_pg_statistic)GETSTRUCT(vardata.statsTuple))->stanullfrac;
658-
else
659-
nullfrac=0.0;
660-
661-
/*
662-
* Now merge the results from the MCV and histogram calculations,
663-
* realizing that the histogram covers only the non-null values that
664-
* are not listed in MCV.
665-
*/
666-
selec *=1.0-nullfrac-mcvsum;
667-
selec+=mcvsel;
668-
}
669-
else
670-
selec=DEFAULT_PARENT_SEL;
671-
672-
ReleaseVariableStats(vardata);
673-
674-
/* result should be in range, but make sure... */
675-
CLAMP_PROBABILITY(selec);
584+
/* Use generic restriction selectivity logic, with default 0.001. */
585+
selec=generic_restriction_selectivity(root,operator,
586+
args,varRelid,
587+
0.001);
676588

677589
PG_RETURN_FLOAT8((float8)selec);
678590
}

‎contrib/pg_trgm/expected/pg_trgm.out

Lines changed: 9 additions & 10 deletions
Original file line numberDiff line numberDiff line change
@@ -5170,16 +5170,15 @@ SELECT similarity('Szczecin', 'Warsaw');
51705170
EXPLAIN (COSTS OFF)
51715171
SELECT DISTINCT city, similarity(city, 'Warsaw'), show_limit()
51725172
FROM restaurants WHERE city % 'Warsaw';
5173-
QUERY PLAN
5174-
-------------------------------------------------------------
5175-
Unique
5176-
-> Sort
5177-
Sort Key: city, (similarity(city, 'Warsaw'::text))
5178-
-> Bitmap Heap Scan on restaurants
5179-
Recheck Cond: (city % 'Warsaw'::text)
5180-
-> Bitmap Index Scan on restaurants_city_idx
5181-
Index Cond: (city % 'Warsaw'::text)
5182-
(7 rows)
5173+
QUERY PLAN
5174+
-------------------------------------------------------------------
5175+
HashAggregate
5176+
Group Key: city, similarity(city, 'Warsaw'::text), show_limit()
5177+
-> Bitmap Heap Scan on restaurants
5178+
Recheck Cond: (city % 'Warsaw'::text)
5179+
-> Bitmap Index Scan on restaurants_city_idx
5180+
Index Cond: (city % 'Warsaw'::text)
5181+
(6 rows)
51835182

51845183
SELECT set_limit(0.3);
51855184
set_limit

‎contrib/pg_trgm/pg_trgm--1.4--1.5.sql

Lines changed: 11 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -10,3 +10,14 @@ LANGUAGE C IMMUTABLE PARALLEL SAFE;
1010

1111
ALTEROPERATOR FAMILY gist_trgm_ops USING gist
1212
ADD FUNCTION10 (text) gtrgm_options (internal);
13+
14+
ALTEROPERATOR % (text,text)
15+
SET (RESTRICT= matchingsel,JOIN= matchingjoinsel);
16+
ALTEROPERATOR<% (text,text)
17+
SET (RESTRICT= matchingsel,JOIN= matchingjoinsel);
18+
ALTEROPERATOR %> (text,text)
19+
SET (RESTRICT= matchingsel,JOIN= matchingjoinsel);
20+
ALTEROPERATOR<<% (text,text)
21+
SET (RESTRICT= matchingsel,JOIN= matchingjoinsel);
22+
ALTEROPERATOR %>> (text,text)
23+
SET (RESTRICT= matchingsel,JOIN= matchingjoinsel);

‎doc/src/sgml/xoper.sgml

Lines changed: 13 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -282,6 +282,18 @@ column OP constant
282282
estimates won't be as good as they could be.
283283
</para>
284284

285+
<para>
286+
Another useful built-in selectivity estimation function
287+
is <function>matchingsel</function>, which will work for almost any
288+
binary operator, if standard MCV and/or histogram statistics are
289+
collected for the input data type(s). Its default estimate is set to
290+
twice the default estimate used in <function>eqsel</function>, making
291+
it most suitable for comparison operators that are somewhat less
292+
strict than equality. (Or you could call the
293+
underlying <function>generic_restriction_selectivity</function>
294+
function, providing a different default estimate.)
295+
</para>
296+
285297
<para>
286298
There are additional selectivity estimation functions designed for geometric
287299
operators in <filename>src/backend/utils/adt/geo_selfuncs.c</filename>: <function>areasel</function>, <function>positionsel</function>,
@@ -319,6 +331,7 @@ table1.column1 OP table2.column2
319331
<member><function>scalarlejoinsel</function> for <literal>&lt;=</literal></member>
320332
<member><function>scalargtjoinsel</function> for <literal>&gt;</literal></member>
321333
<member><function>scalargejoinsel</function> for <literal>&gt;=</literal></member>
334+
<member><function>matchingjoinsel</function> for generic matching operators</member>
322335
<member><function>areajoinsel</function> for 2D area-based comparisons</member>
323336
<member><function>positionjoinsel</function> for 2D position-based comparisons</member>
324337
<member><function>contjoinsel</function> for 2D containment-based comparisons</member>

0 commit comments

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