Sandro Santilli
9bfec15979
Closes #4200 git-svn-id: http://svn.osgeo.org/postgis/trunk@16929 b70326c6-7e19-0410-871a-916f4a2858ee
90 lines
3.4 KiB
PL/PgSQL
90 lines
3.4 KiB
PL/PgSQL
|
|
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;
|
|
|