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

Add BRIN support for spoint and sbox#55

New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to ourterms of service andprivacy statement. We’ll occasionally send you account related emails.

Already on GitHub?Sign in to your account

Merged
vitcpp merged 2 commits intopostgrespro:masterfromvitcpp:brin-support
Aug 27, 2023
Merged
Show file tree
Hide file tree
Changes fromall commits
Commits
File filter

Filter by extension

Filter by extension
Conversations
Failed to load comments.
Loading
Jump to
Jump to file
Failed to load files.
Loading
Diff view
Diff view
10 changes: 5 additions & 5 deletionsMakefile
View file
Open in desktop
Original file line numberDiff line numberDiff line change
Expand Up@@ -11,7 +11,7 @@ MODULE_big = pg_sphere
OBJS = src/sscan.o src/sparse.o src/sbuffer.o src/vector3d.o src/point.o \
src/euler.o src/circle.o src/line.o src/ellipse.o src/polygon.o \
src/path.o src/box.o src/output.o src/gq_cache.o src/gist.o \
src/key.o src/gnomo.o src/epochprop.o
src/key.o src/gnomo.o src/epochprop.o src/brin.o

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

ifneq ($(USE_HEALPIX),0)
REGRESS += healpix moc mocautocast
Expand All@@ -43,7 +43,7 @@ REGRESS_9_5 = index_9.5 # experimental for spoint3

TESTS = init_test tables points euler circle line ellipse poly path box \
index contains_ops contains_ops_compat bounding_box_gist gnomo \
epochprop contains overlaps
epochprop contains overlaps spoint_brin sbox_brin

ifneq ($(USE_HEALPIX),0)
TESTS += healpix moc mocautocast
Expand All@@ -66,7 +66,7 @@ CRUSH_TESTS = init_extended circle_extended
PGS_SQL = pgs_types.sql pgs_point.sql pgs_euler.sql pgs_circle.sql \
pgs_line.sql pgs_ellipse.sql pgs_polygon.sql pgs_path.sql \
pgs_box.sql pgs_contains_ops.sql pgs_contains_ops_compat.sql \
pgs_gist.sql gnomo.sql
pgs_gist.sql gnomo.sql pgs_brin.sql

ifneq ($(USE_HEALPIX),0)
PGS_SQL += healpix.sql
Expand DownExpand Up@@ -262,7 +262,7 @@ endif
pg_sphere--1.2.2--1.2.3.sql:
cat upgrade_scripts/$@.in > $@

pg_sphere--1.2.3--1.3.0.sql:
pg_sphere--1.2.3--1.3.0.sql: pgs_brin.sql.in
cat upgrade_scripts/$@.in > $@

# end of local stuff
Expand Down
3 changes: 2 additions & 1 deletionREADME.pg_sphere
View file
Open in desktop
Original file line numberDiff line numberDiff line change
Expand Up@@ -9,8 +9,9 @@ It provides:
* Object rotation by Euler angles
* Indexing of spherical data types

This is an R-Tree implementation using GiST for spherical objects like
This is an R-tree implementation using GiST for spherical objects like
spherical points and spherical circles with useful functions and operators.
It also supports the Block Range INdexing (BRIN) for large datasets.

NOTICE:
This version will work only with PostgreSQL version 10 and above.
Expand Down
43 changes: 39 additions & 4 deletionsdoc/indices.sgm
View file
Open in desktop
Original file line numberDiff line numberDiff line change
Expand Up@@ -8,15 +8,29 @@
</title>
<para>
<application>pgSphere</application> uses <literal>GiST</literal>
to create spherical indices. An index speeds up the execution
time of operators <link
and Block Range INdexing (<literal>BRIN</literal>) algorithms to create
spherical indices.
<literal>GiST</literal> indexes utilize an R-tree implementation for
spherical objects, while <literal>BRIN</literal> indexes are based on the "summarization"
of data blocks (<literal>pages</literal>) on physical storage in order to
organize data searches on ranges of summarized data that can be easily skipped
Copy link
Contributor

@esabolesabolAug 17, 2023•
edited
Loading

Choose a reason for hiding this comment

The reason will be displayed to describe this comment to others.Learn more.

Either change "data blocks" to "data block" or add an apostrophe after "data blocks" to make it possessive. "data block pages" or "data blocks' pages".

Copy link
ContributorAuthor

Choose a reason for hiding this comment

The reason will be displayed to describe this comment to others.Learn more.

@esabol The 'pages' word is written in the brackets. It is how the text is shown in the generated pdf: "summarization" of data blocks (pages) on physical storage...

I think the word pages is used here as the synonym of data blocks. Not sure, we should make this change.

Copy link
Contributor

Choose a reason for hiding this comment

The reason will be displayed to describe this comment to others.Learn more.

You're right. I didn't see the parentheses. Do not change this.

