Indexing an array for full text search

Question

I am trying to index documents to be searchable on their tag array.

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

Where tags is a (ci)text[]. However, PG will refuse to index array_to_string because it is not always immutable.

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

I've tried creating a homebrew array_to_string immutable function, but I feel like playing with fire as I don't know what I'm doing. Any way not to re-implement it?

Looks like I could just repackage the same function and label it immutable, but looks like there are risks when doing that.

How do I index the array for full-text search?

Answer 1

In my initial answer I suggested a plain cast to text: tags::text. However, while most casts to text from basic types are defined IMMUTABLE, this it is not the case for array types. Obviously because (quoting Tom Lane in a post to pgsql-general):

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

Bold emphasis mine.

We can work with that. The general case cannot be marked as IMMUTABLE. But for the case at hand (cast citext[] or text[] to text) we can safely assume immutability. Create a simple IMMUTABLE SQL function that wraps the function. However, the appeal of my simple solution is mostly gone now. You might as well wrap array_to_string() (like you already pondered) for which similar considerations apply.

For citext[] (create separate functions for text[] if needed):

Either (based on a plain cast to text):

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

This is faster.
Or (using array_to_string() for a result without curly braces):

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

This is a bit more correct.
Then:

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

I did not use the polymorphic type ANYARRAY like in your answer, because I know text[] or citext[] are safe, but I can't vouch for all other array types.

Tested in Postgres 9.4 and works for me.

I added a space between the two strings to avoid false positive matches across the concatenated strings. There is an example in the manual.

If you sometimes want to search just tags or just notes, consider a multicolumn index instead:

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

The risks you are referring to apply to temporal functions mostly, which are used in the referenced question. If time zones (or just the type timestamptz) are involved, results are not actually immutable. We do not lie about immutability here. Our functions are actually IMMUTABLE. Postgres just can't tell from the general implementation it uses.

Related

Often people think they need text search, while similarity search with trigram indexes would be a better fit:

• PostgreSQL LIKE query performance variations

Not relevant in this exact case, but while working with citext, consider this:

• Index on column with data type citext not used

Answer 2

Here's my naive solution, to wrap it and call it immutable, as suspected.

  CREATE FUNCTION immutable_array_to_string(arr ANYARRAY, sep TEXT)
    RETURNS text
    AS $$
      SELECT array_to_string(arr, sep);
    $$
    LANGUAGE SQL
    IMMUTABLE
  ;