[postgis-users] Controlling PostGIS memory usage

[Dan Gast]

Hello,

I've found a way to crash PostgreSQL by running it out of memory, and
after looking at the documentation I'm unable to find tuning
parameters to stop it. Furthermore, I don't believe this query should
be using much memory at all.

I have a person table with 100k rows, and a store table with 100 rows
(schema below).

-- The following works ok:
SELECT COUNT(*) FROM person AS p, store AS s WHERE ST_Distance(p.geog,
s.geog) < 400000;

-- This will run the daemon out of memory:
SELECT COUNT(*) FROM person AS p, store AS s WHERE
ST_Intersects(ST_Buffer(s.geog, 400000), p.geog);

My fact-free suspicion is that ST_Buffer() is creating a GEOS object
for each "person" x "store", and these objects are not being released
after the result of the ST_Intersects(). If I add WHERE clause
conditions to reduce the check count (i.e. store.id < 6), memory usage
is more reasonable.

My questions:

1) Is this known, and if so, what should I read to understand what is
really going on?

2) I've been unsuccessful finding tunables to rein PostGIS-related
memory usage in. Can I control this? How?


Thanks,


Dan


Supporting notes:

Ubuntu 12.04, fully patched, or Ubuntu 14.04 recent but possibly not current.

PostgreSQL 9.3, PostGIS 2.1 out of apt.postgresql.org

Ubuntu 12.04: POSTGIS="2.1.1 r12113" GEOS="3.3.3-CAPI-1.7.4"
PROJ="Rel. 4.7.1, 23 September 2009" GDAL="GDAL 1.9.0, released
2011/12/29" LIBXML="2.7.8" LIBJSON="UNKNOWN" RASTER

Ubuntu 14.04: POSTGIS="2.1.1 r12113" GEOS="3.4.2-CAPI-1.8.2 r3921"
PROJ="Rel. 4.8.0, 6 March 2012" GDAL="GDAL 1.10.1, released
2013/08/26" LIBXML="2.9.1" LIBJSON="UNKNOWN" RASTER

SQL to build an empty DB up to test:

CREATE EXTENSION postgis;

CREATE TABLE person (
    id SERIAL,
    geog Geography(Point,4326)
);

CREATE TABLE store (
    id SERIAL,
    geog Geography(Point,4326)
);

CREATE FUNCTION people(numofthem integer) RETURNS void
    LANGUAGE plpgsql SECURITY DEFINER COST 10
    AS $$
BEGIN
FOR i IN 1 .. numofthem LOOP
INSERT INTO person (geog) VALUES (ST_MakePoint(RANDOM() * 360 - 180,
RANDOM() * 180 - 90));
END LOOP;
END;
$$;

CREATE FUNCTION stores(numofthem integer) RETURNS void
    LANGUAGE plpgsql SECURITY DEFINER COST 10
    AS $$
BEGIN
FOR i IN 1 .. numofthem LOOP
INSERT INTO store (geog) VALUES (ST_MakePoint(RANDOM() * 360 - 180,
RANDOM() * 180 - 90));
END LOOP;
END;
$$;

SELECT people(100000);
SELECT stores(100);