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

Commitfda873b

Browse files
author
Vitaly Davydov
committed
Prepare the original PR of@gbroccolo for the merge
Move brin.c(h) files into src subdirectoryAdd brin support in upgrade scriptFix some code problemsFix test_initFix Indices section in the docAdd test for sbox BRIN indexFix found problems in the doc after reviewFix BRIN support for sbox
1 parent06739a2 commitfda873b

File tree

11 files changed

+336
-72
lines changed

11 files changed

+336
-72
lines changed

‎Makefile

Lines changed: 3 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -11,7 +11,7 @@ MODULE_big = pg_sphere
1111
OBJS = src/sscan.o src/sparse.o src/sbuffer.o src/vector3d.o src/point.o\
1212
src/euler.o src/circle.o src/line.o src/ellipse.o src/polygon.o\
1313
src/path.o src/box.o src/output.o src/gq_cache.o src/gist.o\
14-
src/key.o src/gnomo.o src/epochprop.o brin.o
14+
src/key.o src/gnomo.o src/epochprop.osrc/brin.o
1515

1616
ifneq ($(USE_HEALPIX),0)
1717
OBJS += src/healpix.o src/moc.o src/process_moc.o\
@@ -43,7 +43,7 @@ REGRESS_9_5 = index_9.5 # experimental for spoint3
4343

4444
TESTS = init_test tables points euler circle line ellipse poly path box\
4545
index contains_ops contains_ops_compat bounding_box_gist gnomo\
46-
epochprop contains overlaps
46+
epochprop contains overlaps spoint_brin sbox_brin
4747

4848
ifneq ($(USE_HEALPIX),0)
4949
TESTS += healpix moc mocautocast
@@ -262,7 +262,7 @@ endif
262262
pg_sphere--1.2.2--1.2.3.sql:
263263
cat upgrade_scripts/$@.in>$@
264264

265-
pg_sphere--1.2.3--1.3.0.sql:
265+
pg_sphere--1.2.3--1.3.0.sql: pgs_brin.sql.in
266266
cat upgrade_scripts/$@.in>$@
267267

268268
# end of local stuff

‎README.pg_sphere

Lines changed: 2 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -9,9 +9,9 @@ It provides:
99
* Object rotation by Euler angles
1010
* Indexing of spherical data types
1111

12-
This is an R-Tree implementation using GiST for spherical objects like
12+
This is an R-tree implementation using GiST for spherical objects like
1313
spherical points and spherical circles with useful functions and operators.
14-
It alsosupport the Block Range INdexing (BRIN) for large datasets.
14+
It alsosupports the Block Range INdexing (BRIN) for large datasets.
1515

1616
NOTICE:
1717
This version will work only with PostgreSQL version 10 and above.

‎doc/indices.sgm

