Hello
2011/2/3 Tom Lane <tgl@sss.pgh.pa.us>:
> Bob Price <rjp_email@yahoo.com> writes:
>> I would like to know if there is a way in PostgreSQL to avoid repeating an expensive computation in a SELECT where
theresult 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@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>