[postgis-devel] [PostGIS] #577: possible memory leak with buffer()

[PostGIS]

#577: possible memory leak with buffer()
---------------------------------------------+------------------------------
 Reporter:  davideps                         |       Owner:  pramsey
     Type:  defect                           |      Status:  new    
 Priority:  medium                           |   Milestone:         
Component:  postgis                          |     Version:  1.3.X  
 Keywords:  memory buffer intersect dwithin  |  
---------------------------------------------+------------------------------
 VERSIONS
 PostgrSQL 8.3.11 on x86_64-pc-linux-gnu
 postgis_version() = 1.3 USE_GEOS=1 USE_PROJ=1 USE_STATS=1

 DATA
 Table "parcels09_d3_v2" has about 320,000 polygon rows.

 SYMPTOMS
 The original query ran for 2.5 hours before being terminated manually. It
 used 3.8gb of RAM & 1gb of swap. The modified query ran for 23 minutes and
 used about 600mb of memory. Memory estimates are from simply looking at
 the system monitor, so include basic operating system (UBUNTU) also--but
 nothing else was running at the time.

 QUESTION: why did using buffer & intersect use so much more memory instead
 of just running more slowly?

 --ORIGINAL QUERY
 CREATE TABLE bad_housing_neighbors AS
 SELECT p1.ogc_fid,
 avg(case when
 p2.vod='1' or
 p2.fire='1' or
 p2.condition='3' or
 p2.condition='4'
 then 1 else 0
 end)
 FROM parcels09_d3_v2 as p1, parcels09_d3_v2 as p2
 WHERE st_intersects(buffer(p1.wkb_geometry,528), p2.wkb_geometry)
 GROUP BY p1.ogc_fid
 ORDER BY p1.ogc_fid




 --MODIFIED QUERY
 CREATE TABLE bad_housing_neighbors_528ft AS
 SELECT p1.ogc_fid, count(p2.ogc_fid) as num_neigh_528ft,
 sum(case when
 p2.vod='1' or
 p2.fire='1' or
 p2.condition='3' or
 p2.condition='4'
 then 1 else 0
 end) as bad_cond_528ft,

 avg(case when
 p2.vod='1' or
 p2.fire='1' or
 p2.condition='3' or
 p2.condition='4'
 then 1 else 0
 end) as prc_bad_cond_528ft

 --ok to include "self" (same ogc_fid) in calculation
 FROM parcels09_d3_v2 as p1, parcels09_d3_v2 as p2
 WHERE st_dwithin(p1.wkb_geometry,p2.wkb_geometry,528)
 GROUP BY p1.ogc_fid
 ORDER BY p1.ogc_fid

-- 
Ticket URL: <http://trac.osgeo.org/postgis/ticket/577>
PostGIS <http://trac.osgeo.org/postgis/>
The PostGIS Trac is used for bug, enhancement & task tracking, a user and developer wiki, and a view into the subversion code repository of PostGIS project.