LIKE ALL 過濾器的三元組索引

我發現自己正在尋找一種動態限制搜尋結果的方法，以便所有搜尋都必須存在：

$$ “A TE”,“SOME” $$搜尋數組：

“MS A TEXT B SOMETHING” –> 匹配

“MS AB 某事”-> 不匹配

所以我使用的是 GIN 索引和 LIKE ALL：

CREATE INDEX ix_gin_code ON ilike_test USING GIN(code gin_trgm_ops);

select * from ilike_test where code ilike ALL (:search_terms::text[]);

但是，在按 ALL 過濾時不會使用索引，只有直接 LIKE 和 LIKE ANY 才能使用。

這有什麼原因嗎？或者進行此搜尋的替代（快速）方法是什麼？

實際數據庫：

• 500k 行
• 文本列中位行大小為 4kb

擺弄解釋說：

http://sqlfiddle.com/#!17/cc555/14 - 禁用 seq 掃描，產生一個奇怪的 LIKE ALL 解釋

http://sqlfiddle.com/#!17/cc555/15

我不知道為什麼它會這樣工作。它似乎是核心 PostgreSQL 索引機制的屬性，而不是 pg_trgm 模組本身的一部分。如果您真的想深入研究，PostgreSQL 自託管郵件列表之一可能是一個更好的詢問地點。

由於您要求使用其他方法進行搜尋，因此一種方法是分隔數組並將表達式與 AND 組合：

select * from ilike_test where code ilike '%n/a%"' and code ilike '%03/02/2021#A%';

這將使用索引，即使它與不會使用的索引“相同”。需要重寫查詢以考慮數組的大小並不理想。

請注意， %n/a% 沒有有用的 trigrams，但我看不到以自動化方式使用該資訊的非常可行的方法。（它實際上確實有一個可用的三元組， a。但它可能是您數據中非常常見的一個）

在郵件列表中進行了一些深入搜尋後，到目前為止，我看到的唯一解決方法是來自這個執行緒。

本質上，您可以執行以下操作：

body ilike all (@q::text[]) and body ilike (@q::text[])[1]

強制它使用索引：

Bitmap Heap Scan on test_table  (cost=12.01..16.02 rows=1 width=746) (actual time=0.328..1.366 rows=2 loops=1)
 Recheck Cond: (body ~~* '%n/a%'::text)
 Rows Removed by Index Recheck: 10
 Filter: (body ~~* ALL ('{%n/a%,%03/02/2021#A%}'::text[]))
 Rows Removed by Filter: 6
 Heap Blocks: exact=3
 ->  Bitmap Index Scan on ix_gin_body  (cost=0.00..12.01 rows=1 width=0) (actual time=0.009..0.009 rows=18 loops=1)
       Index Cond: (body ~~* '%n/a%'::text)
Planning Time: 0.072 ms
Execution Time: 1.379 ms

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