PostgreSQL 9.2: GIN Index on citext[]

Question

I need to speedup this query:

SELECT * FROM mytable 
WHERE 'value' = ANY("citext_array_col") LIMIT 1;

where citext_array_col is an array of citext. I tried to create an operator class:

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;

I can create the GIN index with this operator class, but it's useless (with set enable_seqscan = off the planner still uses the sequential scan). I have no idea what ginqueryarrayextract() & co. do, there's no documentation about this.

What i found is intarray extension of GIN index, but the code is in C, and i'm not too familiar with PG C extensions...

Is there a smarter way to create an index for this query? Maybe using text support functions?

Answer 1

The operator class for CIText (adapted from PostgreSQL GIN index on array of uuid as suggested by Florent Guillaume) is as follows:

CREATE OPERATOR CLASS _citext_ops DEFAULT 
  FOR TYPE _citext USING gin AS 
  OPERATOR 1 &&(anyarray, anyarray), 
  OPERATOR 2 @>(anyarray, anyarray), 
  OPERATOR 3 <@(anyarray, anyarray), 
  OPERATOR 4 =(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;

Also the query needs to be modified as for eeeebbbbrrrr's suggestion:

SELECT * FROM mytable 
WHERE citext_array_col && ARRAY['value']::citext[];

Answer 2

PostgreSQL GIN index on array of uuid gives an answer for uuid which can likely be adapted to citext.