Re: how to avoid repeating expensive computation in select

In response to Bob Price <rjp_email(at)yahoo(dot)com>:

> I have been searching through the docs and mailing list and haven't found a way to do this, so I thought I would ask the community.
>
> I would like to know if there is a way in PostgreSQL to avoid repeating an expensive computation in a SELECT where the result is needed both as a returned value and as an expression in the WHERE clause.
>
> As a simple example, consider the following query on a table with 'id' and 'value' columns, and an expensive computation represented as a function:
>
> SELECT id, expensivefunc(value) AS score FROM mytable
> WHERE id LIKE '%z%' AND expensivefunc(value) > 0.5;
>
> It would be great if I could find a way to only compute expensivefunc(value) at most once per row, and not at all if the other WHERE constraints are not satisfied.

Two ways that I can think of:
1) If expensivefunc() doesn't have any side-effects, you can create it
as IMMUTABLE, which tells PostgreSQL that it can cache the result
for optimization purposes. IMMUTABLE is not the default.
2) Create a new column in the table that stores the value of
expensivefunc(value) and add a trigger to the table to ensure that
column is updated any time value is changed. This will slow down
inserts and updates a bit, but it means you can select/compare the
generated column directly with no calculation.

Which one of these is more practical for you depends on a number of
factors about the table, the data, and the function.

--
Bill Moran
http://www.potentialtech.com
http://people.collaborativefusion.com/~wmoran/