Do you train Kung Fu?
Or know someone who does?
Then check out KungFuPeople.com
Mobile version of this pageCustom CacheMiddleware that tells Javascript a page is cached in Django
Next:
Cgunit - Online Gallery
Related blogs
PostgreSQL, MySQL or SQLiteCreating a user for postgresql
Adding a year in PostgreSQL
Date formatting in python or in PostgreSQL
Integer division in programming languages
Running simple SQL commands on the command line
pg_class to check if table exists
List of casts in PostgreSQL
Just Oracle and IBM?
ALTER TABLE patch
Quick PostgreSQL optimization story
Date formatting in Python or in PostgreSQL (part II)
Sorting transform function in PostgreSQL
Why bother with MySQL...
Speed test between django_mongokit and postgresql_psycopg2
UPPER vs. ILIKE
Fastest "boolean SQL queries" possible with Django
Optimization of getting random rows out of a PostgreSQL in Django
Connecting with psycopg2 without a username and password
Related by category
Linux
To sub-select or not sub-select in PostgreSQL
31st of August 2009
I have a query that looks like this (simplified for the sake of brevity):
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
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 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.
