可以過濾掉大多數行時在大表上進行中綴搜尋

我們有一個 Postgres 表，如下所示：

+-----------+---------+---------+---------+
| CLIENT_ID | VALUE_1 | VALUE_2 | VALUE_3 |
+-----------+---------+---------+---------+
|     11234 | aabcdef |   mnfng |  lflgbm |
+-----------+---------+---------+---------+
|     11234 |   xdfef | nfnvnaz | fngnnva |
+-----------+---------+---------+---------+
|     84590 |  pflghh |   otngp | cignral |
+-----------+---------+---------+---------+
|    ...    |   ...   |   ...   |   ...   |

CLIENT_ID是一個索引列。

我們需要執行一個查詢，該查詢將返回屬於特定的所有行，CLIENT_ID並在某些列中具有特定的子字元串VALUE_X。

對客戶端 ID11234和子字元串的簡單ng查詢如下所示：

SELECT * FROM tbl WHERE client_id = '11234' AND (value_1 LIKE '%ng%' OR
value_2 LIKE '%ng%' OR value_3 LIKE '%ng%')

問題是我們的桌子很大。它可能包含 10+ 數百萬行。行可以首先過濾CLIENT_ID- 然後匹配的行子集將只有大約 100k+ 行。

我們的測試表明，當表比較小時，數據庫會對整個表進行順序掃描。它不是很快，但相當快（大約 1 秒）。當表變大時，數據庫首先過濾具有特定CLIENT_ID（已索引）的行，然後對其餘行進行順序掃描。這非常慢（大約 30 秒以上）。

有什麼方法可以加快速度，僅使用 Postgres（我們不想引入 Elasticsearch 等）？

我們目前在生產中使用 Postgres 10.9，但如果有顯著的性能提升（我們的暫存測試顯示沒有），我們可能會升級到最新版本。

謝謝你。

請注意，已經有加快中綴搜尋的執行緒，但這種情況更具體——否則我們可能會過濾掉許多行。

謝謝大家的意見。VALUE_N我的同事剛剛通過在列上使用 Trigram 索引解決了這個問題。現在查詢在 10 毫秒內執行。

花費 30 多秒錶明記憶體中的數據不足。也許你可以扔更多的記憶體。

如果您實際上只搜尋每邊都有 % 緊的兩個字元，那麼 pg_trgm 索引將無濟於事。儘管也許您可以將自己的版本編譯為二元索引。

如果您的表只有這 4 列（或者它有更多，但您只選擇這 4 列的一部分而不是使用 *），那麼您可以使用此索引：

create index on tbl (client_id, value_1, value_2, value_3);

並獲得僅索引掃描。

如果這對您不起作用，因為您需要更多列，那麼您可以向該索引添加主/唯一鍵（此處稱為id），然後像這樣查詢：

create index on tbl (client_id , id, value_1, value_2, value_3);

SELECT tbl.* from 
   (select id FROM tbl WHERE client_id = '99' AND (value_1 LIKE '%ng%' ORvalue_2 LIKE '%ng%' OR value_3 LIKE '%ng%')) t 
   join tbl using (id);

有點遺憾的是 PostgreSQL 不夠聰明，無法為您做到這一點。

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