[postgis-devel] [PostGIS] #851: [raster] Numerous ST_Value query cause the memory to grow indefinitely

[PostGIS]

#851: [raster] Numerous ST_Value query cause the memory to grow indefinitely
----------------------------+-----------------------------------------------
 Reporter:  pracine         |       Owner:  jorgearevalo 
     Type:  defect          |      Status:  assigned     
 Priority:  blocker         |   Milestone:  PostGIS 2.0.0
Component:  postgis raster  |     Version:  trunk        
 Keywords:                  |  
----------------------------+-----------------------------------------------

Comment(by pracine):

 Here are my results:

 {{{
 CREATE OR REPLACE FUNCTION ST_Values(rast raster, band int, OUT val double
 precision)
 RETURNS SETOF double precision
     AS $$
     SELECT ST_Value($1, $2, x, y) val FROM generate_series(1,
 ST_Width($1)) x , generate_series(1, ST_Height($1)) y;
     $$
     LANGUAGE SQL IMMUTABLE;

 --Test 1
 --With rt_context_destroy(): no memory growth
 --Without rt_context_destroy(): very small memory growth
 --2600 milliseconds for 10000
 SELECT ST_Values(rast, 1) val, count(*) count
 FROM srtm_22_03_tiled_10x10
 --WHERE rid <= 10000
 GROUP BY val
 ORDER BY count DESC

 --Test 2
 --With rt_context_destroy(): very small memory growth
 --Without rt_context_destroy(): good memory growth. Crashes eventually.
 --90000 milliseconds for 10000
 SELECT val, count(*) count
 FROM (SELECT ST_Value(rast, 1, x, y) val
       FROM generate_series(1, 10) x, generate_series(1, 10) y,
 srtm_22_03_tiled_10x10
       --WHERE rid <= 10000
      ) foo
 GROUP BY val
 ORDER BY count DESC;

 --Test 3
 --With rt_context_destroy(): no memory growth
 --Without rt_context_destroy(): good memory growth. Crashes eventually.
 --8500 milliseconds for 10000
 SELECT ST_Value(rast, 1, x, y)
 FROM generate_series(1, 10) x, generate_series(1, 10) y,
 srtm_22_03_tiled_10x10
 --WHERE rid <= 10000

 --Test 4
 --With rt_context_destroy(): no memory growth
 --Without rt_context_destroy(): no memory growth
 --10000 milliseconds for 10000
 SELECT x, y
 FROM generate_series(1, 10) x, generate_series(1, 10) y,
 srtm_22_03_tiled_10x10
 --WHERE rid <= 10000
 }}}

 Conclusion: rt_context_destroy() is still very useful...

 Remark: test 2 used to be twice faster than test 1. I will eventually
 search further in the code history when it became slower. I suspect a
 change in serialization.

 The goal of all that is to make sure PL/pgSQL functions can iterate on
 every pixels of a coverage fast enough and without crashing.

-- 
Ticket URL: <http://trac.osgeo.org/postgis/ticket/851#comment:28>
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.