3

My question is about the fastest way to order query results by approximate distance. I've seen conflicting information about this.

My data are lat/lng points stored using WGS84 (SRID 4326).

The general query is simple enough

select * from table where [condition]
order by [distance function]

However, I'm not sure which of the following performs best:

st_distance(geom, st_setsrid(st_point(:lng, :lat), 4326));

geom <<->> st_setsrid(st_point(:lng, :lat), 4326));

geom <-> st_setsrid(st_point(:lng, :lat), 4326));

geom <#> st_setsrid(st_point(:lng, :lat), 4326));

I also understand that each of these can be sped up with different indexes, but I have trouble translating that in actual performance differences. For example, the docs don't mention anything about indexing regarding st_distance. Some answers on SE say that casting to geography speeds up st_distance. Furthermore, while <#> can use any spatial index, <-> will only make use of a 2D GiST index, and <<->> will only make use of a n-d GiST index.

Does that make <#> potentially the fastest?

  • 3
    note that <#> gets distances between the bounding boxes, <<->> between bounding box centroids. while possibly fast, those are only approximations. note also: all KNNs do only use indexes when compared to a constant geometry, as stated in the docs. – ThingumaBob Feb 9 '18 at 16:57
  • Thanks! So for points I'm guessing <-> is more precise than <#> but performance-wise they are the same? Also is there any difference in performance (or anything else) between <<->> and <-> when used on 2D points? – yeedle Feb 9 '18 at 17:00
  • 1
    Bounding box of a point is a point so it is precise. – user30184 Feb 9 '18 at 17:05
  • D'oh!. So for points they are equivalent. What about the 2D vs n-D operator when dealing with 2D points? – yeedle Feb 9 '18 at 17:07
  • You should be careful when calculating distances with lat lon coordinate systems as wgs84, because the distance value depends on the latitude. You should cast to geography not because of performance but to get correct results. – LauriK Feb 9 '18 at 20:15

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.