Re: how to avoid repeating expensive computation in select

On Tue, Mar 1, 2011 at 2:51 AM, Orhan Kavrakoglu <orhan(at)tart(dot)com(dot)tr> wrote:
>> 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.
>
> I think I've seen it said here that PG avoids redundant multiple
> calculations of an expression.
>
> Even so, have you thought about using subqueries?
>
>>   SELECT id, expensivefunc(value) AS score FROM mytable
>>      WHERE id LIKE '%z%' AND expensivefunc(value)>  0.5;
>
> SELECT id, expensivefunc(value) FROM (
> (SELECT id, value FROM mytable WHERE id LIKE '%z%')
> ) WHERE expensivefunc(value) > 0.5;
>
> or even
>
> SELECT id, score FROM (
> SELECT id, expensivefunc(value) AS score FROM (
> (SELECT id, value FROM mytable WHERE id LIKE '%z%')
> )
> ) WHERE score > 0.5

you missed the point: even when you use subqueries postgres can inline
them, 'unsubquerying' your query. I think the OP nailed probably the
best and most logical approach -- use a CTE. It's more formal, and
while not super efficient today, isn't terrible.

merlin