on the base of search filters (see <ulink
url="https://www.postgresql.org/docs/current/brin-intro.html"><citetitle>
PostgreSQL documentation</citetitle></ulink> for further details on BRIN indexes).
As a consequence, BRIN indexes are very small indexes (up to 1000 times smaller
than GiST ones), generally with lower performance compared with a GiST one,
but up to 100 times faster than a full sequential scan of a table performed
without any index. So BRIN indexes are particularly suitable in a big data context.
An index speeds up the execution time of searches based on operators <link
linkend="op.over"><literal>&lt;@</literal></link>, <link
linkend="op.over"><literal>@</literal></link>, <link
linkend="op.over"><literal>&amp;&amp;</literal></link>, <link
linkend="op.cross"><literal>&num;</literal></link>, <link
linkend="op.equal"><literal>=</literal></link>, and <link
linkend="op.equal"><literal>!=</literal></link>. You can create
an index with the following spherical data types:
linkend="op.equal"><literal>!=</literal></link>.
</para>
<para>
You can create a GiST index with the following spherical data types:
</para>
<itemizedlist>
<listitem>
Expand DownExpand Up@@ -55,6 +69,10 @@
</para>
</listitem>
</itemizedlist>
<para>
BRIN indexing supports just spherical points (<type>spoint</type>)
and spherical coordinates range (<type>sbox</type>) at the moment.
</para>
<example>
<title>Simple index of spherical points</title>
<programlisting>
Expand All@@ -65,6 +83,23 @@
<![CDATA[CREATE INDEX test_pos_idx ON test USING GIST (pos);]]>
<![CDATA[VACUUM ANALYZE test;]]>
</programlisting>
<para>
BRIN index can be created through the following syntax:
</para>
<programlisting>
<![CDATA[CREATE INDEX test_pos_idx USING BRIN ON test (pos);]]>
</programlisting>
<para>
By default, BRIN indexes summarize blocks of 128 pages. The smaller the
number of pages specified, the higher the granularity in searches,
and the gap in performance between GiST indexes and BRIN indexes will be
decreased. Note that the size of the BRIN indexes increases as well.
Different summarizations can be specified with the following
command:
</para>
<programlisting>
<![CDATA[CREATE INDEX test_pos_idx USING BRIN ON test (pos) WITH (pages_per_range = 16);]]>
</programlisting>
</example>

</sect2>
Expand Down
4 changes: 2 additions & 2 deletionsexpected/init_test_healpix.out.in
View file
Open in desktop
Original file line numberDiff line numberDiff line change
@@ -1,2 +1,2 @@
psql:pg_sphere.test.sql:9271: NOTICE: return type smoc is only a shell
psql:pg_sphere.test.sql:9277: NOTICE: argument type smoc is only a shell
psql:pg_sphere.test.sql:9684: NOTICE: return type smoc is only a shell
psql:pg_sphere.test.sql:9690: NOTICE: argument type smoc is only a shell
91 changes: 91 additions & 0 deletionsexpected/sbox_brin.out
View file
Open in desktop
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,91 @@
SELECT set_sphere_output_precision(8);
set_sphere_output_precision
-----------------------------
SET 8
(1 row)

CREATE TABLE test_boxes (
b sbox
);
COPY test_boxes (b) FROM stdin;
CREATE OR REPLACE FUNCTION qnodes(q text) RETURNS text
LANGUAGE 'plpgsql' AS
$$
DECLARE
exp TEXT;
mat TEXT[];
ret TEXT[];
BEGIN
FOR exp IN EXECUTE 'EXPLAIN ' || q
LOOP
--RAISE NOTICE 'EXP: %', exp;
mat := regexp_matches(exp, ' *(?:-> *)?(.*Scan on (test_boxes|test_boxes_brin_idx))');
--RAISE NOTICE 'MAT: %', mat;
IF mat IS NOT NULL THEN
ret := array_append(ret, mat[1]);
END IF;
--RAISE NOTICE 'RET: %', ret;
END LOOP;
RETURN array_to_string(ret,',');
END;
$$;
CREATE INDEX test_boxes_brin_idx ON test_boxes USING brin (b);
SET enable_indexscan = OFF;
SET enable_bitmapscan = OFF;
SET enable_seqscan = ON;
SELECT 'scan_seq', qnodes('SELECT * FROM test_boxes WHERE b <@ sbox ''( (10d,10d), (20d,20d) )''');
?column? | qnodes
----------+------------------------
scan_seq | Seq Scan on test_boxes
(1 row)

SELECT * FROM test_boxes WHERE b <@ sbox '( (10d,10d), (20d,20d) )';
b
---
(0 rows)

SELECT 'scan_seq', qnodes('SELECT * FROM test_boxes WHERE b && sbox ''( (10d,10d), (20d,20d) )''');
?column? | qnodes
----------+------------------------
scan_seq | Seq Scan on test_boxes
(1 row)

SELECT * FROM test_boxes WHERE b && sbox '( (10d,10d), (20d,20d) )';
b
--------------------------------------------------------
((0.34906585 , 0.17453293), (0.35006585 , 0.17463293))
(1 row)

