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

Commitbbdd3c3

Browse files
author
Vitaly Davydov
committed
Improve sbox_brin and spoint_brin tests
1 parent1d28b7a commitbbdd3c3

File tree

4 files changed

+135
-59
lines changed

4 files changed

+135
-59
lines changed

‎expected/sbox_brin.out

Lines changed: 74 additions & 29 deletions
Original file line numberDiff line numberDiff line change
@@ -1,7 +1,25 @@
11
CREATE TABLE test_boxes (
22
b sbox
33
);
4-
COPY test_boxes (b) FROM stdin;
4+
CREATE TABLE test_boxes_tmp (
5+
b sbox
6+
);
7+
COPY test_boxes_tmp (b) FROM stdin;
8+
DO $$
9+
DECLARE
10+
idx INT := 0;
11+
BEGIN
12+
WHILE idx < 1000 LOOP
13+
INSERT INTO test_boxes(b) SELECT b FROM test_boxes_tmp;
14+
idx := idx + 1;
15+
END LOOP;
16+
END $$;
17+
SELECT "test_boxes_tmp: number of rows", COUNT(*) FROM test_boxes_tmp
18+
UNION ALL
19+
SELECT "test_boxes: number of rows", COUNT(*) FROM test_boxes;
20+
ERROR: column "test_boxes_tmp: number of rows" does not exist
21+
LINE 1: SELECT "test_boxes_tmp: number of rows", COUNT(*) FROM test_...
22+
^
523
CREATE OR REPLACE FUNCTION qnodes(q text) RETURNS text
624
LANGUAGE 'plpgsql' AS
725
$$
@@ -23,64 +41,91 @@ BEGIN
2341
RETURN array_to_string(ret,',');
2442
END;
2543
$$;
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;
44+
CREATE INDEX test_boxes_gist_idx ON test_boxes USING gist(b);
45+
CLUSTER test_boxes USING test_boxes_gist_idx;
46+
\d+ test_boxes;
47+
Table "public.test_boxes"
48+
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
49+
--------+------+-----------+----------+---------+---------+--------------+-------------
50+
b | sbox | | | | plain | |
51+
Indexes:
52+
"test_boxes_gist_idx" gist (b) CLUSTER
53+
54+
DROP INDEX test_boxes_gist_idx;
55+
CREATE INDEX test_boxes_idx ON test_boxes USING brin (b);
56+
VACUUM ANALYZE;
57+
\d+ test_boxes;
58+
Table "public.test_boxes"
59+
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
60+
--------+------+-----------+----------+---------+---------+--------------+-------------
61+
b | sbox | | | | plain | |
62+
Indexes:
63+
"test_boxes_idx" brin (b)
64+
65+
SELECT COUNT(*) FROM test_boxes;
66+
count
67+
-------
68+
77000
69+
(1 row)
70+
71+
SET enable_indexscan = OFF;
72+
SET enable_bitmapscan = OFF;
73+
SET enable_seqscan = ON;
3074
SELECT 'scan_seq', qnodes('SELECT * FROM test_boxes WHERE b <@ sbox ''( (10d,10d), (20d,20d) )''');
3175
?column? | qnodes
3276
----------+----------
3377
scan_seq | Seq Scan
3478
(1 row)
3579

36-
SELECT * FROM test_boxes WHERE b <@ sbox '( (10d,10d), (20d,20d) )';
37-
b
38-
---
39-
(0 rows)
80+
SELECT COUNT(*) FROM test_boxes WHERE b <@ sbox '( (10d,10d), (20d,20d) )';
81+
count
82+
-------
83+
0
84+
(1 row)
4085

4186
SELECT 'scan_seq', qnodes('SELECT * FROM test_boxes WHERE b && sbox ''( (10d,10d), (20d,20d) )''');
4287
?column? | qnodes
4388
----------+----------
4489
scan_seq | Seq Scan
4590
(1 row)
4691

47-
SELECT* FROM test_boxes WHERE b && sbox '( (10d,10d), (20d,20d) )';
48-
b
49-
------------------------------------------------------------------------------------
50-
((0.349065850398866 , 0.174532925199433), (0.350065850398866 , 0.174632925199433))
92+
SELECTCOUNT(*) FROM test_boxes WHERE b && sbox '( (10d,10d), (20d,20d) )';
93+
count
94+
-------
95+
1000
5196
(1 row)
5297

53-
set enable_indexscan =off;
54-
set enable_bitmapscan =on;
55-
set enable_seqscan =off;
98+
SET enable_indexscan =OFF;
99+
SET enable_bitmapscan =ON;
100+
SET enable_seqscan =OFF;
56101
SELECT 'scan_idx', qnodes('SELECT * FROM test_boxes WHERE b <@ sbox ''( (10d,10d), (20d,20d) )''');
57102
?column? | qnodes
58103
----------+----------
59104
scan_idx | Seq Scan
60105
(1 row)
61106

