[postgis-users] Controlling PostGIS memory usage

[George Silva]

Have you tried using ST_DWithin?


On Wed, Mar 26, 2014 at 5:06 PM, Morten Sickel <morten at sickel.net> wrote:

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



-- 
George R. C. Silva
SIGMA Consultoria
----------------------------
http://www.consultoriasigma.com.br/
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20140326/e550bdd8/attachment.html>