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.
pgp6DEl6JKvoi.pgp
Description: PGP signature
