5

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?

7
  • 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.
    – geozelot
    Commented Feb 9, 2018 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
    Commented Feb 9, 2018 at 17:00
  • 1
    Bounding box of a point is a point so it is precise.
    – user30184
    Commented Feb 9, 2018 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
    Commented Feb 9, 2018 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
    Commented Feb 9, 2018 at 20:15

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.