62-
SELECT * FROM test_boxes WHERE b <@ sbox '( (10d,10d), (20d,20d) )';
63-
b
64-
---
65-
(0 rows)
107+
SELECT COUNT(*) FROM test_boxes WHERE b <@ sbox '( (10d,10d), (20d,20d) )';
108+
count
109+
-------
110+
0
111+
(1 row)
66112

67113
SELECT 'scan_idx', qnodes('SELECT * FROM test_boxes WHERE b && sbox ''( (10d,10d), (20d,20d) )''');
68114
?column? | qnodes
69115
----------+----------
70116
scan_idx | Seq Scan
71117
(1 row)
72118

73-
SELECT* FROM test_boxes WHERE b && sbox '( (10d,10d), (20d,20d) )';
74-
b
75-
------------------------------------------------------------------------------------
76-
((0.349065850398866 , 0.174532925199433), (0.350065850398866 , 0.174632925199433))
119+
SELECTCOUNT(*) FROM test_boxes WHERE b && sbox '( (10d,10d), (20d,20d) )';
120+
count
121+
-------
122+
1000
77123
(1 row)
78124

79125
---- cleanup
80-
DROP INDEX brin_sbox;
81-
ERROR: index "brin_sbox" does not exist
126+
DROP INDEX test_boxes_idx;
82127
DROP TABLE test_boxes;
83128
DROP FUNCTION qnodes(text);
84-
set enable_indexscan =on;
85-
set enable_bitmapscan =on;
86-
set enable_seqscan =on;
129+
SET enable_indexscan =ON;
130+
SET enable_bitmapscan =ON;
131+
SET enable_seqscan =ON;

‎expected/spoint_brin.out

Lines changed: 13 additions & 13 deletions
Original file line numberDiff line numberDiff line change
@@ -13,7 +13,7 @@ BEGIN
1313
FOR exp IN EXECUTE 'EXPLAIN ' || q
1414
LOOP
1515
--RAISE NOTICE 'EXP: %', exp;
16-
mat := regexp_matches(exp, ' *(?:-> *)?(.*Scan)');
16+
mat := regexp_matches(exp, ' *(?:-> *)?(.*Scan on (test_points|brin_spoint))');
1717
--RAISE NOTICE 'MAT: %', mat;
1818
IF mat IS NOT NULL THEN
1919
ret := array_append(ret, mat[1]);
@@ -28,9 +28,9 @@ set enable_indexscan = off;
2828
set enable_bitmapscan = off;
2929
set enable_seqscan = on;
3030
SELECT 'scan_seq', qnodes('SELECT * FROM test_points WHERE p <@ sbox ''( (10d,10d), (20d,20d) )''');
31-
?column? | qnodes
32-
----------+----------
33-
scan_seq | Seq Scan
31+
?column? |qnodes
32+
----------+-------------------------
33+
scan_seq | Seq Scan on test_points
3434
(1 row)
3535

3636
SELECT * FROM test_points WHERE p <@ sbox '( (10d,10d), (20d,20d) )';
@@ -40,9 +40,9 @@ SELECT * FROM test_points WHERE p <@ sbox '( (10d,10d), (20d,20d) )';
4040
(1 row)
4141

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

4848
SELECT * FROM test_points WHERE p && sbox '( (10d,10d), (20d,20d) )';
@@ -55,9 +55,9 @@ set enable_indexscan = off;
5555
set enable_bitmapscan = on;
5656
set enable_seqscan = off;
5757
SELECT 'scan_idx', qnodes('SELECT * FROM test_points WHERE p <@ sbox ''( (10d,10d), (20d,20d) )''');
58-
?column? | qnodes
59-
----------+------------------------------------
60-
scan_idx | Bitmap Heap Scan,Bitmap Index Scan
58+
?column? |qnodes
59+
----------+------------------------------------------------------------------
60+
scan_idx | Bitmap Heap Scan on test_points,Bitmap Index Scan on brin_spoint
6161
(1 row)
6262

6363
SELECT * FROM test_points WHERE p <@ sbox '( (10d,10d), (20d,20d) )';
@@ -67,9 +67,9 @@ SELECT * FROM test_points WHERE p <@ sbox '( (10d,10d), (20d,20d) )';
6767
(1 row)
6868

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

7575
SELECT * FROM test_points WHERE p && sbox '( (10d,10d), (20d,20d) )';

‎sql/sbox_brin.sql

