Re: Slow functional indexes?

От: Stuart Bishop
Тема: Re: Slow functional indexes?
Дата: ,
Msg-id: 453DB9F1.7050204@stuartbishop.net
(см: обсуждение, исходный текст)
Ответ на: Re: Slow functional indexes?  (Tom Lane)
Список: pgsql-performance

Скрыть дерево обсуждения

Slow functional indexes?  (Stuart Bishop, )
 Re: Slow functional indexes?  ("Merlin Moncure", )
 Re: Slow functional indexes?  (Tom Lane, )
  Re: Slow functional indexes?  (Stuart Bishop, )
 Re: Slow functional indexes?  (Stuart Bishop, )
  Re: Slow functional indexes?  (Tom Lane, )
   Re: Slow functional indexes?  (Gene, )

Tom Lane wrote:
> Stuart Bishop <> 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 <>
http://www.stuartbishop.net/


Вложения

В списке pgsql-performance по дате сообщения:

От: Markus Schaber
Дата:
Сообщение: Re: Index on two columns not used
От: Jignesh Shah
Дата:
Сообщение: Re: Copy database performance issue