[postgis-users] use of spatial index in nearest neighbour query ?

[Greg Williamson]

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.There are some examples in the documentation (for 8.4 see <http://www.postgresql.org/docs/8.4/interactive/sql-explain.html>).

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.

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.

HTH,

Greg Williamson




________________________________
From: Smith Roman <autisi at yahoo.com>
To: postgis-users at postgis.refractions.net
Sent: Friday, September 4, 2009 2:20:05 AM
Subject: [postgis-users] use of spatial index in nearest neighbour query ?


Hi,
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.
SELECT gid, descriptio, name, address, suburb, AsText(the_geom), 
           ST_Distance(ST_Transform(the_geom, 26331), ST_Transform(setsrid(makepoint(3.3315343, 6.5593127), 4326), 26331)) as dist FROM lagos_poi84
             WHERE st_dwithin(ST_Transform(the_geom, 26331), ST_Transform(setsrid(makepoint(3.3315343, 6.5593127), 4326), 26331), 50000) 
            and suburb ~* '' and descriptio ~* 'atm' AND name ~* '' ORDER BY dist limit 50

I created an index on the transformed geometry as follows

CREATE INDEX "lagos_poi84UTM26331_idx" ON "public"."lagos_poi84" USING GIST ( ST_Transform("the_geom", 26331) ); 		 



In the postgis manual, it was said that the st_dwithin() function uses a spatial index if available. 
Also, how can tell when a spatial query in postgis is using a spatial index ?
Cheers,
Roman. 



      
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20090904/9d0f89f5/attachment.html>