Skip to content

Commit c085e1c

Browse files
akorotkovakorotkov
committed
Add support for <-> (box, point) operator to GiST box_ops
Index-based calculation of this operator is exact. So, signature of gist_bbox_distance() function is changes so that caller is responsible for setting *recheck flag. Discussion: https://postgr.es/m/f71ba19d-d989-63b6-f04a-abf02ad9345d%40postgrespro.ru Author: Nikita Glukhov Reviewed-by: Tom Lane, Alexander Korotkov
1 parent 6254c55 commit c085e1c

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
static float8
1478-
gist_bbox_distance(GISTENTRY *entry, Datum query,
1479-
StrategyNumber strategy, bool *recheck)
1468+
gist_bbox_distance(GISTENTRY *entry, Datum query, StrategyNumber strategy)
14801469
{
14811470
float8 distance;
14821471
StrategyNumber strategyGroup = strategy / GeoStrategyNumberOffset;
14831472

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

1488+
Datum
1489+
gist_box_distance(PG_FUNCTION_ARGS)
1490+
{
1491+
GISTENTRY *entry = (GISTENTRY *) PG_GETARG_POINTER(0);
1492+
Datum query = PG_GETARG_DATUM(1);
1493+
StrategyNumber strategy = (StrategyNumber) PG_GETARG_UINT16(2);
1494+
1495+
/* Oid subtype = PG_GETARG_OID(3); */
1496+
/* bool *recheck = (bool *) PG_GETARG_POINTER(4); */
1497+
float8 distance;
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
float8 distance;
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
float8 distance;
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 b from gist_tbl where b <@ box(point(5,5), point(6,6));
111111

112+
-- Also test an index-only knn-search
113+
explain (costs off)
114+
select b from gist_tbl where b <@ box(point(5,5), point(6,6))
115+
order by b <-> point(5.2, 5.91);
116+
117+
select b from gist_tbl where 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 b from gist_tbl where b <@ box(point(5,5), point(6,6))
123+
order by point(5.2, 5.91) <-> b;
124+
125+
select b from gist_tbl where b <@ box(point(5,5), point(6,6))
126+
order by point(5.2, 5.91) <-> b;
127+
112128
drop index 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)