Lines changed: 47 additions & 16 deletions
Original file line numberDiff line numberDiff line change
@@ -2,7 +2,11 @@ CREATE TABLE test_boxes (
22
b sbox
33
);
44

5-
COPY test_boxes (b)FROM stdin;
5+
CREATETABLEtest_boxes_tmp (
6+
b sbox
7+
);
8+
9+
COPY test_boxes_tmp (b)FROM stdin;
610
( (0.349065850398866,0.174532925199433), (0.350065850398866,0.174632925199433) )
711
( (1.59875999207035,0.771416330759722), (1.5997599920703498,0.771516330759722) )
812
( (1.59876348272885,0.77141458543047), (1.5997634827288498,0.77151458543047) )
@@ -82,6 +86,20 @@ COPY test_boxes (b) FROM stdin;
8286
( (3.61127820859399,-1.3359535492928), (3.61227820859399,-1.3358535492928) )
8387
\.
8488

89+
DO $$
90+
DECLARE
91+
idxINT :=0;
92+
BEGIN
93+
WHILE idx<1000 LOOP
94+
INSERT INTO test_boxes(b)SELECT bFROM test_boxes_tmp;
95+
idx := idx+1;
96+
END LOOP;
97+
END $$;
98+
99+
SELECT"test_boxes_tmp: number of rows",COUNT(*)FROM test_boxes_tmp
100+
UNION ALL
101+
SELECT"test_boxes: number of rows",COUNT(*)FROM test_boxes;
102+
85103
CREATE OR REPLACEFUNCTIONqnodes(qtext) RETURNStext
86104
LANGUAGE'plpgsql'AS
87105
$$
@@ -104,33 +122,46 @@ BEGIN
104122
END;
105123
$$;
106124

107-
CREATEINDEXtest_boxes_idxON test_boxes USING brin (b) WITH (pages_per_range=16);
125+
CREATEINDEXtest_boxes_gist_idxON test_boxes USING gist(b);
126+
127+
CLUSTER test_boxes USING test_boxes_gist_idx;
128+
129+
\d+ test_boxes;
130+
131+
DROPINDEX test_boxes_gist_idx;
132+
133+
CREATEINDEXtest_boxes_idxON test_boxes USING brin (b);
134+
135+
VACUUM ANALYZE;
136+
137+
\d+ test_boxes;
138+
SELECTCOUNT(*)FROM test_boxes;
108139

109-
set enable_indexscan=off;
110-
set enable_bitmapscan=off;
111-
set enable_seqscan=on;
140+
SET enable_indexscan=OFF;
141+
SET enable_bitmapscan=OFF;
142+
SET enable_seqscan=ON;
112143

113144
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) )';
145+
SELECTCOUNT(*)FROM test_boxesWHERE b<@ sbox'( (10d,10d), (20d,20d) )';
115146

116147
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) )';
148+
SELECTCOUNT(*)FROM test_boxesWHERE b && sbox'( (10d,10d), (20d,20d) )';
118149

119-
set enable_indexscan=off;
120-
set enable_bitmapscan=on;
121-
set enable_seqscan=off;
150+
SET enable_indexscan=OFF;
151+
SET enable_bitmapscan=ON;
152+
SET enable_seqscan=OFF;
122153

123154
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) )';
155+
SELECTCOUNT(*)FROM test_boxesWHERE b<@ sbox'( (10d,10d), (20d,20d) )';
125156

126157
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) )';
158+
SELECTCOUNT(*)FROM test_boxesWHERE b && sbox'( (10d,10d), (20d,20d) )';
128159

129160
---- cleanup
130-
DROPINDEXbrin_sbox;
161+
DROPINDEXtest_boxes_idx;
131162
DROPTABLE test_boxes;
132163
DROPFUNCTION qnodes(text);
133164

134-
set enable_indexscan=on;
135-
set enable_bitmapscan=on;
136-
set enable_seqscan=on;
165+
SET enable_indexscan=ON;
166+
SET enable_bitmapscan=ON;
167+
SET enable_seqscan=ON;

‎sql/spoint_brin.sql

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -93,7 +93,7 @@ BEGIN
9393
FOR expIN EXECUTE'EXPLAIN'|| q
9494
LOOP
9595
--RAISE NOTICE 'EXP: %', exp;
96-
mat := regexp_matches(exp,' *(?:-> *)?(.*Scan)');
96+
mat := regexp_matches(exp,' *(?:-> *)?(.*Scan on (test_points|brin_spoint))');
9797
--RAISE NOTICE 'MAT: %', mat;
9898
IF matIS NOT NULL THEN
9999
ret := array_append(ret, mat[1]);

0 commit comments

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