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

Commit3a900cc

Browse files
author
Markus Nullmeier
committed
add simple crossmatch test
1 parente91555b commit3a900cc

File tree

8 files changed

+229
-1
lines changed

8 files changed

+229
-1
lines changed

‎Makefile

Lines changed: 2 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -8,7 +8,8 @@ EXTENSION = pg_sphere
88
DATA_built = pg_sphere--1.0.sql
99
DOCS = README.pg_sphere COPYRIGHT.pg_sphere
1010
REGRESS = init tables points euler circle line ellipse poly path box index\
11-
contains_ops contains_ops_compat bounding_box_gist gnomo
11+
contains_ops contains_ops_compat bounding_box_gist gnomo\
12+
crossmatch
1213

1314
EXTRA_CLEAN = pg_sphere--1.0.sql$(PGS_SQL)
1415

‎data/test_spherepointx.data

Lines changed: 24 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,24 @@
1+
(1.33,0.61)
2+
(-0.43,-0.17)
3+
(2.35,0.65)
4+
(-0.93,-0.29)
5+
(-0.99,0.92)
6+
(1.87,0.11)
7+
(1.43,0.82)
8+
(2.19,0.94)
9+
(0.13,0.34)
10+
(0.92,-0.19)
11+
(0.42,0.56)
12+
(-1.7,-0.77)
13+
(1.88,-0.45)
14+
(0.51,0.10)
15+
(0.54,0.26)
16+
(-2.94,1.04)
17+
(1.44,0.52)
18+
(0.38,0.03)
19+
(1.95,1.33)
20+
(3.11,0.63)
21+
(-1.84,-0.18)
22+
(-3.36,-0.55)
23+
(-2.42,0.06)
24+
\.

‎expected/crossmatch.out

