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

Commitc085e1c

Browse files
committed
Add support for <-> (box, point) operator to GiST box_ops
Index-based calculation of this operator is exact. So, signature ofgist_bbox_distance() function is changes so that caller is responsible forsetting *recheck flag.Discussion:https://postgr.es/m/f71ba19d-d989-63b6-f04a-abf02ad9345d%40postgrespro.ruAuthor: Nikita GlukhovReviewed-by: Tom Lane, Alexander Korotkov
1 parent6254c55 commitc085e1c

File tree

7 files changed

+133
-17
lines changed

7 files changed

+133
-17
lines changed

‎doc/src/sgml/gist.sgml

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -83,6 +83,7 @@
8383
<literal>~=</literal>
8484
</entry>
8585
<entry>
86+
<literal>&lt;-&gt;</literal>
8687
</entry>
8788
</row>
8889
<row>

‎src/backend/access/gist/gistproc.c

Lines changed: 31 additions & 17 deletions
Original file line numberDiff line numberDiff line change
@@ -1464,26 +1464,12 @@ gist_point_distance(PG_FUNCTION_ARGS)
14641464
PG_RETURN_FLOAT8(distance);
14651465
}
14661466

1467-
/*
1468-
* The inexact GiST distance method for geometric types that store bounding
1469-
* boxes.
1470-
*
1471-
* Compute lossy distance from point to index entries. The result is inexact
1472-
* because index entries are bounding boxes, not the exact shapes of the
1473-
* indexed geometric types. We use distance from point to MBR of index entry.
1474-
* This is a lower bound estimate of distance from point to indexed geometric
1475-
* type.
1476-
*/
14771467
staticfloat8
1478-
gist_bbox_distance(GISTENTRY*entry,Datumquery,
1479-
StrategyNumberstrategy,bool*recheck)
1468+
gist_bbox_distance(GISTENTRY*entry,Datumquery,StrategyNumberstrategy)
14801469
{
14811470
float8distance;
14821471
StrategyNumberstrategyGroup=strategy /GeoStrategyNumberOffset;
14831472

1484-
/* Bounding box distance is always inexact. */
1485-
*recheck= true;
1486-
14871473
switch (strategyGroup)
14881474
{
14891475
casePointStrategyNumberGroup:
@@ -1499,6 +1485,32 @@ gist_bbox_distance(GISTENTRY *entry, Datum query,
14991485
returndistance;
15001486
}
15011487

1488+
Datum
1489+
gist_box_distance(PG_FUNCTION_ARGS)
1490+
{
1491+
GISTENTRY*entry= (GISTENTRY*)PG_GETARG_POINTER(0);
1492+
Datumquery=PG_GETARG_DATUM(1);
1493+
StrategyNumberstrategy= (StrategyNumber)PG_GETARG_UINT16(2);
1494+
1495+
/* Oid subtype = PG_GETARG_OID(3); */
1496+
/* bool *recheck = (bool *) PG_GETARG_POINTER(4); */
1497+
float8distance;
1498+
1499+
distance=gist_bbox_distance(entry,query,strategy);
1500+
1501+
PG_RETURN_FLOAT8(distance);
1502+
}
1503+
1504+
/*
1505+
* The inexact GiST distance methods for geometric types that store bounding
1506+
* boxes.
1507+
*
1508+
* Compute lossy distance from point to index entries. The result is inexact
1509+
* because index entries are bounding boxes, not the exact shapes of the
1510+
* indexed geometric types. We use distance from point to MBR of index entry.
1511+
* This is a lower bound estimate of distance from point to indexed geometric
1512+
* type.
1513+
*/
15021514
Datum
15031515
gist_circle_distance(PG_FUNCTION_ARGS)
15041516
{
@@ -1510,7 +1522,8 @@ gist_circle_distance(PG_FUNCTION_ARGS)
15101522
bool*recheck= (bool*)PG_GETARG_POINTER(4);
15111523
float8distance;
15121524

1513-
distance=gist_bbox_distance(entry,query,strategy,recheck);
1525+
distance=gist_bbox_distance(entry,query,strategy);
1526+
*recheck= true;
15141527

15151528
PG_RETURN_FLOAT8(distance);
15161529
}
@@ -1526,7 +1539,8 @@ gist_poly_distance(PG_FUNCTION_ARGS)
15261539
bool*recheck= (bool*)PG_GETARG_POINTER(4);
15271540
float8distance;
15281541

1529-
distance=gist_bbox_distance(entry,query,strategy,recheck);
1542+
distance=gist_bbox_distance(entry,query,strategy);
1543+
*recheck= true;
15301544

15311545
PG_RETURN_FLOAT8(distance);
15321546
}

‎src/include/catalog/pg_amop.dat

Lines changed: 3 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1081,6 +1081,9 @@
10811081
amopstrategy => '13', amopopr => '~(box,box)', amopmethod => 'gist' },
10821082
{ amopfamily => 'gist/box_ops', amoplefttype => 'box', amoprighttype => 'box',
10831083
amopstrategy => '14', amopopr => '@(box,box)', amopmethod => 'gist' },
1084+
{ amopfamily => 'gist/box_ops', amoplefttype => 'box', amoprighttype => 'point',
1085+
amopstrategy => '15', amoppurpose => 'o', amopopr => '<->(box,point)',
1086+
amopmethod => 'gist', amopsortfamily => 'btree/float_ops' },
10841087

