Re: Slow functional indexes?

Tom Lane wrote:
> Stuart Bishop <stuart(dot)bishop(at)canonical(dot)com> writes:
>> I would like to understand what causes some of my indexes to be slower to
>> use than others with PostgreSQL 8.1.
>
> I was about to opine that it was all about different levels of
> correlation between the index order and physical table order ... but
> your experiments with freshly clustered indexes seem to cast doubt
> on that idea. Are you sure your function is really immutable? A buggy
> function could possibly lead to a "clustered" index not being in
> physical order.

Definitely immutable. Here is the function definition:

CREATE OR REPLACE FUNCTION person_sort_key(displayname text, name text)
RETURNS text
LANGUAGE plpythonu IMMUTABLE RETURNS NULL ON NULL INPUT AS
$$
# NB: If this implementation is changed, the person_sort_idx needs to be
# rebuilt along with any other indexes using it.
import re

try:
strip_re = SD["strip_re"]
except KeyError:
strip_re = re.compile("(?:[^\w\s]|[\d_])", re.U)
SD["strip_re"] = strip_re

displayname, name = args

# Strip noise out of displayname. We do not have to bother with
# name, as we know it is just plain ascii.
displayname = strip_re.sub('', displayname.decode('UTF-8').lower())
return ("%s, %s" % (displayname.strip(), name)).encode('UTF-8')
$$;

--
Stuart Bishop <stuart(at)stuartbishop(dot)net>
http://www.stuartbishop.net/