[postgis-users] Performance and memory problems

[Andrea Aime]

Andrea Aime wrote:

> Hi all,
> yesterday I've started playing with postgis. Nice tool.
> For the record, since I wanted to play with sources and did not
> want to break a working machine, I've set up a Linux mandrake 9.1
> in a VMWare virtual machine, got the pg 7.4, geos, postgis 0.8 sources 
> and compiled everything (proj is available as an rpm), set up and loaded 
> some shapefile into the dbms, created the r-tree indexes (oh, I also 
> increased the postgres shared memory to around 64MB).
> 
> Now, loading speed is acceptable, index creation is fast (less than a 
> second even with thousands of geometries), but when
> using geos I start to feel the pain. Example: I have two shapefiles
> downloaded from http://nationalatlas.gov/atlasftp.html, specifically
> http://edcftp.cr.usgs.gov/pub/data/nationalatlas/countyp020.tar.gz
> and http://edcftp.cr.usgs.gov/pub/data/nationalatlas/hucs00m020.tar.gz 
> (but using only the polygonal file contained in this tar),
> that is, conties and hydrologic units, that are about 14 MB each
> (6000 counties and 5000 hydrologic units).
> 
> I want to overlay them with the following command:
> 
> select intersection(counties.the_geom, hydrounit_poly.the_geom), 
> counties.county, hydrounit_poly.map_label, hydrounit_poly.reg_name
> into hydro_counties
> from counties, hydrounit_poly
> where counties.the_geom && hydrounit_poly.the_geom
> 
> Explain says that the spatial indexes are used. After 30 seconds of 
> intense computation the backend bombs out due to an out of memory...
> why? The overlay process should keep in memory only the current 
> reference geometry from conties and the intersecting ones from 
> hydrounit_poly, that is, a few kylobytes...
> Moreover I see that the postmaster actually doing the computation was
> using 12 MB or resident memory and 6 MB of shared memory (top output),
> so I don't see why it bombs in the first place...
> 

I increased the shared memory but no luck, it bombs anyway. I now 
suspect is not a memory problem. Backend exits with signal 11.
I've also checked the geometries with the statements:

select count(*) from counties where not isValid(the_geom);
select count(*) from hydrounit_poly where not isValid(the_geom);

but both returns 0. Any suggestion?

Best regards
Andrea Aime