Search Postgresql Archives

Re: Index optimization ?

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
   (send "unregister YourEmailAddressHere" to majordomo@xxxxxxxxxxxxxx)

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux