Skip site navigation (1) Skip section navigation (2)

Re: Implied Functional index use (redux)

From: Gregory Stark <stark(at)enterprisedb(dot)com>
To: "Simon Riggs" <simon(at)2ndquadrant(dot)com>
Cc: <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: Implied Functional index use (redux)
Date: 2007-01-28 17:01:36
Message-ID: 8764ar84r3.fsf@stark.xeocode.com (view raw or flat)
Thread:
Lists: pgsql-hackers
"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

In response to

Responses

pgsql-hackers by date

Next:From: Joshua D. DrakeDate: 2007-01-28 18:10:14
Subject: Re: Modifying and solidifying contrib
Previous:From: Oleg BartunovDate: 2007-01-28 16:38:58
Subject: docbook question: how to center cell in tables ?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group