[postgis-users] Slow Query Times for Split Tiles

[Michael Smedberg]

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> 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<postgis-users-bounces at postgis.refractions.net>[mailto:
>>> postgis-users-
>>> bounces at postgis.refractions.**net <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>   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<postgis-users at postgis.refractions.net>
>>> http://postgis.refractions.**net/mailman/listinfo/postgis-**users<http://postgis.refractions.net/mailman/listinfo/postgis-users>
>>>
>>>
>> ______________________________**_________________
>> postgis-users mailing list
>> postgis-users at postgis.**refractions.net<postgis-users at postgis.refractions.net>
>> http://postgis.refractions.**net/mailman/listinfo/postgis-**users<http://postgis.refractions.net/mailman/listinfo/postgis-users>
>>
>>
>>
>
> ______________________________**_________________
> postgis-users mailing list
> postgis-users at postgis.**refractions.net<postgis-users at postgis.refractions.net>
> http://postgis.refractions.**net/mailman/listinfo/postgis-**users<http://postgis.refractions.net/mailman/listinfo/postgis-users>
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20111101/be15d746/attachment.html>