Searching against a large list of people at the same time in Postgres
I have a list of people that varies between
2-5kentries that has this general format:
firstname1 lastname1, firstname2 lastname2, firstname3 lastname3 ...
I have a
user_tablewhich contains about
250-300kentries with a
lastnamecolumn. What I'm trying to do is let users input that list of names into a field, and return all users in the database with matching names. My first instinct was to create a view called
name_searchthat looks like this ->
create view name_search as ( select id, to_tsvector(concat(firstname, ' ', lastname)) as search_field from user_table )
Then I can search against it by turning all the names in the comma separated list into a string like this
const queryString = "(firstname1 lastname1) | (firstname2 lastname2) | (firstname3 lastname3) ..."
then I can pass that string into this query
select * from name_search where search_field @@ to_tsquery(queryString)
I'm leaving out some of the details like escaping the string etc etc. but my issue is that this is horribly slow. I was wondering if someone could help me create a solution to this that is faster.
I don't see a case for full text search here.
You can provide your input string as is to this query:
SELECT t.id, firstname, lastname FROM user_table t JOIN ( SELECT split_part(fullname, ' ', 1) AS firstname , split_part(fullname, ' ', 2) AS lastname FROM unnest(string_to_array('firstname1 lastname1, firstname2 lastname2, firstname3 lastname3', ', ')) fullname ) input USING (firstname, lastname);
An index on
(firstname, lastname)should help performance while the input list isn't too long. For retrieving more than a couple percent of all rows, a sequential scan is faster anyway.