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
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 |