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

Commit339ed93

Browse files
authored
Merge pull request#102 from cybertec-postgresql/hash-opclass
Hash opclass for spoint
2 parentsb4a6fe4 +035cfe9 commit339ed93

File tree

7 files changed

+109
-2
lines changed

7 files changed

+109
-2
lines changed

‎Makefile

Lines changed: 2 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -55,7 +55,7 @@ CRUSH_TESTS = init_extended circle_extended
5555
PGS_SQL = pgs_types.sql pgs_point.sql pgs_euler.sql pgs_circle.sql\
5656
pgs_line.sql pgs_ellipse.sql pgs_polygon.sql pgs_path.sql\
5757
pgs_box.sql pgs_contains_ops.sql pgs_contains_ops_compat.sql\
58-
pgs_gist.sql gnomo.sql pgs_brin.sql pgs_circle_sel.sql
58+
pgs_gist.sql gnomo.sql pgs_brin.sql pgs_circle_sel.sql pgs_hash.sql
5959

6060
ifneq ($(USE_HEALPIX),0)
6161
TESTS += healpix moc moc1 moc100 mocautocast
@@ -199,7 +199,7 @@ ifeq ($(has_index_options),y)
199199
pg_sphere--1.3.1--1.3.2.sql: pgs_moc_options.sql.in
200200
endif
201201
endif
202-
pg_sphere--1.3.1--1.3.2.sql: pgs_circle_sel.sql.in
202+
pg_sphere--1.3.1--1.3.2.sql: pgs_circle_sel.sql.in pgs_hash.sql.in
203203
cat upgrade_scripts/$@.in$^>$@
204204

205205
# end of local stuff

‎doc/indices.sgm

Lines changed: 25 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -153,4 +153,29 @@
153153

154154
</sect1>
155155

156+
<sect1 id="ind.hash">
157+
<title>
158+
Hash index
159+
</title>
160+
<para>
161+
Values of type <type>spoint</type> can be indexed using a
162+
<literal>HASH</literal> index, supporting the
163+
<link linkend="op.equal"><literal>=</literal></link> operator.
164+
The <literal>spoint_hash_ops</literal> operator class also enables
165+
<literal>DISTINCT</literal> queries on <type>spoint</type>.
166+
</para>
167+
<example>
168+
<title>Hash index of spoints</title>
169+
<programlisting>
170+
<![CDATA[CREATE TABLE sky (]]>
171+
<![CDATA[ star spoint]]>
172+
<![CDATA[);]]>
173+
<![CDATA[CREATE INDEX ON sky USING HASH (star);]]>
174+
<![CDATA[]]>
175+
<![CDATA[SELECT DISTINCT star FROM sky;]]>
176+
</programlisting>
177+
</example>
178+
179+
</sect1>
180+
156181
</chapter>

‎expected/index.out

Lines changed: 36 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -195,3 +195,39 @@ EXPLAIN (COSTS OFF) SELECT count(*) FROM spheretmp1b WHERE p = spoint '(3.09 , 1
195195
4
196196
(1 row)
197197

198+
-- test hash opclass
199+
CREATE TABLE spheretmp1c AS TABLE spheretmp1;
200+
SELECT p FROM spheretmp1c WHERE p <@ scircle '<(1,1),0.2>' ORDER BY p::text;
201+
p
202+
---------------
203+
(0.67 , 0.97)
204+
(0.67 , 0.97)
205+
(0.67 , 0.97)
206+
(0.67 , 0.97)
207+
(1.07 , 1.09)
208+
(1.07 , 1.09)
209+
(1.07 , 1.09)
210+
(1.07 , 1.09)
211+
(1.24 , 0.95)
212+
(1.24 , 0.95)
213+
(1.24 , 0.95)
214+
(1.24 , 0.95)
215+
(12 rows)
216+
217+
WITH points AS (SELECT DISTINCT p FROM spheretmp1c WHERE p <@ scircle '<(1,1),0.2>')
218+
SELECT p FROM points ORDER BY p::text;
219+
p
220+
---------------
221+
(0.67 , 0.97)
222+
(1.07 , 1.09)
223+
(1.24 , 0.95)
224+
(3 rows)
225+
226+
CREATE INDEX spheretmp1c_hash_idx ON spheretmp1c USING hash(p);
227+
EXPLAIN (COSTS OFF) SELECT * FROM spheretmp1c WHERE p = '(0.67 , 0.97)';
228+
QUERY PLAN
229+
------------------------------------------------------
230+
Index Scan using spheretmp1c_hash_idx on spheretmp1c
231+
Index Cond: (p = '(0.67 , 0.97)'::spoint)
232+
(2 rows)
233+

