To sub-select or not sub-select in PostgreSQL

31 August 2009   0 comments   Linux

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.

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:
How do log ALL PostgresSQL 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
Fastest "boolean SQL queries" possible with Django 14 January 2011
UPPER vs. ILIKE 19 April 2010
Speed test between django_mongokit and postgresql_psycopg2 09 March 2010
Why bother with MySQL... 09 October 2008
Sorting transform function in PostgreSQL 03 August 2006