Lines changed: 14 additions & 16 deletions
Original file line numberDiff line numberDiff line change
@@ -10,17 +10,17 @@
1010
<application>pgSphere</application> uses <literal>GiST</literal>
1111
and Block Range INdexing (<literal>BRIN</literal>) algorithms to create
1212
spherical indices.
13-
<literal>GiST</literal>index represents the R-Tree implementation for
14-
spherical objects, while <literal>BRINs</literal> are based on "summarization"
13+
<literal>GiST</literal>indexes utilize an R-tree implementation for
14+
spherical objects, while <literal>BRIN</literal>indexesare based on the "summarization"
1515
of data blocks (<literal>pages</literal>) on physical storage in order to
1616
organize data searches on ranges of summarized data that can be easily skipped
1717
on the base of search filters (see <ulink
18-
url="https://www.postgresql.org/docs/9.5/static/brin-intro.html"><citetitle>
19-
PostgreSQL documentation</citetitle></ulink> for further details onBRINs).
20-
As a consequence,BRINs result to be reallysmall indexes (up to 1000 times
21-
than GiST ones), generally with loweraperformance compared with a GiST one,
18+
url="https://www.postgresql.org/docs/current/brin-intro.html"><citetitle>
19+
PostgreSQL documentation</citetitle></ulink> for further details onBRIN indexes).
20+
As a consequence,BRIN indexes are verysmall indexes (up to 1000 times smaller
21+
than GiST ones), generally with lower performance compared with a GiST one,
2222
but up to 100 times faster than a full sequential scan of a table performed
23-
without any index. SoBRINs are particularly suitable in a big data context.
23+
without any index. SoBRIN indexes are particularly suitable in a big data context.
2424
An index speeds up the execution time of searches based on operators <link
2525
linkend="op.over"><literal>&lt;@</literal></link>, <link
2626
linkend="op.over"><literal>@</literal></link>, <link
@@ -29,9 +29,6 @@
2929
linkend="op.equal"><literal>=</literal></link>, and <link
3030
linkend="op.equal"><literal>!=</literal></link>.
3131
</para>
32-
<title>
33-
Create a spherical index
34-
</title>
3532
<para>
3633
You can create a GiST index with the following spherical data types:
3734
</para>
@@ -87,17 +84,18 @@
8784
<![CDATA[VACUUM ANALYZE test;]]>
8885
</programlisting>
8986
<para>
90-
BRINs can be created through the following syntax:
87+
BRIN index can be created through the following syntax:
9188
</para>
9289
<programlisting>
9390
<![CDATA[CREATE INDEX test_pos_idx USING BRIN ON test (pos);]]>
9491
</programlisting>
9592
<para>
96-
By default, BRINs summarize block of 128 pages. The lower numbers
97-
of pages are specified, the higher granularity is reached during
98-
the searches, and performance's gap between GiST indexes and BRINs
99-
is lower (consider that BRINs size increases as well). Different
100-
summarizations can be used with the following command:
93+
By default, BRIN indexes summarize blocks of 128 pages. The smaller the
94+
number of pages specified, the higher the granularity in searches,
95+
and the gap in performance between GiST indexes and BRIN indexes will be
96+
decreased. Note that the size of the BRIN indexes increases as well.
97+
Different summarizations can be specified with the following
98+
command:
10199
</para>
102100
<programlisting>
103101
<![CDATA[CREATE INDEX test_pos_idx USING BRIN ON test (pos) WITH (pages_per_range = 16);]]>

‎expected/init_test_healpix.out.in

Lines changed: 2 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -1,2 +1,2 @@
1-
psql:pg_sphere.test.sql:9271: NOTICE: return type smoc is only a shell
2-
psql:pg_sphere.test.sql:9277: NOTICE: argument type smoc is only a shell
1+
psql:pg_sphere.test.sql:9684: NOTICE: return type smoc is only a shell
2+
psql:pg_sphere.test.sql:9690: NOTICE: argument type smoc is only a shell

‎expected/sbox_brin.out