SET enable_indexscan = OFF;
SET enable_bitmapscan = ON;
SET enable_seqscan = OFF;
SELECT 'scan_idx', qnodes('SELECT * FROM test_boxes WHERE b <@ sbox ''( (10d,10d), (20d,20d) )''');
?column? | qnodes
----------+-------------------------------------------------------------------------
scan_idx | Bitmap Heap Scan on test_boxes,Bitmap Index Scan on test_boxes_brin_idx
(1 row)

SELECT * FROM test_boxes WHERE b <@ sbox '( (10d,10d), (20d,20d) )';
b
---
(0 rows)

SELECT 'scan_idx', qnodes('SELECT * FROM test_boxes WHERE b && sbox ''( (10d,10d), (20d,20d) )''');
?column? | qnodes
----------+-------------------------------------------------------------------------
scan_idx | Bitmap Heap Scan on test_boxes,Bitmap Index Scan on test_boxes_brin_idx
(1 row)

SELECT * FROM test_boxes WHERE b && sbox '( (10d,10d), (20d,20d) )';
b
--------------------------------------------------------
((0.34906585 , 0.17453293), (0.35006585 , 0.17463293))
(1 row)

---- cleanup
DROP INDEX test_boxes_brin_idx;
DROP TABLE test_boxes;
DROP FUNCTION qnodes(text);
SET enable_indexscan = ON;
SET enable_bitmapscan = ON;
SET enable_seqscan = ON;
87 changes: 87 additions & 0 deletionsexpected/spoint_brin.out
View file
Open in desktop
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,87 @@
CREATE TABLE test_points (
p spoint
);
COPY test_points (p) FROM stdin;
CREATE OR REPLACE FUNCTION qnodes(q text) RETURNS text
LANGUAGE 'plpgsql' AS
$$
DECLARE
exp TEXT;
mat TEXT[];
ret TEXT[];
BEGIN
FOR exp IN EXECUTE 'EXPLAIN ' || q
LOOP
--RAISE NOTICE 'EXP: %', exp;
mat := regexp_matches(exp, ' *(?:-> *)?(.*Scan on (test_points|brin_spoint))');
--RAISE NOTICE 'MAT: %', mat;
IF mat IS NOT NULL THEN
ret := array_append(ret, mat[1]);
END IF;
--RAISE NOTICE 'RET: %', ret;
END LOOP;
RETURN array_to_string(ret,',');
END;
$$;
CREATE INDEX brin_spoint ON test_points USING brin (p) WITH (pages_per_range = 16);
set enable_indexscan = off;
set enable_bitmapscan = off;
set enable_seqscan = on;
SELECT 'scan_seq', qnodes('SELECT * FROM test_points WHERE p <@ sbox ''( (10d,10d), (20d,20d) )''');
?column? | qnodes
----------+-------------------------
scan_seq | Seq Scan on test_points
(1 row)

SELECT * FROM test_points WHERE p <@ sbox '( (10d,10d), (20d,20d) )';
p
-----------------------------------------
(0.349065850398866 , 0.174532925199433)
(1 row)

SELECT 'scan_seq', qnodes('SELECT * FROM test_points WHERE p && sbox ''( (10d,10d), (20d,20d) )''');
?column? | qnodes
----------+-------------------------
scan_seq | Seq Scan on test_points
(1 row)

SELECT * FROM test_points WHERE p && sbox '( (10d,10d), (20d,20d) )';
p
-----------------------------------------
(0.349065850398866 , 0.174532925199433)
(1 row)

set enable_indexscan = off;
set enable_bitmapscan = on;
set enable_seqscan = off;
SELECT 'scan_idx', qnodes('SELECT * FROM test_points WHERE p <@ sbox ''( (10d,10d), (20d,20d) )''');
?column? | qnodes
----------+------------------------------------------------------------------
scan_idx | Bitmap Heap Scan on test_points,Bitmap Index Scan on brin_spoint
(1 row)

SELECT * FROM test_points WHERE p <@ sbox '( (10d,10d), (20d,20d) )';
p
-----------------------------------------
(0.349065850398866 , 0.174532925199433)
(1 row)

SELECT 'scan_idx', qnodes('SELECT * FROM test_points WHERE p && sbox ''( (10d,10d), (20d,20d) )''');
?column? | qnodes
----------+------------------------------------------------------------------
scan_idx | Bitmap Heap Scan on test_points,Bitmap Index Scan on brin_spoint
(1 row)

SELECT * FROM test_points WHERE p && sbox '( (10d,10d), (20d,20d) )';
p
-----------------------------------------
(0.349065850398866 , 0.174532925199433)
(1 row)

-- cleanup
DROP INDEX brin_spoint;
DROP TABLE test_points;
DROP FUNCTION qnodes(text);
set enable_indexscan = on;
set enable_bitmapscan = on;
set enable_seqscan = on;
Loading
[8]ページ先頭
©2009-2025 Movatter.jp