Fwd: Raster performance

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 users to request summarizations of
various geographic boundaries around the world. Therefore our raster
datasets are global. We are in the process of conducting some benchmarks
for our system and we noticed something unexpected.

The query is the same except the first is run on a raster (46gigs) in out
of database (outdb) and the second is the same raster (46gigs) stored in
database (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. But what is also interesting is all the extra pieces that are
brought in with outdb.

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

--
David Haynes, Ph.D.
Research Associate Terra Populus
Minnesota Population Center
www.terrapop.org