Lines changed: 91 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,91 @@
1+
SELECT set_sphere_output_precision(8);
2+
set_sphere_output_precision
3+
-----------------------------
4+
SET 8
5+
(1 row)
6+
7+
CREATE TABLE test_boxes (
8+
b sbox
9+
);
10+
COPY test_boxes (b) FROM stdin;
11+
CREATE OR REPLACE FUNCTION qnodes(q text) RETURNS text
12+
LANGUAGE 'plpgsql' AS
13+
$$
14+
DECLARE
15+
exp TEXT;
16+
mat TEXT[];
17+
ret TEXT[];
18+
BEGIN
19+
FOR exp IN EXECUTE 'EXPLAIN ' || q
20+
LOOP
21+
--RAISE NOTICE 'EXP: %', exp;
22+
mat := regexp_matches(exp, ' *(?:-> *)?(.*Scan on (test_boxes|test_boxes_brin_idx))');
23+
--RAISE NOTICE 'MAT: %', mat;
24+
IF mat IS NOT NULL THEN
25+
ret := array_append(ret, mat[1]);
26+
END IF;
27+
--RAISE NOTICE 'RET: %', ret;
28+
END LOOP;
29+
RETURN array_to_string(ret,',');
30+
END;
31+
$$;
32+
CREATE INDEX test_boxes_brin_idx ON test_boxes USING brin (b);
33+
SET enable_indexscan = OFF;
34+
SET enable_bitmapscan = OFF;
35+
SET enable_seqscan = ON;
36+
SELECT 'scan_seq', qnodes('SELECT * FROM test_boxes WHERE b <@ sbox ''( (10d,10d), (20d,20d) )''');
37+
?column? | qnodes
38+
----------+------------------------
39+
scan_seq | Seq Scan on test_boxes
40+
(1 row)
41+
42+
SELECT * FROM test_boxes WHERE b <@ sbox '( (10d,10d), (20d,20d) )';
43+
b
44+
---
45+
(0 rows)
46+
47+
SELECT 'scan_seq', qnodes('SELECT * FROM test_boxes WHERE b && sbox ''( (10d,10d), (20d,20d) )''');
48+
?column? | qnodes
49+
----------+------------------------
50+
scan_seq | Seq Scan on test_boxes
51+
(1 row)
52+
53+
SELECT * FROM test_boxes WHERE b && sbox '( (10d,10d), (20d,20d) )';
54+
b
55+
--------------------------------------------------------
56+
((0.34906585 , 0.17453293), (0.35006585 , 0.17463293))
57+
(1 row)
58+
59+
SET enable_indexscan = OFF;
60+
SET enable_bitmapscan = ON;
61+
SET enable_seqscan = OFF;
62+
SELECT 'scan_idx', qnodes('SELECT * FROM test_boxes WHERE b <@ sbox ''( (10d,10d), (20d,20d) )''');
63+
?column? | qnodes
64+
----------+-------------------------------------------------------------------------
65+
scan_idx | Bitmap Heap Scan on test_boxes,Bitmap Index Scan on test_boxes_brin_idx
66+
(1 row)
67+
68+
SELECT * FROM test_boxes WHERE b <@ sbox '( (10d,10d), (20d,20d) )';
69+
b
70+
---
71+
(0 rows)
72+
73+
SELECT 'scan_idx', qnodes('SELECT * FROM test_boxes WHERE b && sbox ''( (10d,10d), (20d,20d) )''');
74+
?column? | qnodes
75+
----------+-------------------------------------------------------------------------
76+
scan_idx | Bitmap Heap Scan on test_boxes,Bitmap Index Scan on test_boxes_brin_idx
77+
(1 row)
78+
79+
SELECT * FROM test_boxes WHERE b && sbox '( (10d,10d), (20d,20d) )';
80+
b
81+
--------------------------------------------------------
82+
((0.34906585 , 0.17453293), (0.35006585 , 0.17463293))
83+
(1 row)
84+
85+
---- cleanup
86+
DROP INDEX test_boxes_brin_idx;
87+
DROP TABLE test_boxes;
88+
DROP FUNCTION qnodes(text);
89+
SET enable_indexscan = ON;
90+
SET enable_bitmapscan = ON;
91+
SET enable_seqscan = ON;

‎expected/spoint_brin.out

Lines changed: 65 additions & 34 deletions
Original file line numberDiff line numberDiff line change
@@ -1,56 +1,87 @@
1-
CREATE TABLE
2-
COPY 77
3-
CREATE FUNCTION
4-
CREATE INDEX
5-
SET
6-
SET
7-
SET
8-
?column? | qnodes
9-
----------+----------
10-
scan_seq | Seq Scan
11-
(1 row)
12-
13-
p
1+
CREATE TABLE test_points (
2+
p spoint
3+
);
4+
COPY test_points (p) FROM stdin;
5+
CREATE OR REPLACE FUNCTION qnodes(q text) RETURNS text
6+
LANGUAGE 'plpgsql' AS
7+
$$
8+
DECLARE
9+
exp TEXT;
10+
mat TEXT[];
11+
ret TEXT[];
12+
BEGIN
13+
FOR exp IN EXECUTE 'EXPLAIN ' || q
14+
LOOP
15+
--RAISE NOTICE 'EXP: %', exp;
16+
mat := regexp_matches(exp, ' *(?:-> *)?(.*Scan on (test_points|brin_spoint))');
17+
--RAISE NOTICE 'MAT: %', mat;
18+
IF mat IS NOT NULL THEN
19+
ret := array_append(ret, mat[1]);
20+
END IF;
21+
--RAISE NOTICE 'RET: %', ret;
22+
END LOOP;
23+
RETURN array_to_string(ret,',');
24+
END;
25+
$$;
26+
CREATE INDEX brin_spoint ON test_points USING brin (p) WITH (pages_per_range = 16);
27+
set enable_indexscan = off;
28+
set enable_bitmapscan = off;
29+
set enable_seqscan = on;
30+
SELECT 'scan_seq', qnodes('SELECT * FROM test_points WHERE p <@ sbox ''( (10d,10d), (20d,20d) )''');
31+
?column? | qnodes
32+
----------+-------------------------
33+
scan_seq | Seq Scan on test_points
34+
(1 row)
35+
36+
SELECT * FROM test_points WHERE p <@ sbox '( (10d,10d), (20d,20d) )';
37+
p
1438
-----------------------------------------
1539
(0.349065850398866 , 0.174532925199433)
1640
(1 row)
1741

