Use extra column to save an unaccented copy of a string instead of using unaccent module 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?
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?