<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html; charset=UTF-8" http-equiv="Content-Type">
</head>
<body bgcolor="#ffffff" text="#000000">
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?<br>
<br>
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)));<br>
QUERY
PLAN
<br>
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------<br>
Seq Scan on public.srtm3 (cost=0.00..23.90 rows=1 width=32) (actual
time=73.355..73.374 rows=1 loops=1)<br>
Output: st_value(rast, 1,
st_transform('0101000020E61000000000000000A05F400000000000C04240'::geometry,
st_srid(rast)), true)<br>
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))<br>
Total runtime: 73.429 ms<br>
(4 rows)<br>
<br>
On 11/1/2011 8:43 PM, Michael Smedberg wrote:
<blockquote
cite="mid:CAE5gK6rgQ_YrYA8NW6YR6UD0mqarFA=Z6xYCWT9BAz4n=X39HQ@mail.gmail.com"
type="cite">You can use the EXPLAIN command (<a moz-do-not-send="true"
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 moz-do-not-send="true" href="mailto:elliott@cpi.com">elliott@cpi.com</a>></span>
wrote:<br>
<blockquote class="gmail_quote"
style="border-left: 1px solid rgb(204, 204, 204); margin: 0pt 0pt 0pt 0.8ex; padding-left: 1ex;">I
know that the tiles are indexed. How do I tell if the query is using
the index?
<div>
<div class="h5"><br>
<br>
On 11/1/2011 5:34 PM, Pierre Racine wrote:<br>
<blockquote class="gmail_quote"
style="border-left: 1px solid rgb(204, 204, 204); margin: 0pt 0pt 0pt 0.8ex; padding-left: 1ex;">
Did you check if your query is using the index?<br>
<br>
<br>
<blockquote class="gmail_quote"
style="border-left: 1px solid rgb(204, 204, 204); margin: 0pt 0pt 0pt 0.8ex; padding-left: 1ex;">
-----Original Message-----<br>
From: <a moz-do-not-send="true"
href="mailto:postgis-users-bounces@postgis.refractions.net"
target="_blank">postgis-users-bounces@postgis.refractions.net</a>
[mailto:<a moz-do-not-send="true" href="mailto:postgis-users-"
target="_blank">postgis-users-</a><br>
<a moz-do-not-send="true"
href="mailto:bounces@postgis.refractions.net" target="_blank">bounces@postgis.refractions.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="border-left: 1px solid rgb(204, 204, 204); margin: 0pt 0pt 0pt 0.8ex; padding-left: 1ex;">
On 1 November 2011 20:23, elliott<<a moz-do-not-send="true"
href="mailto:elliott@cpi.com" target="_blank">elliott@cpi.com</a>>
wrote:<br>
<br>
<br>
<blockquote class="gmail_quote"
style="border-left: 1px solid rgb(204, 204, 204); margin: 0pt 0pt 0pt 0.8ex; 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="border-left: 1px solid rgb(204, 204, 204); margin: 0pt 0pt 0pt 0.8ex; padding-left: 1ex;">
<blockquote class="gmail_quote"
style="border-left: 1px solid rgb(204, 204, 204); margin: 0pt 0pt 0pt 0.8ex; 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="border-left: 1px solid rgb(204, 204, 204); margin: 0pt 0pt 0pt 0.8ex; 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="border-left: 1px solid rgb(204, 204, 204); margin: 0pt 0pt 0pt 0.8ex; 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_Point(
lon,lat), 4326),<br>
ST_SRID(rast))) from srtm3 where filename='filename'<br>
AND ST_Intersects(rast, ST_Transform(ST_SetSRID(ST_Point(lat,lon),<br>
4326), ST_SRID(rast)));<br>
<br>
<blockquote class="gmail_quote"
style="border-left: 1px solid rgb(204, 204, 204); margin: 0pt 0pt 0pt 0.8ex; padding-left: 1ex;">
Best regards,<br>
<br>
<br>
</blockquote>
_______________________________________________<br>
postgis-users mailing list<br>
<a moz-do-not-send="true"
href="mailto:postgis-users@postgis.refractions.net" target="_blank">postgis-users@postgis.refractions.net</a><br>
<a moz-do-not-send="true"
href="http://postgis.refractions.net/mailman/listinfo/postgis-users"
target="_blank">http://postgis.refractions.net/mailman/listinfo/postgis-users</a><br>
<br>
</blockquote>
_______________________________________________<br>
postgis-users mailing list<br>
<a moz-do-not-send="true"
href="mailto:postgis-users@postgis.refractions.net" target="_blank">postgis-users@postgis.refractions.net</a><br>
<a moz-do-not-send="true"
href="http://postgis.refractions.net/mailman/listinfo/postgis-users"
target="_blank">http://postgis.refractions.net/mailman/listinfo/postgis-users</a><br>
<br>
<br>
</blockquote>
<br>
_______________________________________________<br>
postgis-users mailing list<br>
<a moz-do-not-send="true"
href="mailto:postgis-users@postgis.refractions.net" target="_blank">postgis-users@postgis.refractions.net</a><br>
<a moz-do-not-send="true"
href="http://postgis.refractions.net/mailman/listinfo/postgis-users"
target="_blank">http://postgis.refractions.net/mailman/listinfo/postgis-users</a><br>
</div>
</div>
</blockquote>
</div>
<br>
<pre wrap="">
<fieldset class="mimeAttachmentHeader"></fieldset>
_______________________________________________
postgis-users mailing list
<a class="moz-txt-link-abbreviated" href="mailto:postgis-users@postgis.refractions.net">postgis-users@postgis.refractions.net</a>
<a class="moz-txt-link-freetext" href="http://postgis.refractions.net/mailman/listinfo/postgis-users">http://postgis.refractions.net/mailman/listinfo/postgis-users</a>
</pre>
</blockquote>
<br>
</body>
</html>