Lines changed: 127 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,127 @@
1+
SET enable_indexscan = OFF;
2+
SET enable_seqscan = ON;
3+
EXPLAIN (COSTS OFF)
4+
SELECT count(*)
5+
FROM spheretmp1 t1 JOIN spheretmpx tx ON dist(t1.p, tx.p) < 0.03;
6+
QUERY PLAN
7+
--------------------------------------------------------------------
8+
Aggregate
9+
-> Nested Loop
10+
Join Filter: (dist(t1.p, tx.p) < '0.03'::double precision)
11+
-> Seq Scan on spheretmpx tx
12+
-> Materialize
13+
-> Seq Scan on spheretmp1 t1
14+
(6 rows)
15+
16+
SELECT count(*)
17+
FROM spheretmp1 t1 JOIN spheretmpx tx ON dist(t1.p, tx.p) < 0.03;
18+
count
19+
-------
20+
44
21+
(1 row)
22+
23+
SELECT DISTINCT concat_ws(', ', t1.p, tx.p) AS xm
24+
FROM spheretmp1 t1 JOIN spheretmpx tx ON dist(t1.p, tx.p) < 0.03 ORDER BY xm;
25+
xm
26+
--------------------------------------------------------
27+
(0.13 , 0.34), (0.13 , 0.34)
28+
(0.38 , 0.04), (0.38 , 0.03)
29+
(0.55 , 0.27), (0.54 , 0.26)
30+
(1.32 , 0.61), (1.33 , 0.61)
31+
(1.85 , -0.46), (1.88 , -0.45)
32+
(1.89 , 0.1), (1.87 , 0.11)
33+
(1.94 , 1.33), (1.95 , 1.33)
34+
(2.19 , 0.94), (2.19 , 0.94)
35+
(3.34318530717959 , 1.05), (3.34318530717959 , 1.04)
36+
(3.87318530717959 , 0.05), (3.86318530717959 , 0.06)
37+
(5.88318530717959 , -0.17), (5.85318530717959 , -0.17)
38+
(11 rows)
39+
40+
SET enable_seqscan = OFF;
41+
SET enable_indexscan = ON;
42+
CREATE INDEX idx_spoint1_1 ON spheretmp1 USING gist (p spoint);
43+
CREATE INDEX idx_spoint1_x ON spheretmpx USING gist (p spoint);
44+
ANALYZE spheretmp1;
45+
ANALYZE spheretmpx;
46+
EXPLAIN (COSTS OFF)
47+
SELECT count(*)
48+
FROM spheretmp1 t1 JOIN spheretmpx tx ON t1.p <@ scircle(tx.p, 0.03);
49+
QUERY PLAN
50+
--------------------------------------------------------------------------
51+
Aggregate
52+
-> Nested Loop
53+
-> Seq Scan on spheretmpx tx
54+
-> Index Scan using idx_spoint1_1 on spheretmp1 t1
55+
Index Cond: (p <@ scircle(tx.p, '0.03'::double precision))
56+
(5 rows)
57+
58+
SELECT count(*)
59+
FROM spheretmp1 t1 JOIN spheretmpx tx ON t1.p <@ scircle(tx.p, 0.03);
60+
count
61+
-------
62+
44
63+
(1 row)
64+
65+
SELECT DISTINCT concat_ws(', ', t1.p, tx.p) AS xm
66+
FROM spheretmp1 t1 JOIN spheretmpx tx ON t1.p <@ scircle(tx.p, 0.03) ORDER BY xm;
67+
xm
68+
--------------------------------------------------------
69+
(0.13 , 0.34), (0.13 , 0.34)
70+
(0.38 , 0.04), (0.38 , 0.03)
71+
(0.55 , 0.27), (0.54 , 0.26)
72+
(1.32 , 0.61), (1.33 , 0.61)
73+
(1.85 , -0.46), (1.88 , -0.45)
74+
(1.89 , 0.1), (1.87 , 0.11)
75+
(1.94 , 1.33), (1.95 , 1.33)
76+
(2.19 , 0.94), (2.19 , 0.94)
77+
(3.34318530717959 , 1.05), (3.34318530717959 , 1.04)
78+
(3.87318530717959 , 0.05), (3.86318530717959 , 0.06)
79+
(5.88318530717959 , -0.17), (5.85318530717959 , -0.17)
80+
(11 rows)
81+
82+
DROP INDEX idx_spoint1_1;
83+
DROP INDEX idx_spoint1_x;
84+
CREATE INDEX idx_spoint2_1 ON spheretmp1 USING gist (p spoint2);
85+
CREATE INDEX idx_spoint2_x ON spheretmpx USING gist (p spoint2);
86+
ANALYZE spheretmp1;
87+
ANALYZE spheretmpx;
88+
EXPLAIN (COSTS OFF)
89+
SELECT count(*)
90+
FROM spheretmp1 t1 JOIN spheretmpx tx ON dist(t1.p, tx.p) < 0.03;
91+
QUERY PLAN
92+
------------------------------------
93+
Aggregate
94+
-> Custom Scan (CrossmatchJoin)
95+
Outer index: idx_spoint2_1
96+
Inner index: idx_spoint2_x
97+
Threshold: 0.03
98+
(5 rows)
99+
100+
SELECT count(*)
101+
FROM spheretmp1 t1 JOIN spheretmpx tx ON dist(t1.p, tx.p) < 0.03;
102+
count
103+
-------
104+
44
105+
(1 row)
106+
107+
SELECT DISTINCT concat_ws(', ', t1.p, tx.p) AS xm
108+
FROM spheretmp1 t1 JOIN spheretmpx tx ON dist(t1.p, tx.p) < 0.03 ORDER BY xm;
109+
xm
110+
--------------------------------------------------------
111+
(0.13 , 0.34), (0.13 , 0.34)
112+
(0.38 , 0.04), (0.38 , 0.03)
113+
(0.55 , 0.27), (0.54 , 0.26)
114+
(1.32 , 0.61), (1.33 , 0.61)
115+
(1.85 , -0.46), (1.88 , -0.45)
116+
(1.89 , 0.1), (1.87 , 0.11)
117+
(1.94 , 1.33), (1.95 , 1.33)
118+
(2.19 , 0.94), (2.19 , 0.94)
119+
(3.34318530717959 , 1.05), (3.34318530717959 , 1.04)
120+
(3.87318530717959 , 0.05), (3.86318530717959 , 0.06)
121+
(5.88318530717959 , -0.17), (5.85318530717959 , -0.17)
122+
(11 rows)
123+
124+
SET enable_seqscan = ON;
125+
SET enable_indexscan = ON;
126+
DROP INDEX idx_spoint2_1;
127+
DROP INDEX idx_spoint2_x;

‎expected/index.out

Lines changed: 5 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -108,3 +108,8 @@ SELECT count(*) FROM spheretmp4 WHERE l && scircle '<(1,1),0.3>' ;
108108
40
109109
(1 row)
110110

111+
DROP INDEX aaaidx;
112+
DROP INDEX bbbidx;
113+
DROP INDEX cccidx;
114+
DROP INDEX dddidx;
115+
SET enable_seqscan = ON;

‎expected/tables.out

