Speedup filtering on citext[] columns - Mailing list 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