"Simon Riggs" <[EMAIL PROTECTED]> writes: > In a thread in July last year, I raised the possibility of transforming > a query to allow functional indexes to be utilised automatically. > http://archives.postgresql.org/pgsql-hackers/2006-07/msg00323.php > > This idea can work and has many benefits, but there are some > complexities. I want to summarise those issues first, then make a more > practical and hopefully more acceptable proposal. > > Taken together the complexities would have lead us to have additional > TRANSFORMABLE clauses on TYPEs, FUNCTIONs and potentially encoding > schemes. All of which, I agree, just too much complexity to allow this > to be specified.
I've thought further about this and I believe the problem is simpler than we were thinking previously. All we need is one boolean flag on the equality operator for the data type (or perhaps it would be more convenient to have it on the operator class) that indicates that two objects will never compare equal unless they're binary equal. As long as we know that binary unequal implies operator class unequal then we can know that any immutable function will always return the same value for any two equal data. And therefore we can be guaranteed that any elements we want to look up with "col = $1" will be included in the elements returned by looking up "foo(col) = foo($1)". We don't need to know anything about foo() beyond its immutability. If we want to be able to do inequality lookups as well then we'll have to have a flag on the function indicating it's "order preserving". That is for any values a, b the property a<b ==> f(a)<=f(b) holds. The problem is that you can't guarantee that for all operator classes since someone can always come along and define a new operator class with a new ordering that breaks your guarantee. I suppose we could just have the flag indicate that the property holds for the default operator class for the argument data types. But even if we only handled equality I think it would still be a very useful feature. It would allow doing things like creating an index on soundex(lastname) or crc32(url) and have them be automatically useful without altering queries. And it would mean you wouldn't have to create redundant indexes on lastname and lower(lastname). We could always look to generalize it to inequalities later. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com ---------------------------(end of broadcast)--------------------------- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match