Postgres Pro
Downloads
Home   >   mailing lists

Thread: Memory leak with SP-GIST indexes

Memory leak with SP-GIST indexes

From
Esteban Zimanyi
Date:
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
------------------------------------------------------------