Dear all
When executing the following script in the latest version 10.1 of PostgreSQL the process is killed after consuming all available memory. This was also the case with version 9.6.5.
-------------------------------------------------------------------------------
CREATE OR REPLACE FUNCTION random_float(low float, high float)
RETURNS FLOAT AS $$
BEGIN
RETURN random()* (high-low + 1) + low;
END;
$$ language 'plpgsql' STRICT;
-------------------------------------------------------------------------------
CREATE OR REPLACE FUNCTION random_box(lowx float, highx float,
lowy float, highy float, maxsize float)
RETURNS box AS $$
DECLARE
xmin float;
ymin float;
size float;
BEGIN
xmin = random_float(lowx, highx);
ymin = random_float(lowy, highy);
size = random_float(1, maxsize);
RETURN box(point(xmin, ymin), point(xmin + size, ymin + size));
END;
$$ language 'plpgsql' STRICT;
-------------------------------------------------------------------------------
drop table if exists test_box_10M;
create table test_box_10M as
select k, random_box(0, 1000, 0, 1000, 10) as b
from generate_series(1,1e7) k;
create index test_box_10M_spgist_idx on test_box_10M using spgist(b)
explain
select count(*)
from test_box_10M t1, test_box_10M t2
where t1.b && t2.b;
select count(*)
from test_box_10M t1, test_box_10M t2
where t1.b && t2.b;
-------------------------------------------------------------------------------
This can be easily seen with the command "sudo top". The percentage of memory used by the postgresql processes continuously grow.
If on the contrary we use a gist index as follows
drop index test_box_10M_spgist_idx;
create index test_box_10M_gist_idx on test_box_10M using gist(b);
the query proceeds normally.
Could someone help me to solve this problem?
Regards
Esteban
--
------------------------------------------------------------
Prof. Esteban Zimanyi
Department of Computer & Decision Engineering (CoDE) CP 165/15
Universite Libre de Bruxelles
Avenue F. D. Roosevelt 50
B-1050 Brussels, Belgium
fax: + 32.2.650.47.13
tel: + 32.2.650.31.85
------------------------------------------------------------