To sub-select or not sub-select in PostgreSQL
31 August 2009
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.
The results are quite interesting:
Version 1: 8.4834 ms Version 2: 7.67792 ms
Clearly there is an advantage with wrapping it in a sub-select but clearly the difference is tiny.
It goes to show that the majority of the time is spent on other things such as ordering and sequential compares rather than on calculations as the one I had in my example. Ah, well. A valuable experience nevertheless.