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

Commit075f0a8

Browse files
committed
Add support for <-> (box, point) operator to SP-GiST box_ops
Opclass support functions already can handle this operator, just catalogadjustment appears to be required.Discussion:https://postgr.es/m/f71ba19d-d989-63b6-f04a-abf02ad9345d%40postgrespro.ruAuthor: Nikita GlukhovReviewed-by: Tom Lane, Alexander Korotkov
1 parentc085e1c commit075f0a8

File tree

5 files changed

+135
-24
lines changed

5 files changed

+135
-24
lines changed

‎doc/src/sgml/spgist.sgml

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -139,6 +139,7 @@
139139
<literal>|&amp;&gt;</literal>
140140
</entry>
141141
<entry>
142+
<literal>&lt;-&gt;</literal>
142143
</entry>
143144
</row>
144145
<row>

‎src/include/catalog/pg_amop.dat

Lines changed: 4 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1546,6 +1546,10 @@
15461546
amopstrategy => '11', amopopr => '|>>(box,box)', amopmethod => 'spgist' },
15471547
{ amopfamily => 'spgist/box_ops', amoplefttype => 'box', amoprighttype => 'box',
15481548
amopstrategy => '12', amopopr => '|&>(box,box)', amopmethod => 'spgist' },
1549+
{ amopfamily => 'spgist/box_ops', amoplefttype => 'box',
1550+
amoprighttype => 'point', amopstrategy => '15', amoppurpose => 'o',
1551+
amopopr => '<->(box,point)', amopmethod => 'spgist',
1552+
amopsortfamily => 'btree/float_ops' },
15491553