18-
?column? | qnodes
19-
----------+----------
20-
scan_seq | Seq Scan
42+
SELECT 'scan_seq', qnodes('SELECT * FROM test_points WHERE p && sbox ''( (10d,10d), (20d,20d) )''');
43+
?column? | qnodes
44+
----------+-------------------------
45+
scan_seq | Seq Scan on test_points
2146
(1 row)
2247

23-
p
48+
SELECT * FROM test_points WHERE p && sbox '( (10d,10d), (20d,20d) )';
49+
p
2450
-----------------------------------------
2551
(0.349065850398866 , 0.174532925199433)
2652
(1 row)
2753

28-
SET
29-
SET
30-
SET
31-
?column? | qnodes
32-
----------+------------------------------------
33-
scan_idx | Bitmap Heap Scan,Bitmap Index Scan
54+
set enable_indexscan = off;
55+
set enable_bitmapscan = on;
56+
set enable_seqscan = off;
57+
SELECT 'scan_idx', qnodes('SELECT * FROM test_points WHERE p <@ sbox ''( (10d,10d), (20d,20d) )''');
58+
?column? | qnodes
59+
----------+------------------------------------------------------------------
60+
scan_idx | Bitmap Heap Scan on test_points,Bitmap Index Scan on brin_spoint
3461
(1 row)
3562

36-
p
63+
SELECT * FROM test_points WHERE p <@ sbox '( (10d,10d), (20d,20d) )';
64+
p
3765
-----------------------------------------
3866
(0.349065850398866 , 0.174532925199433)
3967
(1 row)
4068

41-
?column? | qnodes
42-
----------+------------------------------------
43-
scan_idx | Bitmap Heap Scan,Bitmap Index Scan
69+
SELECT 'scan_idx', qnodes('SELECT * FROM test_points WHERE p && sbox ''( (10d,10d), (20d,20d) )''');
70+
?column? | qnodes
71+
----------+------------------------------------------------------------------
72+
scan_idx | Bitmap Heap Scan on test_points,Bitmap Index Scan on brin_spoint
4473
(1 row)
4574

46-
p
75+
SELECT * FROM test_points WHERE p && sbox '( (10d,10d), (20d,20d) )';
76+
p
4777
-----------------------------------------
4878
(0.349065850398866 , 0.174532925199433)
4979
(1 row)
5080

51-
DROP INDEX
52-
DROP TABLE
53-
DROP FUNCTION
54-
SET
55-
SET
56-
SET
81+
-- cleanup
82+
DROP INDEX brin_spoint;
83+
DROP TABLE test_points;
84+
DROP FUNCTION qnodes(text);
85+
set enable_indexscan = on;
86+
set enable_bitmapscan = on;
87+
set enable_seqscan = on;

‎pgs_brin.sql.in

Lines changed: 4 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -406,6 +406,8 @@ ALTER OPERATOR FAMILY brin_inclusion_spheric_ops USING brin ADD
406406
OPERATOR 7 @>(sbox, spoint),
407407
OPERATOR 7 @>(spoint, sbox),
408408
OPERATOR 8 <@(sbox, spoint),
409-
OPERATOR 8 <@(spoint, sbox);
410-
409+
OPERATOR 8 <@(spoint, sbox),
411410

411+
OPERATOR 3 &&(sbox, sbox),
412+
OPERATOR 7 @>(sbox, sbox),
413+
OPERATOR 8 <@(sbox, sbox);

0 commit comments

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