postgresql - 索引数组以进行全文搜索

Question

我正在尝试将文档编入索引以便在其标签数组中进行搜索。

CREATE INDEX doc_search_idx ON documents
      USING gin( 
    to_tsvector('english', array_to_string(tags, ' ')) ||
    to_tsvector('english', coalesce(notes, '')))
)

其中 tags 是一个 (ci)text[]。但是，PG 会拒绝索引 array_to_string 因为它 is not always immutable .

PG::InvalidObjectDefinition: ERROR:  functions in index expression must be marked IMMUTABLE

我已经尝试创建一个自制的 array_to_string 不可变函数，但我感觉就像在玩火，因为我不知道自己在做什么。有什么办法不重新实现它？

看起来我可以重新打包相同的函数并将其标记为不可变，但看起来像 there are risks这样做的时候。

如何索引数组以进行全文搜索？

Answer 1

在我最初的回答中，我建议直接转换为文本:tags::text。然而，虽然大多数从基本类型到文本的转换都定义为 IMMUTABLE，但数组类型并非如此。显然是因为 ( quoting Tom Lane in a post to pgsql-general ):

Because it's implemented via array_out/array_in rather than any moredirect method, and those are marked stable because they potentiallyinvoke non-immutable element I/O functions.

大胆强调我的。

我们可以解决这个问题。一般情况不能标记为IMMUTABLE。但是对于手头的情况(将 citext[] 或 text[] 转换为 text)，我们可以安全地假设不变性。创建一个简单的 IMMUTABLE 包装函数的 SQL 函数。但是，我的简单解决方案的吸引力现在几乎消失了。您也可以包装 array_to_string()(就像您已经考虑过的那样)，因为类似的考虑适用。

对于 citext[](如果需要，为 text[] 创建单独的函数):

两者之一(基于对 text 的简单转换):

CREATE OR REPLACE FUNCTION f_ciarr2text(citext[]) 
  RETURNS text LANGUAGE sql IMMUTABLE AS 'SELECT $1::text';

这样更快。
或者(使用 array_to_string() 得到没有大括号的结果):

CREATE OR REPLACE FUNCTION f_ciarr2text(citext[]) 
  RETURNS text LANGUAGE sql IMMUTABLE AS $$SELECT array_to_string($1, ',')$$;

这个比较正确。
然后:

CREATE INDEX doc_search_idx ON documents USING gin (
   to_tsvector('english', COALESCE(f_ciarr2text(tags), '')
                || ' ' || COALESCE(notes,'')));

我没有使用像in your answer这样的多态类型ANYARRAY ，因为我知道 text[] 或 citext[] 是安全的，但我不能保证 所有 其他数组类型。

在 Postgres 9.4 中测试并适用于我。

我在两个字符串之间添加了一个空格，以避免串联字符串之间的误报匹配。有一个 example in the manual .

如果您有时只想搜索 tags 或 notes，请考虑使用多列索引:

CREATE INDEX doc_search_idx ON documents USING gin (
             to_tsvector('english', COALESCE(f_ciarr2text(tags), '')
          ,  to_tsvector('english', COALESCE(notes,''));

您所指的风险主要适用于时间函数，这些函数用于 referenced question .如果涉及时区(或仅涉及 timestamptz 类型)，则结果实际上并不是不可变的。我们不会在这里对不变性撒谎。我们的函数实际上 IMMUTABLE。 Postgres 无法从它使用的一般实现中分辨出来。

相关

人们通常认为他们需要 text search ，而使用三元组索引的相似性搜索更适合:

• PostgreSQL LIKE query performance variations

在这种情况下不相关，但在使用 citext 时，请考虑:

• Index on column with data type citext not used