[postgis-devel] Query Optimizer on outdb rasters

[Bborie Park]

Heh. Super late. But, I believe the problem has something to do with the
out-db and how it plays with PostgreSQL's cache, or more specifically, the
lack there of. In-db, once warm, is essentially using some cache
(PostgreSQL or the OS).

Out-db is a constant hit. How is your out-db stored? Local store? Shared
file-system like NFS? If shared, using some caching?

-bborie

On Tue, May 19, 2015 at 1:53 PM, David Haynes II <dahaynes at umn.edu> wrote:

> 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
>
> _______________________________________________
> postgis-devel mailing list
> postgis-devel at lists.osgeo.org
> http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-devel
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-devel/attachments/20150618/3b703b6f/attachment.html>