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

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?

Answers


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

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 the 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;

If you want to know if it's faster to pack the calculation into a CTE or subquery, just test it with EXPLAIN ANALYZE. (I doubt it.)

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. I quote the manual here:

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.


Need Your Help

WPF Custom Items Control with search support

wpf combobox custom-controls selector

I need to write a custom WPF control that should look like a ComboBox with extended items search feature. For this purpose I'd like to have a TextBox and a Popup with a search TextBox and a ListBox...

What is the difference between writing jquery.fn.colorbox and jQuery.colorbox

javascript jquery colorbox

When i write jQuery.fn.colorbox() it gives me same results as jQuery.colorbox(); I want to understand the significance of fn in jQuery.

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.