<div dir="ltr">Have you tried using ST_DWithin?</div><div class="gmail_extra"><br><br><div class="gmail_quote">On Wed, Mar 26, 2014 at 5:06 PM, Morten Sickel <span dir="ltr"><<a href="mailto:morten@sickel.net" target="_blank">morten@sickel.net</a>></span> wrote:<br>
<blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex">I am not able to say if there is anyting wrong with your query - but in<br>
general, you should look for memory tuning of postgres - there are quite a<br>
few options - also some that defines how the memory is used. A good<br>
starting point should be<br>
<a href="https://wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Server" target="_blank">https://wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Server</a><br>
<br>
Morten<br>
<br>
Dan Gast skrev:<br>
<div class="HOEnZb"><div class="h5">> Hello,<br>
><br>
> I've found a way to crash PostgreSQL by running it out of memory, and<br>
> after looking at the documentation I'm unable to find tuning<br>
> parameters to stop it. Furthermore, I don't believe this query should<br>
> be using much memory at all.<br>
><br>
> I have a person table with 100k rows, and a store table with 100 rows<br>
> (schema below).<br>
><br>
> -- The following works ok:<br>
> SELECT COUNT(*) FROM person AS p, store AS s WHERE ST_Distance(p.geog,<br>
> s.geog) < 400000;<br>
><br>
> -- This will run the daemon out of memory:<br>
> SELECT COUNT(*) FROM person AS p, store AS s WHERE<br>
> ST_Intersects(ST_Buffer(s.geog, 400000), p.geog);<br>
><br>
> My fact-free suspicion is that ST_Buffer() is creating a GEOS object<br>
> for each "person" x "store", and these objects are not being released<br>
> after the result of the ST_Intersects(). If I add WHERE clause<br>
> conditions to reduce the check count (i.e. <a href="http://store.id" target="_blank">store.id</a> < 6), memory usage<br>
> is more reasonable.<br>
><br>
> My questions:<br>
><br>
> 1) Is this known, and if so, what should I read to understand what is<br>
> really going on?<br>
><br>
> 2) I've been unsuccessful finding tunables to rein PostGIS-related<br>
> memory usage in. Can I control this? How?<br>
><br>
><br>
> Thanks,<br>
><br>
><br>
> Dan<br>
><br>
><br>
> Supporting notes:<br>
><br>
> Ubuntu 12.04, fully patched, or Ubuntu 14.04 recent but possibly not<br>
> current.<br>
><br>
> PostgreSQL 9.3, PostGIS 2.1 out of <a href="http://apt.postgresql.org" target="_blank">apt.postgresql.org</a><br>
><br>
> Ubuntu 12.04: POSTGIS="2.1.1 r12113" GEOS="3.3.3-CAPI-1.7.4"<br>
> PROJ="Rel. 4.7.1, 23 September 2009" GDAL="GDAL 1.9.0, released<br>
> 2011/12/29" LIBXML="2.7.8" LIBJSON="UNKNOWN" RASTER<br>
><br>
> Ubuntu 14.04: POSTGIS="2.1.1 r12113" GEOS="3.4.2-CAPI-1.8.2 r3921"<br>
> PROJ="Rel. 4.8.0, 6 March 2012" GDAL="GDAL 1.10.1, released<br>
> 2013/08/26" LIBXML="2.9.1" LIBJSON="UNKNOWN" RASTER<br>
><br>
> SQL to build an empty DB up to test:<br>
><br>
> CREATE EXTENSION postgis;<br>
><br>
> CREATE TABLE person (<br>
> id SERIAL,<br>
> geog Geography(Point,4326)<br>
> );<br>
><br>
> CREATE TABLE store (<br>
> id SERIAL,<br>
> geog Geography(Point,4326)<br>
> );<br>
><br>
> CREATE FUNCTION people(numofthem integer) RETURNS void<br>
> LANGUAGE plpgsql SECURITY DEFINER COST 10<br>
> AS $$<br>
> BEGIN<br>
> FOR i IN 1 .. numofthem LOOP<br>
> INSERT INTO person (geog) VALUES (ST_MakePoint(RANDOM() * 360 - 180,<br>
> RANDOM() * 180 - 90));<br>
> END LOOP;<br>
> END;<br>
> $$;<br>
><br>
> CREATE FUNCTION stores(numofthem integer) RETURNS void<br>
> LANGUAGE plpgsql SECURITY DEFINER COST 10<br>
> AS $$<br>
> BEGIN<br>
> FOR i IN 1 .. numofthem LOOP<br>
> INSERT INTO store (geog) VALUES (ST_MakePoint(RANDOM() * 360 - 180,<br>
> RANDOM() * 180 - 90));<br>
> END LOOP;<br>
> END;<br>
> $$;<br>
><br>
> SELECT people(100000);<br>
> SELECT stores(100);<br>
> _______________________________________________<br>
> postgis-users mailing list<br>
> <a href="mailto:postgis-users@lists.osgeo.org">postgis-users@lists.osgeo.org</a><br>
> <a href="http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users" target="_blank">http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users</a><br>
><br>
<br>
<br>
_______________________________________________<br>
postgis-users mailing list<br>
<a href="mailto:postgis-users@lists.osgeo.org">postgis-users@lists.osgeo.org</a><br>
<a href="http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users" target="_blank">http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users</a><br>
</div></div></blockquote></div><br><br clear="all"><div><br></div>-- <br><div dir="ltr">George R. C. Silva<br>SIGMA Consultoria<div>----------------------------<br><div><a href="http://www.consultoriasigma.com.br/" target="_blank">http://www.consultoriasigma.com.br/</a><div style="padding:0px;margin-left:0px;margin-top:0px;overflow:hidden;word-wrap:break-word;color:black;font-size:10px;text-align:left;line-height:130%">
</div><div style="padding:0px;margin-left:0px;margin-top:0px;overflow:hidden;word-wrap:break-word;color:black;font-size:10px;text-align:left;line-height:130%"></div><div style="padding:0px;margin-left:0px;margin-top:0px;overflow:hidden;word-wrap:break-word;color:black;font-size:10px;text-align:left;line-height:130%">
</div></div></div></div>
</div>