From 48190c2289ca92156fbbab9fa6b86d048fda41c1 Mon Sep 17 00:00:00 2001 From: Nikita Glukhov Date: Thu, 7 Mar 2019 23:49:47 +0300 Subject: [PATCH 3/3] Add operator <->(box, point) to SP-GiST box_ops --- doc/src/sgml/spgist.sgml | 1 + src/include/catalog/pg_amop.dat | 4 ++ src/test/regress/expected/box.out | 82 +++++++++++++++++++++++++----- src/test/regress/expected/sanity_check.out | 2 + src/test/regress/sql/box.sql | 70 ++++++++++++++++++++----- 5 files changed, 135 insertions(+), 24 deletions(-) diff --git a/doc/src/sgml/spgist.sgml b/doc/src/sgml/spgist.sgml index a816856..81ddf5d 100644 --- a/doc/src/sgml/spgist.sgml +++ b/doc/src/sgml/spgist.sgml @@ -139,6 +139,7 @@ |&> + <-> diff --git a/src/include/catalog/pg_amop.dat b/src/include/catalog/pg_amop.dat index ebc38ae..232557e 100644 --- a/src/include/catalog/pg_amop.dat +++ b/src/include/catalog/pg_amop.dat @@ -1546,6 +1546,10 @@ amopstrategy => '11', amopopr => '|>>(box,box)', amopmethod => 'spgist' }, { amopfamily => 'spgist/box_ops', amoplefttype => 'box', amoprighttype => 'box', amopstrategy => '12', amopopr => '|&>(box,box)', amopmethod => 'spgist' }, +{ amopfamily => 'spgist/box_ops', amoplefttype => 'box', + amoprighttype => 'point', amopstrategy => '15', amoppurpose => 'o', + amopopr => '<->(box,point)', amopmethod => 'spgist', + amopsortfamily => 'btree/float_ops' }, # SP-GiST poly_ops (supports polygons) { amopfamily => 'spgist/poly_ops', amoplefttype => 'polygon', diff --git a/src/test/regress/expected/box.out b/src/test/regress/expected/box.out index 998b522..4d0f169 100644 --- a/src/test/regress/expected/box.out +++ b/src/test/regress/expected/box.out @@ -480,23 +480,33 @@ DROP INDEX box_spgist; -- -- Test the SP-GiST index on the larger volume of data -- -CREATE TABLE quad_box_tbl (b box); +CREATE TABLE quad_box_tbl (id int, b box); INSERT INTO quad_box_tbl - SELECT box(point(x * 10, y * 10), point(x * 10 + 5, y * 10 + 5)) - FROM generate_series(1, 100) x, - generate_series(1, 100) y; + SELECT (x - 1) * 100 + y, box(point(x * 10, y * 10), point(x * 10 + 5, y * 10 + 5)) + FROM generate_series(1, 100) x, + generate_series(1, 100) y; -- insert repeating data to test allTheSame INSERT INTO quad_box_tbl - SELECT '((200, 300),(210, 310))' - FROM generate_series(1, 1000); + SELECT i, '((200, 300),(210, 310))' + FROM generate_series(10001, 11000) AS i; INSERT INTO quad_box_tbl - VALUES - (NULL), - (NULL), - ('((-infinity,-infinity),(infinity,infinity))'), - ('((-infinity,100),(-infinity,500))'), - ('((-infinity,-infinity),(700,infinity))'); +VALUES + (11001, NULL), + (11002, NULL), + (11003, '((-infinity,-infinity),(infinity,infinity))'), + (11004, '((-infinity,100),(-infinity,500))'), + (11005, '((-infinity,-infinity),(700,infinity))'); CREATE INDEX quad_box_tbl_idx ON quad_box_tbl USING spgist(b); +-- get reference results for ORDER BY distance from seq scan +SET enable_seqscan = ON; +SET enable_indexscan = OFF; +SET enable_bitmapscan = OFF; +CREATE TABLE quad_box_tbl_ord_seq1 AS +SELECT rank() OVER (ORDER BY b <-> point '123,456') n, b <-> point '123,456' dist, id +FROM quad_box_tbl; +CREATE TABLE quad_box_tbl_ord_seq2 AS +SELECT rank() OVER (ORDER BY b <-> point '123,456') n, b <-> point '123,456' dist, id +FROM quad_box_tbl WHERE b <@ box '((200,300),(500,600))'; SET enable_seqscan = OFF; SET enable_indexscan = ON; SET enable_bitmapscan = ON; @@ -578,6 +588,54 @@ SELECT count(*) FROM quad_box_tbl WHERE b ~= box '((200,300),(205,305))'; 1 (1 row) +-- test ORDER BY distance +SET enable_indexscan = ON; +SET enable_bitmapscan = OFF; +EXPLAIN (COSTS OFF) +SELECT rank() OVER (ORDER BY b <-> point '123,456') n, b <-> point '123,456' dist, id +FROM quad_box_tbl; + QUERY PLAN +--------------------------------------------------------- + WindowAgg + -> Index Scan using quad_box_tbl_idx on quad_box_tbl + Order By: (b <-> '(123,456)'::point) +(3 rows) + +CREATE TEMP TABLE quad_box_tbl_ord_idx1 AS +SELECT rank() OVER (ORDER BY b <-> point '123,456') n, b <-> point '123,456' dist, id +FROM quad_box_tbl; +SELECT * +FROM quad_box_tbl_ord_seq1 seq FULL JOIN quad_box_tbl_ord_idx1 idx + ON seq.n = idx.n AND seq.id = idx.id AND + (seq.dist = idx.dist OR seq.dist IS NULL AND idx.dist IS NULL) +WHERE seq.id IS NULL OR idx.id IS NULL; + n | dist | id | n | dist | id +---+------+----+---+------+---- +(0 rows) + +EXPLAIN (COSTS OFF) +SELECT rank() OVER (ORDER BY b <-> point '123,456') n, b <-> point '123,456' dist, id +FROM quad_box_tbl WHERE b <@ box '((200,300),(500,600))'; + QUERY PLAN +--------------------------------------------------------- + WindowAgg + -> Index Scan using quad_box_tbl_idx on quad_box_tbl + Index Cond: (b <@ '(500,600),(200,300)'::box) + Order By: (b <-> '(123,456)'::point) +(4 rows) + +CREATE TEMP TABLE quad_box_tbl_ord_idx2 AS +SELECT rank() OVER (ORDER BY b <-> point '123,456') n, b <-> point '123,456' dist, id +FROM quad_box_tbl WHERE b <@ box '((200,300),(500,600))'; +SELECT * +FROM quad_box_tbl_ord_seq2 seq FULL JOIN quad_box_tbl_ord_idx2 idx + ON seq.n = idx.n AND seq.id = idx.id AND + (seq.dist = idx.dist OR seq.dist IS NULL AND idx.dist IS NULL) +WHERE seq.id IS NULL OR idx.id IS NULL; + n | dist | id | n | dist | id +---+------+----+---+------+---- +(0 rows) + RESET enable_seqscan; RESET enable_indexscan; RESET enable_bitmapscan; diff --git a/src/test/regress/expected/sanity_check.out b/src/test/regress/expected/sanity_check.out index 8ff0da1..d6e75ff 100644 --- a/src/test/regress/expected/sanity_check.out +++ b/src/test/regress/expected/sanity_check.out @@ -165,6 +165,8 @@ pg_user_mapping|t point_tbl|t polygon_tbl|t quad_box_tbl|t +quad_box_tbl_ord_seq1|f +quad_box_tbl_ord_seq2|f quad_point_tbl|t quad_poly_tbl|t radix_text_tbl|t diff --git a/src/test/regress/sql/box.sql b/src/test/regress/sql/box.sql index 6710fc9..cd3e002 100644 --- a/src/test/regress/sql/box.sql +++ b/src/test/regress/sql/box.sql @@ -192,28 +192,41 @@ DROP INDEX box_spgist; -- -- Test the SP-GiST index on the larger volume of data -- -CREATE TABLE quad_box_tbl (b box); +CREATE TABLE quad_box_tbl (id int, b box); INSERT INTO quad_box_tbl - SELECT box(point(x * 10, y * 10), point(x * 10 + 5, y * 10 + 5)) - FROM generate_series(1, 100) x, - generate_series(1, 100) y; + SELECT (x - 1) * 100 + y, box(point(x * 10, y * 10), point(x * 10 + 5, y * 10 + 5)) + FROM generate_series(1, 100) x, + generate_series(1, 100) y; -- insert repeating data to test allTheSame INSERT INTO quad_box_tbl - SELECT '((200, 300),(210, 310))' - FROM generate_series(1, 1000); + SELECT i, '((200, 300),(210, 310))' + FROM generate_series(10001, 11000) AS i; INSERT INTO quad_box_tbl - VALUES - (NULL), - (NULL), - ('((-infinity,-infinity),(infinity,infinity))'), - ('((-infinity,100),(-infinity,500))'), - ('((-infinity,-infinity),(700,infinity))'); +VALUES + (11001, NULL), + (11002, NULL), + (11003, '((-infinity,-infinity),(infinity,infinity))'), + (11004, '((-infinity,100),(-infinity,500))'), + (11005, '((-infinity,-infinity),(700,infinity))'); CREATE INDEX quad_box_tbl_idx ON quad_box_tbl USING spgist(b); +-- get reference results for ORDER BY distance from seq scan +SET enable_seqscan = ON; +SET enable_indexscan = OFF; +SET enable_bitmapscan = OFF; + +CREATE TABLE quad_box_tbl_ord_seq1 AS +SELECT rank() OVER (ORDER BY b <-> point '123,456') n, b <-> point '123,456' dist, id +FROM quad_box_tbl; + +CREATE TABLE quad_box_tbl_ord_seq2 AS +SELECT rank() OVER (ORDER BY b <-> point '123,456') n, b <-> point '123,456' dist, id +FROM quad_box_tbl WHERE b <@ box '((200,300),(500,600))'; + SET enable_seqscan = OFF; SET enable_indexscan = ON; SET enable_bitmapscan = ON; @@ -232,6 +245,39 @@ SELECT count(*) FROM quad_box_tbl WHERE b @> box '((201,301),(202,303))'; SELECT count(*) FROM quad_box_tbl WHERE b <@ box '((100,200),(300,500))'; SELECT count(*) FROM quad_box_tbl WHERE b ~= box '((200,300),(205,305))'; +-- test ORDER BY distance +SET enable_indexscan = ON; +SET enable_bitmapscan = OFF; + +EXPLAIN (COSTS OFF) +SELECT rank() OVER (ORDER BY b <-> point '123,456') n, b <-> point '123,456' dist, id +FROM quad_box_tbl; + +CREATE TEMP TABLE quad_box_tbl_ord_idx1 AS +SELECT rank() OVER (ORDER BY b <-> point '123,456') n, b <-> point '123,456' dist, id +FROM quad_box_tbl; + +SELECT * +FROM quad_box_tbl_ord_seq1 seq FULL JOIN quad_box_tbl_ord_idx1 idx + ON seq.n = idx.n AND seq.id = idx.id AND + (seq.dist = idx.dist OR seq.dist IS NULL AND idx.dist IS NULL) +WHERE seq.id IS NULL OR idx.id IS NULL; + + +EXPLAIN (COSTS OFF) +SELECT rank() OVER (ORDER BY b <-> point '123,456') n, b <-> point '123,456' dist, id +FROM quad_box_tbl WHERE b <@ box '((200,300),(500,600))'; + +CREATE TEMP TABLE quad_box_tbl_ord_idx2 AS +SELECT rank() OVER (ORDER BY b <-> point '123,456') n, b <-> point '123,456' dist, id +FROM quad_box_tbl WHERE b <@ box '((200,300),(500,600))'; + +SELECT * +FROM quad_box_tbl_ord_seq2 seq FULL JOIN quad_box_tbl_ord_idx2 idx + ON seq.n = idx.n AND seq.id = idx.id AND + (seq.dist = idx.dist OR seq.dist IS NULL AND idx.dist IS NULL) +WHERE seq.id IS NULL OR idx.id IS NULL; + RESET enable_seqscan; RESET enable_indexscan; RESET enable_bitmapscan; -- 2.7.4