[postgis-devel] Query Optimizer on outdb rasters

[David Haynes II]

Hello,

I have a question about the query optimizer and its performance on spatial
datasets, specifically rasters. My use case is rather unique, the
application that I am developing allows researchers to request
summarizations of various geographic boundaries around the world. Therefore
our raster datasets are global. We are conducting some benchmarks for our
system and we noticed something unexpected. Hopefully we are doing
something wrong.

The query is the same except the first is run on a raster (46gigs) in outdb
and the second is the same raster (46gigs) stored indb.
The raster is multibanded (13), with each band representing one entire
MODIS global scene. A single year of MODIS is approximately 3.6 gigs.

The outdb is being out performed by indb, because the query optimizer gets
smarter. In the explain syntax you can all see all the extra pieces that
are brought in with oudb.

with poly as
( SELECT gid, label as name, ST_Transform(geom, 6842) as geom  FROM
us_counties )
, rast_select as
( SELECT r.rid as id, s.name , ST_CLIP(r.rast, s.geom) as rast from
rasters.glc2000 as r inner join poly as s on ST_Intersects(r.rast, s.geom) )
select r.id, r.name, ST_Count(r.rast, 1, True)


QUERY PLAN With Outdb
--------------------------------------------------------------------------------------------------
Sort   (cost=93911.29..93926.80 rows=6204 width=254)
  Sort Key: r.id, r.name
  CTE poly
    ->  Seq Scan on us_counties  (cost=0.00..112.86 rows=3109 width=62247)
  CTE rast_select
    ->  Nested Loop  (cost=0.28..76131.41 rows=62033 width=1086)
          ->  CTE Scan on poly s  (cost=0.00..62.18 rows=3109 width=250)
          ->  Index Scan using modis_rast_gist on modis r_1
 (cost=0.28..24.40 rows=2 width=836)
                Index Cond: ((rast)::geometry && s.geom)
                Filter: _st_intersects(s.geom, rast, NULL::integer)
  ->  HashAggregate  (cost=17214.16..17276.20 rows=6204 width=254)
        ->  CTE Scan on rast_select r  (cost=0.00..1240.66 rows=62033
width=254)

QUERY PLAN With Indb
-------------------------------------------------------------------------------------------------------------
Sort   (cost=69547.29..69562.80 rows=6204 width=254)
  Sort Key: r.id, r.name
  CTE poly
    ->  Seq Scan on us_counties  (cost=0.00..112.86 rows=3109 width=62247)
  CTE rast_select
    ->  Nested Loop  (cost=0.28..51767.41 rows=62033 width=272)
          ->  CTE Scan on poly s  (cost=0.00..62.18 rows=3109 width=250)
          ->  Index Scan using modis_noout_rast_gist on modis_noout r_1
 (cost=0.28..16.56 rows=2 width=22)
                Index Cond: ((rast)::geometry && s.geom)
                Filter: _st_intersects(s.geom, rast, NULL::integer)
  ->  HashAggregate  (cost=17214.16..17276.20 rows=6204 width=254)
        ->  CTE Scan on rast_select r  (cost=0.00..1240.66 rows=62033
width=254)
-- 
David Haynes, Ph.D.
Research Associate Terra Populus
Minnesota Population Center
www.terrapop.org
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-devel/attachments/20150519/3e40d322/attachment.html>