Re: Using an index to materialize a function

Robert James <srobertjames@gmail.com> wrote:

> If I have a slow_function(), and I create an index of
> slow_function(field), will Postgres use that index to avoid having to
> recompute the function?
>
> Example:
>
> SELECT slow_function(field1) FROM table1 WHERE id = 5
>
> It won't use the index on field1 to _find_ the record.  Can it use it
> to compute the field?

If you're not going to search on the function results you are
probably better off adding it to the table itself and maintaining
it on BEFORE INSERT and BEFORE UPDATE triggers.  You could play
around with trying to put it just in an index with other columns
and hoping for an index-only scan, but that is probably not a great
way to go.

--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company