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

Commit7d08ce2

Browse files
committed
Distinguish selectivity of < from <= and > from >=.
Historically, the selectivity functions have simply not distinguished< from <=, or > from >=, arguing that the fraction of the population thatsatisfies the "=" aspect can be considered to be vanishingly small, if thecomparison value isn't any of the most-common-values for the variable.(If it is, the code path that executes the operator against each MCV willtake care of things properly.) But that isn't really true unless we'redealing with a continuum of variable values, and in practice we seldom are.If "x = const" would estimate a nonzero number of rows for a given constvalue, then it follows that we ought to estimate different numbers of rowsfor "x < const" and "x <= const", even if the const is not one of the MCVs.Handling this more honestly makes a significant difference in edge cases,such as the estimate for a tight range (x BETWEEN y AND z where y and zare close together).Hence, split scalarltsel into scalarltsel/scalarlesel, and similarlysplit scalargtsel into scalargtsel/scalargesel. Adjust <= and >=operator definitions to reference the new selectivity functions.Improve the core ineq_histogram_selectivity() function to make acorrection for equality. (Along the way, I learned quite a bit aboutexactly why that function gives good answers, which I tried to memorializein improved comments.)The corresponding join selectivity functions were, and remain, just stubs.But I chose to split them similarly, to avoid confusion and to prevent theneed for doing this exercise again if someone ever makes them less stubby.In passing, change ineq_histogram_selectivity's clamp for extremeprobability estimates so that it varies depending on the histogramsize, instead of being hardwired at 0.0001. With the default histogramsize of 100 entries, you still get the old clamp value, but biggerhistograms should allow us to put more faith in edge values.Tom Lane, reviewed by Aleksander Alekseev and Kuntal GhoshDiscussion:https://postgr.es/m/12232.1499140410@sss.pgh.pa.us
1 parent089880b commit7d08ce2

File tree

21 files changed

+633
-273
lines changed

21 files changed

+633
-273
lines changed

‎contrib/citext/Makefile

Lines changed: 2 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -3,7 +3,8 @@
33
MODULES = citext
44

55
EXTENSION = citext
6-
DATA = citext--1.4.sql citext--1.3--1.4.sql\
6+
DATA = citext--1.4.sql citext--1.4--1.5.sql\
7+
citext--1.3--1.4.sql\
78
citext--1.2--1.3.sql citext--1.1--1.2.sql\
89
citext--1.0--1.1.sql citext--unpackaged--1.0.sql
910
PGFILEDESC = "citext - case-insensitive character string data type"

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

Lines changed: 14 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,14 @@
1+
/* contrib/citext/citext--1.4--1.5.sql*/
2+
3+
-- complain if script is sourced in psql, rather than via ALTER EXTENSION
4+
\echo Use"ALTER EXTENSION citext UPDATE TO '1.5'" to load this file. \quit
5+
6+
ALTEROPERATOR<= (citext, citext)SET (
7+
RESTRICT= scalarlesel,
8+
JOIN= scalarlejoinsel
9+
);
10+
11+
ALTEROPERATOR>= (citext, citext)SET (
12+
RESTRICT= scalargesel,
13+
JOIN= scalargejoinsel
14+
);

‎contrib/citext/citext.control

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -1,5 +1,5 @@
11
# citext extension
22
comment = 'data type for case-insensitive character strings'
3-
default_version = '1.4'
3+
default_version = '1.5'
44
module_pathname = '$libdir/citext'
55
relocatable = true

‎contrib/cube/Makefile

Lines changed: 2 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -4,7 +4,8 @@ MODULE_big = cube
44
OBJS= cube.o cubeparse.o$(WIN32RES)
55

66
EXTENSION = cube
7-
DATA = cube--1.2.sql cube--1.1--1.2.sql cube--1.0--1.1.sql\
7+
DATA = cube--1.2.sql cube--1.2--1.3.sql\
8+
cube--1.1--1.2.sql cube--1.0--1.1.sql\
89
cube--unpackaged--1.0.sql
910
PGFILEDESC = "cube - multidimensional cube data type"
1011

‎contrib/cube/cube--1.2--1.3.sql

Lines changed: 12 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,12 @@
1+
/* contrib/cube/cube--1.2--1.3.sql*/
2+
3+
-- complain if script is sourced in psql, rather than via ALTER EXTENSION
4+
\echo Use"ALTER EXTENSION cube UPDATE TO '1.3'" to load this file. \quit
5+
6+
ALTEROPERATOR<= (cube, cube)SET (
7+
RESTRICT= scalarlesel,JOIN= scalarlejoinsel
8+
);
9+
10+
ALTEROPERATOR>= (cube, cube)SET (
11+
RESTRICT= scalargesel,JOIN= scalargejoinsel
12+
);

