Re: how to avoid repeating expensive computation in select

Hello

2011/2/3 Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>:
> Bob Price <rjp_email(at)yahoo(dot)com> writes:
>> 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.
>
> Use a subselect.  You might need OFFSET 0 to prevent the planner from
> "flattening" the subselect, eg
>
>    SELECT whatever FROM
>      (SELECT *, expensivefunc(value) AS score FROM mytable OFFSET 0) ss
>    WHERE id LIKE '%z%' AND score > 0.5;
>
> Keep in mind that in the above formulation, expensivefunc will be
> evaluated at rows that don't pass the LIKE test.  So you probably want
> to push down as much as you can into the sub-select's WHERE clause.
> The planner will not help you with that if you put in the OFFSET 0
> optimization-fence.  It's a good idea to use EXPLAIN (or even better
> EXPLAIN VERBOSE, if you're using >= 8.4) to confirm that you're getting
> the plan you want.

What about to increase a COST value? Can it help?

Regards

Pavel

>
>                        regards, tom lane
>
> --
> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>