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?