Speedup filtering on citext[] columns

Поиск
Список
Период
Сортировка
От Davide Setti
Тема Speedup filtering on citext[] columns
Дата
Msg-id CA+p5JUaHNZ3A+R=0Qsg_mLfiiS-eyCZsWX_BRd6=SO7XDSDevw@mail.gmail.com
обсуждение исходный текст
Список pgsql-general
Hi,
i'm trying to speedup 3 kind of query on citext arrays (PGSQL 9.2).

* element lookup (eg. 'value' = ANY("citext_array_col"))
* substring search in any element
* substring search at the beginning of any element

Are GIN indexes the right tool for this job? I tried building an
operator class using ginarrayextract & co. with no luck (i can create
the index but it's not used by the planner). Here the operator class
(copy & paste code, i don't understand this section of the doc...):

CREATE OPERATOR CLASS gin__citext_ops
FOR TYPE citext[] USING gin
AS
        OPERATOR        6       = (anyarray, anyarray),
        FUNCTION        1       citext_cmp (citext, citext),
        FUNCTION        2       ginarrayextract (anyarray, internal, internal),
        FUNCTION        3       ginqueryarrayextract(anyarray,
internal, smallint, internal, internal, internal, internal),
        FUNCTION        4       ginarrayconsistent(internal, smallint,
anyarray, integer, internal, internal, internal, internal),
        STORAGE         citext;

Regards.
--

Davide Setti
code: http://github.com/vad


В списке pgsql-general по дате отправления:

Предыдущее
От: Chris Travers
Дата:
Сообщение: Re: inserting huge file into bytea cause out of memory
Следующее
От: JD Wong
Дата:
Сообщение: Sharing data directories between machines