From: Stuart Bishop <stuart@stuartbishop.net>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: pgsql-performance@postgresql.org
Subject: Re: Slow functional indexes?
Date: Tue, 24 Oct 2006 15:00:01 +0800
Message-ID: <453DB9F1.7050204@stuartbishop.net> (raw)
In-Reply-To: <20906.1161401432@sss.pgh.pa.us>
References: <45387666.204@canonical.com>
<20906.1161401432@sss.pgh.pa.us>
Tom Lane wrote:
> Stuart Bishop <stuart.bishop@canonical.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@stuartbishop.net>
http://www.stuartbishop.net/Attachments:
signature.asc (application/pgp-signature, 190B) download
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: stuart@stuartbishop.net, tgl@sss.pgh.pa.us
Subject: Re: Slow functional indexes?
In-Reply-To: <453DB9F1.7050204@stuartbishop.net>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox