Searching against a large list of people at the same time in Postgres



  • I have a list of people that varies between 2-5k entries that has this general format:

    firstname1 lastname1, firstname2 lastname2, firstname3 lastname3 ...

    I have a user_table which contains about 250-300k entries with a firstname and lastname column. 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_search that 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.

    Related:

    • https://stackoverflow.com/a/8612456/939860
    • https://dba.stackexchange.com/questions/166867/postgresql-importing-thousands-of-columns-as-an-array/166905#166905
    • https://dba.stackexchange.com/questions/46410/how-do-i-insert-a-row-which-contains-a-foreign-key/46477#46477



Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2