Re: Index optimization ?

Martijn van Oosterhout wrote:

>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.
>
>
I just try to learn, so that is ok :-) Tom gave me a solution that
works, so now I struggle to understand why.

>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.
>
>
But if you take a look at a function, it has a return type. So "currval"
always returns a BIGINT no matter what kind of parameters are given,
that is a part of the declaration, as far as I can see. Why are this
type info not used to match an index, as the type is the same no matter
what row we are in, or no matter its parameter value (or context). The
value change, but not the type. The type is used to find a matching
index is it not ?

Am I misunderstanding you ?

>You can see that with that interpretation an index on id doesn't help.
>
>
No, I think this is the problem, I don't see :-) The function promise to
return a certain type, and type can be used to find the prober index (if
any).

>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.
>
>
If and only if the function returns a different value TYPE, otherwise it
can use the same index but with different values, of the same type alias
use index scan.

But again, I am sure there is something I have misunderstud :-)

Thanks for trying :-)

/BL