select * from person where first_name similar to 'ДАЙНЕКО ПЕТР ВИКТОРОВИЧ' or last_name similar to 'ДАЙНЕКО ПЕТР ВИКТОРОВИЧ' or patronymic similar to 'ДАЙНЕКО ПЕТР ВИКТОРОВИЧ' or email similar to 'ДАЙНЕКО ПЕТР ВИКТОРОВИЧ'

0


I have a table with text colums last_name,first_name,patronymic,email

Example: we put 1 record to it

'Ivan', 'Vishov', 'Valentinovich', 'aaa@a.com'
Now on client-side we have only one text input. The client can write the following combination to query:

Ivan
Vishov Ivan
Ivan Vishov
Ivan Valentinovich
Ivan aaa@a.ru
In all these cases I want to get the related record.

select * from respondent
where to_tsvector(first_name || ' ' || last_name || ' ' || patronymic || ' '
  || email) @@ to_tsquery('Дайнеко & Петр & Викторович')

create index test_gin on respondent using GIN (to_tsvector('russian',first_name || ' ' || last_name || ' ' || patronymic || ' '
  || email));

explain analyze
select * from respondent
where to_tsvector(first_name || ' ' || last_name || ' ' || patronymic || ' '
  || email) @@ plainto_tsquery('пётра      дайнека (**??% викторовича');

Gather  (cost=1000.00..543517.63 rows=1 width=276) (actual time=2711.236..13188.142 rows=3 loops=1)
  Workers Planned: 2
  Workers Launched: 2
  ->  Parallel Seq Scan on respondent  (cost=0.00..542517.53 rows=1 width=276) (actual time=6679.406..13147.197 rows=1 loops=3)
        Filter: (to_tsvector(((((((first_name || ' '::text) || last_name) || ' '::text) || patronymic) || ' '::text) || email)) @@ plainto_tsquery('пётра      дайнека (**??% викторовича'::text))
        Rows Removed by Filter: 666669
Planning Time: 0.154 ms
Execution Time: 13188.170 ms

explain analyze
select * from respondent
where to_tsvector(first_name || ' ' || last_name || ' ' || patronymic || ' '
  || email) @@ plainto_tsquery(null);

 select * from respondent where
to_tsvector('russian',coalesce(first_name,'') || ' ' || coalesce(last_name,'') || ' ' || coalesce(patronymic,'') || ' '
                        || email) @@ plainto_tsquery('russian','');

select * from respondent where
to_tsvector('russian',coalesce(first_name,'null')) @@ plainto_tsquery('russian','null');

 select * from respondent where
to_tsvector('russian',coalesce(first_name,'') || ' ' || coalesce(last_name,'') || ' ' || coalesce(patronymic,'') || ' '
  || email) @@ plainto_tsquery('russian', ???? )