‎contrib/cube/cube.control

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -1,5 +1,5 @@
11
# cube extension
22
comment = 'data type for multidimensional cubes'
3-
default_version = '1.2'
3+
default_version = '1.3'
44
module_pathname = '$libdir/cube'
55
relocatable = true

‎contrib/hstore/Makefile

Lines changed: 2 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -5,7 +5,8 @@ OBJS = hstore_io.o hstore_op.o hstore_gist.o hstore_gin.o hstore_compat.o \
55
$(WIN32RES)
66

77
EXTENSION = hstore
8-
DATA = hstore--1.4.sql hstore--1.3--1.4.sql hstore--1.2--1.3.sql\
8+
DATA = hstore--1.4.sql hstore--1.4--1.5.sql\
9+
hstore--1.3--1.4.sql hstore--1.2--1.3.sql\
910
hstore--1.1--1.2.sql hstore--1.0--1.1.sql\
1011
hstore--unpackaged--1.0.sql
1112
PGFILEDESC = "hstore - key/value pair data type"

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

Lines changed: 14 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,14 @@
1+
/* contrib/hstore/hstore--1.4--1.5.sql*/
2+
3+
-- complain if script is sourced in psql, rather than via ALTER EXTENSION
4+
\echo Use"ALTER EXTENSION hstore UPDATE TO '1.5'" to load this file. \quit
5+
6+
ALTEROPERATOR#<=# (hstore, hstore) SET (
7+
RESTRICT= scalarlesel,
8+
JOIN= scalarlejoinsel
9+
);
10+
11+
ALTEROPERATOR#>=# (hstore, hstore) SET (
12+
RESTRICT= scalargesel,
13+
JOIN= scalargejoinsel
14+
);

‎contrib/hstore/hstore.control

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -1,5 +1,5 @@
11
# hstore extension
22
comment = 'data type for storing sets of (key, value) pairs'
3-
default_version = '1.4'
3+
default_version = '1.5'
44
module_pathname = '$libdir/hstore'
55
relocatable = true

‎contrib/isn/Makefile

Lines changed: 2 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -3,7 +3,8 @@
33
MODULES = isn
44

55
EXTENSION = isn
6-
DATA = isn--1.1.sql isn--1.0--1.1.sql isn--unpackaged--1.0.sql
6+
DATA = isn--1.1.sql isn--1.1--1.2.sql\
7+
isn--1.0--1.1.sql isn--unpackaged--1.0.sql
78
PGFILEDESC = "isn - data types for international product numbering standards"
89

910
REGRESS = isn

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

