在多個文本欄位上進行模式匹配的更快查詢

我有一個包含超過 20M 元組的 Postgres 表：

first_name | last_name | email
-------------------------------------------
bat        | man       | batman@wayne.com
arya       | vidal     | foo@email.com
max        | joe       | bar@email.com

要過濾我正在使用的記錄：

SELECT *
 FROM people
WHERE (first_name || '' || last_name) ILIKE '%bat%man%' OR 
   first_name ILIKE '%bat%man%'  OR  
   last_name ILIKE '%bat%man%'   OR
   email ILIKE '%bat%man%'
   LIMIT 25 OFFSET 0

即使有索引，搜尋也需要將近一分鐘才能返回結果。、和

有索引。(first_name || '' || last_name)``first_name``last_name``email

我可以做些什麼來提高這個查詢的性能？

對於您的模式匹配，您最好使用三元組索引。先讀這個：

• LIKE 是如何實現的？

我假設您的表達式中有錯字(first_name || '' || last_name)，這對於空字元串沒有意義，而您真的想要(first_name || ' ' || last_name)- 帶有空格字元。

假設任一列都可以為 NULL，則需要 NULL 安全連接，簡單的解決方案是concat_ws()：

• 合併兩列並添加到一個新列中

但是該函式不是IMMUTABLE（連結答案中的解釋），因此您不能直接在索引表達式中使用它。您可以使用IMMUTABLE函式包裝器：

CREATE OR REPLACE FUNCTION f_immutable_concat_ws(s text, t1 text, t2 text)
 RETURNS text
 LANGUAGE sql IMMUTABLE PARALLEL SAFE AS
'SELECT concat_ws(s, t1, t2)';

包裝器可能是IMMUTABLE因為它只接受text參數。（但不要試圖在呼叫中強制要求文本表示不是不可變的數據類型！）

無論哪種方式，這更冗長但內部成本更少並且速度更快：

CREATE OR REPLACE FUNCTION f_immutable_concat_ws(s text, t1 text, t2 text)
 RETURNS text
 LANGUAGE sql IMMUTABLE PARALLEL SAFE AS
$func$
SELECT CASE
        WHEN t1 IS NULL THEN t2
        WHEN t2 IS NULL THEN t1
        ELSE t1 || s || t2
      END
$func$;

或者，使用硬編碼的空格字元：

CREATE OR REPLACE FUNCTION f_concat_space(t1 text, t2 text)
 RETURNS text AS
 LANGUAGE sql IMMUTABLE PARALLEL SAFE
$func$
SELECT CASE
        WHEN t1 IS NULL THEN t2
        WHEN t2 IS NULL THEN t1
        ELSE t1 || ' ' || t2
      END
$func$;

使其PARALLEL SAFE（在 Postgres 9.6 或更高版本中）不妨礙並行性。（而且因為它符合條件！）

基於最後一個函式的索引，我建議：

CREATE INDEX people_gin_trgm_idx ON people
USING gin (f_concat_space(first_name, last_name) gin_trgm_ops, email gin_trgm_ops);

我添加email為第二個索引列，因為您似乎同時檢查兩者。

為 20M 行創建索引需要一段時間，最好不要在最高負載期間，或者使用CREATE INDEX CONCURRENTLY .... GIN 索引可以比普通的 B-tree 索引大得多，並且維護成本更高。請務必執行最新版本的 Postgres，最近版本中對 GIN 索引進行了重大改進。

那麼你稍微適應和簡化的查詢應該是快速和正確的：

SELECT *
FROM   people
WHERE  f_concat_space(first_name, last_name) ILIKE '%bat%man%'
   OR email ILIKE '%bat%man%'
LIMIT  25;

此查詢只需要一個索引。

有關的：

• 在 PostgreSQL 中使用 LIKE、SIMILAR TO 或正則表達式進行模式匹配
• 如何創建索引以加快對錶達式的聚合 LIKE 查詢？
• 如何將 JSON 數組轉換為 Postgres 數組？

引用自：https://dba.stackexchange.com/questions/117403