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