10851088
# gist point_ops
10861089
{ amopfamily => 'gist/point_ops', amoplefttype => 'point',

‎src/include/catalog/pg_amproc.dat

Lines changed: 2 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -419,6 +419,8 @@
419419
amprocrighttype => 'box', amprocnum => '6', amproc => 'gist_box_picksplit' },
420420
{ amprocfamily => 'gist/box_ops', amproclefttype => 'box',
421421
amprocrighttype => 'box', amprocnum => '7', amproc => 'gist_box_same' },
422+
{ amprocfamily => 'gist/box_ops', amproclefttype => 'box',
423+
amprocrighttype => 'box', amprocnum => '8', amproc => 'gist_box_distance' },
422424
{ amprocfamily => 'gist/poly_ops', amproclefttype => 'polygon',
423425
amprocrighttype => 'polygon', amprocnum => '1',
424426
amproc => 'gist_poly_consistent' },

‎src/include/catalog/pg_proc.dat

Lines changed: 4 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -7844,6 +7844,10 @@
78447844
{ oid => '2584', descr => 'GiST support',
78457845
proname => 'gist_box_same', prorettype => 'internal',
78467846
proargtypes => 'box box internal', prosrc => 'gist_box_same' },
7847+
{ oid => '3998', descr => 'GiST support',
7848+
proname => 'gist_box_distance', prorettype => 'float8',
7849+
proargtypes => 'internal box int2 oid internal',
7850+
prosrc => 'gist_box_distance' },
78477851
{ oid => '2585', descr => 'GiST support',
78487852
proname => 'gist_poly_consistent', prorettype => 'bool',
78497853
proargtypes => 'internal polygon int2 oid internal',

‎src/test/regress/expected/gist.out

Lines changed: 76 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -203,6 +203,82 @@ select b from gist_tbl where b <@ box(point(5,5), point(6,6));
203203
(6,6),(6,6)
204204
(21 rows)
205205

206+
-- Also test an index-only knn-search
207+
explain (costs off)
208+
select b from gist_tbl where b <@ box(point(5,5), point(6,6))
209+
order by b <-> point(5.2, 5.91);
210+
QUERY PLAN
211+
------------------------------------------------------
212+
Index Only Scan using gist_tbl_box_index on gist_tbl
213+
Index Cond: (b <@ '(6,6),(5,5)'::box)
214+
Order By: (b <-> '(5.2,5.91)'::point)
215+
(3 rows)
216+
217+
select b from gist_tbl where b <@ box(point(5,5), point(6,6))
218+
order by b <-> point(5.2, 5.91);
219+
b
220+
-------------------------
221+
(5.55,5.55),(5.55,5.55)
222+
(5.6,5.6),(5.6,5.6)
223+
(5.5,5.5),(5.5,5.5)
224+
(5.65,5.65),(5.65,5.65)
225+
(5.45,5.45),(5.45,5.45)
226+
(5.7,5.7),(5.7,5.7)
227+
(5.4,5.4),(5.4,5.4)
228+
(5.75,5.75),(5.75,5.75)
229+
(5.35,5.35),(5.35,5.35)
230+
(5.8,5.8),(5.8,5.8)
231+
(5.3,5.3),(5.3,5.3)
232+
(5.85,5.85),(5.85,5.85)
233+
(5.25,5.25),(5.25,5.25)
234+
(5.9,5.9),(5.9,5.9)
235+
(5.2,5.2),(5.2,5.2)
236+
(5.95,5.95),(5.95,5.95)
237+
(5.15,5.15),(5.15,5.15)
238+
(6,6),(6,6)
239+
(5.1,5.1),(5.1,5.1)
240+
(5.05,5.05),(5.05,5.05)
241+
(5,5),(5,5)
242+
(21 rows)
243+
244+
-- Check commuted case as well
245+
explain (costs off)
246+
select b from gist_tbl where b <@ box(point(5,5), point(6,6))
247+
order by point(5.2, 5.91) <-> b;
248+
QUERY PLAN
249+
------------------------------------------------------
250+
Index Only Scan using gist_tbl_box_index on gist_tbl
251+
Index Cond: (b <@ '(6,6),(5,5)'::box)
252+
Order By: (b <-> '(5.2,5.91)'::point)
253+
(3 rows)
254+
255+
select b from gist_tbl where b <@ box(point(5,5), point(6,6))
256+
order by point(5.2, 5.91) <-> b;
257+
b
258+
-------------------------
259+
(5.55,5.55),(5.55,5.55)
260+
(5.6,5.6),(5.6,5.6)
261+
(5.5,5.5),(5.5,5.5)
262+
(5.65,5.65),(5.65,5.65)
263+
(5.45,5.45),(5.45,5.45)
264+
(5.7,5.7),(5.7,5.7)
265+
(5.4,5.4),(5.4,5.4)
266+
(5.75,5.75),(5.75,5.75)
267+
(5.35,5.35),(5.35,5.35)
268+
(5.8,5.8),(5.8,5.8)
269+
(5.3,5.3),(5.3,5.3)
270+
(5.85,5.85),(5.85,5.85)
271+
(5.25,5.25),(5.25,5.25)
272+
(5.9,5.9),(5.9,5.9)
273+
(5.2,5.2),(5.2,5.2)
274+
(5.95,5.95),(5.95,5.95)
275+
(5.15,5.15),(5.15,5.15)
276+
(6,6),(6,6)
277+
(5.1,5.1),(5.1,5.1)
278+
(5.05,5.05),(5.05,5.05)
279+
(5,5),(5,5)
280+
(21 rows)
281+
206282
drop index gist_tbl_box_index;
207283
-- Test that an index-only scan is not chosen, when the query involves the
208284
-- circle column (the circle opclass does not support index-only scans).

‎src/test/regress/sql/gist.sql

Lines changed: 16 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -109,6 +109,22 @@ select b from gist_tbl where b <@ box(point(5,5), point(6,6));
109109
-- execute the same
110110
select bfrom gist_tblwhere b<@box(point(5,5),point(6,6));
111111

112+
-- Also test an index-only knn-search
113+
explain (costs off)
114+
select bfrom gist_tblwhere b<@box(point(5,5),point(6,6))
115+
order by b<->point(5.2,5.91);
116+
117+
select bfrom gist_tblwhere b<@box(point(5,5),point(6,6))
118+
order by b<->point(5.2,5.91);
119+
120+
-- Check commuted case as well
121+
explain (costs off)
122+
select bfrom gist_tblwhere b<@box(point(5,5),point(6,6))
123+
order bypoint(5.2,5.91)<-> b;
124+
125+
select bfrom gist_tblwhere b<@box(point(5,5),point(6,6))
126+
order bypoint(5.2,5.91)<-> b;
127+
112128
dropindex gist_tbl_box_index;
113129

114130
-- Test that an index-only scan is not chosen, when the query involves the

0 commit comments

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