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

Hash opclass for spoint#102

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
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
4 changes: 2 additions & 2 deletionsMakefile
View file
Open in desktop
Original file line numberDiff line numberDiff line change
Expand Up@@ -55,7 +55,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_brin.sql pgs_circle_sel.sql
pgs_gist.sql gnomo.sql pgs_brin.sql pgs_circle_sel.sql pgs_hash.sql

ifneq ($(USE_HEALPIX),0)
TESTS += healpix moc moc1 moc100 mocautocast
Expand DownExpand Up@@ -199,7 +199,7 @@ ifeq ($(has_index_options),y)
pg_sphere--1.3.1--1.3.2.sql: pgs_moc_options.sql.in
endif
endif
pg_sphere--1.3.1--1.3.2.sql: pgs_circle_sel.sql.in
pg_sphere--1.3.1--1.3.2.sql: pgs_circle_sel.sql.in pgs_hash.sql.in
cat upgrade_scripts/$@.in $^ > $@

# end of local stuff
Expand Down
25 changes: 25 additions & 0 deletionsdoc/indices.sgm
View file
Open in desktop
Original file line numberDiff line numberDiff line change
Expand Up@@ -153,4 +153,29 @@

</sect1>

<sect1 id="ind.hash">
<title>
Hash index
</title>
<para>
Values of type <type>spoint</type> can be indexed using a
<literal>HASH</literal> index, supporting the
<link linkend="op.equal"><literal>=</literal></link> operator.
The <literal>spoint_hash_ops</literal> operator class also enables
<literal>DISTINCT</literal> queries on <type>spoint</type>.
</para>
<example>
<title>Hash index of spoints</title>
<programlisting>
<![CDATA[CREATE TABLE sky (]]>
<![CDATA[ star spoint]]>
<![CDATA[);]]>
<![CDATA[CREATE INDEX ON sky USING HASH (star);]]>
<![CDATA[]]>
<![CDATA[SELECT DISTINCT star FROM sky;]]>
</programlisting>
</example>

</sect1>

</chapter>
36 changes: 36 additions & 0 deletionsexpected/index.out
View file
Open in desktop
Original file line numberDiff line numberDiff line change
Expand Up@@ -195,3 +195,39 @@ EXPLAIN (COSTS OFF) SELECT count(*) FROM spheretmp1b WHERE p = spoint '(3.09 , 1
4
(1 row)

-- test hash opclass
CREATE TABLE spheretmp1c AS TABLE spheretmp1;
SELECT p FROM spheretmp1c WHERE p <@ scircle '<(1,1),0.2>' ORDER BY p::text;
p
---------------
(0.67 , 0.97)
(0.67 , 0.97)
(0.67 , 0.97)
(0.67 , 0.97)
(1.07 , 1.09)
(1.07 , 1.09)
(1.07 , 1.09)
(1.07 , 1.09)
(1.24 , 0.95)
(1.24 , 0.95)
(1.24 , 0.95)
(1.24 , 0.95)
(12 rows)

WITH points AS (SELECT DISTINCT p FROM spheretmp1c WHERE p <@ scircle '<(1,1),0.2>')
SELECT p FROM points ORDER BY p::text;
p
---------------
(0.67 , 0.97)
(1.07 , 1.09)
(1.24 , 0.95)
(3 rows)

CREATE INDEX spheretmp1c_hash_idx ON spheretmp1c USING hash(p);
EXPLAIN (COSTS OFF) SELECT * FROM spheretmp1c WHERE p = '(0.67 , 0.97)';
QUERY PLAN
------------------------------------------------------
Index Scan using spheretmp1c_hash_idx on spheretmp1c
Index Cond: (p = '(0.67 , 0.97)'::spoint)
(2 rows)

19 changes: 19 additions & 0 deletionspgs_hash.sql.in
View file
Open in desktop
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,19 @@
CREATE FUNCTION spoint_hash32 (spoint)
RETURNS int
IMMUTABLE STRICT
PARALLEL SAFE
LANGUAGE C
AS 'MODULE_PATHNAME', 'spherepoint_hash32';

UPDATE pg_operator
SET oprcanhash = true
WHERE oprname = '=' AND
oprleft = 'spoint'::regtype AND oprright = 'spoint'::regtype;

/* PG17: ALTER OPERATOR = (spoint, spoint) SET (HASHES); */

CREATE OPERATOR CLASS spoint_hash_ops
DEFAULT FOR TYPE spoint USING hash
AS
OPERATOR 1 = (spoint, spoint),
FUNCTION 1 spoint_hash32(spoint);
11 changes: 11 additions & 0 deletionssql/index.sql
View file
Open in desktop
Original file line numberDiff line numberDiff line change
Expand Up@@ -76,3 +76,14 @@ EXPLAIN (COSTS OFF) SELECT count(*) FROM spheretmp1b WHERE p <@ scircle '<(1,1),
SELECT count(*) FROM spheretmp1b WHERE p <@ scircle '<(1,1),0.3>';
EXPLAIN (COSTS OFF) SELECT count(*) FROM spheretmp1b WHERE p = spoint '(3.09 , 1.25)';
SELECT count(*) FROM spheretmp1b WHERE p = spoint '(3.09 , 1.25)';

-- test hash opclass

CREATE TABLE spheretmp1c AS TABLE spheretmp1;

SELECT p FROM spheretmp1c WHERE p <@ scircle '<(1,1),0.2>' ORDER BY p::text;
WITH points AS (SELECT DISTINCT p FROM spheretmp1c WHERE p <@ scircle '<(1,1),0.2>')
SELECT p FROM points ORDER BY p::text;

CREATE INDEX spheretmp1c_hash_idx ON spheretmp1c USING hash(p);
EXPLAIN (COSTS OFF) SELECT * FROM spheretmp1c WHERE p = '(0.67 , 0.97)';
11 changes: 11 additions & 0 deletionssrc/point.c
View file
Open in desktop
Original file line numberDiff line numberDiff line change
Expand Up@@ -16,6 +16,7 @@ PG_FUNCTION_INFO_V1(spherepoint_y);
PG_FUNCTION_INFO_V1(spherepoint_z);
PG_FUNCTION_INFO_V1(spherepoint_xyz);
PG_FUNCTION_INFO_V1(spherepoint_equal);
PG_FUNCTION_INFO_V1(spherepoint_hash32);

static Oid point_id = InvalidOid;

Expand DownExpand Up@@ -309,3 +310,13 @@ spherepoint_equal(PG_FUNCTION_ARGS)

PG_RETURN_BOOL(spoint_eq(p1, p2));
}

Datum
spherepoint_hash32(PG_FUNCTION_ARGS)
{
SPoint *p1 = (SPoint *) PG_GETARG_POINTER(0);
Datumh1 = DirectFunctionCall1(hashfloat8, p1->lat);
Datumh2 = DirectFunctionCall1(hashfloat8, p1->lng);

PG_RETURN_INT32(DatumGetInt32(h1) ^ DatumGetInt32(h2));
}
5 changes: 5 additions & 0 deletionssrc/point.h
View file
Open in desktop
Original file line numberDiff line numberDiff line change
Expand Up@@ -102,4 +102,9 @@ Datumspherepoint_xyz(PG_FUNCTION_ARGS);
*/
Datumspherepoint_equal(PG_FUNCTION_ARGS);

/*
* Compute a 32-bit hash value of a point.
*/
Datumspherepoint_hash32(PG_FUNCTION_ARGS);

#endif
[8]ページ先頭
©2009-2025 Movatter.jp