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