[postgis-users] Controlling PostGIS memory usage

[Morten Sickel]

I am not able to say if there is anyting wrong with your query - but in
general, you should look for memory tuning of postgres - there are quite a
few options - also some that defines how the memory is used. A good
starting point should be
https://wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Server

Morten

Dan Gast skrev:
> 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);
> _______________________________________________
> postgis-users mailing list
> postgis-users at lists.osgeo.org
> http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
>