Message-ID: <453DB9F1.7050204@stuartbishop.net>
Date: Tue, 24 Oct 2006 15:00:01 +0800
From: Stuart Bishop <stuart@stuartbishop.net>
User-Agent: Thunderbird 1.5.0.7 (X11/20060922)
MIME-Version: 1.0
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: pgsql-performance@postgresql.org
Subject: Re: Slow functional indexes?
References: <45387666.204@canonical.com> <20906.1161401432@sss.pgh.pa.us>
In-Reply-To: <20906.1161401432@sss.pgh.pa.us>
OpenPGP: id=BAC6374A;
	url=http://stuartbishop.net/stuartbishop.asc
Content-Type: multipart/signed; micalg=pgp-sha1;
	protocol="application/pgp-signature";
	boundary="------------enigAFCF518133706AF2C6E0A53F"

This is an OpenPGP/MIME signed message (RFC 2440 and 3156)
--------------enigAFCF518133706AF2C6E0A53F
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: quoted-printable

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.
>=20
> 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 =3D SD["strip_re"]
    except KeyError:
        strip_re =3D re.compile("(?:[^\w\s]|[\d_])", re.U)
        SD["strip_re"] =3D strip_re

    displayname, name =3D args

    # Strip noise out of displayname. We do not have to bother with
    # name, as we know it is just plain ascii.
    displayname =3D strip_re.sub('', displayname.decode('UTF-8').lower())=

    return ("%s, %s" % (displayname.strip(), name)).encode('UTF-8')
$$;


--=20
Stuart Bishop <stuart@stuartbishop.net>
http://www.stuartbishop.net/


--------------enigAFCF518133706AF2C6E0A53F
Content-Type: application/pgp-signature; name="signature.asc"
Content-Description: OpenPGP digital signature
Content-Disposition: attachment; filename="signature.asc"

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.2.2 (GNU/Linux)

iD8DBQFFPbnxAfqZj7rGN0oRAtWUAJ4wcSbRpUSV20YeOzl9tMvIVVzoHwCcCNcw
7S+QbgXhnjFkUbILPSgJE+A=
=UEBm
-----END PGP SIGNATURE-----

--------------enigAFCF518133706AF2C6E0A53F--