From d5286aa905c9173b3fc4f911682089149bd3caef Mon Sep 17 00:00:00 2001 From: Teodor Sigaev Date: Tue, 21 Mar 2017 16:23:10 +0300 Subject: [PATCH] Fix support for some operators (&<, &>, $<|, |&>) in box operator class of SP-GiST. Bug exists since initial commit of box opclass for SP-GiST, so backpath to 9.6 Author: Nikita Glukhov with minor editorization of tests by me Reviewed-by: Kyotaro Horiguchi, Anastasia Lubennikova https://commitfest.postgresql.org/13/981/ --- src/backend/utils/adt/geo_spgist.c | 24 ++++- src/test/regress/expected/box.out | 104 +++++++++++++++++++++ src/test/regress/expected/sanity_check.out | 1 + src/test/regress/sql/box.sql | 47 ++++++++++ 4 files changed, 172 insertions(+), 4 deletions(-) diff --git a/src/backend/utils/adt/geo_spgist.c b/src/backend/utils/adt/geo_spgist.c index aacb3409bd..f6334bae14 100644 --- a/src/backend/utils/adt/geo_spgist.c +++ b/src/backend/utils/adt/geo_spgist.c @@ -286,6 +286,14 @@ lower2D(RangeBox *range_box, Range *query) FPlt(range_box->right.low, query->low); } +/* Can any range from range_box not extend to the right side of the query? */ +static bool +overLower2D(RangeBox *range_box, Range *query) +{ + return FPle(range_box->left.low, query->high) && + FPle(range_box->right.low, query->high); +} + /* Can any range from range_box to be higher than this argument? */ static bool higher2D(RangeBox *range_box, Range *query) @@ -294,6 +302,14 @@ higher2D(RangeBox *range_box, Range *query) FPgt(range_box->right.high, query->high); } +/* Can any range from range_box not extend to the left side of the query? */ +static bool +overHigher2D(RangeBox *range_box, Range *query) +{ + return FPge(range_box->left.high, query->low) && + FPge(range_box->right.high, query->low); +} + /* Can any rectangle from rect_box be left of this argument? */ static bool left4D(RectBox *rect_box, RangeBox *query) @@ -305,7 +321,7 @@ left4D(RectBox *rect_box, RangeBox *query) static bool overLeft4D(RectBox *rect_box, RangeBox *query) { - return lower2D(&rect_box->range_box_x, &query->right); + return overLower2D(&rect_box->range_box_x, &query->left); } /* Can any rectangle from rect_box be right of this argument? */ @@ -319,7 +335,7 @@ right4D(RectBox *rect_box, RangeBox *query) static bool overRight4D(RectBox *rect_box, RangeBox *query) { - return higher2D(&rect_box->range_box_x, &query->right); + return overHigher2D(&rect_box->range_box_x, &query->left); } /* Can any rectangle from rect_box be below of this argument? */ @@ -333,7 +349,7 @@ below4D(RectBox *rect_box, RangeBox *query) static bool overBelow4D(RectBox *rect_box, RangeBox *query) { - return lower2D(&rect_box->range_box_y, &query->left); + return overLower2D(&rect_box->range_box_y, &query->right); } /* Can any rectangle from rect_box be above of this argument? */ @@ -347,7 +363,7 @@ above4D(RectBox *rect_box, RangeBox *query) static bool overAbove4D(RectBox *rect_box, RangeBox *query) { - return higher2D(&rect_box->range_box_y, &query->right); + return overHigher2D(&rect_box->range_box_y, &query->right); } /* diff --git a/src/test/regress/expected/box.out b/src/test/regress/expected/box.out index 5f8b9455e8..49af242c8c 100644 --- a/src/test/regress/expected/box.out +++ b/src/test/regress/expected/box.out @@ -455,3 +455,107 @@ EXPLAIN (COSTS OFF) SELECT * FROM box_temp WHERE f1 ~= '(20,20),(40,40)'; RESET enable_seqscan; DROP INDEX box_spgist; +-- +-- Test the SP-GiST index on the larger volume of data +-- +CREATE TABLE quad_box_tbl (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; +-- insert repeating data to test allTheSame +INSERT INTO quad_box_tbl + SELECT '((200, 300),(210, 310))' + FROM generate_series(1, 1000); +INSERT INTO quad_box_tbl + VALUES + (NULL), + (NULL), + ('((-infinity,-infinity),(infinity,infinity))'), + ('((-infinity,100),(-infinity,500))'), + ('((-infinity,-infinity),(700,infinity))'); +CREATE INDEX quad_box_tbl_idx ON quad_box_tbl USING spgist(b); +SET enable_seqscan = OFF; +SET enable_indexscan = ON; +SET enable_bitmapscan = ON; +SELECT count(*) FROM quad_box_tbl WHERE b << box '((100,200),(300,500))'; + count +------- + 901 +(1 row) + +SELECT count(*) FROM quad_box_tbl WHERE b &< box '((100,200),(300,500))'; + count +------- + 3901 +(1 row) + +SELECT count(*) FROM quad_box_tbl WHERE b && box '((100,200),(300,500))'; + count +------- + 1653 +(1 row) + +SELECT count(*) FROM quad_box_tbl WHERE b &> box '((100,200),(300,500))'; + count +------- + 10100 +(1 row) + +SELECT count(*) FROM quad_box_tbl WHERE b >> box '((100,200),(300,500))'; + count +------- + 7000 +(1 row) + +SELECT count(*) FROM quad_box_tbl WHERE b >> box '((100,200),(300,500))'; + count +------- + 7000 +(1 row) + +SELECT count(*) FROM quad_box_tbl WHERE b <<| box '((100,200),(300,500))'; + count +------- + 1900 +(1 row) + +SELECT count(*) FROM quad_box_tbl WHERE b &<| box '((100,200),(300,500))'; + count +------- + 5901 +(1 row) + +SELECT count(*) FROM quad_box_tbl WHERE b |&> box '((100,200),(300,500))'; + count +------- + 9100 +(1 row) + +SELECT count(*) FROM quad_box_tbl WHERE b |>> box '((100,200),(300,500))'; + count +------- + 5000 +(1 row) + +SELECT count(*) FROM quad_box_tbl WHERE b @> box '((201,301),(202,303))'; + count +------- + 1003 +(1 row) + +SELECT count(*) FROM quad_box_tbl WHERE b <@ box '((100,200),(300,500))'; + count +------- + 1600 +(1 row) + +SELECT count(*) FROM quad_box_tbl WHERE b ~= box '((200,300),(205,305))'; + count +------- + 1 +(1 row) + +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 b5eff55e9b..88b4c973a1 100644 --- a/src/test/regress/expected/sanity_check.out +++ b/src/test/regress/expected/sanity_check.out @@ -155,6 +155,7 @@ pg_type|t pg_user_mapping|t point_tbl|t polygon_tbl|t +quad_box_tbl|t quad_point_tbl|t radix_text_tbl|t ramp|f diff --git a/src/test/regress/sql/box.sql b/src/test/regress/sql/box.sql index 128a016963..135ac108eb 100644 --- a/src/test/regress/sql/box.sql +++ b/src/test/regress/sql/box.sql @@ -179,3 +179,50 @@ EXPLAIN (COSTS OFF) SELECT * FROM box_temp WHERE f1 ~= '(20,20),(40,40)'; RESET enable_seqscan; DROP INDEX box_spgist; + +-- +-- Test the SP-GiST index on the larger volume of data +-- +CREATE TABLE quad_box_tbl (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; + +-- insert repeating data to test allTheSame +INSERT INTO quad_box_tbl + SELECT '((200, 300),(210, 310))' + FROM generate_series(1, 1000); + +INSERT INTO quad_box_tbl + VALUES + (NULL), + (NULL), + ('((-infinity,-infinity),(infinity,infinity))'), + ('((-infinity,100),(-infinity,500))'), + ('((-infinity,-infinity),(700,infinity))'); + +CREATE INDEX quad_box_tbl_idx ON quad_box_tbl USING spgist(b); + +SET enable_seqscan = OFF; +SET enable_indexscan = ON; +SET enable_bitmapscan = ON; + +SELECT count(*) FROM quad_box_tbl WHERE b << box '((100,200),(300,500))'; +SELECT count(*) FROM quad_box_tbl WHERE b &< box '((100,200),(300,500))'; +SELECT count(*) FROM quad_box_tbl WHERE b && box '((100,200),(300,500))'; +SELECT count(*) FROM quad_box_tbl WHERE b &> box '((100,200),(300,500))'; +SELECT count(*) FROM quad_box_tbl WHERE b >> box '((100,200),(300,500))'; +SELECT count(*) FROM quad_box_tbl WHERE b >> box '((100,200),(300,500))'; +SELECT count(*) FROM quad_box_tbl WHERE b <<| box '((100,200),(300,500))'; +SELECT count(*) FROM quad_box_tbl WHERE b &<| box '((100,200),(300,500))'; +SELECT count(*) FROM quad_box_tbl WHERE b |&> box '((100,200),(300,500))'; +SELECT count(*) FROM quad_box_tbl WHERE b |>> box '((100,200),(300,500))'; +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))'; + +RESET enable_seqscan; +RESET enable_indexscan; +RESET enable_bitmapscan; -- 2.40.0