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
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
|
--- build a larger database
\i :regdir/core/regress_lots_of_points.sql
--- test some of the searching capabilities
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 := array_append(ret, mat[1]);
END IF;
--RAISE NOTICE 'RET: %', ret;
END LOOP;
RETURN array_to_string(ret,',');
END;
$$;
-- GiST index
CREATE INDEX quick_gist on test using gist (the_geom);
set enable_indexscan = off;
set enable_bitmapscan = off;
set enable_seqscan = on;
SELECT 'scan_idx', qnodes('select * from test where the_geom && ST_MakePoint(0,0)');
select num,ST_astext(the_geom) from test where the_geom && 'BOX3D(125 125,135 135)'::box3d order by num;
set enable_indexscan = on;
set enable_bitmapscan = off;
set enable_seqscan = off;
SELECT 'scan_seq', qnodes('select * from test where the_geom && ST_MakePoint(0,0)');
select num,ST_astext(the_geom) from test where the_geom && 'BOX3D(125 125,135 135)'::box3d order by num;
CREATE FUNCTION estimate_error(qry text, tol int)
RETURNS text
LANGUAGE 'plpgsql' VOLATILE AS $$
DECLARE
anl TEXT; -- analisys
err INT; -- absolute difference between planned and actual rows
est INT; -- estimated count
act INT; -- actual count
mat TEXT[];
BEGIN
-- TODO: rewrite using json output ?
EXECUTE 'EXPLAIN ANALYZE ' || qry INTO anl;
SELECT regexp_matches(anl, E' rows=([0-9]*) .* rows=([0-9\.]*) ')
INTO mat;
est := mat[1];
act := mat[2]::numeric::integer;
err = abs(est-act);
RETURN act || '+-' || tol || ':' || coalesce(
nullif((err < tol)::text,'false'),
'false:'||err::text
);
END;
$$;
-- There are 50000 points in the table with full extent being
-- BOX(0.001693 0.000185,999.968899 999.997026)
CREATE TABLE sample_queries AS
SELECT 1 as id, 5 as tol, 'ST_MakeEnvelope(125,125,135,135)' as box
UNION ALL
SELECT 2, 60, 'ST_MakeEnvelope(0,0,135,135)'
UNION ALL
SELECT 3, 500, 'ST_MakeEnvelope(0,0,500,500)'
UNION ALL
SELECT 4, 600, 'ST_MakeEnvelope(0,0,1000,1000)'
;
-- We raise the statistics target to the limit
ALTER TABLE test ALTER COLUMN the_geom SET STATISTICS 10000;
ANALYZE test;
SELECT '&&', id, estimate_error(
'select num from test where the_geom && ' || box, tol )
FROM sample_queries ORDER BY id;
-- Test selectivity estimation of functional indexes
CREATE INDEX expressional_gist on test using gist ( st_centroid(the_geom) );
ANALYZE test;
SELECT 'expr &&', id, estimate_error(
'select num from test where st_centroid(the_geom) && ' || box, tol )
FROM sample_queries ORDER BY id;
DROP TABLE test;
DROP TABLE sample_queries;
DROP FUNCTION estimate_error(text, int);
DROP FUNCTION qnodes(text);
set enable_indexscan = on;
set enable_bitmapscan = on;
set enable_seqscan = on;
-- _ST_SortableHash is a work around Postgres parallel sort requiring recalculation of abbreviated keys.
select '_st_sortablehash', _ST_SortableHash('POINT(0 0)'), _ST_SortableHash('SRID=4326;POINT(0 0)'), _ST_SortableHash('SRID=3857;POINT(0 0)');
|
