Re: Implied Functional index use (redux)

"Simon Riggs" <simon(at)2ndquadrant(dot)com> 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