Add KNNGIST support to contrib/btree_gist.

This extends GiST's support for nearest-neighbor searches to many of the
standard data types.

Teodor Sigaev

11 files changed, 275 insertions, 0 deletions

diff --git a/contrib/btree_gist/expected/cash.out b/contrib/btree_gist/expected/cash.out
index 2beb8cd92dc..a4100d844e7 100644
--- a/contrib/btree_gist/expected/cash.out
+++ b/contrib/btree_gist/expected/cash.out
@@ -32,6 +32,14 @@ SELECT count(*) FROM moneytmp WHERE a >  '22649.64';
    253
 (1 row)
 
+SELECT a, a <-> '21472.79' FROM moneytmp ORDER BY a <-> '21472.79' LIMIT 3;
+     a      | ?column? 
+------------+----------
+ $21,472.79 |    $0.00
+ $21,469.25 |    $3.54
+ $21,915.01 |  $442.22
+(3 rows)
+
 CREATE INDEX moneyidx ON moneytmp USING gist ( a );
 SET enable_seqscan=off;
 SELECT count(*) FROM moneytmp WHERE a <  '22649.64'::money;
@@ -64,3 +72,20 @@ SELECT count(*) FROM moneytmp WHERE a >  '22649.64'::money;
    253
 (1 row)
 
+EXPLAIN (COSTS OFF)
+SELECT a, a <-> '21472.79' FROM moneytmp ORDER BY a <-> '21472.79' LIMIT 3;
+                  QUERY PLAN                   
+-----------------------------------------------
+ Limit
+   ->  Index Scan using moneyidx on moneytmp
+         Order By: (a <-> '$21,472.79'::money)
+(3 rows)
+
+SELECT a, a <-> '21472.79' FROM moneytmp ORDER BY a <-> '21472.79' LIMIT 3;
+     a      | ?column? 
+------------+----------
+ $21,472.79 |    $0.00
+ $21,469.25 |    $3.54
+ $21,915.01 |  $442.22
+(3 rows)
+
diff --git a/contrib/btree_gist/expected/date.out b/contrib/btree_gist/expected/date.out
index f2082cf391a..4a360bea6d6 100644
--- a/contrib/btree_gist/expected/date.out
+++ b/contrib/btree_gist/expected/date.out
@@ -32,6 +32,14 @@ SELECT count(*) FROM datetmp WHERE a >  '2001-02-13';
    313
 (1 row)
 
+SELECT a, a <-> '2001-02-13' FROM datetmp ORDER BY a <-> '2001-02-13' LIMIT 3;
+     a      | ?column? 
+------------+----------
+ 02-13-2001 |        0
+ 02-11-2001 |        2
+ 03-24-2001 |       39
+(3 rows)
+
 CREATE INDEX dateidx ON datetmp USING gist ( a );
 SET enable_seqscan=off;
 SELECT count(*) FROM datetmp WHERE a <  '2001-02-13'::date;
@@ -64,3 +72,20 @@ SELECT count(*) FROM datetmp WHERE a >  '2001-02-13'::date;
    313
 (1 row)
 
+EXPLAIN (COSTS OFF)
+SELECT a, a <-> '2001-02-13' FROM datetmp ORDER BY a <-> '2001-02-13' LIMIT 3;
+                  QUERY PLAN                  
+----------------------------------------------
+ Limit
+   ->  Index Scan using dateidx on datetmp
+         Order By: (a <-> '02-13-2001'::date)
+(3 rows)
+
+SELECT a, a <-> '2001-02-13' FROM datetmp ORDER BY a <-> '2001-02-13' LIMIT 3;
+     a      | ?column? 
+------------+----------
+ 02-13-2001 |        0
+ 02-11-2001 |        2
+ 03-24-2001 |       39
+(3 rows)
+
diff --git a/contrib/btree_gist/expected/float4.out b/contrib/btree_gist/expected/float4.out
index c7f65c8d7fc..1695f7805a0 100644
--- a/contrib/btree_gist/expected/float4.out
+++ b/contrib/btree_gist/expected/float4.out
@@ -32,6 +32,14 @@ SELECT count(*) FROM float4tmp WHERE a >  -179.0;
    302
 (1 row)
 