15501554
# SP-GiST poly_ops (supports polygons)
15511555
{ amopfamily => 'spgist/poly_ops', amoplefttype => 'polygon',

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

Lines changed: 70 additions & 12 deletions
Original file line numberDiff line numberDiff line change
@@ -480,23 +480,33 @@ DROP INDEX box_spgist;
480480
--
481481
-- Test the SP-GiST index on the larger volume of data
482482
--
483-
CREATE TABLE quad_box_tbl (b box);
483+
CREATE TABLE quad_box_tbl (id int,b box);
484484
INSERT INTO quad_box_tbl
485-
SELECT box(point(x * 10, y * 10), point(x * 10 + 5, y * 10 + 5))
486-
FROM generate_series(1, 100) x,
487-
generate_series(1, 100) y;
485+
SELECT (x - 1) * 100 + y, box(point(x * 10, y * 10), point(x * 10 + 5, y * 10 + 5))
486+
FROM generate_series(1, 100) x,
487+
generate_series(1, 100) y;
488488
-- insert repeating data to test allTheSame
489489
INSERT INTO quad_box_tbl
490-
SELECT '((200, 300),(210, 310))'
491-
FROM generate_series(1, 1000);
490+
SELECT i, '((200, 300),(210, 310))'
491+
FROM generate_series(10001, 11000) AS i;
492492
INSERT INTO quad_box_tbl
493-
VALUES
494-
(NULL),
495-
(NULL),
496-
('((-infinity,-infinity),(infinity,infinity))'),
497-
('((-infinity,100),(-infinity,500))'),
498-
('((-infinity,-infinity),(700,infinity))');
493+
VALUES
494+
(11001,NULL),
495+
(11002,NULL),
496+
(11003,'((-infinity,-infinity),(infinity,infinity))'),
497+
(11004,'((-infinity,100),(-infinity,500))'),
498+
(11005,'((-infinity,-infinity),(700,infinity))');
499499
CREATE INDEX quad_box_tbl_idx ON quad_box_tbl USING spgist(b);
500+
-- get reference results for ORDER BY distance from seq scan
501+
SET enable_seqscan = ON;
502+
SET enable_indexscan = OFF;
503+
SET enable_bitmapscan = OFF;
504+
CREATE TABLE quad_box_tbl_ord_seq1 AS
505+
SELECT rank() OVER (ORDER BY b <-> point '123,456') n, b <-> point '123,456' dist, id
506+
FROM quad_box_tbl;
507+
CREATE TABLE quad_box_tbl_ord_seq2 AS
508+
SELECT rank() OVER (ORDER BY b <-> point '123,456') n, b <-> point '123,456' dist, id
509+
FROM quad_box_tbl WHERE b <@ box '((200,300),(500,600))';
500510
SET enable_seqscan = OFF;
501511
SET enable_indexscan = ON;
502512
SET enable_bitmapscan = ON;
@@ -578,6 +588,54 @@ SELECT count(*) FROM quad_box_tbl WHERE b ~= box '((200,300),(205,305))';
578588
1
579589
(1 row)
580590

591+
-- test ORDER BY distance
592+
SET enable_indexscan = ON;
593+
SET enable_bitmapscan = OFF;
594+
EXPLAIN (COSTS OFF)
595+
SELECT rank() OVER (ORDER BY b <-> point '123,456') n, b <-> point '123,456' dist, id
596+
FROM quad_box_tbl;
597+
QUERY PLAN
598+
---------------------------------------------------------
599+
WindowAgg
600+
-> Index Scan using quad_box_tbl_idx on quad_box_tbl
601+
Order By: (b <-> '(123,456)'::point)
602+
(3 rows)
603+
604+
CREATE TEMP TABLE quad_box_tbl_ord_idx1 AS
605+
SELECT rank() OVER (ORDER BY b <-> point '123,456') n, b <-> point '123,456' dist, id
606+
FROM quad_box_tbl;
607+
SELECT *
608+
FROM quad_box_tbl_ord_seq1 seq FULL JOIN quad_box_tbl_ord_idx1 idx
609+
ON seq.n = idx.n AND seq.id = idx.id AND
610+
(seq.dist = idx.dist OR seq.dist IS NULL AND idx.dist IS NULL)
611+
WHERE seq.id IS NULL OR idx.id IS NULL;
612+
n | dist | id | n | dist | id
613+
---+------+----+---+------+----
614+
(0 rows)
615+
616+
EXPLAIN (COSTS OFF)
617+
SELECT rank() OVER (ORDER BY b <-> point '123,456') n, b <-> point '123,456' dist, id
618+
FROM quad_box_tbl WHERE b <@ box '((200,300),(500,600))';
619+
QUERY PLAN
620+
---------------------------------------------------------
621+
WindowAgg
622+
-> Index Scan using quad_box_tbl_idx on quad_box_tbl
623+
Index Cond: (b <@ '(500,600),(200,300)'::box)
624+
Order By: (b <-> '(123,456)'::point)
625+
(4 rows)
626+
627+
CREATE TEMP TABLE quad_box_tbl_ord_idx2 AS
628+
SELECT rank() OVER (ORDER BY b <-> point '123,456') n, b <-> point '123,456' dist, id
629+
FROM quad_box_tbl WHERE b <@ box '((200,300),(500,600))';
630+
SELECT *
631+
FROM quad_box_tbl_ord_seq2 seq FULL JOIN quad_box_tbl_ord_idx2 idx
632+
ON seq.n = idx.n AND seq.id = idx.id AND
633+
(seq.dist = idx.dist OR seq.dist IS NULL AND idx.dist IS NULL)
634+
WHERE seq.id IS NULL OR idx.id IS NULL;
635+
n | dist | id | n | dist | id
636+
---+------+----+---+------+----
637+
(0 rows)
638+
581639
RESET enable_seqscan;
582640
RESET enable_indexscan;
583641
RESET enable_bitmapscan;

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

Lines changed: 2 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -165,6 +165,8 @@ pg_user_mapping|t
165165
point_tbl|t
166166
polygon_tbl|t
167167
quad_box_tbl|t
168+
quad_box_tbl_ord_seq1|f
169+
quad_box_tbl_ord_seq2|f
168170
quad_point_tbl|t
169171
quad_poly_tbl|t
170172
radix_text_tbl|t

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

Lines changed: 58 additions & 12 deletions
Original file line numberDiff line numberDiff line change
@@ -192,28 +192,41 @@ DROP INDEX box_spgist;
192192
--
193193
-- Test the SP-GiST index on the larger volume of data
194194
--
195-
CREATETABLEquad_box_tbl (bbox);
195+
CREATETABLEquad_box_tbl (idint,bbox);
196196

197197
INSERT INTO quad_box_tbl
198-
SELECTbox(point(x*10, y*10),point(x*10+5, y*10+5))
199-
FROM generate_series(1,100) x,
200-
generate_series(1,100) y;
198+
SELECT (x-1)*100+ y,box(point(x*10, y*10),point(x*10+5, y*10+5))
199+
FROM generate_series(1,100) x,
200+
generate_series(1,100) y;
201201

202202
-- insert repeating data to test allTheSame
203203
INSERT INTO quad_box_tbl
204-
SELECT'((200, 300),(210, 310))'
205-
FROM generate_series(1,1000);
204+
SELECT i,'((200, 300),(210, 310))'
205+
FROM generate_series(10001,11000)AS i;
206206

207207
INSERT INTO quad_box_tbl
208-
VALUES
209-
(NULL),
210-
(NULL),
211-
('((-infinity,-infinity),(infinity,infinity))'),
212-
('((-infinity,100),(-infinity,500))'),
213-
('((-infinity,-infinity),(700,infinity))');
208+
VALUES
209+
(11001,NULL),
210+
(11002,NULL),
211+
(11003,'((-infinity,-infinity),(infinity,infinity))'),
212+
(11004,'((-infinity,100),(-infinity,500))'),
213+
(11005,'((-infinity,-infinity),(700,infinity))');
214214

215215
CREATEINDEXquad_box_tbl_idxON quad_box_tbl USING spgist(b);
216216

217+
-- get reference results for ORDER BY distance from seq scan
218+
SET enable_seqscan=ON;
219+
SET enable_indexscan= OFF;
220+
SET enable_bitmapscan= OFF;
221+
222+
CREATETABLEquad_box_tbl_ord_seq1AS
223+
SELECT rank() OVER (ORDER BY b<->point'123,456') n, b<->point'123,456' dist, id
224+
FROM quad_box_tbl;
225+
226+
CREATETABLEquad_box_tbl_ord_seq2AS
227+
SELECT rank() OVER (ORDER BY b<->point'123,456') n, b<->point'123,456' dist, id
228+
FROM quad_box_tblWHERE b<@box'((200,300),(500,600))';
229+
217230
SET enable_seqscan= OFF;
218231
SET enable_indexscan=ON;
219232
SET enable_bitmapscan=ON;
@@ -232,6 +245,39 @@ SELECT count(*) FROM quad_box_tbl WHERE b @> box '((201,301),(202,303))';
232245
SELECTcount(*)FROM quad_box_tblWHERE b<@box'((100,200),(300,500))';
233246
SELECTcount(*)FROM quad_box_tblWHERE b ~=box'((200,300),(205,305))';
234247

248+
-- test ORDER BY distance
249+
SET enable_indexscan=ON;
250+
SET enable_bitmapscan= OFF;
251+
252+
EXPLAIN (COSTS OFF)
253+
SELECT rank() OVER (ORDER BY b<->point'123,456') n, b<->point'123,456' dist, id
254+
FROM quad_box_tbl;
255+
256+
CREATE TEMP TABLE quad_box_tbl_ord_idx1AS
257+
SELECT rank() OVER (ORDER BY b<->point'123,456') n, b<->point'123,456' dist, id
258+
FROM quad_box_tbl;
259+
260+
SELECT*
261+
FROM quad_box_tbl_ord_seq1 seq FULLJOIN quad_box_tbl_ord_idx1 idx
262+
ONseq.n=idx.nANDseq.id=idx.idAND
263+
(seq.dist=idx.distORseq.dist ISNULLANDidx.dist ISNULL)
264+
WHEREseq.id ISNULLORidx.id ISNULL;
265+
266+
267+
EXPLAIN (COSTS OFF)
268+
SELECT rank() OVER (ORDER BY b<->point'123,456') n, b<->point'123,456' dist, id
269+
FROM quad_box_tblWHERE b<@box'((200,300),(500,600))';
270+
271+
CREATE TEMP TABLE quad_box_tbl_ord_idx2AS
272+
SELECT rank() OVER (ORDER BY b<->point'123,456') n, b<->point'123,456' dist, id
273+
FROM quad_box_tblWHERE b<@box'((200,300),(500,600))';
274+
275+
SELECT*
276+
FROM quad_box_tbl_ord_seq2 seq FULLJOIN quad_box_tbl_ord_idx2 idx
277+
ONseq.n=idx.nANDseq.id=idx.idAND
278+
(seq.dist=idx.distORseq.dist ISNULLANDidx.dist ISNULL)
279+
WHEREseq.id ISNULLORidx.id ISNULL;
280+
235281
RESET enable_seqscan;
236282
RESET enable_indexscan;
237283
RESET enable_bitmapscan;

0 commit comments

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