To sub-select or not sub-select in PostgreSQL

31 August 2009   0 comments   Linux

Mind That Age!

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

Powered by Fusion×

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.

Follow @peterbe on Twitter

Comments

Thank you for posting a comment

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:
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
Connecting with psycopg2 without a username and password 24 February 2011
Optimization of getting random rows out of a PostgreSQL in Django 23 February 2011
Related by Text:
Select all relations in PostgreSQL 10 December 2015
In jQuery, using the :visible selector can be dangerous 14 September 2010
Drop down selects that learn 10 July 2005
Optimization of QuerySet.get() with or without select_related 03 November 2016
CSS selector bug in IE? 05 December 2006