Thread: Index on function referring other table

Saluton,

from the following passage in the man page on create index:

,----[ man l create_index ]
|      CREATE [ UNIQUE ] INDEX index_name ON table
|          [ USING acc_name ] ( func_name( column [, ... ]) [ ops_name ] )
| ...
|      In  the  second syntax shown above, an index is defined on
|      the result of a user-specified function func_name  applied
|      to  one or more attributes of a single class.  These func­
|      tional indices can be used to obtain fast access  to  data
|      based on operators that would normally require some trans­
|      formation to apply them to the base data.
`----

I get the impression that I can use an index on any function to speed
up access to that particular function, e.g. if I want to match strings
against a certain standardized form. Now consider a function like

,----
| create function whatever(int4) returns text as '
|   declare
|     r record;
|   begin
|     select data into r
|     from otherTable where otherTable.id = $1
|     order by whatever limit 1;
|     return r.data;
|   end;' language 'plpgsql';
`----

I might be tempted to have an index on whatever(firstTable.intField)
in order to be able to return this as fast as possible. But as the
function result obviously depends on data in a different table -
otherTable in the above function -, I wonder (a) how the index code
might figure out that a change to otherTable might trigger a change in
the function results and (b) whether maintaining such an index would
not in fact be a very tedious (and, consequently, slow) task.

So I guess it there must be some restriction on the legal functions
for such a construction, but I cannot find anything in the
documentation. Or am I simply wrong?

Albert.


--

--------------------------------------------------------------------------
Albert Reiner                                   <areiner@tph.tuwien.ac.at>
Deutsch       *       English       *       Esperanto       *       Latine
--------------------------------------------------------------------------

"Albert REINER" <areiner@tph.tuwien.ac.at> writes:
> I might be tempted to have an index on whatever(firstTable.intField)
> in order to be able to return this as fast as possible. But as the
> function result obviously depends on data in a different table -
> otherTable in the above function -, I wonder (a) how the index code
> might figure out that a change to otherTable might trigger a change in
> the function results and (b) whether maintaining such an index would
> not in fact be a very tedious (and, consequently, slow) task.

The answer is that the system takes no account of any such thing.
Therefore an index function that depends on any data other than the
presented arguments is a dangerous animal.

I wouldn't want to see the system try to forbid this sort of thing,
because I can see uses for it, *as long as you don't change the
reference table* (or, perhaps, drop and rebuild the index when you do).
But you'd better keep in mind that sharp tools can injure careless users.

> So I guess it there must be some restriction on the legal functions
> for such a construction, but I cannot find anything in the
> documentation. Or am I simply wrong?

The documentation probably fails to mention that :-(

            regards, tom lane