[postgis-devel] ST_Value out of memory

[Etienne Bellemare]

Hi all,

I have points tables containing 1-2M (lidar) points and a raster containing
750k tiles of 100x100 pixels. So I'd like to get the value of the raster for
each point coordinate. But I run out of memory. I was quite surprised to see
it was actually possible to run out of memory with PostgeSQL as I thought it
would switch to disk when the 3Gb RAM memory would fill (I've seen the
process go as high as 1,7 Gb according to the task manager). But indeed it
seems it's possible to run out of memory. The disk containing the db still
have 900 Gb of free space.  The solutions I've seen so far on the web look
more like production database solutions. Mine is actually for research
purpose : I'm the only one making transactions on it so I don't have many
queries running at the same time. Furthermore, I'm not a db ninja, so
messing around with all these settings... I wasn't sure to say the least.

I couldn't isolate a single factor to the bug. Of course, point table size
looks like a factor, the biggest table succeding was 467Mb ( I have tables
of size up to 600Mb). But some 200 Mb tables ran out of memory while other
went OK. Other factor seems to be the tiles (low number of tiles
intersecting the point table, more likely to not run out of memory.) So, I'm
calling for some input on either tests to perform to isolate the bug factors
or to avoid running out of memory.

Etienne
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-devel/attachments/20110117/423f1ac9/attachment.html>