Use extra column to save an unaccented copy of a string instead of using unaccent module

Question

I am heavily using the unaccent module in a table with 500.000 rows where each row contains proportionally a lot of accented characters.

The issue I am facing is that my queries have become time consuming monsters.

Is it utterly unacceptable -in terms of database design- to save an unaccented copy of each string in another column of the table in question and query that one instead of using the unaccent module?

Answer 1

You could store an unaccented version redundantly. And create index on that column to support your queries.

Or, better yet, to avoid bloating your table with redundant data, create a functional index on the original column. You need an IMMUTABLE function for that, while unaccent() is only STABLE. Detailed instructions:

• Does PostgreSQL support “accent insensitive” collations?

Then, based on the function f_unaccent() from the linked answer:

CREATE INDEX tbl_unaccent_col_idx ON tbl(public.f_unaccent(col));

Or some other index. Maybe use the full text search (FTS) infrastructure with unaccent as dictionary. Example:

• Creating a case-insensitive and accent/diacritics insensitive search on a field

Or use it in a trigram index:

• Queries using GIN index on f_unaccent() seem slow?
• Why does a slight change in the search term slow down the query so much?