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 по дате отправления: