Subject: [PATCH 2/3] Add operator <->(box, point) to GiST box_ops

From: Nikita Glukhov <[email-masked]>
Date: Thu, 7 Mar 2019 23:49:31 +0300

---
 doc/src/sgml/gist.sgml             |  1 +
 src/backend/access/gist/gistproc.c | 53 ++++++++++++++++++--------
 src/include/catalog/pg_amop.dat    |  3 ++
 src/include/catalog/pg_amproc.dat  |  2 +
 src/include/catalog/pg_proc.dat    |  4 ++
 src/test/regress/expected/gist.out | 76 ++++++++++++++++++++++++++++++++++++++
 src/test/regress/sql/gist.sql      | 16 ++++++++
 7 files changed, 140 insertions(+), 15 deletions(-)

diff --git a/doc/src/sgml/gist.sgml b/doc/src/sgml/gist.sgml
index 44a3b2c..e903319 100644
--- a/doc/src/sgml/gist.sgml
+++ b/doc/src/sgml/gist.sgml
@@ -83,6 +83,7 @@
        <literal>~=</literal>
       </entry>
       <entry>
+       <literal>&lt;-&gt;</literal>
       </entry>
      </row>
      <row>
diff --git a/src/backend/access/gist/gistproc.c b/src/backend/access/gist/gistproc.c
index c9da230..89643b4 100644
--- a/src/backend/access/gist/gistproc.c
+++ b/src/backend/access/gist/gistproc.c
@@ -1464,26 +1464,13 @@ gist_point_distance(PG_FUNCTION_ARGS)
 	PG_RETURN_FLOAT8(distance);
 }
 
-/*
- * The inexact GiST distance method for geometric types that store bounding
- * boxes.
- *
- * Compute lossy distance from point to index entries.  The result is inexact
- * because index entries are bounding boxes, not the exact shapes of the
- * indexed geometric types.  We use distance from point to MBR of index entry.
- * This is a lower bound estimate of distance from point to indexed geometric
- * type.
- */
 static float8
-gist_bbox_distance(GISTENTRY *entry, Datum query,
-				   StrategyNumber strategy, bool *recheck)
+gist_box_distance_helper(GISTENTRY *entry, Datum query,
+						 StrategyNumber strategy)
 {
 	float8		distance;
 	StrategyNumber strategyGroup = strategy / GeoStrategyNumberOffset;
 
-	/* Bounding box distance is always inexact. */
-	*recheck = true;
-
 	switch (strategyGroup)
 	{
 		case PointStrategyNumberGroup:
@@ -1500,6 +1487,42 @@ gist_bbox_distance(GISTENTRY *entry, Datum query,
 }
 
 Datum
+gist_box_distance(PG_FUNCTION_ARGS)
+{
+	GISTENTRY  *entry = (GISTENTRY *) PG_GETARG_POINTER(0);
+	Datum		query = PG_GETARG_DATUM(1);
+	StrategyNumber strategy = (StrategyNumber) PG_GETARG_UINT16(2);
+
+	/* Oid subtype = PG_GETARG_OID(3); */
+	/* bool	   *recheck = (bool *) PG_GETARG_POINTER(4); */
+	float8		distance;
+
+	distance = gist_box_distance_helper(entry, query, strategy);
+
+	PG_RETURN_FLOAT8(distance);
+}
+
+/*
+ * The inexact GiST distance method for geometric types that store bounding
+ * boxes.
+ *
+ * Compute lossy distance from point to index entries.  The result is inexact
+ * because index entries are bounding boxes, not the exact shapes of the
+ * indexed geometric types.  We use distance from point to MBR of index entry.
+ * This is a lower bound estimate of distance from point to indexed geometric
+ * type.
+ */
+static float8
+gist_bbox_distance(GISTENTRY *entry, Datum query,
+				   StrategyNumber strategy, bool *recheck)
+{
+	/* Bounding box distance is always inexact. */
+	*recheck = true;
+
+	return gist_box_distance_helper(entry, query, strategy);
+}
+
+Datum
 gist_circle_distance(PG_FUNCTION_ARGS)
 {
 	GISTENTRY  *entry = (GISTENTRY *) PG_GETARG_POINTER(0);
diff --git a/src/include/catalog/pg_amop.dat b/src/include/catalog/pg_amop.dat
index 0ab95d8..8e74f86 100644
--- a/src/include/catalog/pg_amop.dat
+++ b/src/include/catalog/pg_amop.dat
@@ -1081,6 +1081,9 @@
   amopstrategy => '13', amopopr => '~(box,box)', amopmethod => 'gist' },
 { amopfamily => 'gist/box_ops', amoplefttype => 'box', amoprighttype => 'box',
   amopstrategy => '14', amopopr => '@(box,box)', amopmethod => 'gist' },
+{ amopfamily => 'gist/box_ops', amoplefttype => 'box', amoprighttype => 'point',
+  amopstrategy => '15', amoppurpose => 'o', amopopr => '<->(box,point)',
+  amopmethod => 'gist', amopsortfamily => 'btree/float_ops' },
 
 # gist point_ops
 { amopfamily => 'gist/point_ops', amoplefttype => 'point',
diff --git a/src/include/catalog/pg_amproc.dat b/src/include/catalog/pg_amproc.dat
index 020b741..5567b7e 100644
--- a/src/include/catalog/pg_amproc.dat
+++ b/src/include/catalog/pg_amproc.dat
@@ -419,6 +419,8 @@
   amprocrighttype => 'box', amprocnum => '6', amproc => 'gist_box_picksplit' },
 { amprocfamily => 'gist/box_ops', amproclefttype => 'box',
   amprocrighttype => 'box', amprocnum => '7', amproc => 'gist_box_same' },
+{ amprocfamily => 'gist/box_ops', amproclefttype => 'box',
+  amprocrighttype => 'box', amprocnum => '8', amproc => 'gist_box_distance' },
 { amprocfamily => 'gist/poly_ops', amproclefttype => 'polygon',
   amprocrighttype => 'polygon', amprocnum => '1',
   amproc => 'gist_poly_consistent' },
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index 3800150..d6477a3 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -7760,6 +7760,10 @@
 { oid => '2584', descr => 'GiST support',
   proname => 'gist_box_same', prorettype => 'internal',
   proargtypes => 'box box internal', prosrc => 'gist_box_same' },
+{ oid => '3998', descr => 'GiST support',
+  proname => 'gist_box_distance', prorettype => 'float8',
+  proargtypes => 'internal box int2 oid internal',
+  prosrc => 'gist_box_distance' },
 { oid => '2585', descr => 'GiST support',
   proname => 'gist_poly_consistent', prorettype => 'bool',
   proargtypes => 'internal polygon int2 oid internal',
diff --git a/src/test/regress/expected/gist.out b/src/test/regress/expected/gist.out
index f5a2993..e8c2685 100644
--- a/src/test/regress/expected/gist.out
+++ b/src/test/regress/expected/gist.out
@@ -205,6 +205,82 @@ select b from gist_tbl where b <@ box(point(5,5), point(6,6));
  (6,6),(6,6)
 (21 rows)
 
+-- Also test an index-only knn-search
+explain (costs off)
+select b from gist_tbl where b <@ box(point(5,5), point(6,6))
+order by b <-> point(5.2, 5.91);
+                      QUERY PLAN                      
+------------------------------------------------------
+ Index Only Scan using gist_tbl_box_index on gist_tbl
+   Index Cond: (b <@ '(6,6),(5,5)'::box)
+   Order By: (b <-> '(5.2,5.91)'::point)
+(3 rows)
+
+select b from gist_tbl where b <@ box(point(5,5), point(6,6))
+order by b <-> point(5.2, 5.91);
+            b            
+-------------------------
+ (5.55,5.55),(5.55,5.55)
+ (5.6,5.6),(5.6,5.6)
+ (5.5,5.5),(5.5,5.5)
+ (5.65,5.65),(5.65,5.65)
+ (5.45,5.45),(5.45,5.45)
+ (5.7,5.7),(5.7,5.7)
+ (5.4,5.4),(5.4,5.4)
+ (5.75,5.75),(5.75,5.75)
+ (5.35,5.35),(5.35,5.35)
+ (5.8,5.8),(5.8,5.8)
+ (5.3,5.3),(5.3,5.3)
+ (5.85,5.85),(5.85,5.85)
+ (5.25,5.25),(5.25,5.25)
+ (5.9,5.9),(5.9,5.9)
+ (5.2,5.2),(5.2,5.2)
+ (5.95,5.95),(5.95,5.95)
+ (5.15,5.15),(5.15,5.15)
+ (6,6),(6,6)
+ (5.1,5.1),(5.1,5.1)
+ (5.05,5.05),(5.05,5.05)
+ (5,5),(5,5)
+(21 rows)
+
+-- Check commuted case as well
+explain (costs off)
+select b from gist_tbl where b <@ box(point(5,5), point(6,6))
+order by point(5.2, 5.91) <-> b;
+                      QUERY PLAN                      
+------------------------------------------------------
+ Index Only Scan using gist_tbl_box_index on gist_tbl
+   Index Cond: (b <@ '(6,6),(5,5)'::box)
+   Order By: (b <-> '(5.2,5.91)'::point)
+(3 rows)
+
+select b from gist_tbl where b <@ box(point(5,5), point(6,6))
+order by point(5.2, 5.91) <-> b;
+            b            
+-------------------------
+ (5.55,5.55),(5.55,5.55)
+ (5.6,5.6),(5.6,5.6)
+ (5.5,5.5),(5.5,5.5)
+ (5.65,5.65),(5.65,5.65)
+ (5.45,5.45),(5.45,5.45)
+ (5.7,5.7),(5.7,5.7)
+ (5.4,5.4),(5.4,5.4)
+ (5.75,5.75),(5.75,5.75)
+ (5.35,5.35),(5.35,5.35)
+ (5.8,5.8),(5.8,5.8)
+ (5.3,5.3),(5.3,5.3)
+ (5.85,5.85),(5.85,5.85)
+ (5.25,5.25),(5.25,5.25)
+ (5.9,5.9),(5.9,5.9)
+ (5.2,5.2),(5.2,5.2)
+ (5.95,5.95),(5.95,5.95)
+ (5.15,5.15),(5.15,5.15)
+ (6,6),(6,6)
+ (5.1,5.1),(5.1,5.1)
+ (5.05,5.05),(5.05,5.05)
+ (5,5),(5,5)
+(21 rows)
+
 drop index gist_tbl_box_index;
 -- Test that an index-only scan is not chosen, when the query involves the
 -- circle column (the circle opclass does not support index-only scans).
diff --git a/src/test/regress/sql/gist.sql b/src/test/regress/sql/gist.sql
index bae722f..406fb83 100644
--- a/src/test/regress/sql/gist.sql
+++ b/src/test/regress/sql/gist.sql
@@ -111,6 +111,22 @@ select b from gist_tbl where b <@ box(point(5,5), point(6,6));
 -- execute the same
 select b from gist_tbl where b <@ box(point(5,5), point(6,6));
 
+-- Also test an index-only knn-search
+explain (costs off)
+select b from gist_tbl where b <@ box(point(5,5), point(6,6))
+order by b <-> point(5.2, 5.91);
+
+select b from gist_tbl where b <@ box(point(5,5), point(6,6))
+order by b <-> point(5.2, 5.91);
+
+-- Check commuted case as well
+explain (costs off)
+select b from gist_tbl where b <@ box(point(5,5), point(6,6))
+order by point(5.2, 5.91) <-> b;
+
+select b from gist_tbl where b <@ box(point(5,5), point(6,6))
+order by point(5.2, 5.91) <-> b;
+
 drop index gist_tbl_box_index;
 
 -- Test that an index-only scan is not chosen, when the query involves the
-- 
2.7.4

--------------B01ADF66983FA504E371CA13
Content-Type: text/x-patch;
 name="0003-Add-box-point-distance-operator-to-SP-GiST-box_ops-v01.patch"
Content-Transfer-Encoding: 7bit
Content-Disposition: attachment;
 filename*0="0003-Add-box-point-distance-operator-to-SP-GiST-box_ops-v01.";
 filename*1="patch"