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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Kevin Grittner <kgrittn@ymail.com> writes:

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.

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general