<html><head><style type="text/css"><!-- DIV {margin:0px;} --></style></head><body><div style="font-family:times new roman,new york,times,serif;font-size:12pt"><div>To see how PostgreSQL is doing something "under the hood," run the SELECT inside an EXPLAIN ANALYZE statement; the system will typically show "Seq Scan" for portions of a query that use a sequential scan and otherwise it will show information on the index it uses and some data on the number of rows expected vs. found.<span> There are some examples in the documentation (for 8.4 see <<a target="_blank" href="http://www.postgresql.org/docs/8.4/interactive/sql-explain.html">http://www.postgresql.org/docs/8.4/interactive/sql-explain.html</a>>).</span><br><br>Typically the planner will choose a sequential scan for tables that are tiny or where the estimated number of rows is quite large. Remember to run "ANALYZE lagos_poi84;" after creating the index to let the planner have a better estimate
fo the number of rows in the table.<br><br>Feel free to post back here with the results of EXPLAIN ANALYZE ..., but also include some details on the postgres version and the specific postGIS/GEOS version.<br><br>HTH,<br><br>Greg Williamson<br></div><div style="font-family: times new roman,new york,times,serif; font-size: 12pt;"><br><div style="font-family: times new roman,new york,times,serif; font-size: 12pt;"><font face="Tahoma" size="2"><hr size="1"><b><span style="font-weight: bold;">From:</span></b> Smith Roman <autisi@yahoo.com><br><b><span style="font-weight: bold;">To:</span></b> postgis-users@postgis.refractions.net<br><b><span style="font-weight: bold;">Sent:</span></b> Friday, September 4, 2009 2:20:05 AM<br><b><span style="font-weight: bold;">Subject:</span></b> [postgis-users] use of spatial index in nearest neighbour query ?<br></font><br>
<table border="0" cellpadding="0" cellspacing="0"><tbody><tr><td style="font-family: inherit; font-style: inherit; font-variant: inherit; font-weight: inherit; font-size: inherit; line-height: inherit; font-size-adjust: inherit; font-stretch: inherit; -x-system-font: none;" valign="top"><div id="yiv373365924"><p>Hi,</p><p></p><p>I will like to find out if this nearest neighbour query uses a postgis spatial index. The query is supposed to get the nearest atm's (POI) around a given location.</p><p>SELECT gid, descriptio, name, address, suburb, AsText(the_geom), <br> ST_Distance(ST_Transform(the_geom, 26331), ST_Transform(setsrid(makepoint(3.3315343, 6.5593127), 4326), 26331)) as dist FROM lagos_poi84<br> WHERE st_dwithin(ST_Transform(the_geom, 26331), ST_Transform(setsrid(makepoint(3.3315343, 6.5593127), 4326), 26331), 50000) <br> and suburb ~* '' and descriptio ~* 'atm' AND name ~* '' ORDER BY dist
limit 50<br><br>I created an index on the transformed geometry as follows<br></p><p>CREATE INDEX "lagos_poi84UTM26331_idx" ON "public"."lagos_poi84" USING GIST ( ST_Transform("the_geom",
26331) ); <br><br><br></p><p>In the postgis manual, it was said that the st_dwithin() function uses a spatial index if available. </p><p>Also, how can tell when a spatial query in postgis is using a spatial index ?</p><p></p><p>Cheers,</p><p>Roman.</p></div></td></tr></tbody></table><br>
</div></div></div><br>
</body></html>