Postgresql query to search by first and last name, what indexes should I create?

user_10088259 0
Viewed 1555

I'm using this query to search by "first and last name" or by "last and first name":

SELECT
    "id",
    "firstname",
    "lastname"
FROM
    "plyers"
WHERE
  lower( REPLACE ( CONCAT ( lastname, firstname ), ' ', '' ) ) LIKE '%joh%'
  OR
  lower( REPLACE ( CONCAT ( firstname, lastname ), ' ', '' ) ) LIKE '%joh%'
ORDER BY "id" DESC

GIVEN THAT

  1. I'm using Postgresql 13

  2. User can search with "John Belushi" or "Belushi John" or "lushi" or "lushi jo"

  3. The search text (joh in this case) is always lowered() and cleaned of every space: before, during and after each word. E.g. if I write " lushi JO " the final search text is: "lushijo"

  4. This query is used A LOT to search for players in a list and for the automatic completion of some selects

QUESTIONS

  1. Do you think it's a good query?

  2. Do you think there is a way I can avoid CONCAT(lastname,firstname) and CONCAT(firstname,lastname)?

  3. What indexes should I create? I read this: https://stackoverflow.com/a/2709967/10088259 but I didn't quite understand how to use it.

postgresql sql performance
2 Answers

Use generated columns, and put indices on those.

ALTER TABLE test
    ADD COLUMN first_last text GENERATED ALWAYS AS (lower(REPLACE(first || last, ' ', ''))) STORED;

CREATE INDEX IF NOT EXISTS players_first_last_idx ON test USING gin (first_last gin_trgm_ops);

ALTER TABLE test
    ADD COLUMN last_first text GENERATED ALWAYS AS (lower(REPLACE(last || first, ' ', ''))) STORED;

CREATE INDEX IF NOT EXISTS players_first_last_idx ON test USING gin (last_first gin_trgm_ops);

Now the WHERE clause will be able to use the indices:

SELECT
    "id",
    "firstname",
    "lastname"
FROM
    "plyers"
WHERE
  first_last LIKE '%joh%'
  OR
  last_first LIKE '%joh%'
ORDER BY "id" DESC

Good explanation of creating indices for LIKE queries: https://niallburkley.com/blog/index-columns-for-like-in-postgres/

user_48937530

Let me answer using an "Answer" instead of a "Comment" ...

If you want to allow for the versatility that you suggest -

  1. A fast data access - taking advantage of any existing indexes, is only possible if you allow for a case-sensitive search argument for the start of the string. My biggest tables of this type were never in PostreSQL, but in 3 or 4 other brands. But all of them, when using an index, did better with WHERE fname >='Johnny' AND fname < 'Johnz' than with WHERE NAME LIKE 'Johnny%'. I actually once coded a front-end to do that change for me ...
  2. It helps, if you can, to actually add a column fullname VARCHAR(128) DEFAULT firstname||' '||lastname to your table (length guessed as about double the length of firstname and lastname - keep it as short as you can afford without truncating the strings.
  3. Add non-unique one-column indexes for firstname, lastname, and your new fullname.
  4. Finally, be frank and open to the end users, telling them that an "begins-with" predicate will always be considerably faster than a "contains" predicate - let alone a case insensitive predicate.

If your front-end is a normal SQL client, the end-user must code their queries to respect the points above.

If you build your own front-end application, allow in the search functionality for "is equal to, case sensitive", "begins with. case sensitive" and a comparison with firstname, lastname, and, if it makes sense, fullname - as the preferred search method, but then also for "contains", "begins-with" - "is ''equal'' to" and "contains", all case insensitive - but with a warning, in the dialogue or the docu, that these latter ones are much slower.

user_67882550
Related