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

Commit048ecad

Browse files
author
Vitaly Davydov
committed
Add test for sbox BRIN index
1 parentcc67320 commit048ecad

File tree

3 files changed

+223
-1
lines changed

3 files changed

+223
-1
lines changed

‎Makefile

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -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 spoint_brin
46+
epochprop contains overlaps spoint_brin sbox_brin
4747

4848
ifneq ($(USE_HEALPIX),0)
4949
TESTS += healpix moc mocautocast

‎expected/sbox_brin.out

Lines changed: 86 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,86 @@
1+
CREATE TABLE test_boxes (
2+
b sbox
3+
);
4+
COPY test_boxes (b) 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)');
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 test_boxes_idx ON test_boxes USING brin (b) 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_boxes WHERE b <@ sbox ''( (10d,10d), (20d,20d) )''');
31+
?column? | qnodes
32+
----------+----------
33+
scan_seq | Seq Scan
34+
(1 row)
35+
36+
SELECT * FROM test_boxes WHERE b <@ sbox '( (10d,10d), (20d,20d) )';
37+
b
38+
---
39+
(0 rows)
40+
41+
SELECT 'scan_seq', qnodes('SELECT * FROM test_boxes WHERE b && sbox ''( (10d,10d), (20d,20d) )''');
42+
?column? | qnodes
43+
----------+----------
44+
scan_seq | Seq Scan
45+
(1 row)
46+
47+
SELECT * FROM test_boxes WHERE b && sbox '( (10d,10d), (20d,20d) )';
48+
b
49+
------------------------------------------------------------------------------------
50+
((0.349065850398866 , 0.174532925199433), (0.350065850398866 , 0.174632925199433))
51+
(1 row)
52+
53+
set enable_indexscan = off;
54+
set enable_bitmapscan = on;
55+
set enable_seqscan = off;
56+
SELECT 'scan_idx', qnodes('SELECT * FROM test_boxes WHERE b <@ sbox ''( (10d,10d), (20d,20d) )''');
57+
?column? | qnodes
58+
----------+----------
59+
scan_idx | Seq Scan
60+
(1 row)
61+
62+
SELECT * FROM test_boxes WHERE b <@ sbox '( (10d,10d), (20d,20d) )';
63+
b
64+
---
65+
(0 rows)
66+
67+
SELECT 'scan_idx', qnodes('SELECT * FROM test_boxes WHERE b && sbox ''( (10d,10d), (20d,20d) )''');
68+
?column? | qnodes
69+
----------+----------
70+
scan_idx | Seq Scan
71+
(1 row)
72+
73+
SELECT * FROM test_boxes WHERE b && sbox '( (10d,10d), (20d,20d) )';
74+
b
75+
------------------------------------------------------------------------------------
76+
((0.349065850398866 , 0.174532925199433), (0.350065850398866 , 0.174632925199433))
77+
(1 row)
78+
79+
---- cleanup
80+
DROP INDEX brin_sbox;
81+
ERROR: index "brin_sbox" does not exist
82+
DROP TABLE test_boxes;
83+
DROP FUNCTION qnodes(text);
84+
set enable_indexscan = on;
85+
set enable_bitmapscan = on;
86+
set enable_seqscan = on;

‎sql/sbox_brin.sql

