sql - 按名字和姓氏搜索的 Postgresql 查询，我应该创建哪些索引？

Question

我正在使用此查询按“名字和姓氏”或“姓氏和名字”进行搜索:

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

鉴于此

1. 我正在使用 Postgresql 13

2. 用户可以搜索“John Belushi”或“Belushi John”或“lushi”或“lushi乔”

3. 搜索文本(在本例中为 joh)始终是 lowered() 并清除每个空格:每个单词之前、期间和之后。例如。如果我写“ lushi JO ”，最终的搜索文本是:“lushijo”

4. 此查询经常用于搜索列表中的玩家以及自动完成某些选择

问题

1. 您认为这是一个很好的查询吗？

2. 你认为有什么方法可以避免 CONCAT(lastname,firstname) 和 CONCAT(firstname,lastname) 吗？

3. 我应该创建什么索引？我读到这个:https://stackoverflow.com/a/2709967/10088259但我不太明白如何使用它。

Answer 1

使用generated columns , 并在上面加上索引。

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);

现在 WHERE 子句将能够使用索引:

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

为 LIKE 查询创建索引的很好的解释:https://niallburkley.com/blog/index-columns-for-like-in-postgres/