# kleptog@svana.org / 2005-01-16 17:48:08 +0100:
> 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 ).
That's what happens if you declare the function VOLATILE.
Make it STABLE, and the function call will be evaluated only once
for the whole table scan. That's just what Tom Lane suggested in
his post.
--
If you cc me or remove the list(s) completely I'll most likely ignore
your message. see http://www.eyrie.org./~eagle/faqs/questions.html
