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

Commitc79fccc

Browse files
authored
Merge pull request#55 from vitcpp/brin-support
Add BRIN support for spoint and sbox
2 parentsd0afcdb +fa429a5 commitc79fccc

11 files changed

+1305
-12
lines changed

‎Makefile

Lines changed: 5 additions & 5 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
14+
src/key.o src/gnomo.o src/epochprop.o src/brin.o
1515

1616
ifneq ($(USE_HEALPIX),0)
1717
OBJS += src/healpix.o src/moc.o src/process_moc.o\
@@ -33,7 +33,7 @@ DATA_built = $(RELEASE_SQL) \
3333
DOCS = README.pg_sphere COPYRIGHT.pg_sphere
3434
REGRESS = init tables points euler circle line ellipse poly path box index\
3535
contains_ops contains_ops_compat bounding_box_gist gnomo epochprop\
36-
contains overlaps
36+
contains overlaps spoint_brin sbox_brin
3737

3838
ifneq ($(USE_HEALPIX),0)
3939
REGRESS += healpix moc mocautocast
@@ -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
@@ -66,7 +66,7 @@ CRUSH_TESTS = init_extended circle_extended
6666
PGS_SQL = pgs_types.sql pgs_point.sql pgs_euler.sql pgs_circle.sql\
6767
pgs_line.sql pgs_ellipse.sql pgs_polygon.sql pgs_path.sql\
6868
pgs_box.sql pgs_contains_ops.sql pgs_contains_ops_compat.sql\
69-
pgs_gist.sql gnomo.sql
69+
pgs_gist.sql gnomo.sql pgs_brin.sql
7070

7171
ifneq ($(USE_HEALPIX),0)
7272
PGS_SQL += healpix.sql
@@ -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 & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -9,8 +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 also supports the Block Range INdexing (BRIN) for large datasets.
1415

1516
NOTICE:
1617
This version will work only with PostgreSQL version 10 and above.

‎doc/indices.sgm

Lines changed: 39 additions & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -8,15 +8,29 @@
88
</title>
99
<para>
1010
<application>pgSphere</application> uses <literal>GiST</literal>
11-
to create spherical indices. An index speeds up the execution
12-
time of operators <link
11+
and Block Range INdexing (<literal>BRIN</literal>) algorithms to create
12+
spherical indices.
13+
<literal>GiST</literal> indexes utilize an R-tree implementation for
14+
spherical objects, while <literal>BRIN</literal> indexes are based on the "summarization"
15+
of data blocks (<literal>pages</literal>) on physical storage in order to
16+
organize data searches on ranges of summarized data that can be easily skipped
17+
on the base of search filters (see <ulink
18+
url="https://www.postgresql.org/docs/current/brin-intro.html"><citetitle>
19+
PostgreSQL documentation</citetitle></ulink> for further details on BRIN indexes).
20+
As a consequence, BRIN indexes are very small indexes (up to 1000 times smaller
21+
than GiST ones), generally with lower performance compared with a GiST one,
22+
but up to 100 times faster than a full sequential scan of a table performed
23+
without any index. So BRIN indexes are particularly suitable in a big data context.
24+
An index speeds up the execution time of searches based on operators <link
1325
linkend="op.over"><literal>&lt;@</literal></link>, <link
1426
linkend="op.over"><literal>@</literal></link>, <link
1527
linkend="op.over"><literal>&amp;&amp;</literal></link>, <link
1628
linkend="op.cross"><literal>&num;</literal></link>, <link
1729
linkend="op.equal"><literal>=</literal></link>, and <link
18-
linkend="op.equal"><literal>!=</literal></link>. You can create
19-
an index with the following spherical data types:
30+
linkend="op.equal"><literal>!=</literal></link>.
31+
</para>
32+
<para>
33+
You can create a GiST index with the following spherical data types:
2034
</para>
2135
<itemizedlist>
2236
<listitem>
@@ -55,6 +69,10 @@
5569
</para>
5670
</listitem>
5771
</itemizedlist>
72+
<para>
73+
BRIN indexing supports just spherical points (<type>spoint</type>)
74+
and spherical coordinates range (<type>sbox</type>) at the moment.
75+
</para>
5876
<example>
5977
<title>Simple index of spherical points</title>
6078
<programlisting>
@@ -65,6 +83,23 @@
6583
<![CDATA[CREATE INDEX test_pos_idx ON test USING GIST (pos);]]>
6684
<![CDATA[VACUUM ANALYZE test;]]>
6785
</programlisting>
86+
<para>
87+
BRIN index can be created through the following syntax:
88+
</para>
89+
<programlisting>
90+
<![CDATA[CREATE INDEX test_pos_idx USING BRIN ON test (pos);]]>
91+
</programlisting>
92+
<para>
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:
99+
</para>
100+
<programlisting>
101+
<![CDATA[CREATE INDEX test_pos_idx USING BRIN ON test (pos) WITH (pages_per_range = 16);]]>
102+
</programlisting>
68103
</example>
69104

70105
</sect2>

‎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: 87 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,87 @@
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
38+
-----------------------------------------
39+
(0.349065850398866 , 0.174532925199433)
40+
(1 row)
41+
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
46+
(1 row)
47+
48+
SELECT * FROM test_points WHERE p && sbox '( (10d,10d), (20d,20d) )';
49+
p
50+
-----------------------------------------
51+
(0.349065850398866 , 0.174532925199433)
52+
(1 row)
53+
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
61+
(1 row)
62+
63+
SELECT * FROM test_points WHERE p <@ sbox '( (10d,10d), (20d,20d) )';
64+
p
65+
-----------------------------------------
66+
(0.349065850398866 , 0.174532925199433)
67+
(1 row)
68+
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
73+
(1 row)
74+
75+
SELECT * FROM test_points WHERE p && sbox '( (10d,10d), (20d,20d) )';
76+
p
77+
-----------------------------------------
78+
(0.349065850398866 , 0.174532925199433)
79+
(1 row)
80+
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;

0 commit comments

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