Add Indexes to Speed Up Geocoder Near Search

Add indexes to speed up Geocoder near search

The slowdown is likely caused by math operations and not by fetching table data. Part of your criteria is not against record fields but against the outcome of the math operation on other records so it is becoming an O(N²).

The reason Postgres does not use an index and chooses Seq scan instead is because it decides that most of the table records will have to be fetched while querying. When most records in the table are to be fetched, indexes may not bring much benefit if any.

To speed things up you should consider using spacial indexes and vicinity-based search of PostGis or, alternatively, Elasticsearch with Geo Distance Query.

Is it necessary to add index to latitude and longitude fields

If you end up querying database for records using latitude and longitude, you'll definitely benefit from adding an index. Indexes will be used not only for exact matching queries, but also for comparison queries, such as select * from table_name where latitude between 30 and 40 and longitude > 50.
Depending on the queries and the number of records, postgres query planner will choose the most optimal way to find matching records (either sequential scan, or index scan).

Spatial index is slow when trying to find all the points within a range of a geocode. How do I make this faster?

You might need to use an Index hint (i.e. WITH(INDEX( [INDEX_NAME] )) I think 2008 R2 might have resolved this though.

Select top 100 
ci.Geocode.STDistance(@g), ci.CIOI  
from CustomerInformation WITH(INDEX(IX_CI_Geocode))
ci where ci.Geocode.Filter(@region) = 1 
order by ci.Geocode.STDistance(@g) asc 

Proximity Search

If there are enough records for speed to matter, here's a way to index them ahead of time.

Define a grid of bins about 20 miles on a side. Store the bin number with each store's record. At search time, compute the numbers of all bins that intersect a 20-mile radius from your search point. Then retrieve all stores in any of those bins, and proceed as before.

---

Related Topics