On Sun, Jan 16, 2005 at 05:30:22PM +0100, Bo Lorentsen wrote:
> >One could conceivably attempt to make a functional index using
> >plus_random(), but the result it gives every time is indeterminant.
> >How would you be able to usefully search for values in an index that
> >is based on this function? Would it make sense do to do so?
>
> What you say is that PG can't see the difference between this
> "plus_random" and the "currval", right.
>
> But if I have a select (a quite strange one), like this :
>
> SELECT * FROM test_table WHERE id = plus_random( test_col );
>
> I don't understand the problem. The function always return an integer as
> specified in the function decl. so why not use the PK index for search,
> instead of using seq scan ? The value is totally unpredictable but it is
> still an integer and the pk index is still useful regarding performance !
No, it depends on your interpretation of the query. Note, I'm not up
with the SQL standard so maybe it doesn't work like this, but this is
what I think the problem is.
The above query can be interpreted as: for each row in test_table,
compare id against plus_random( test_col ). Now, in theory the
plus_random function needs to be evaluated for every row, each time
giving a different value, thus it may or may not match id.
You can see that with that interpretation an index on id doesn't help.
If you interpret the query so plus_random is evaluted only once, then
an index will help. If test_col is a column of the table then there is
no way an index can help you.
Hope this helps,
--
Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/
> Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
> tool for doing 5% of the work and then sitting around waiting for someone
> else to do the other 95% so you can sue them.