Re: Using an index to materialize a function

Kevin Grittner <kgrittn(at)ymail(dot)com> writes:
> Robert James <srobertjames(at)gmail(dot)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.

Yeah, the functionality for that is pretty primitive right now. It
will happen if the conditions are right, but the planner doesn't credit
a plan of that form with saving the function computation, so the index
would have to be one it would use anyway. Moreover the index has to cover
the base column(s) of the function call or a index-only scan will be ruled
out. Putting those things together, what you'd need for the above example
is an index on (id, slow_function(field1), field1). Or, if you sometimes
have queries that constrain id and field1, you could build the index on
(id, field1, slow_function(field1)). Either way, this is going to be
a bulky and rather special-purpose index, so the usefulness of doing this
is debatable. Kevin's idea of an auto-maintained column in the base
table is probably better.

Even with the planner deficiencies rectified, an index on
slow_function(field1) alone would be totally useless for this query.
There's no way to magically find the index entry for a given row.
An index on (id, slow_function(field1)) could be useful, given a better
planner.

regards, tom lane