[postgis-devel] [PostGIS] #1005: [raster] Problem with ST_SummaryStats(text, text) results

[PostGIS]

#1005: [raster] Problem with ST_SummaryStats(text, text) results
----------------------------+-----------------------------------------------
 Reporter:  pracine         |       Owner:  dustymugs    
     Type:  defect          |      Status:  assigned     
 Priority:  medium          |   Milestone:  PostGIS 2.0.0
Component:  postgis raster  |     Version:  trunk        
 Keywords:                  |  
----------------------------+-----------------------------------------------

Comment(by robe):

 Pierre,

 Okay I'm seeing similar behavior on PostgreSQL 9.1 for ST_SummaryStats and
 ST_Histogram.  So I think it has to do with if you materialize vs. not.

 So here is a single tile raster I have with 1,517,411 pixels.


 {{{
 -- This takes 1,147ms --
 SELECT (f).*
 FROM (SELECT (ST_SummaryStats(rast,1)) As f
 from philly_map) As foo;
 }}}

 --but this takes 214 ms

 {{{

 SELECT (f).*
 FROM (SELECT (ST_SummaryStats(rast,1)) As f
 from philly_map OFFSET 0) As foo;
 }}}

 NOTE in the second -- I used the OFFSET 0 hack to force materialization of
 the subselect as described here --
 http://www.postgresonline.com/journal/archives/113-How-to-force-
 PostgreSQL-to-use-a-pre-calculated-value.html

 -- The histogram case is tempermental if it materializes
 -- or not since it is dependent on number of rows


 {{{
 -- Takes 294 ms returns 22 rows (with the offset takes 306ms -- so about
 the same
 SELECT (f).*
 FROM (SELECT ST_Histogram(rast,1) As f
 from philly_map) As foo;

 }}}

 So I guess short answer -- if we are going to be doing wrapper stuff, we
 better put in an OFFSET 0 in there just to be safe.  Haven't tested on
 8.4, but as I recall way back, its the same story.

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