How to re-use result for SELECT, WHERE and ORDER BY clauses?

Question

The following query returns the venues near us (lat: 62.0, lon: 25.0) inside whose radius we fall in ordered by distance:

SELECT *, 
     earth_distance(ll_to_earth(62.0, 25.0), 
     ll_to_earth(lat, lon)) AS distance 
FROM venues 
WHERE earth_distance(ll_to_earth(62.0, 25.0), ll_to_earth(lat, lon)) <= radius 
ORDER BY earth_distance(ll_to_earth(62.0, 25.0), ll_to_earth(lat, lon))

Is it possible (and advisable) to re-use the result from earth_distance(ll_to_earth(62.0, 25.0), ll_to_earth(lat, lon)) instead of computing it separately for SELECT, WHERE and ORDER BY clauses?

Answer 1

In the GROUP BY and ORDER BY clauses you can refer to column aliases (output columns) or even ordinal numbers of SELECT list items. The manual:

Each expression can be the name or ordinal number of an output column (SELECT list item), or it can be an arbitrary expression formed from input-column values.

Bold emphasis mine.

But in the WHERE and HAVING clauses, you can only refer to columns from base tables (input columns), so you have to spell out your function call.

SELECT *, earth_distance(ll_to_earth(62.0, 25.0), ll_to_earth(lat, lon)) AS dist
FROM   venues 
WHERE  earth_distance(ll_to_earth(62.0, 25.0), ll_to_earth(lat, lon)) <= radius 
ORDER  BY distance;

To see which is faster, calculation in a CTE or subquery, just test it with EXPLAIN ANALYZE:

SELECT *
FROM  (
   SELECT *
         ,earth_distance(ll_to_earth(62.0, 25.0), ll_to_earth(lat, lon)) AS dist
   FROM   venues
   ) x
WHERE  distance <= radius 
ORDER  BY distance;

Like Mike commented, by declaring a function STABLE (or IMMUTABLE) you inform the query planner that results from a function call can be reused multiple times for identical calls within a single statement. The manual:

A STABLE function cannot modify the database and is guaranteed to return the same results given the same arguments for all rows within a single statement. This category allows the optimizer to optimize multiple calls of the function to a single call.

Bold emphasis mine.