Have you tried using ST_DWithin?
On Wed, Mar 26, 2014 at 5:06 PM, Morten Sickel <[email protected]> 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 > > [email protected] > > http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users > > > > > _______________________________________________ > postgis-users mailing list > [email protected] > http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users > -- George R. C. Silva SIGMA Consultoria ---------------------------- http://www.consultoriasigma.com.br/
_______________________________________________ postgis-users mailing list [email protected] http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