+SELECT a, a <-> '-179.0' FROM float4tmp ORDER BY a <-> '-179.0' LIMIT 3;
+    a     | ?column? 
+----------+----------
+     -179 |        0
+ -189.024 |  10.0239
+ -158.177 |  20.8226
+(3 rows)
+
 CREATE INDEX float4idx ON float4tmp USING gist ( a );
 SET enable_seqscan=off;
 SELECT count(*) FROM float4tmp WHERE a <  -179.0::float4;
@@ -64,3 +72,20 @@ SELECT count(*) FROM float4tmp WHERE a >  -179.0::float4;
    302
 (1 row)
 
+EXPLAIN (COSTS OFF)
+SELECT a, a <-> '-179.0' FROM float4tmp ORDER BY a <-> '-179.0' LIMIT 3;
+                  QUERY PLAN                   
+-----------------------------------------------
+ Limit
+   ->  Index Scan using float4idx on float4tmp
+         Order By: (a <-> (-179)::real)
+(3 rows)
+
+SELECT a, a <-> '-179.0' FROM float4tmp ORDER BY a <-> '-179.0' LIMIT 3;
+    a     | ?column? 
+----------+----------
+     -179 |        0
+ -189.024 |  10.0239
+ -158.177 |  20.8226
+(3 rows)
+
diff --git a/contrib/btree_gist/expected/float8.out b/contrib/btree_gist/expected/float8.out
index 4598ac87a2c..7d2228b7973 100644
--- a/contrib/btree_gist/expected/float8.out
+++ b/contrib/btree_gist/expected/float8.out
@@ -32,6 +32,14 @@ SELECT count(*) FROM float8tmp WHERE a >  -1890.0;
    306
 (1 row)
 
+SELECT a, a <-> '-1890.0' FROM float8tmp ORDER BY a <-> '-1890.0' LIMIT 3;
+      a       |  ?column?  
+--------------+------------
+        -1890 |          0
+ -2003.634512 | 113.634512
+  -1769.73634 |  120.26366
+(3 rows)
+
 CREATE INDEX float8idx ON float8tmp USING gist ( a );
 SET enable_seqscan=off;
 SELECT count(*) FROM float8tmp WHERE a <  -1890.0::float8;
@@ -64,3 +72,20 @@ SELECT count(*) FROM float8tmp WHERE a >  -1890.0::float8;
    306
 (1 row)
 
+EXPLAIN (COSTS OFF)
+SELECT a, a <-> '-1890.0' FROM float8tmp ORDER BY a <-> '-1890.0' LIMIT 3;
+                     QUERY PLAN                      
+-----------------------------------------------------
+ Limit
+   ->  Index Scan using float8idx on float8tmp
+         Order By: (a <-> (-1890)::double precision)
+(3 rows)
+
+SELECT a, a <-> '-1890.0' FROM float8tmp ORDER BY a <-> '-1890.0' LIMIT 3;
+      a       |  ?column?  
+--------------+------------
+        -1890 |          0
+ -2003.634512 | 113.634512
+  -1769.73634 |  120.26366
+(3 rows)
+
diff --git a/contrib/btree_gist/expected/int2.out b/contrib/btree_gist/expected/int2.out
index a82819c26a1..b1cc3b14b25 100644
--- a/contrib/btree_gist/expected/int2.out
+++ b/contrib/btree_gist/expected/int2.out
@@ -32,6 +32,14 @@ SELECT count(*) FROM int2tmp WHERE a >  237;
    248
 (1 row)
 
+SELECT a, a <-> '237' FROM int2tmp ORDER BY a <-> '237' LIMIT 3;
+  a  | ?column? 
+-----+----------
+ 237 |        0
+ 232 |        5
+ 228 |        9
+(3 rows)
+
 CREATE INDEX int2idx ON int2tmp USING gist ( a );
 SET enable_seqscan=off;
 SELECT count(*) FROM int2tmp WHERE a <  237::int2;
@@ -64,3 +72,20 @@ SELECT count(*) FROM int2tmp WHERE a >  237::int2;
    248
 (1 row)
 