Lines changed: 2 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -5,6 +5,8 @@ CREATE TABLE spheretmp1 (p spoint);
55
\copy spheretmp1 from 'data/test_spherepoint.data'
66
\copy spheretmp1 from 'data/test_spherepoint.data'
77
\copy spheretmp1 from 'data/test_spherepoint.data'
8+
CREATE TABLE spheretmpx (p spoint);
9+
\copy spheretmpx from 'data/test_spherepointx.data'
810
CREATE TABLE spheretmp2 (c scircle);
911
\copy spheretmp2 from 'data/test_spherecircle.data'
1012
\copy spheretmp2 from 'data/test_spherecircle.data'

‎sql/crossmatch.sql

Lines changed: 60 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,60 @@
1+
SET enable_indexscan= OFF;
2+
SET enable_seqscan=ON;
3+
4+
EXPLAIN (COSTS OFF)
5+
SELECTcount(*)
6+
FROM spheretmp1 t1JOIN spheretmpx txON dist(t1.p,tx.p)<0.03;
7+
8+
SELECTcount(*)
9+
FROM spheretmp1 t1JOIN spheretmpx txON dist(t1.p,tx.p)<0.03;
10+
11+
SELECT DISTINCT concat_ws(',',t1.p,tx.p)AS xm
12+
FROM spheretmp1 t1JOIN spheretmpx txON dist(t1.p,tx.p)<0.03ORDER BY xm;
13+
14+
15+
16+
SET enable_seqscan= OFF;
17+
SET enable_indexscan=ON;
18+
19+
CREATEINDEXidx_spoint1_1ON spheretmp1 USING gist (p spoint);
20+
CREATEINDEXidx_spoint1_xON spheretmpx USING gist (p spoint);
21+
22+
ANALYZE spheretmp1;
23+
ANALYZE spheretmpx;
24+
25+
EXPLAIN (COSTS OFF)
26+
SELECTcount(*)
27+
FROM spheretmp1 t1JOIN spheretmpx txONt1.p<@ scircle(tx.p,0.03);
28+
29+
SELECTcount(*)
30+
FROM spheretmp1 t1JOIN spheretmpx txONt1.p<@ scircle(tx.p,0.03);
31+
32+
SELECT DISTINCT concat_ws(',',t1.p,tx.p)AS xm
33+
FROM spheretmp1 t1JOIN spheretmpx txONt1.p<@ scircle(tx.p,0.03)ORDER BY xm;
34+
35+
DROPINDEX idx_spoint1_1;
36+
DROPINDEX idx_spoint1_x;
37+
38+
39+
40+
CREATEINDEXidx_spoint2_1ON spheretmp1 USING gist (p spoint2);
41+
CREATEINDEXidx_spoint2_xON spheretmpx USING gist (p spoint2);
42+
43+
ANALYZE spheretmp1;
44+
ANALYZE spheretmpx;
45+
46+
EXPLAIN (COSTS OFF)
47+
SELECTcount(*)
48+
FROM spheretmp1 t1JOIN spheretmpx txON dist(t1.p,tx.p)<0.03;
49+
50+
SELECTcount(*)
51+
FROM spheretmp1 t1JOIN spheretmpx txON dist(t1.p,tx.p)<0.03;
52+
53+
SELECT DISTINCT concat_ws(',',t1.p,tx.p)AS xm
54+
FROM spheretmp1 t1JOIN spheretmpx txON dist(t1.p,tx.p)<0.03ORDER BY xm;
55+
56+
SET enable_seqscan=ON;
57+
SET enable_indexscan=ON;
58+
59+
DROPINDEX idx_spoint2_1;
60+
DROPINDEX idx_spoint2_x;

‎sql/index.sql

Lines changed: 6 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -51,3 +51,9 @@ SELECT count(*) FROM spheretmp4 WHERE l @ scircle '<(1,1),0.3>' ;
5151

5252
SELECTcount(*)FROM spheretmp4WHERE l && scircle'<(1,1),0.3>' ;
5353

54+
DROPINDEX aaaidx;
55+
DROPINDEX bbbidx;
56+
DROPINDEX cccidx;
57+
DROPINDEX dddidx;
58+
59+
SET enable_seqscan=ON;

‎sql/tables.sql

Lines changed: 3 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -9,6 +9,9 @@ CREATE TABLE spheretmp1 (p spoint);
99
\copy spheretmp1from'data/test_spherepoint.data'
1010
\copy spheretmp1from'data/test_spherepoint.data'
1111

12+
CREATETABLEspheretmpx (p spoint);
13+
\copy spheretmpxfrom'data/test_spherepointx.data'
14+
1215
CREATETABLEspheretmp2 (c scircle);
1316

1417
\copy spheretmp2from'data/test_spherecircle.data'

0 commit comments

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