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
88
89
|
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)');
--RAISE NOTICE 'MAT: %', mat;
IF mat IS NOT NULL THEN
ret := mat[1];
END IF;
--RAISE NOTICE 'RET: %', ret;
END LOOP;
RETURN ret;
END;
$$;
-------------------------------------------------------------------------------
create table tbl_geomcollection (
k serial,
g geometry
);
\copy tbl_geomcollection from 'regress_spgist_index_3d.data';
create table test_spgist_idx_3d(
op char(3),
noidx bigint,
noidxscan varchar(32),
spgistidx bigint,
spgidxscan varchar(32));
-------------------------------------------------------------------------------
set enable_indexscan = off;
set enable_bitmapscan = off;
set enable_seqscan = on;
insert into test_spgist_idx_3d(op, noidx, noidxscan)
select '&/&', count(*), qnodes('select count(*) from tbl_geomcollection t1, tbl_geomcollection t2 where t1.g &/& t2.g') from tbl_geomcollection t1, tbl_geomcollection t2 where t1.g &/& t2.g;
insert into test_spgist_idx_3d(op, noidx, noidxscan)
select '@>>', count(*), qnodes('select count(*) from tbl_geomcollection t1, tbl_geomcollection t2 where t1.g @>> t2.g') from tbl_geomcollection t1, tbl_geomcollection t2 where t1.g @>> t2.g;
insert into test_spgist_idx_3d(op, noidx, noidxscan)
select '<<@', count(*), qnodes('select count(*) from tbl_geomcollection t1, tbl_geomcollection t2 where t1.g <<@ t2.g') from tbl_geomcollection t1, tbl_geomcollection t2 where t1.g <<@ t2.g;
insert into test_spgist_idx_3d(op, noidx, noidxscan)
select '~==', count(*), qnodes('select count(*) from tbl_geomcollection t1, tbl_geomcollection t2 where t1.g ~== t2.g') from tbl_geomcollection t1, tbl_geomcollection t2 where t1.g ~== t2.g;
------------------------------------------------------------------------------
create index tbl_geomcollection_spgist_3d_idx on tbl_geomcollection using spgist(g spgist_geometry_ops_3d);
set enable_indexscan = on;
set enable_bitmapscan = off;
set enable_seqscan = off;
update test_spgist_idx_3d
set spgistidx = ( select count(*) from tbl_geomcollection t1, tbl_geomcollection t2 where t1.g &/& t2.g ),
spgidxscan = qnodes(' select count(*) from tbl_geomcollection t1, tbl_geomcollection t2 where t1.g &/& t2.g ')
where op = '&/&';
update test_spgist_idx_3d
set spgistidx = ( select count(*) from tbl_geomcollection t1, tbl_geomcollection t2 where t1.g @>> t2.g ),
spgidxscan = qnodes(' select count(*) from tbl_geomcollection t1, tbl_geomcollection t2 where t1.g @>> t2.g ')
where op = '@>>';
update test_spgist_idx_3d
set spgistidx = ( select count(*) from tbl_geomcollection t1, tbl_geomcollection t2 where t1.g <<@ t2.g ),
spgidxscan = qnodes(' select count(*) from tbl_geomcollection t1, tbl_geomcollection t2 where t1.g <<@ t2.g ')
where op = '<<@';
update test_spgist_idx_3d
set spgistidx = ( select count(*) from tbl_geomcollection t1, tbl_geomcollection t2 where t1.g ~== t2.g ),
spgidxscan = qnodes(' select count(*) from tbl_geomcollection t1, tbl_geomcollection t2 where t1.g ~== t2.g ')
where op = '~==';
-------------------------------------------------------------------------------
select * from test_spgist_idx_3d;
-------------------------------------------------------------------------------
DROP TABLE tbl_geomcollection CASCADE;
DROP TABLE test_spgist_idx_3d CASCADE;
DROP FUNCTION qnodes;
|