+EXPLAIN (COSTS OFF)
+SELECT a, a <-> '237' FROM int2tmp ORDER BY a <-> '237' LIMIT 3;
+                QUERY PLAN                 
+-------------------------------------------
+ Limit
+   ->  Index Scan using int2idx on int2tmp
+         Order By: (a <-> 237::smallint)
+(3 rows)
+
+SELECT a, a <-> '237' FROM int2tmp ORDER BY a <-> '237' LIMIT 3;
+  a  | ?column? 
+-----+----------
+ 237 |        0
+ 232 |        5
+ 228 |        9
+(3 rows)
+
diff --git a/contrib/btree_gist/expected/int4.out b/contrib/btree_gist/expected/int4.out
index d4ac0e15135..41bed1f6e34 100644
--- a/contrib/btree_gist/expected/int4.out
+++ b/contrib/btree_gist/expected/int4.out
@@ -32,6 +32,14 @@ SELECT count(*) FROM int4tmp WHERE a >  237;
    248
 (1 row)
 
+SELECT a, a <-> '237' FROM int4tmp ORDER BY a <-> '237' LIMIT 3;
+  a  | ?column? 
+-----+----------
+ 237 |        0
+ 232 |        5
+ 228 |        9
+(3 rows)
+
 CREATE INDEX int4idx ON int4tmp USING gist ( a );
 SET enable_seqscan=off;
 SELECT count(*) FROM int4tmp WHERE a <  237::int4;
@@ -64,3 +72,20 @@ SELECT count(*) FROM int4tmp WHERE a >  237::int4;
    248
 (1 row)
 
+EXPLAIN (COSTS OFF)
+SELECT a, a <-> '237' FROM int4tmp ORDER BY a <-> '237' LIMIT 3;
+                QUERY PLAN                 
+-------------------------------------------
+ Limit
+   ->  Index Scan using int4idx on int4tmp
+         Order By: (a <-> 237)
+(3 rows)
+
+SELECT a, a <-> '237' FROM int4tmp ORDER BY a <-> '237' LIMIT 3;
+  a  | ?column? 
+-----+----------
+ 237 |        0
+ 232 |        5
+ 228 |        9
+(3 rows)
+
diff --git a/contrib/btree_gist/expected/int8.out b/contrib/btree_gist/expected/int8.out
index 1da484525b5..ff0af4a5fb8 100644
--- a/contrib/btree_gist/expected/int8.out
+++ b/contrib/btree_gist/expected/int8.out
@@ -32,6 +32,14 @@ SELECT count(*) FROM int8tmp WHERE a >  464571291354841;
    270
 (1 row)
 
+SELECT a, a <-> '464571291354841' FROM int8tmp ORDER BY a <-> '464571291354841' LIMIT 3;
+        a        |    ?column?    
+-----------------+----------------
+ 464571291354841 |              0
+ 457257666629329 |  7313624725512
+ 478227196042750 | 13655904687909
+(3 rows)
+
 CREATE INDEX int8idx ON int8tmp USING gist ( a );
 SET enable_seqscan=off;
 SELECT count(*) FROM int8tmp WHERE a <  464571291354841::int8;
@@ -64,3 +72,20 @@ SELECT count(*) FROM int8tmp WHERE a >  464571291354841::int8;
    270
 (1 row)
 
+EXPLAIN (COSTS OFF)
+SELECT a, a <-> '464571291354841' FROM int8tmp ORDER BY a <-> '464571291354841' LIMIT 3;
+                    QUERY PLAN                     
+---------------------------------------------------
+ Limit
+   ->  Index Scan using int8idx on int8tmp
+         Order By: (a <-> 464571291354841::bigint)
+(3 rows)
+
+SELECT a, a <-> '464571291354841' FROM int8tmp ORDER BY a <-> '464571291354841' LIMIT 3;
+        a        |    ?column?    
+-----------------+----------------
+ 464571291354841 |              0
+ 457257666629329 |  7313624725512
+ 478227196042750 | 13655904687909
+(3 rows)
+
diff --git a/contrib/btree_gist/expected/interval.out b/contrib/btree_gist/expected/interval.out
index 4c78e21346c..6955251a045 100644
--- a/contrib/btree_gist/expected/interval.out
+++ b/contrib/btree_gist/expected/interval.out
@@ -32,6 +32,14 @@ SELECT count(*) FROM intervaltmp WHERE a >  '199 days 21:21:23';
    270
 (1 row)
 
