Re: how to avoid repeating expensive computation in select

That's interesting Tom.


This is a discussion coming up sometimes at PostGIS lists since PostGIS
often handles "expensive" calculations.

Regina wrote a blog post about it:
http://postgresonline.com/journal/archives/113-How-to-force-PostgreSQL-to-use-a-pre-calculated-value.html

I thought the "offset 0" trick was just a dirty hack, but coming from
you, Tom, I assume it is a robust way of doing it.

I also tried some of the queries we tried then, almost 2 years ago, and
I think it seems like PostgreSQL handles this much better in 9.0. Is
that possible?
What was strange then was that PostGIS functions marked immutable also
was recalculated between Select and where-clause and also if used
multiple times in the Select part.

But I think (from very few tests) that the result of the function was
reused in a better way now.

Can that be the case?

Thanks

Nicklas Avén


On Thu, 2011-02-03 at 13:16 -0500, Tom Lane wrote:
> 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.
>
>             regards, tom lane
>