I'm developing a search for entities by name in Postgres database. Name usually consists of 1-3 words and may contain symbols &, !, (, ), -, etc.
I'm using gin trigram index and queries: WHERE name ILIKE '%something%', ILIKE 'a%' and WHERE name % 'abc' for fuzzy search (if nothing was found by the exact match via ILIKE).
The problem is that we need to support search by any characters, not only letters and numbers. Trigram index ignores such characters.
I've tried text_pattern_ops index for this case but without any success: queries such as WHERE name ILIKE '%$%' are extremely slow
So, is there any way to efficiently process such queries? Do I need a full text search for this purpose?
UPD:
Table is like:
| id (int) | Name (text) |
|---|---|
| 123 | Dolce&Gabbana |
Queries are like:
SELECT name FROM brand WHERE name ILIKE '%&%' ORDER BY name;
UPD2:
Query plan for
EXPLAIN(analyze, verbose, buffers, settings)
SELECT name FROM brand
WHERE name ILIKE '%$%'
ORDER BY name
Index was created as:
CREATE INDEX brand_trgm_idx ON brand USING gin (name gin_trgm_ops);
Table was created as:
CREATE TABLE brand (
id serial PRIMARY KEY,
name TEXT,
collection_id TEXT,
created_at TIMESTAMP DEFAULT now() NOT NULL,
created_by TEXT NOT NULL
);
Also tried:
CREATE INDEX brand_name_idx ON brand (name text_pattern_ops);
UPD3:
Checked query analyze for the same db, but with ~1M entries:


id int Name text ... 123 Dolce&Gabbana.Queries are like:SELECT name FROM brand WHERE name ilike '%&%' ORDER BY name;text_pattern_opsis only good for left-anchored patters. Your example pattern'%&%'cannot use such an index. See: stackoverflow.com/a/13452528/939860 If you really hunt for single punctuation characters, you need a tailored index ...