Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commitd5286aa

Browse files
committed
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.6Author: Nikita Glukhov with minor editorization of tests by meReviewed-by: Kyotaro Horiguchi, Anastasia Lubennikovahttps://commitfest.postgresql.org/13/981/
1 parent29bf501 commitd5286aa

File tree

4 files changed

+172
-4
lines changed

4 files changed

+172
-4
lines changed

‎src/backend/utils/adt/geo_spgist.c

Lines changed: 20 additions & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -286,6 +286,14 @@ lower2D(RangeBox *range_box, Range *query)
286286
FPlt(range_box->right.low,query->low);
287287
}
288288

289+
/* Can any range from range_box not extend to the right side of the query? */
290+
staticbool
291+
overLower2D(RangeBox*range_box,Range*query)
292+
{
293+
returnFPle(range_box->left.low,query->high)&&
294+
FPle(range_box->right.low,query->high);
295+
}
296+
289297
/* Can any range from range_box to be higher than this argument? */
290298
staticbool
291299
higher2D(RangeBox*range_box,Range*query)
@@ -294,6 +302,14 @@ higher2D(RangeBox *range_box, Range *query)
294302
FPgt(range_box->right.high,query->high);
295303
}
296304

305+
/* Can any range from range_box not extend to the left side of the query? */
306+
staticbool
307+
overHigher2D(RangeBox*range_box,Range*query)
308+
{
309+
returnFPge(range_box->left.high,query->low)&&
310+
FPge(range_box->right.high,query->low);
311+
}
312+
297313
/* Can any rectangle from rect_box be left of this argument? */
298314
staticbool
299315
left4D(RectBox*rect_box,RangeBox*query)
@@ -305,7 +321,7 @@ left4D(RectBox *rect_box, RangeBox *query)
305321
staticbool
306322
overLeft4D(RectBox*rect_box,RangeBox*query)
307323
{
308-
returnlower2D(&rect_box->range_box_x,&query->right);
324+
returnoverLower2D(&rect_box->range_box_x,&query->left);
309325
}
310326

311327
/* Can any rectangle from rect_box be right of this argument? */
@@ -319,7 +335,7 @@ right4D(RectBox *rect_box, RangeBox *query)
319335
staticbool
320336
overRight4D(RectBox*rect_box,RangeBox*query)
321337
{
322-
returnhigher2D(&rect_box->range_box_x,&query->right);
338+
returnoverHigher2D(&rect_box->range_box_x,&query->left);
323339
}
324340

325341
/* Can any rectangle from rect_box be below of this argument? */
@@ -333,7 +349,7 @@ below4D(RectBox *rect_box, RangeBox *query)
333349
staticbool
334350
overBelow4D(RectBox*rect_box,RangeBox*query)
335351
{
336-
returnlower2D(&rect_box->range_box_y,&query->left);
352+
returnoverLower2D(&rect_box->range_box_y,&query->right);
337353
}
338354

339355
/* Can any rectangle from rect_box be above of this argument? */
@@ -347,7 +363,7 @@ above4D(RectBox *rect_box, RangeBox *query)
347363
staticbool
348364
overAbove4D(RectBox*rect_box,RangeBox*query)
349365
{
350-
returnhigher2D(&rect_box->range_box_y,&query->right);
366+
returnoverHigher2D(&rect_box->range_box_y,&query->right);
351367
}
352368

