I have a query that looks like this (simplified for the sake of brevity):
SELECT
gl.id,
miles_between_lat_long(46.519582, 6.632121,
gl.latitude::numeric, gl.longitude::numeric
) AS distance
FROM
kungfuperson gl
miles_between_lat_long(46.519582, 6.632121,
gl.latitude::numeric, gl.longitude::numeric
) < 5000
ORDER BY distance ASC;
It basically finds other entries in a table (which has columns for latitude and longitude) but only returns those that are within a certain distance (from a known latitude/longitude point). Running this query on my small table takes about 7 milliseconds. (I used EXPLAIN ANALYZE)
So I thought, how about if I wrap it in a sub-select so that the function miles_between_lat_long()
is only used once per row. Surely that would make it a lot faster. I accept that it wouldn't be twice as fast because wrapping it in a sub-select would also add some extra computation. Here's the "improved" version:
SELECT * FROM (
SELECT
gl.id,
miles_between_lat_long(46.519582, 6.632121,
gl.latitude::numeric, gl.longitude::numeric
) AS distance
FROM
kungfuperson gl
) AS ss
WHERE ss.distance < 5000
ORDER BY ss.distance ASC;
To test it I wrote a little script that randomly runs these two versions many many times (about 50 times) each and then compare the averages.
Truncated! Read the rest by clicking the link below.