PostgreSQL 9.2: 在citext[]上创建GIN索引

Question

我需要加速这个查询：

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

其中citext_array_col是一个citext数组。我尝试创建一个操作符类：

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;

我可以使用这个操作类创建GIN索引，但是它没什么用（即使使用set enable_seqscan = off也会使用顺序扫描的计划）。我不知道ginqueryarrayextract() & co.具体做什么，也没有相关文档。

我发现GIN索引的intarray扩展，但是代码是用C语言写的，我对PG C扩展不是太熟悉...

有没有更聪明的方法来创建此查询的索引？或者使用文本支持函数？

- vad

Answer 1

以下是CIText的操作类（改编自PostgreSQL GIN index on array of uuid，由Florent Guillaume建议）：

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;

此外，根据eeeebbbbrrrr的建议，需要修改查询：

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

- Roberto

Answer 2

PostgreSQL GIN索引在UUID数组上的应用提供了对于uuid的答案，这可能可以适用于citext。

- Florent Guillaume

Answer 3

我相信你想要做的是像这样声明操作符类：

     CREATE OPERATOR CLASS _citext_ops DEFAULT FOR TYPE citext[] USING gin
AS

        OPERATOR        3       && (anyarray, anyarray),
        OPERATOR        6       = (anyarray, anyarray),
        OPERATOR        7       @> (anyarray, anyarray),
        OPERATOR        8       <@ (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;

然后你的查询将会有稍微不同的形式：

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

同时确保你已经使用gin创建了正确的索引：

CREATE INDEX idxfoo ON mytable USING gin (citext_array_col);

这将查找 citext_array_col 包含任何元素中的 "value" 的所有行。"&&" 运算符是重叠运算符。请参见 http://www.postgresql.org/docs/9.3/static/functions-array.html。

(编辑：打字错误)

- eeeebbbbrrrr

---

请注意，原始代码无法正常工作。OPERATOR 后面的数字是策略，对于仅使用策略 1-4 的 GIN 是错误的。请参阅 http://www.postgresql.org/docs/9.2/static/xindex.html#XINDEX-STRATEGIES。 看起来列出的策略是 GiST 的策略。 - Florent Guillaume

这解决了我的问题，与 uuid 回答一起。非常感谢！ - vad