[postgis-users] Slow Query Times for Split Tiles

[elliott]

Thanks for the suggestion.  From the output, it doesn't look like it is 
using the index even though an index was created with the 
raster2pgsql.py script.  Is there a specific way to tell the query to 
use the index?

ENVDB=# EXPLAIN ANALYZE VERBOSE select ST_Value(rast, 
ST_Transform(ST_SetSRID(ST_Point(126.5, 37.5), 4326), ST_SRID(rast))) 
from srtm3 where filename='N37E126.hgt' AND ST_Intersects(rast, 
ST_Transform(ST_SetSRID(ST_Point(126.5, 37.5), 4326), ST_SRID(rast)));
                                                                                                                                                               QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
  Seq Scan on public.srtm3  (cost=0.00..23.90 rows=1 width=32) (actual 
time=73.355..73.374 rows=1 loops=1)
    Output: st_value(rast, 1, 
st_transform('0101000020E61000000000000000A05F400000000000C04240'::geometry, 
st_srid(rast)), true)
    Filter: ((srtm3.filename = 'N37E126.hgt'::text) AND 
((srtm3.rast)::geometry && 
st_transform('0101000020E61000000000000000A05F400000000000C04240'::geometry, 
st_srid(srtm3.rast))) AND _st_intersects(srtm3.rast, 
st_transform('0101000020E61000000000000000A05F400000000000C04240'::geometry, 
st_srid(srtm3.rast)), NULL::integer))
  Total runtime: 73.429 ms
(4 rows)

On 11/1/2011 8:43 PM, Michael Smedberg wrote:
> You can use the EXPLAIN command 
> (http://www.postgresql.org/docs/8.4/static/sql-explain.html) to see 
> what indices are used to execute a SQL statement.
>
> On Tue, Nov 1, 2011 at 2:42 PM, elliott <elliott at cpi.com 
> <mailto:elliott at cpi.com>> wrote:
>
>     I know that the tiles are indexed.  How do I tell if the query is
>     using the index?
>
>
>     On 11/1/2011 5:34 PM, Pierre Racine wrote:
>
>         Did you check if your query is using the index?
>
>
>             -----Original Message-----
>             From: postgis-users-bounces at postgis.refractions.net
>             <mailto:postgis-users-bounces at postgis.refractions.net>
>             [mailto:postgis-users- <mailto:postgis-users->
>             bounces at postgis.refractions.net
>             <mailto:bounces at postgis.refractions.net>] On Behalf Of elliott
>             Sent: Tuesday, November 01, 2011 4:48 PM
>             To: PostGIS Users Discussion
>             Subject: Re: [postgis-users] Slow Query Times for Split Tiles
>
>             On 11/1/2011 4:27 PM, Mateusz Łoskot wrote:
>
>                 On 1 November 2011 20:23, elliott<elliott at cpi.com
>                 <mailto:elliott at cpi.com>>   wrote:
>
>
>                     Should splitting raster files into tiles increase
>                     query performance?
>
>                     I have a two 1201x1201 raster tiles that were
>                     loaded by the following
>                     scripts:
>
>                     raster2pgsql.py -a -r N37E126.hgt -F N37E126 -t
>                     srtm3 -s 4326
>                     -k1201x1201>  srtm.sql psql -f srtm.sql TEST
>
>                     raster2pgsql.py -a -r N37E127.hgt -F N37E127 -t
>                     srtm3 -s 4327
>                     -k50x50>  srtm.sql psql -f srtm.sql TEST
>
>                     Querying these raster files appears to produce
>                     approximately the same
>                     timing results.  Should the 50x50 have better
>                     performance than the
>
>             1201x1201?
>
>                     Taking almost 3 minutes to query 10000 points
>                     seems to be very slow.
>
>
>                 Do you query tiles or points (what points?)?
>
>
>                      Querying tiles for altitudes at lat/lon pairs.
>
>                 Do you measure itme of only query only or query and
>                 raster blob
>                 processing/rendering?
>
>
>                      Time is for query only.
>
>                 What's your actual SQL command?
>
>
>                      This query is run for 10000 lat/lon pairs -
>
>                      select ST_Value(rast,
>             ST_Transform(ST_SetSRID(ST_Point( lon,lat), 4326),
>             ST_SRID(rast))) from srtm3 where filename='filename'
>             AND ST_Intersects(rast,
>             ST_Transform(ST_SetSRID(ST_Point(lat,lon),
>             4326), ST_SRID(rast)));
>
>                 Best regards,
>
>
>             _______________________________________________
>             postgis-users mailing list
>             postgis-users at postgis.refractions.net
>             <mailto:postgis-users at postgis.refractions.net>
>             http://postgis.refractions.net/mailman/listinfo/postgis-users
>
>         _______________________________________________
>         postgis-users mailing list
>         postgis-users at postgis.refractions.net
>         <mailto:postgis-users at postgis.refractions.net>
>         http://postgis.refractions.net/mailman/listinfo/postgis-users
>
>
>
>     _______________________________________________
>     postgis-users mailing list
>     postgis-users at postgis.refractions.net
>     <mailto:postgis-users at postgis.refractions.net>
>     http://postgis.refractions.net/mailman/listinfo/postgis-users
>
>
>
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>    

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20111102/f0774ff8/attachment.html>