I have a MATERIALIZED VIEW inside PostgreSQL with indices:
CREATE INDEX idx_tutor_person_subject_view_subject_name_lower ON tutor_person_subject_view (LOWER(subject_name));
CREATE INDEX idx_tutor_person_subject_view_subject_name_ru ON tutor_person_subject_view USING GIN (to_tsvector('russian', subject_name));
CREATE INDEX idx_tutor_person_subject_view_user_id ON tutor_person_subject_view (user_id);
The following query returns zero rows, but expected 85K
SELECT * FROM tutor_person_subject_view WHERE to_tsvector('russian', subject_name) @@ websearch_to_tsquery('russian', 'мат');
This query returns 85K rows:
SELECT * FROM tutor_person_subject_view WHERE tutor_person_subject_view.subject_name ILIKE '%мат%';
I have no special task to use exactly “websearch_to_tsquery”, I need a FAST way to select by ILIKE ‘%мат%’ clause. What should I do for that?