+SELECT a, a <-> '199 days 21:21:23' FROM intervaltmp ORDER BY a <-> '199 days 21:21:23' LIMIT 3;
+                  a                  |               ?column?               
+-------------------------------------+--------------------------------------
+ @ 199 days 21 hours 21 mins 23 secs | @ 0
+ @ 183 days 6 hours 52 mins 48 secs  | @ 16 days 14 hours 28 mins 35 secs
+ @ 220 days 19 hours 5 mins 42 secs  | @ 21 days -2 hours -15 mins -41 secs
+(3 rows)
+
 CREATE INDEX intervalidx ON intervaltmp USING gist ( a );
 SET enable_seqscan=off;
 SELECT count(*) FROM intervaltmp WHERE a <  '199 days 21:21:23'::interval;
@@ -64,3 +72,20 @@ SELECT count(*) FROM intervaltmp WHERE a >  '199 days 21:21:23'::interval;
    270
 (1 row)
 
+EXPLAIN (COSTS OFF)
+SELECT a, a <-> '199 days 21:21:23' FROM intervaltmp ORDER BY a <-> '199 days 21:21:23' LIMIT 3;
+                                QUERY PLAN                                 
+---------------------------------------------------------------------------
+ Limit
+   ->  Index Scan using intervalidx on intervaltmp
+         Order By: (a <-> '@ 199 days 21 hours 21 mins 23 secs'::interval)
+(3 rows)
+
+SELECT a, a <-> '199 days 21:21:23' FROM intervaltmp ORDER BY a <-> '199 days 21:21:23' LIMIT 3;
+                  a                  |               ?column?               
+-------------------------------------+--------------------------------------
+ @ 199 days 21 hours 21 mins 23 secs | @ 0
+ @ 183 days 6 hours 52 mins 48 secs  | @ 16 days 14 hours 28 mins 35 secs
+ @ 220 days 19 hours 5 mins 42 secs  | @ 21 days -2 hours -15 mins -41 secs
+(3 rows)
+
diff --git a/contrib/btree_gist/expected/time.out b/contrib/btree_gist/expected/time.out
index 015a67f6a17..1b9da4e192a 100644
--- a/contrib/btree_gist/expected/time.out
+++ b/contrib/btree_gist/expected/time.out
@@ -32,6 +32,14 @@ SELECT count(*) FROM timetmp WHERE a >  '10:57:11';
    292
 (1 row)
 
+SELECT a, a <-> '10:57:11' FROM timetmp ORDER BY a <-> '10:57:11' LIMIT 3;
+    a     |    ?column?     
+----------+-----------------
+ 10:57:11 | @ 0
+ 10:57:10 | @ 1 sec
+ 10:55:32 | @ 1 min 39 secs
+(3 rows)
+
 CREATE INDEX timeidx ON timetmp USING gist ( a );
 SET enable_seqscan=off;
 SELECT count(*) FROM timetmp WHERE a <  '10:57:11'::time;
@@ -64,3 +72,20 @@ SELECT count(*) FROM timetmp WHERE a >  '10:57:11'::time;
    292
 (1 row)
 
+EXPLAIN (COSTS OFF)
+SELECT a, a <-> '10:57:11' FROM timetmp ORDER BY a <-> '10:57:11' LIMIT 3;
+                          QUERY PLAN                          
+--------------------------------------------------------------
+ Limit
+   ->  Index Scan using timeidx on timetmp
+         Order By: (a <-> '10:57:11'::time without time zone)
+(3 rows)
+
+SELECT a, a <-> '10:57:11' FROM timetmp ORDER BY a <-> '10:57:11' LIMIT 3;
+    a     |    ?column?     
+----------+-----------------
+ 10:57:11 | @ 0
+ 10:57:10 | @ 1 sec
+ 10:55:32 | @ 1 min 39 secs
+(3 rows)
+
diff --git a/contrib/btree_gist/expected/timestamp.out b/contrib/btree_gist/expected/timestamp.out
index dc53895ca21..cc3624f0843 100644
--- a/contrib/btree_gist/expected/timestamp.out
+++ b/contrib/btree_gist/expected/timestamp.out
@@ -32,6 +32,14 @@ SELECT count(*) FROM timestamptmp WHERE a >  '2004-10-26 08:55:08';
    289
 (1 row)
 
