To sub-select or not sub-select in PostgreSQL

31 August 2009   0 comments   Linux

Mind That Age!

This blog post is 9 years old! Most likely, its content is outdated. Especially if it's technical.

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.

Comments

Your email will never ever be published


Related posts

Previous:
Custom CacheMiddleware that tells Javascript a page is cached in Django 24 August 2009
Next:
Cgunit - Online Gallery 31 August 2009
Related by Keyword:
Best EXPLAIN ANALYZE benchmark script 19 April 2018
When Docker is too slow, use your host 11 January 2018
How do log ALL PostgreSQL SQL happening 20 July 2015
Fastest database for Tornado 09 October 2013
Postgres collation errors on CITEXT fields when upgrading to 9.1 21 May 2012
Related by Text:
jQuery and Highslide JS 08 January 2008
I'm back! Peterbe.com has been renewed 05 June 2005
Anti-McCain propaganda videos 12 August 2008
I'm Prolog 01 May 2007
Ever wondered how much $87 Billion is? 04 November 2003