[postgis-users] Performance and memory problems
andrea.aime at aliceposta.it
Thu Dec 4 04:02:33 PST 2003
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
> 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?
More information about the postgis-users