具有字元變化（ILIKE）的int的PostgreSQL btree索引不起作用

您好，我有大約 5000 萬個論壇，每個論壇有超過 3000 萬個主題，這是我在 PostgreSQL 中的主題結構

CREATE TABLE public.forum_topic
(
   "forum" integer NOT NULL,
   "user" integer NOT NULL,
   "submit" integer NOT NULL,
   "subject" character varying(64) NOT NULL,
   "content" character varying(8192) NOT NULL,
   "ip" integer NOT NULL,
   PRIMARY KEY ("forum", "user", "submit")
);

這是我的搜尋查詢，在論壇 1000（論壇 ID）中找到每個帶有關鍵字“PHP”的主題（在其主題中）

SELECT * FROM "forum_topic" WHERE "forum"=1000 AND "subject" ILIKE '%PHP%' LIMIT 10

我知道我必須為這種搜尋創建一個索引，我做到了。

CREATE INDEX forum_topic_forum_subject_idx
   ON public.forum_topic USING btree
   (forum, subject varchar_pattern_ops);

但是當我執行選擇查詢（頂部）時，沒有使用我創建的’forum_topic_forum_subject_idx’索引的跡象，執行它需要10秒！！！！！！

Seq Scan on forum_topic as forum_topic (rows=0 loops=5)
Filter: (((subject)::text ~~* '%PHP%'::text) AND (forum = 1000))
Rows Removed by Filter: 9998001

你對我的情況有什麼想法嗎？對於這種索引，您有更好的方法嗎？

我正在使用 PostgreSQL 13

- - 更新 - -

我創建了一個杜松子酒索引：

create index idx_forum_topic on forum_topic using gin (forum,subject gin_trgm_ops);

但是發生了一些事情！我執行了 2 個查詢，一個基於 ‘idx_forum_topic’ 索引執行，一個基於 ‘seq scan’ 執行！！！！

SELECT * FROM "forum_topic" WHERE "forum"=26854 AND "subject" ILIKE '%mmap2%' LIMIT 2;

結果（1秒）：

Bitmap Heap Scan on forum_topic as forum_topic (rows=1 loops=1)
Recheck Cond: ((forum = 26854) AND ((subject)::text ~~* '%mmap2%'::text))
Heap Blocks: exact=1

和

SELECT * FROM "forum_topic" WHERE "forum"=2 AND "subject" ILIKE '%mmap2%' LIMIT 2;

結果（27 秒）

Seq Scan on forum_topic as forum_topic (rows=1 loops=1)
Filter: (((subject)::text ~~* '%mmap2%'::text) AND (forum = 2))
Rows Removed by Filter: 49990003

！！！問題是什麼 ！！！

帶有 btree 索引的 varchar_pattern_ops 僅適用於不以萬用字元開頭的模式。所以不是 for ilike '%PHP%'，它確實以萬用字元開頭。

多列 GIN 索引與多列 btree 索引不同。它們不能有效地將列連結在一起。它們更像是兩個單列索引，在這裡使用必須與 BitmapAnd 組合。這可能還不錯，但仍然不如 btree 式連結效率高。

這可能是嘗試使用 GIST trigram 索引而不是 GIN trigram 的情況。多列 GIST 索引比 GIN 更好地將列連結在一起。儘管它們在進行三元組搜尋時效率不高，但您必須嘗試一下，看看更好的連結是否能克服更差的效率。

至於為什麼更改常量時它會改變，您應該向我們展示每個查詢的完整 EXPLAIN (ANALYZE, BUFFERS)，以及將 WHERE 子句的每個部分隔離的查詢，並讓我們知道您擁有的任何非預設配置設置。

使用三元組索引。

create extension btree_gin;

create extension pg_trgm;

create index idx_forum_topic on forum_topic using gin (forum,subject gin_trgm_ops);

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