Lines changed: 136 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,136 @@
1+
CREATETABLEtest_boxes (
2+
b sbox
3+
);
4+
5+
COPY test_boxes (b)FROM stdin;
6+
( (0.349065850398866,0.174532925199433), (0.350065850398866,0.174632925199433) )
7+
( (1.59875999207035,0.771416330759722), (1.5997599920703498,0.771516330759722) )
8+
( (1.59876348272885,0.77141458543047), (1.5997634827288498,0.77151458543047) )
9+
( (1.59876697338736,0.771412840101218), (1.5997669733873598,0.771512840101218) )
10+
( (1.59877046404586,0.771411094771966), (1.5997704640458599,0.771511094771966) )
11+
( (1.59877395470437,0.771409349442714), (1.59977395470437,0.771509349442714) )
12+
( (1.59877744536287,0.771407604113461), (1.59977744536287,0.771507604113461) )
13+
( (1.59878093602137,0.77140585878421), (1.59978093602137,0.77150585878421) )
14+
( (1.59878442667988,0.771404113454958), (1.59978442667988,0.771504113454958) )
15+
( (1.59878791733838,0.771402368125706), (1.59978791733838,0.7715023681257059) )
16+
( (1.59879140799689,0.771400622796454), (1.5997914079968898,0.771500622796454) )
17+
( (1.59879489865539,0.771398877467202), (1.5997948986553898,0.771498877467202) )
18+
( (1.59879838931389,0.77139713213795), (1.5997983893138898,0.77149713213795) )
19+
( (1.5988018799724,0.771395386808698), (1.5998018799723999,0.771495386808698) )
20+
( (1.5988053706309,0.771393641479446), (1.5998053706309,0.771493641479446) )
21+
( (1.59880886128941,0.771391896150194), (1.59980886128941,0.771491896150194) )
22+
( (1.59881235194791,0.771390150820941), (1.59981235194791,0.771490150820941) )
23+
( (1.59881584260641,0.77138840549169), (1.59981584260641,0.77148840549169) )
24+
( (1.59881933326492,0.771386660162438), (1.59981933326492,0.7714866601624379) )
25+
( (1.59882282392342,0.771384914833186), (1.5998228239234198,0.771484914833186) )
26+
( (1.59882631458193,0.771383169503934), (1.5998263145819298,0.771483169503934) )
27+
( (1.59882980524043,0.771381424174682), (1.5998298052404298,0.771481424174682) )
28+
( (1.59883329589893,0.77137967884543), (1.5998332958989299,0.77147967884543) )
29+
( (1.59883678655744,0.771377933516178), (1.5998367865574399,0.771477933516178) )
30+
( (1.59884027721594,0.771376188186926), (1.59984027721594,0.771476188186926) )
31+
( (1.59884376787445,0.771374442857673), (1.59984376787445,0.771474442857673) )
32+
( (1.59884725853295,0.771372697528422), (1.59984725853295,0.771472697528422) )
33+
( (1.59885074919145,0.77137095219917), (1.59985074919145,0.77147095219917) )
34+
( (1.59885423984996,0.771369206869918), (1.5998542398499598,0.7714692068699179) )
35+
( (1.59885773050846,0.771367461540666), (1.5998577305084598,0.771467461540666) )
36+
( (1.59886122116697,0.771365716211414), (1.5998612211669698,0.771465716211414) )
37+
( (1.59886471182547,0.771363970882162), (1.5998647118254699,0.771463970882162) )
38+
( (1.59886820248397,0.77136222555291), (1.5998682024839699,0.77146222555291) )
39+
( (1.59887169314248,0.771360480223658), (1.59987169314248,0.771460480223658) )
40+
( (1.59887518380098,0.771358734894406), (1.59987518380098,0.771458734894406) )
41+
( (1.59887867445948,0.771356989565154), (1.59987867445948,0.771456989565154) )
42+
( (1.59888216511799,0.771355244235902), (1.59988216511799,0.771455244235902) )
43+
( (1.59888565577649,0.77135349890665), (1.5998856557764898,0.7714534989066499) )
44+
( (1.598889146435,0.771351753577398), (1.5998891464349998,0.771451753577398) )
45+
( (1.5988926370935,0.771350008248146), (1.5998926370934998,0.771450008248146) )
46+
( (1.59889612775201,0.771348262918894), (1.5998961277520098,0.771448262918894) )
47+
( (1.59889961841051,0.771346517589642), (1.5998996184105099,0.771446517589642) )
48+
( (1.59890310906901,0.77134477226039), (1.59990310906901,0.77144477226039) )
49+
( (1.59890659972752,0.771343026931138), (1.59990659972752,0.771443026931138) )
50+
( (1.59891009038602,0.771341281601886), (1.59991009038602,0.771441281601886) )
51+
( (1.59891358104452,0.771339536272634), (1.59991358104452,0.771439536272634) )
52+
( (1.59891707170303,0.771337790943382), (1.59991707170303,0.7714377909433819) )
53+
( (1.59892056236153,0.77133604561413), (1.5999205623615298,0.77143604561413) )
54+
( (1.59892405302004,0.771334300284878), (1.5999240530200398,0.771434300284878) )
55+
( (1.59892754367854,0.771332554955626), (1.5999275436785398,0.771432554955626) )
56+
( (1.59893103433705,0.771330809626374), (1.5999310343370499,0.771430809626374) )
57+
( (1.59893452499555,0.771329064297122), (1.59993452499555,0.771429064297122) )
58+
( (1.59893801565405,0.77132731896787), (1.59993801565405,0.77142731896787) )
59+
( (1.59894150631256,0.771325573638618), (1.59994150631256,0.771425573638618) )
60+
( (1.59894499697106,0.771323828309366), (1.59994499697106,0.771423828309366) )
61+
( (1.59894848762957,0.771322082980114), (1.59994848762957,0.7714220829801139) )
62+
( (1.59895197828807,0.771320337650862), (1.5999519782880698,0.771420337650862) )
63+
( (1.59895546894657,0.77131859232161), (1.5999554689465698,0.77141859232161) )
64+
( (3.61121537674092,-1.33598496521933), (3.6122153767409197,-1.33588496521933) )
65+
( (3.61121886739942,-1.33598321989008), (3.6122188673994198,-1.33588321989008) )
66+
( (3.61122235805792,-1.33598147456083), (3.61222235805792,-1.33588147456083) )
67+
( (3.61122584871643,-1.33597972923158), (3.61222584871643,-1.33587972923158) )
68+
( (3.61122933937493,-1.33597798390233), (3.61222933937493,-1.33587798390233) )
69+
( (3.61123283003344,-1.33597623857307), (3.61223283003344,-1.33587623857307) )
70+
( (3.61123632069194,-1.33597449324382), (3.61223632069194,-1.33587449324382) )
71+
( (3.61123981135044,-1.33597274791457), (3.61223981135044,-1.33587274791457) )
72+
( (3.61124330200895,-1.33597100258532), (3.6122433020089497,-1.33587100258532) )
73+
( (3.61124679266745,-1.33596925725607), (3.6122467926674497,-1.33586925725607) )
74+
( (3.61125028332595,-1.33596751192682), (3.6122502833259498,-1.33586751192682) )
75+
( (3.61125377398446,-1.33596576659756), (3.61225377398446,-1.33586576659756) )
76+
( (3.61125726464296,-1.33596402126831), (3.61225726464296,-1.33586402126831) )
77+
( (3.61126075530147,-1.33596227593906), (3.61226075530147,-1.33586227593906) )
78+
( (3.61126424595997,-1.33596053060981), (3.61226424595997,-1.33586053060981) )
79+
( (3.61126773661847,-1.33595878528056), (3.61226773661847,-1.33585878528056) )
80+
( (3.61127122727698,-1.3359570399513), (3.6122712272769797,-1.3358570399513001) )
81+
( (3.61127471793548,-1.33595529462205), (3.6122747179354797,-1.33585529462205) )
82+
( (3.61127820859399,-1.3359535492928), (3.61227820859399,-1.3358535492928) )
83+
\.
84+
85+
CREATE OR REPLACEFUNCTIONqnodes(qtext) RETURNStext
86+
LANGUAGE'plpgsql'AS
87+
$$
88+
DECLARE
89+
expTEXT;
90+
matTEXT[];
91+
retTEXT[];
92+
BEGIN
93+
FOR expIN EXECUTE'EXPLAIN'|| q
94+
LOOP
95+
--RAISE NOTICE 'EXP: %', exp;
96+
mat := regexp_matches(exp,' *(?:-> *)?(.*Scan)');
97+
--RAISE NOTICE 'MAT: %', mat;
98+
IF matIS NOT NULL THEN
99+
ret := array_append(ret, mat[1]);
100+
END IF;
101+
--RAISE NOTICE 'RET: %', ret;
102+
END LOOP;
103+
RETURN array_to_string(ret,',');
104+
END;
105+
$$;
106+
107+
CREATEINDEXtest_boxes_idxON test_boxes USING brin (b) WITH (pages_per_range=16);
108+
109+
set enable_indexscan= off;
110+
set enable_bitmapscan= off;
111+
set enable_seqscan=on;
112+
113+
SELECT'scan_seq', qnodes('SELECT * FROM test_boxes WHERE b <@ sbox''( (10d,10d), (20d,20d) )''');
114+
SELECT*FROM test_boxesWHERE b<@ sbox'( (10d,10d), (20d,20d) )';
115+
116+
SELECT'scan_seq', qnodes('SELECT * FROM test_boxes WHERE b && sbox''( (10d,10d), (20d,20d) )''');
117+
SELECT*FROM test_boxesWHERE b && sbox'( (10d,10d), (20d,20d) )';
118+
119+
set enable_indexscan= off;
120+
set enable_bitmapscan=on;
121+
set enable_seqscan= off;
122+
123+
SELECT'scan_idx', qnodes('SELECT * FROM test_boxes WHERE b <@ sbox''( (10d,10d), (20d,20d) )''');
124+
SELECT*FROM test_boxesWHERE b<@ sbox'( (10d,10d), (20d,20d) )';
125+
126+
SELECT'scan_idx', qnodes('SELECT * FROM test_boxes WHERE b && sbox''( (10d,10d), (20d,20d) )''');
127+
SELECT*FROM test_boxesWHERE b && sbox'( (10d,10d), (20d,20d) )';
128+
129+
---- cleanup
130+
DROPINDEX brin_sbox;
131+
DROPTABLE test_boxes;
132+
DROPFUNCTION qnodes(text);
133+
134+
set enable_indexscan=on;
135+
set enable_bitmapscan=on;
136+
set enable_seqscan=on;

0 commit comments

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