3

I would like to know if it makes sense to create an index combining two functions while using Full Text Search: lower(name) and f_unaccent(name) Where f_unaccent is just my wrapper to make unaccent function immutable.

I do have an index working on: f_unaccent(name) using varchar_pattern_ops. My question is: would an index that combines lower and unaccent functions be triggered by full_text_search lower(f_unaccent(name)).

I don't know if the lower function would be useful working with Full Text Search algorithms.

3

First, we need to clarify a few things.

Pattern Ops and Trigram

This type of query can work on varchar/text pattern_ops,

foo LIKE 'bar%'

Queries with Un-anchored search patterns reqire trigram.

foo LIKE '%bar%'

Both of them can work on function expressions, so long as you maintain the same sequence in the query that you have in the index.

Full text search is something completely different. It transforms your text into lexemes by stubbing to the root words, lowercasing, removing stop words, and a slew of other tricks. Keep reading:

Unaccent by itself (wouldn't suggest it)

Unfortunately though unaccent is only "stable" not "immutable." And so if you try to create an index on unaccent(text) you'll get an error, functions in index expression must be marked IMMUTABLE. I assume this is why you created the wrapper f_accent having found that on Erwins answer on Stack Overflow. Which looks like this,

CREATE EXTENSION unaccent;
CREATE OR REPLACE FUNCTION f_unaccent(text)
  RETURNS text AS
$func$
SELECT public.unaccent('public.unaccent', $1)  -- schema-qualify function and dictionary
$func$
LANGUAGE sql
IMMUTABLE;

CREATE TABLE foo AS
  SELECT 'asdf'::text AS mytext;
CREATE INDEX ON foo (lower(f_unaccent(mytext)) text_pattern_ops);

Or, for trigram,

CREATE INDEX ON foo
USING gin(lower(f_unaccent(mytext)) gin_trgm_ops);

Now remember you have to carry that long sequence lower(f_unaccent(mytext)) through to make it work..

SELECT *
FROM foo
WHERE lower(f_unaccent(mytext)) LIKE '%whatever%';

Unaccent with a FTS Dictionary

Instead of that method, I would create a dictionary that uses unaccent and use FTS. With custom dictionaries though it will likely do what you want. FTS converts the words to tokens which makes searching very simple and fast.

CREATE EXTENSION unaccent; -- still required it plugs into FTS.

CREATE TEXT SEARCH CONFIGURATION mydict ( COPY = simple );
ALTER TEXT SEARCH CONFIGURATION mydict
  ALTER MAPPING FOR hword, hword_part, word
  WITH unaccent, simple;

Now you should be able to

You can see the tokens and what it's doing here,

select to_tsvector(
  'mydict',
  'Zoë and Renée are elected to the council of Cheese Eating Surrender Monkeys'
);
                                                             to_tsvector                                                             
-------------------------------------------------------------------------------------------------------------------------------------
 'and':2 'are':4 'cheese':10 'council':8 'eating':11 'elected':5 'monkeys':13 'of':9 'renee':3 'surrender':12 'the':7 'to':6 'zoe':1
(1 row)

And you can query the table simply like this,

SELECT *
FROM foo
WHERE to_tsvector('mydict', mytext) @@ 'cheese & council';

You don't have to use simple either. If the column contains statements in just one language you can use one of the other dictionaries to simplify things and remove stop words.

  • Hi Evan, many thanks for your response, it gives me the info I need to start tunning my searches. And yes, my wrapper function to unaccent was the one proposed by Erwin :) I think I will create a tsvector column with a gin index to search against, and will populate that column using a trigger, with the plain string column contents to_tsvector. – Rubén_ic Jun 23 '17 at 7:51
  • 1
    I wouldn't use a trigger, there is no reason for it. – Evan Carroll Jun 23 '17 at 13:52

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.