Chris, Did you confirm that your btree index works fine without the spatial query part? Run the query without the spatial part at all. If you don't have enough data distinct categories, its often faster for PostgreSQL not to use the btree index or at least it thinks so. Its also sometimes some problem with differences in data types used that it can't use an index. I don't think that is your issue here though. One way to try to force it to use all indexes is to do a set enable_seqscan = off; Before running the query and if that works, increasing the seq_page_cost Hope that helps Leo and Regina, h <http://www.postgis.us> ttp://www.postgis.us
_____ From: postgis-users-boun...@postgis.refractions.net [mailto:postgis-users-boun...@postgis.refractions.net] On Behalf Of Christian Beaumont Sent: Friday, July 16, 2010 12:31 PM To: postgis-users@postgis.refractions.net Subject: [postgis-users] Execution plan with spatial index Greetings, I have a table with around a million rows with each row containing a geography POINT (I'm covering the entire world). With these rows I need to perform many nearest neighbor searches to locate other entities within a range of 0 to 100km. The location field has a GiST index With 5,000 records in the table my average query took around 0.5ms; however, now I have a million records the query time has gone up to around 4ms. I did a reindex/vacuum etc. My goal is to try and make this query as fast as possible since it underpins everything I'm doing and the target hardware won't be as powerful as my development box. As well as the location column, I also have a "entity_type" column that is a simple integer - 1, 2, ... (n)... I figured I could improve performance by adding an index on the entity_type column, then filter the rowset prior to nearest neighbor search. My logic was that it must be quicker to isolate a 10% subset of the records using a simple integer index before feeding in to the expensive GiST index. Unfortunately when I did this, PostgresSQL didn't use my entity_type_idx at all. Instead, it did the nearest neighbor search using the GiST index, then did a simple filter on the collected records based on the entity_type. I tried a few tricks to make it use the index but no-luck. Any ideas for speeding this up would be very much appreciated! Right now my best idea would be to have separate tables for each entity type, but that wouldn't be fun as I don't know the entity types in advance. cheers, -chris Here is pseudo-code of the query and execution plan/analysis. CB_GetPlace() is one of my helper functions that returns a geography from an entity id (marked stable). SELECT entity_id, category_id, ST_Distance(location, CB_GetPlace(someEntityID)) as arcLength FROM entities WHERE category_id = 1 AND ST_DWithin(location, CB_GetPlace(someEntityID), someRadius) ORDER BY arcLength; "Sort (cost=26.64..26.64 rows=1 width=140) (actual time=4.207..4.209 rows=16 loops=1)" " Sort Key: (_st_distance(location, cb_getplace(someEntityID::bigint), 0::double precision, true))" " Sort Method: quicksort Memory: 18kB" " -> Index Scan using place_idx on "entities" (cost=0.03..26.63 rows=1 width=140) (actual time=1.691..4.187 rows=16 loops=1)" " Index Cond: (location && _st_expand(cb_getplace(someEntityID::bigint), someRadius::double precision))" " Filter: ((urt_id = 1) AND (cb_getplace(someEntityID::bigint) && _st_expand(location, someRadius::double precision)) AND _st_dwithin(location, cb_getplace(someEntityID::bigint), someRadius::double precision, true))" "Total runtime: 4.242 ms" If it matters, my test platform is PostGIS 1.5.1 with PostgreSQL 8.4.4-1 (Windows 32bit build) though my target platform is Ubuntu x64.
_______________________________________________ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
