Speedup filtering on citext[] columns

From: Davide Setti <davide(dot)setti(at)gmail(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: Speedup filtering on citext[] columns
Date: 2013-08-06 17:17:21
Message-ID: CA+p5JUaHNZ3A+R=0Qsg_mLfiiS-eyCZsWX_BRd6=SO7XDSDevw@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: 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

Browse pgsql-general by date

  From Date Subject
Next Message JD Wong 2013-08-06 17:45:06 Sharing data directories between machines
Previous Message Chris Travers 2013-08-06 16:49:06 Re: inserting huge file into bytea cause out of memory