<div dir="ltr"><div><div>Hello,</div><div><br></div><div>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.</div><div><br></div><div>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.</div><div>The raster is multibanded (13), with each band representing one entire MODIS global scene. A single year of MODIS is approximately 3.6 gigs.</div><div><br></div><div>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. </div><div><br></div><div>with poly as </div><div>( SELECT gid, label as name, ST_Transform(geom, 6842) as geom FROM us_counties )</div><div>, rast_select as</div><div>( SELECT r.rid as id, <a href="http://s.name">s.name</a> , 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) )</div><div>select <a href="http://r.id">r.id</a>, <a href="http://r.name">r.name</a>, ST_Count(r.rast, 1, True)</div><div><span class="" style="white-space:pre"> </span> </div><div><span class="" style="white-space:pre"> </span> </div><div>QUERY PLAN With Outdb <span class="" style="white-space:pre"> </span></div><div>--------------------------------------------------------------------------------------------------<span class="" style="white-space:pre"> </span></div><div>Sort (cost=93911.29..93926.80 rows=6204 width=254)<span class="" style="white-space:pre"> </span></div><div> Sort Key: <a href="http://r.id">r.id</a>, <a href="http://r.name">r.name</a><span class="" style="white-space:pre"> </span></div><div> CTE poly<span class="" style="white-space:pre"> </span></div><div> -> Seq Scan on us_counties (cost=0.00..112.86 rows=3109 width=62247)<span class="" style="white-space:pre"> </span></div><div> CTE rast_select<span class="" style="white-space:pre"> </span></div><div> -> Nested Loop (cost=0.28..76131.41 rows=62033 width=1086)<span class="" style="white-space:pre"> </span></div><div> -> CTE Scan on poly s (cost=0.00..62.18 rows=3109 width=250)<span class="" style="white-space:pre"> </span></div><div> -> Index Scan using modis_rast_gist on modis r_1 (cost=0.28..24.40 rows=2 width=836)<span class="" style="white-space:pre"> </span></div><div> Index Cond: ((rast)::geometry && s.geom)<span class="" style="white-space:pre"> </span></div><div> Filter: _st_intersects(s.geom, rast, NULL::integer)<span class="" style="white-space:pre"> </span></div><div> -> HashAggregate (cost=17214.16..17276.20 rows=6204 width=254)<span class="" style="white-space:pre"> </span></div><div> -> CTE Scan on rast_select r (cost=0.00..1240.66 rows=62033 width=254)<span class="" style="white-space:pre"> </span></div><div><br></div><div>QUERY PLAN With Indb <span class="" style="white-space:pre"> </span></div><div>-------------------------------------------------------------------------------------------------------------<span class="" style="white-space:pre"> </span></div><div>Sort (cost=69547.29..69562.80 rows=6204 width=254)<span class="" style="white-space:pre"> </span></div><div> Sort Key: <a href="http://r.id">r.id</a>, <a href="http://r.name">r.name</a><span class="" style="white-space:pre"> </span></div><div> CTE poly<span class="" style="white-space:pre"> </span></div><div> -> Seq Scan on us_counties (cost=0.00..112.86 rows=3109 width=62247)<span class="" style="white-space:pre"> </span></div><div> CTE rast_select<span class="" style="white-space:pre"> </span></div><div> -> Nested Loop (cost=0.28..51767.41 rows=62033 width=272)<span class="" style="white-space:pre"> </span></div><div> -> CTE Scan on poly s (cost=0.00..62.18 rows=3109 width=250)<span class="" style="white-space:pre"> </span></div><div> -> Index Scan using modis_noout_rast_gist on modis_noout r_1 (cost=0.28..16.56 rows=2 width=22)<span class="" style="white-space:pre"> </span></div><div> Index Cond: ((rast)::geometry && s.geom)<span class="" style="white-space:pre"> </span></div><div> Filter: _st_intersects(s.geom, rast, NULL::integer)<span class="" style="white-space:pre"> </span></div><div> -> HashAggregate (cost=17214.16..17276.20 rows=6204 width=254)<span class="" style="white-space:pre"> </span></div><div> -> CTE Scan on rast_select r (cost=0.00..1240.66 rows=62033 width=254)</div></div>-- <br><div class="gmail_signature"><div dir="ltr">David Haynes, Ph.D.<div>Research Associate Terra Populus</div><div>Minnesota Population Center</div><div><a href="http://www.terrapop.org" target="_blank">www.terrapop.org</a></div></div></div>
</div>