Re: how to avoid repeating expensive computation in select - Mailing list pgsql-general

Thanks for all of the responses.

If the IMMUTABLE flag on a function does what the docs say then it might satisfy my needs.

Here is a more specific example of what I need to do, with a new custom data type and a new function:

- define new complex data type X
- create table mytable ( id varchar, value X )
- create function expensivefunc(X,X) which is implemented in C
- select id, expensivefunc(value, 'constantdata...'::X) as score
   from mytable where expensivefunc(value, 'constantdata...'::X) > 0.5;

If I set the COST of expensivefunc high, and label it IMMUTABLE, will the query executor note that the two invocations
toexpensivefunc have the same inputs so it can only call it once and re-use the result the second time? 

I imagine that it might be a problem to pass 'constantdata...'::X to both invocations.  I guess that I could create a
one-timeuse function that declared a variable with this 'constantdata...'::X value, and then pass this variable in both
calls.

Would this work?

Thanks again!
Bob



--- On Thu, 2/3/11, Bill Moran <wmoran@potentialtech.com> wrote:

> From: Bill Moran <wmoran@potentialtech.com>
> Subject: Re: [GENERAL] how to avoid repeating expensive computation in select
> To: "Bob Price" <rjp_email@yahoo.com>
> Cc: pgsql-general@postgresql.org
> Date: Thursday, February 3, 2011, 12:23 PM
> In response to Bob Price <rjp_email@yahoo.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/
>