1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
|
CREATE TABLE test_points (
p spoint
);
COPY test_points (p) FROM stdin;
CREATE OR REPLACE FUNCTION qnodes(q text) RETURNS text
LANGUAGE 'plpgsql' AS
$$
DECLARE
exp TEXT;
mat TEXT[];
ret TEXT[];
BEGIN
FOR exp IN EXECUTE 'EXPLAIN ' || q
LOOP
--RAISE NOTICE 'EXP: %', exp;
mat := regexp_matches(exp, ' *(?:-> *)?(.*Scan on (test_points|brin_spoint))');
--RAISE NOTICE 'MAT: %', mat;
IF mat IS NOT NULL THEN
ret := array_append(ret, mat[1]);
END IF;
--RAISE NOTICE 'RET: %', ret;
END LOOP;
RETURN array_to_string(ret,',');
END;
$$;
CREATE INDEX brin_spoint ON test_points USING brin (p) WITH (pages_per_range = 16);
set enable_indexscan = off;
set enable_bitmapscan = off;
set enable_seqscan = on;
SELECT 'scan_seq', qnodes('SELECT * FROM test_points WHERE p <@ sbox ''( (10d,10d), (20d,20d) )''');
?column? | qnodes
----------+-------------------------
scan_seq | Seq Scan on test_points
(1 row)
SELECT * FROM test_points WHERE p <@ sbox '( (10d,10d), (20d,20d) )';
p
-----------------------------------------
(0.349065850398866 , 0.174532925199433)
(1 row)
SELECT 'scan_seq', qnodes('SELECT * FROM test_points WHERE p && sbox ''( (10d,10d), (20d,20d) )''');
?column? | qnodes
----------+-------------------------
scan_seq | Seq Scan on test_points
(1 row)
SELECT * FROM test_points WHERE p && sbox '( (10d,10d), (20d,20d) )';
p
-----------------------------------------
(0.349065850398866 , 0.174532925199433)
(1 row)
set enable_indexscan = off;
set enable_bitmapscan = on;
set enable_seqscan = off;
SELECT 'scan_idx', qnodes('SELECT * FROM test_points WHERE p <@ sbox ''( (10d,10d), (20d,20d) )''');
?column? | qnodes
----------+------------------------------------------------------------------
scan_idx | Bitmap Heap Scan on test_points,Bitmap Index Scan on brin_spoint
(1 row)
SELECT * FROM test_points WHERE p <@ sbox '( (10d,10d), (20d,20d) )';
p
-----------------------------------------
(0.349065850398866 , 0.174532925199433)
(1 row)
SELECT 'scan_idx', qnodes('SELECT * FROM test_points WHERE p && sbox ''( (10d,10d), (20d,20d) )''');
?column? | qnodes
----------+------------------------------------------------------------------
scan_idx | Bitmap Heap Scan on test_points,Bitmap Index Scan on brin_spoint
(1 row)
SELECT * FROM test_points WHERE p && sbox '( (10d,10d), (20d,20d) )';
p
-----------------------------------------
(0.349065850398866 , 0.174532925199433)
(1 row)
-- cleanup
DROP INDEX brin_spoint;
DROP TABLE test_points;
DROP FUNCTION qnodes(text);
set enable_indexscan = on;
set enable_bitmapscan = on;
set enable_seqscan = on;
|
