[postgis-devel] [PostGIS] #2024: raster: ST_Union is buggy

[PostGIS]

#2024: raster: ST_Union is buggy
----------------------+-----------------------------------------------------
  Reporter:  robe     |       Owner:  dustymugs    
      Type:  defect   |      Status:  closed       
  Priority:  blocker  |   Milestone:  PostGIS 2.1.0
 Component:  raster   |     Version:  trunk        
Resolution:  fixed    |    Keywords:               
----------------------+-----------------------------------------------------

Comment(by robe):

 FWIW: I really got to rethink my tile sizes.  It seems we are more
 sensitive to number of rows that need to be traversed than we are to
 number of pixels that need to be traversed.  So I think the memory copy
 between the C and PostgreSQL layer is still eating up most of our time.

 My 1000x1000 does so much better:

 {{{  -- both of these should traverse the same number of pixels
 -- 16,958 ms  (1 band) 5 tiles clipping (1000x1000 tile sizes)
 SELECT  ST_Union(rast,1) , count(*)
 FROM (SELECT ST_Clip(rast,geom) As rast
         FROM test_1000_1000
            CROSS JOIN ST_Buffer(ST_GeomFromText('POINT(231237.436173996
 887025.024778253)',26986)
     ,200, 'quad_segs=2') As geom
 WHERE ST_Intersects(rast,  geom ) ) As foo;


 -- 51,714ms  (1 band), 152 tiles clipping (100x100 tile sizes)
 SELECT  ST_Union(rast,1) ,count(*)
 FROM (SELECT ST_Clip(rast,geom) As rast
         FROM test_100_100
            CROSS JOIN ST_Buffer(ST_GeomFromText('POINT(231237.436173996
 887025.024778253)',26986)
     ,200, 'quad_segs=2') As geom
 WHERE ST_Intersects(rast,  geom ) ) As foo;
 }}}

 -- more interestingly, clipping makes things worse in 2.1.0 case even
 though it
 -- should in theory reduce the number of pixels that need to be traversed
 -- here is the same run without clipping

 {{{
 -- 14,196 ms  (1 band) 5 tiles no clipping
 SELECT  ST_Union(rast,1) , count(*)
 FROM (SELECT rast
         FROM test_1000_1000
            CROSS JOIN ST_Buffer(ST_GeomFromText('POINT(231237.436173996
 887025.024778253)',26986)
     ,200, 'quad_segs=2') As geom
 WHERE ST_Intersects(rast,  geom ) ) As foo;

 -- 39,374 ms  (1 band), 152 tiles no clipping
 SELECT  ST_Union(rast,1) ,count(*)
 FROM (SELECT rast
         FROM test_100_100
            CROSS JOIN ST_Buffer(ST_GeomFromText('POINT(231237.436173996
 887025.024778253)',26986)
     ,200, 'quad_segs=2') As geom
 WHERE ST_Intersects(rast,  geom ) ) As foo;
 }}}

 I'm beginning to think that the reason 2.0.1 seemed to improve with
 clipping while 2.1 doesn't is simply because in 2.0.1 the clipping time
 was dwarfed significantly by the union (copy pain), so the benefit it gave
 to union providing smaller tiles much outweighed the additional
 processing.

 In 2.1.0 this is not the case -- clipping speed and union speed are more
 in par so the extra cycles (e.g. 5 or 152 really adds weight since union
 has much less of issue traversing)

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