‎pgs_hash.sql.in

Lines changed: 19 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,19 @@
1+
CREATE FUNCTION spoint_hash32 (spoint)
2+
RETURNS int
3+
IMMUTABLE STRICT
4+
PARALLEL SAFE
5+
LANGUAGE C
6+
AS 'MODULE_PATHNAME', 'spherepoint_hash32';
7+
8+
UPDATE pg_operator
9+
SET oprcanhash = true
10+
WHERE oprname = '=' AND
11+
oprleft = 'spoint'::regtype AND oprright = 'spoint'::regtype;
12+
13+
/* PG17: ALTER OPERATOR = (spoint, spoint) SET (HASHES); */
14+
15+
CREATE OPERATOR CLASS spoint_hash_ops
16+
DEFAULT FOR TYPE spoint USING hash
17+
AS
18+
OPERATOR 1 = (spoint, spoint),
19+
FUNCTION 1 spoint_hash32(spoint);

‎sql/index.sql

Lines changed: 11 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -76,3 +76,14 @@ EXPLAIN (COSTS OFF) SELECT count(*) FROM spheretmp1b WHERE p <@ scircle '<(1,1),
7676
SELECTcount(*)FROM spheretmp1bWHERE p<@ scircle'<(1,1),0.3>';
7777
EXPLAIN (COSTS OFF)SELECTcount(*)FROM spheretmp1bWHERE p= spoint'(3.09 , 1.25)';
7878
SELECTcount(*)FROM spheretmp1bWHERE p= spoint'(3.09 , 1.25)';
79+
80+
-- test hash opclass
81+
82+
CREATETABLEspheretmp1cAS TABLE spheretmp1;
83+
84+
SELECT pFROM spheretmp1cWHERE p<@ scircle'<(1,1),0.2>'ORDER BY p::text;
85+
WITH pointsAS (SELECT DISTINCT pFROM spheretmp1cWHERE p<@ scircle'<(1,1),0.2>')
86+
SELECT pFROM pointsORDER BY p::text;
87+
88+
CREATEINDEXspheretmp1c_hash_idxON spheretmp1c USING hash(p);
89+
EXPLAIN (COSTS OFF)SELECT*FROM spheretmp1cWHERE p='(0.67 , 0.97)';

‎src/point.c

Lines changed: 11 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -16,6 +16,7 @@ PG_FUNCTION_INFO_V1(spherepoint_y);
1616
PG_FUNCTION_INFO_V1(spherepoint_z);
1717
PG_FUNCTION_INFO_V1(spherepoint_xyz);
1818
PG_FUNCTION_INFO_V1(spherepoint_equal);
19+
PG_FUNCTION_INFO_V1(spherepoint_hash32);
1920

2021
staticOidpoint_id=InvalidOid;
2122

@@ -309,3 +310,13 @@ spherepoint_equal(PG_FUNCTION_ARGS)
309310

310311
PG_RETURN_BOOL(spoint_eq(p1,p2));
311312
}
313+
314+
Datum
315+
spherepoint_hash32(PG_FUNCTION_ARGS)
316+
{
317+
SPoint*p1= (SPoint*)PG_GETARG_POINTER(0);
318+
Datumh1=DirectFunctionCall1(hashfloat8,p1->lat);
319+
Datumh2=DirectFunctionCall1(hashfloat8,p1->lng);
320+
321+
PG_RETURN_INT32(DatumGetInt32(h1) ^DatumGetInt32(h2));
322+
}

‎src/point.h

Lines changed: 5 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -102,4 +102,9 @@ Datumspherepoint_xyz(PG_FUNCTION_ARGS);
102102
*/
103103
Datumspherepoint_equal(PG_FUNCTION_ARGS);
104104

105+
/*
106+
* Compute a 32-bit hash value of a point.
107+
*/
108+
Datumspherepoint_hash32(PG_FUNCTION_ARGS);
109+
105110
#endif

0 commit comments

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