+SELECT a, a <-> '2004-10-26 08:55:08' FROM timestamptmp ORDER BY a <-> '2004-10-26 08:55:08' LIMIT 3;
+            a             |              ?column?              
+--------------------------+------------------------------------
+ Tue Oct 26 08:55:08 2004 | @ 0
+ Sun Oct 31 06:35:03 2004 | @ 4 days 21 hours 39 mins 55 secs
+ Mon Nov 29 20:12:43 2004 | @ 34 days 11 hours 17 mins 35 secs
+(3 rows)
+
 CREATE INDEX timestampidx ON timestamptmp USING gist ( a );
 SET enable_seqscan=off;
 SELECT count(*) FROM timestamptmp WHERE a <  '2004-10-26 08:55:08'::timestamp;
@@ -64,3 +72,20 @@ SELECT count(*) FROM timestamptmp WHERE a >  '2004-10-26 08:55:08'::timestamp;
    289
 (1 row)
 
+EXPLAIN (COSTS OFF)
+SELECT a, a <-> '2004-10-26 08:55:08' FROM timestamptmp ORDER BY a <-> '2004-10-26 08:55:08' LIMIT 3;
+                                    QUERY PLAN                                     
+-----------------------------------------------------------------------------------
+ Limit
+   ->  Index Scan using timestampidx on timestamptmp
+         Order By: (a <-> 'Tue Oct 26 08:55:08 2004'::timestamp without time zone)
+(3 rows)
+
+SELECT a, a <-> '2004-10-26 08:55:08' FROM timestamptmp ORDER BY a <-> '2004-10-26 08:55:08' LIMIT 3;
+            a             |              ?column?              
+--------------------------+------------------------------------
+ Tue Oct 26 08:55:08 2004 | @ 0
+ Sun Oct 31 06:35:03 2004 | @ 4 days 21 hours 39 mins 55 secs
+ Mon Nov 29 20:12:43 2004 | @ 34 days 11 hours 17 mins 35 secs
+(3 rows)
+
diff --git a/contrib/btree_gist/expected/timestamptz.out b/contrib/btree_gist/expected/timestamptz.out
index 391705f0dbe..88d2404c44b 100644
--- a/contrib/btree_gist/expected/timestamptz.out
+++ b/contrib/btree_gist/expected/timestamptz.out
@@ -92,6 +92,14 @@ SELECT count(*) FROM timestamptztmp WHERE a >  '2018-12-18 10:59:54 GMT+4';
    157
 (1 row)
 
+SELECT a, a <-> '2018-12-18 10:59:54 GMT+2' FROM timestamptztmp ORDER BY a <-> '2018-12-18 10:59:54 GMT+2' LIMIT 3;
+              a               |             ?column?              
+------------------------------+-----------------------------------
+ Tue Dec 18 05:59:54 2018 PST | @ 1 hour
+ Thu Jan 10 03:01:34 2019 PST | @ 22 days 22 hours 1 min 40 secs
+ Thu Jan 24 12:28:12 2019 PST | @ 37 days 7 hours 28 mins 18 secs
+(3 rows)
+
 CREATE INDEX timestamptzidx ON timestamptztmp USING gist ( a );
 SET enable_seqscan=off;
 SELECT count(*) FROM timestamptztmp WHERE a <  '2018-12-18 10:59:54 GMT+3'::timestamptz;
@@ -184,3 +192,20 @@ SELECT count(*) FROM timestamptztmp WHERE a >  '2018-12-18 10:59:54 GMT+4'::time
    157
 (1 row)
 
+EXPLAIN (COSTS OFF)
+SELECT a, a <-> '2018-12-18 10:59:54 GMT+2' FROM timestamptztmp ORDER BY a <-> '2018-12-18 10:59:54 GMT+2' LIMIT 3;
+                                     QUERY PLAN                                     
+------------------------------------------------------------------------------------
+ Limit
+   ->  Index Scan using timestamptzidx on timestamptztmp
+         Order By: (a <-> 'Tue Dec 18 04:59:54 2018 PST'::timestamp with time zone)
+(3 rows)
+
+SELECT a, a <-> '2018-12-18 10:59:54 GMT+2' FROM timestamptztmp ORDER BY a <-> '2018-12-18 10:59:54 GMT+2' LIMIT 3;
+              a               |             ?column?              
+------------------------------+-----------------------------------
+ Tue Dec 18 05:59:54 2018 PST | @ 1 hour
+ Thu Jan 10 03:01:34 2019 PST | @ 22 days 22 hours 1 min 40 secs
+ Thu Jan 24 12:28:12 2019 PST | @ 37 days 7 hours 28 mins 18 secs
+(3 rows)
+