Lines changed: 228 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,228 @@
1+
/* contrib/isn/isn--1.1--1.2.sql*/
2+
3+
-- complain if script is sourced in psql, rather than via ALTER EXTENSION
4+
\echo Use"ALTER EXTENSION isn UPDATE TO '1.2'" to load this file. \quit
5+
6+
ALTEROPERATOR<= (ean13, ean13)SET (
7+
RESTRICT= scalarlesel,
8+
JOIN= scalarlejoinsel);
9+
10+
ALTEROPERATOR>= (ean13, ean13)SET (
11+
RESTRICT= scalargesel,
12+
JOIN= scalargejoinsel);
13+
14+
ALTEROPERATOR<= (ean13, isbn13)SET (
15+
RESTRICT= scalarlesel,
16+
JOIN= scalarlejoinsel);
17+
18+
ALTEROPERATOR>= (ean13, isbn13)SET (
19+
RESTRICT= scalargesel,
20+
JOIN= scalargejoinsel);
21+
22+
ALTEROPERATOR<= (isbn13, ean13)SET (
23+
RESTRICT= scalarlesel,
24+
JOIN= scalarlejoinsel);
25+
26+
ALTEROPERATOR>= (isbn13, ean13)SET (
27+
RESTRICT= scalargesel,
28+
JOIN= scalargejoinsel);
29+
30+
ALTEROPERATOR<= (ean13, ismn13)SET (
31+
RESTRICT= scalarlesel,
32+
JOIN= scalarlejoinsel);
33+
34+
ALTEROPERATOR>= (ean13, ismn13)SET (
35+
RESTRICT= scalargesel,
36+
JOIN= scalargejoinsel);
37+
38+
ALTEROPERATOR<= (ismn13, ean13)SET (
39+
RESTRICT= scalarlesel,
40+
JOIN= scalarlejoinsel);
41+
42+
ALTEROPERATOR>= (ismn13, ean13)SET (
43+
RESTRICT= scalargesel,
44+
JOIN= scalargejoinsel);
45+
46+
ALTEROPERATOR<= (ean13, issn13)SET (
47+
RESTRICT= scalarlesel,
48+
JOIN= scalarlejoinsel);
49+
50+
ALTEROPERATOR>= (ean13, issn13)SET (
51+
RESTRICT= scalargesel,
52+
JOIN= scalargejoinsel);
53+
54+
ALTEROPERATOR<= (ean13, isbn)SET (
55+
RESTRICT= scalarlesel,
56+
JOIN= scalarlejoinsel);
57+
58+
ALTEROPERATOR>= (ean13, isbn)SET (
59+
RESTRICT= scalargesel,
60+
JOIN= scalargejoinsel);
61+
62+
ALTEROPERATOR<= (ean13, ismn)SET (
63+
RESTRICT= scalarlesel,
64+
JOIN= scalarlejoinsel);
65+
66+
ALTEROPERATOR>= (ean13, ismn)SET (
67+
RESTRICT= scalargesel,
68+
JOIN= scalargejoinsel);
69+
70+
ALTEROPERATOR<= (ean13, issn)SET (
71+
RESTRICT= scalarlesel,
72+
JOIN= scalarlejoinsel);
73+
74+
ALTEROPERATOR>= (ean13, issn)SET (
75+
RESTRICT= scalargesel,
76+
JOIN= scalargejoinsel);
77+
78+
ALTEROPERATOR<= (ean13, upc)SET (
79+
RESTRICT= scalarlesel,
80+
JOIN= scalarlejoinsel);
81+
82+
ALTEROPERATOR>= (ean13, upc)SET (
83+
RESTRICT= scalargesel,
84+
JOIN= scalargejoinsel);
85+
86+
ALTEROPERATOR<= (isbn13, isbn13)SET (
87+
RESTRICT= scalarlesel,
88+
JOIN= scalarlejoinsel);
89+
90+
ALTEROPERATOR>= (isbn13, isbn13)SET (
91+
RESTRICT= scalargesel,
92+
JOIN= scalargejoinsel);
93+
94+
ALTEROPERATOR<= (isbn13, isbn)SET (
95+
RESTRICT= scalarlesel,
96+
JOIN= scalarlejoinsel);
97+
98+
ALTEROPERATOR>= (isbn13, isbn)SET (
99+
RESTRICT= scalargesel,
100+
JOIN= scalargejoinsel);
101+
102+
ALTEROPERATOR<= (isbn, isbn)SET (
103+
RESTRICT= scalarlesel,
104+
JOIN= scalarlejoinsel);
105+
106+
ALTEROPERATOR>= (isbn, isbn)SET (
107+
RESTRICT= scalargesel,
108+
JOIN= scalargejoinsel);
109+
110+
ALTEROPERATOR<= (isbn, isbn13)SET (
111+
RESTRICT= scalarlesel,
112+
JOIN= scalarlejoinsel);
113+
114+
ALTEROPERATOR>= (isbn, isbn13)SET (
115+
RESTRICT= scalargesel,
116+
JOIN= scalargejoinsel);
117+
118+
ALTEROPERATOR<= (isbn, ean13)SET (
119+
RESTRICT= scalarlesel,
120+
JOIN= scalarlejoinsel);
121+
122+
ALTEROPERATOR>= (isbn, ean13)SET (
123+
RESTRICT= scalargesel,
124+
JOIN= scalargejoinsel);
125+
126+
ALTEROPERATOR<= (ismn13, ismn13)SET (
127+
RESTRICT= scalarlesel,
128+
JOIN= scalarlejoinsel);
129+
130+
ALTEROPERATOR>= (ismn13, ismn13)SET (
131+
RESTRICT= scalargesel,
132+
JOIN= scalargejoinsel);
133+
134+
ALTEROPERATOR<= (ismn13, ismn)SET (
135+
RESTRICT= scalarlesel,
136+
JOIN= scalarlejoinsel);
137+
138+
ALTEROPERATOR>= (ismn13, ismn)SET (
139+
RESTRICT= scalargesel,
140+
JOIN= scalargejoinsel);
141+
142+
ALTEROPERATOR<= (ismn, ismn)SET (
143+
RESTRICT= scalarlesel,
144+
JOIN= scalarlejoinsel);
145+
146+
ALTEROPERATOR>= (ismn, ismn)SET (
147+
RESTRICT= scalargesel,
148+
JOIN= scalargejoinsel);
149+
150+
ALTEROPERATOR<= (ismn, ismn13)SET (
151+
RESTRICT= scalarlesel,
152+
JOIN= scalarlejoinsel);
153+
154+
ALTEROPERATOR>= (ismn, ismn13)SET (
155+
RESTRICT= scalargesel,
156+
JOIN= scalargejoinsel);
157+
158+
ALTEROPERATOR<= (ismn, ean13)SET (
159+
RESTRICT= scalarlesel,
160+
JOIN= scalarlejoinsel);
161+
162+
ALTEROPERATOR>= (ismn, ean13)SET (
163+
RESTRICT= scalargesel,
164+
JOIN= scalargejoinsel);
165+
166+
ALTEROPERATOR<= (issn13, issn13)SET (
167+
RESTRICT= scalarlesel,
168+
JOIN= scalarlejoinsel);
169+
170+
ALTEROPERATOR>= (issn13, issn13)SET (
171+
RESTRICT= scalargesel,
172+
JOIN= scalargejoinsel);
173+
174+
ALTEROPERATOR<= (issn13, issn)SET (
175+
RESTRICT= scalarlesel,
176+
JOIN= scalarlejoinsel);
177+
178+
ALTEROPERATOR>= (issn13, issn)SET (
179+
RESTRICT= scalargesel,
180+
JOIN= scalargejoinsel);
181+
182+
ALTEROPERATOR<= (issn13, ean13)SET (
183+
RESTRICT= scalarlesel,
184+
JOIN= scalarlejoinsel);
185+
186+
ALTEROPERATOR>= (issn13, ean13)SET (
187+
RESTRICT= scalargesel,
188+
JOIN= scalargejoinsel);
189+
190+
ALTEROPERATOR<= (issn, issn)SET (
191+
RESTRICT= scalarlesel,
192+
JOIN= scalarlejoinsel);
193+
194+
ALTEROPERATOR>= (issn, issn)SET (
195+
RESTRICT= scalargesel,
196+
JOIN= scalargejoinsel);
197+
198+
ALTEROPERATOR<= (issn, issn13)SET (
199+
RESTRICT= scalarlesel,
200+
JOIN= scalarlejoinsel);
201+
202+
ALTEROPERATOR>= (issn, issn13)SET (
203+
RESTRICT= scalargesel,
204+
JOIN= scalargejoinsel);
205+
206+
ALTEROPERATOR<= (issn, ean13)SET (
207+
RESTRICT= scalarlesel,
208+
JOIN= scalarlejoinsel);
209+
210+
ALTEROPERATOR>= (issn, ean13)SET (
211+
RESTRICT= scalargesel,
212+
JOIN= scalargejoinsel);
213+
214+
ALTEROPERATOR<= (upc, upc)SET (
215+
RESTRICT= scalarlesel,
216+
JOIN= scalarlejoinsel);
217+
218+
ALTEROPERATOR>= (upc, upc)SET (
219+
RESTRICT= scalargesel,
220+
JOIN= scalargejoinsel);
221+
222+
ALTEROPERATOR<= (upc, ean13)SET (
223+
RESTRICT= scalarlesel,
224+
JOIN= scalarlejoinsel);
225+
226+
ALTEROPERATOR>= (upc, ean13)SET (
227+
RESTRICT= scalargesel,
228+
JOIN= scalargejoinsel);

‎contrib/isn/isn.control

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -1,5 +1,5 @@
11
# isn extension
22
comment = 'data types for international product numbering standards'
3-
default_version = '1.1'
3+
default_version = '1.2'
44
module_pathname = '$libdir/isn'
55
relocatable = true

‎doc/src/sgml/xindex.sgml

Lines changed: 1 addition & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -801,8 +801,7 @@ CREATE OPERATOR &lt; (
801801
It is important to specify the correct commutator and negator operators,
802802
as well as suitable restriction and join selectivity
803803
functions, otherwise the optimizer will be unable to make effective
804-
use of the index. Note that the less-than, equal, and
805-
greater-than cases should use different selectivity functions.
804+
use of the index.
806805
</para>
807806

808807
<para>

0 commit comments

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