🌐 AI搜索 & 代理 主页
Skip to content

Commit 6d9a76f

Browse files
author
Vitaly Davydov
committed
Improve sbox_brin and spoint_brin tests
1 parent 1d28b7a commit 6d9a76f

File tree

4 files changed

+137
-59
lines changed

4 files changed

+137
-59
lines changed

‎expected/sbox_brin.out‎

Lines changed: 75 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,92 @@ 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+
VACUUM FULL;
56+
VACUUM ANALYZE;
57+
CREATE INDEX test_boxes_idx ON test_boxes USING brin (b);
58+
\d+ test_boxes;
59+
Table "public.test_boxes"
60+
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
61+
--------+------+-----------+----------+---------+---------+--------------+-------------
62+
b | sbox | | | | plain | |
63+
Indexes:
64+
"test_boxes_idx" brin (b)
65+
66+
SELECT COUNT(*) FROM test_boxes;
67+
count
68+
-------
69+
77000
70+
(1 row)
71+
72+
SET enable_indexscan = OFF;
73+
SET enable_bitmapscan = OFF;
74+
SET enable_seqscan = ON;
3075
SELECT 'scan_seq', qnodes('SELECT * FROM test_boxes WHERE b <@ sbox ''( (10d,10d), (20d,20d) )''');
3176
?column? | qnodes
3277
----------+----------
3378
scan_seq | Seq Scan
3479
(1 row)
3580

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

4187
SELECT 'scan_seq', qnodes('SELECT * FROM test_boxes WHERE b && sbox ''( (10d,10d), (20d,20d) )''');
4288
?column? | qnodes
4389
----------+----------
4490
scan_seq | Seq Scan
4591
(1 row)
4692

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

53-
set enable_indexscan = off;
54-
set enable_bitmapscan = on;
55-
set enable_seqscan = off;
99+
SET enable_indexscan = OFF;
100+
SET enable_bitmapscan = ON;
101+
SET enable_seqscan = OFF;
56102
SELECT 'scan_idx', qnodes('SELECT * FROM test_boxes WHERE b <@ sbox ''( (10d,10d), (20d,20d) )''');
57103
?column? | qnodes
58104
----------+----------
59105
scan_idx | Seq Scan
60106
(1 row)
61107

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

67114
SELECT 'scan_idx', qnodes('SELECT * FROM test_boxes WHERE b && sbox ''( (10d,10d), (20d,20d) )''');
68115
?column? | qnodes
69116
----------+----------
70117
scan_idx | Seq Scan
71118
(1 row)
72119

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

79126
---- cleanup
80-
DROP INDEX brin_sbox;
81-
ERROR: index "brin_sbox" does not exist
127+
DROP INDEX test_boxes_idx;
82128
DROP TABLE test_boxes;
83129
DROP FUNCTION qnodes(text);
84-
set enable_indexscan = on;
85-
set enable_bitmapscan = on;
86-
set enable_seqscan = on;
130+
SET enable_indexscan = ON;
131+
SET enable_bitmapscan = ON;
132+
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: 48 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+
CREATE TABLE test_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+
idx INT := 0;
92+
BEGIN
93+
WHILE idx < 1000 LOOP
94+
INSERT INTO test_boxes(b) SELECT b FROM 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 REPLACE FUNCTION qnodes(q text) RETURNS text
86104
LANGUAGE 'plpgsql' AS
87105
$$
@@ -104,33 +122,47 @@ BEGIN
104122
END;
105123
$$;
106124

107-
CREATE INDEX test_boxes_idx ON test_boxes USING brin (b) WITH (pages_per_range = 16);
125+
CREATE INDEX test_boxes_gist_idx ON test_boxes USING gist(b);
126+
127+
CLUSTER test_boxes USING test_boxes_gist_idx;
128+
129+
\d+ test_boxes;
130+
131+
DROP INDEX test_boxes_gist_idx;
132+
133+
VACUUM FULL;
134+
VACUUM ANALYZE;
135+
136+
CREATE INDEX test_boxes_idx ON test_boxes USING brin (b);
137+
138+
\d+ test_boxes;
139+
SELECT COUNT(*) FROM test_boxes;
108140

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

113145
SELECT 'scan_seq', qnodes('SELECT * FROM test_boxes WHERE b <@ sbox ''( (10d,10d), (20d,20d) )''');
114-
SELECT * FROM test_boxes WHERE b <@ sbox '( (10d,10d), (20d,20d) )';
146+
SELECT COUNT(*) FROM test_boxes WHERE b <@ sbox '( (10d,10d), (20d,20d) )';
115147

116148
SELECT 'scan_seq', qnodes('SELECT * FROM test_boxes WHERE b && sbox ''( (10d,10d), (20d,20d) )''');
117-
SELECT * FROM test_boxes WHERE b && sbox '( (10d,10d), (20d,20d) )';
149+
SELECT COUNT(*) FROM test_boxes WHERE b && sbox '( (10d,10d), (20d,20d) )';
118150

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

123155
SELECT 'scan_idx', qnodes('SELECT * FROM test_boxes WHERE b <@ sbox ''( (10d,10d), (20d,20d) )''');
124-
SELECT * FROM test_boxes WHERE b <@ sbox '( (10d,10d), (20d,20d) )';
156+
SELECT COUNT(*) FROM test_boxes WHERE b <@ sbox '( (10d,10d), (20d,20d) )';
125157

126158
SELECT 'scan_idx', qnodes('SELECT * FROM test_boxes WHERE b && sbox ''( (10d,10d), (20d,20d) )''');
127-
SELECT * FROM test_boxes WHERE b && sbox '( (10d,10d), (20d,20d) )';
159+
SELECT COUNT(*) FROM test_boxes WHERE b && sbox '( (10d,10d), (20d,20d) )';
128160

129161
---- cleanup
130-
DROP INDEX brin_sbox;
162+
DROP INDEX test_boxes_idx;
131163
DROP TABLE test_boxes;
132164
DROP FUNCTION qnodes(text);
133165

134-
set enable_indexscan = on;
135-
set enable_bitmapscan = on;
136-
set enable_seqscan = on;
166+
SET enable_indexscan = ON;
167+
SET enable_bitmapscan = ON;
168+
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 exp IN 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 mat IS NOT NULL THEN
9999
ret := array_append(ret, mat[1]);

0 commit comments

Comments
 (0)