[postgis-users] Slow Query Times for Split Tiles

[elliott]

On 11/1/2011 4:27 PM, Mateusz Łoskot wrote:
> On 1 November 2011 20:23, elliott<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,
>