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
|
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_nd (
k serial,
g geometry
);
\copy tbl_geomcollection_nd from 'regress_gist_index_nd.data';
create table test_gist_idx_nd(
op char(3),
noidx bigint,
noidxscan varchar(32),
gistidx bigint,
gidxscan varchar(32));
-------------------------------------------------------------------------------
set enable_indexscan = off;
set enable_bitmapscan = off;
set enable_seqscan = on;
insert into test_gist_idx_nd(op, noidx, noidxscan)
select '&&&', count(*), qnodes('select count(*) from tbl_geomcollection_nd t1, tbl_geomcollection_nd t2 where t1.g &&& t2.g') from tbl_geomcollection_nd t1, tbl_geomcollection_nd t2 where t1.g &&& t2.g;
insert into test_gist_idx_nd(op, noidx, noidxscan)
select '~~', count(*), qnodes('select count(*) from tbl_geomcollection_nd t1, tbl_geomcollection_nd t2 where t1.g ~~ t2.g') from tbl_geomcollection_nd t1, tbl_geomcollection_nd t2 where t1.g ~~ t2.g;
insert into test_gist_idx_nd(op, noidx, noidxscan)
select '@@', count(*), qnodes('select count(*) from tbl_geomcollection_nd t1, tbl_geomcollection_nd t2 where t1.g @@ t2.g') from tbl_geomcollection_nd t1, tbl_geomcollection_nd t2 where t1.g @@ t2.g;
insert into test_gist_idx_nd(op, noidx, noidxscan)
select '~~=', count(*), qnodes('select count(*) from tbl_geomcollection_nd t1, tbl_geomcollection_nd t2 where t1.g ~~= t2.g') from tbl_geomcollection_nd t1, tbl_geomcollection_nd t2 where t1.g ~~= t2.g;
------------------------------------------------------------------------------
create index tbl_geomcollection_nd_gist_nd_idx on tbl_geomcollection_nd using gist(g gist_geometry_ops_nd);
set enable_indexscan = on;
set enable_bitmapscan = off;
set enable_seqscan = off;
update test_gist_idx_nd
set gistidx = ( select count(*) from tbl_geomcollection_nd t1, tbl_geomcollection_nd t2 where t1.g &&& t2.g ),
gidxscan = qnodes(' select count(*) from tbl_geomcollection_nd t1, tbl_geomcollection_nd t2 where t1.g &&& t2.g ')
where op = '&&&';
update test_gist_idx_nd
set gistidx = ( select count(*) from tbl_geomcollection_nd t1, tbl_geomcollection_nd t2 where t1.g ~~ t2.g ),
gidxscan = qnodes(' select count(*) from tbl_geomcollection_nd t1, tbl_geomcollection_nd t2 where t1.g ~~ t2.g ')
where op = '~~';
update test_gist_idx_nd
set gistidx = ( select count(*) from tbl_geomcollection_nd t1, tbl_geomcollection_nd t2 where t1.g @@ t2.g ),
gidxscan = qnodes(' select count(*) from tbl_geomcollection_nd t1, tbl_geomcollection_nd t2 where t1.g @@ t2.g ')
where op = '@@';
update test_gist_idx_nd
set gistidx = ( select count(*) from tbl_geomcollection_nd t1, tbl_geomcollection_nd t2 where t1.g ~~= t2.g ),
gidxscan = qnodes(' select count(*) from tbl_geomcollection_nd t1, tbl_geomcollection_nd t2 where t1.g ~~= t2.g ')
where op = '~~=';
-------------------------------------------------------------------------------
select * from test_gist_idx_nd;
-------------------------------------------------------------------------------
DROP TABLE tbl_geomcollection_nd CASCADE;
DROP TABLE test_gist_idx_nd CASCADE;
DROP FUNCTION qnodes (text);
|