353369
/*

‎src/test/regress/expected/box.out

Lines changed: 104 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -455,3 +455,107 @@ EXPLAIN (COSTS OFF) SELECT * FROM box_temp WHERE f1 ~= '(20,20),(40,40)';
455455

456456
RESET enable_seqscan;
457457
DROP INDEX box_spgist;
458+
--
459+
-- Test the SP-GiST index on the larger volume of data
460+
--
461+
CREATE TABLE quad_box_tbl (b box);
462+
INSERT INTO quad_box_tbl
463+
SELECT box(point(x * 10, y * 10), point(x * 10 + 5, y * 10 + 5))
464+
FROM generate_series(1, 100) x,
465+
generate_series(1, 100) y;
466+
-- insert repeating data to test allTheSame
467+
INSERT INTO quad_box_tbl
468+
SELECT '((200, 300),(210, 310))'
469+
FROM generate_series(1, 1000);
470+
INSERT INTO quad_box_tbl
471+
VALUES
472+
(NULL),
473+
(NULL),
474+
('((-infinity,-infinity),(infinity,infinity))'),
475+
('((-infinity,100),(-infinity,500))'),
476+
('((-infinity,-infinity),(700,infinity))');
477+
CREATE INDEX quad_box_tbl_idx ON quad_box_tbl USING spgist(b);
478+
SET enable_seqscan = OFF;
479+
SET enable_indexscan = ON;
480+
SET enable_bitmapscan = ON;
481+
SELECT count(*) FROM quad_box_tbl WHERE b << box '((100,200),(300,500))';
482+
count
483+
-------
484+
901
485+
(1 row)
486+
487+
SELECT count(*) FROM quad_box_tbl WHERE b &< box '((100,200),(300,500))';
488+
count
489+
-------
490+
3901
491+
(1 row)
492+
493+
SELECT count(*) FROM quad_box_tbl WHERE b && box '((100,200),(300,500))';
494+
count
495+
-------
496+
1653
497+
(1 row)
498+
499+
SELECT count(*) FROM quad_box_tbl WHERE b &> box '((100,200),(300,500))';
500+
count
501+
-------
502+
10100
503+
(1 row)
504+
505+
SELECT count(*) FROM quad_box_tbl WHERE b >> box '((100,200),(300,500))';
506+
count
507+
-------
508+
7000
509+
(1 row)
510+
511+
SELECT count(*) FROM quad_box_tbl WHERE b >> box '((100,200),(300,500))';
512+
count
513+
-------
514+
7000
515+
(1 row)
516+
517+
SELECT count(*) FROM quad_box_tbl WHERE b <<| box '((100,200),(300,500))';
518+
count
519+
-------
520+
1900
521+
(1 row)
522+
523+
SELECT count(*) FROM quad_box_tbl WHERE b &<| box '((100,200),(300,500))';
524+
count
525+
-------
526+
5901
527+
(1 row)
528+
529+
SELECT count(*) FROM quad_box_tbl WHERE b |&> box '((100,200),(300,500))';
530+
count
531+
-------
532+
9100
533+
(1 row)
534+
535+
SELECT count(*) FROM quad_box_tbl WHERE b |>> box '((100,200),(300,500))';
536+
count
537+
-------
538+
5000
539+
(1 row)
540+
541+
SELECT count(*) FROM quad_box_tbl WHERE b @> box '((201,301),(202,303))';
542+
count
543+
-------
544+
1003
545+
(1 row)
546+
547+
SELECT count(*) FROM quad_box_tbl WHERE b <@ box '((100,200),(300,500))';
548+
count
549+
-------
550+
1600
551+
(1 row)
552+
553+
SELECT count(*) FROM quad_box_tbl WHERE b ~= box '((200,300),(205,305))';
554+
count
555+
-------
556+
1
557+
(1 row)
558+
559+
RESET enable_seqscan;
560+
RESET enable_indexscan;
561+
RESET enable_bitmapscan;

‎src/test/regress/expected/sanity_check.out

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -155,6 +155,7 @@ pg_type|t
155155
pg_user_mapping|t
156156
point_tbl|t
157157
polygon_tbl|t
158+
quad_box_tbl|t
158159
quad_point_tbl|t
159160
radix_text_tbl|t
160161
ramp|f

‎src/test/regress/sql/box.sql

Lines changed: 47 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -179,3 +179,50 @@ EXPLAIN (COSTS OFF) SELECT * FROM box_temp WHERE f1 ~= '(20,20),(40,40)';
179179
RESET enable_seqscan;
180180

181181
DROPINDEX box_spgist;
182+
183+
--
184+
-- Test the SP-GiST index on the larger volume of data
185+
--
186+
CREATETABLEquad_box_tbl (bbox);
187+
188+
INSERT INTO quad_box_tbl
189+
SELECTbox(point(x*10, y*10),point(x*10+5, y*10+5))
190+
FROM generate_series(1,100) x,
191+
generate_series(1,100) y;
192+
193+
-- insert repeating data to test allTheSame
194+
INSERT INTO quad_box_tbl
195+
SELECT'((200, 300),(210, 310))'
196+
FROM generate_series(1,1000);
197+
198+
INSERT INTO quad_box_tbl
199+
VALUES
200+
(NULL),
201+
(NULL),
202+
('((-infinity,-infinity),(infinity,infinity))'),
203+
('((-infinity,100),(-infinity,500))'),
204+
('((-infinity,-infinity),(700,infinity))');
205+
206+
CREATEINDEXquad_box_tbl_idxON quad_box_tbl USING spgist(b);
207+
208+
SET enable_seqscan= OFF;
209+
SET enable_indexscan=ON;
210+
SET enable_bitmapscan=ON;
211+
212+
SELECTcount(*)FROM quad_box_tblWHERE b<<box'((100,200),(300,500))';
213+
SELECTcount(*)FROM quad_box_tblWHERE b &<box'((100,200),(300,500))';
214+
SELECTcount(*)FROM quad_box_tblWHERE b &&box'((100,200),(300,500))';
215+
SELECTcount(*)FROM quad_box_tblWHERE b &>box'((100,200),(300,500))';
216+
SELECTcount(*)FROM quad_box_tblWHERE b>>box'((100,200),(300,500))';
217+
SELECTcount(*)FROM quad_box_tblWHERE b>>box'((100,200),(300,500))';
218+
SELECTcount(*)FROM quad_box_tblWHERE b<<|box'((100,200),(300,500))';
219+
SELECTcount(*)FROM quad_box_tblWHERE b &<|box'((100,200),(300,500))';
220+
SELECTcount(*)FROM quad_box_tblWHERE b |&>box'((100,200),(300,500))';
221+
SELECTcount(*)FROM quad_box_tblWHERE b |>>box'((100,200),(300,500))';
222+
SELECTcount(*)FROM quad_box_tblWHERE b @>box'((201,301),(202,303))';
223+
SELECTcount(*)FROM quad_box_tblWHERE b<@box'((100,200),(300,500))';
224+
SELECTcount(*)FROM quad_box_tblWHERE b ~=box'((200,300),(205,305))';
225+
226+
RESET enable_seqscan;
227+
RESET enable_indexscan;
228+
RESET enable_bitmapscan;

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp