Postgres 不使用表達式索引

我正在使用 Postgres 9.5。這可能與PostgreSQL 在 count(*) 期間未使用索引所指出的 9.6 中的更改有關，但我還不能確定。

我有一個表，其中有一character varying列名為text. 我正在嘗試優化必須在此列上執行正則表達式的查詢。

這通常看起來像

select * from table where text ~ 'some|key|words|we|want';

或相反

select * from table where text !~ 'some|key|words|we|want';

有時加入

select category, count(*)
from other_t join table on (other_t.table_id = table.id)
where table.text ~ 'some|key|words|we|want';

select category, count(*)
from other_t join
   (select id
    from table
    where table.text ~ 'some|key|words|we|want') as ti on (other_t = ti.id);

請注意，正則表達式始終相同。

我不想直接索引該列，因為 AFAIK 索引文本列會導致一個相對巨大的索引，這只會浪費空間。這排除了上面連結文章的部分索引。所以，我一直在嘗試使用表達式索引，我應該能夠這樣做，因為正則表達式總是相同的。

但是，我無法讓 Postgres 使用索引！

我已經完成了一個vacuum analyze，我已經嘗試了我能想像到的所有類型的查詢，但它總是只做一個Seq Scan.

該列平均約 85 個字元，如果匹配，通常只有一兩個我們正在尋找的關鍵字。

有任何想法嗎？

我對完整的關鍵字搜尋不感興趣。我們有一個有限的、穩定的要查找的單詞列表。當我真正需要的是 Postgres 決定使用的表達式索引時，我不想在此文本列上創建一個巨大的 trigram/ts_vector 索引。

我們不能消除文本列，因為它還有其他用途。我們已經考慮過添加一個列，該列本質上是該查詢的記憶體，但這很難看。如果這是最好的選擇，那就這樣吧，但我們真的應該能夠在這裡使用索引。

索引創建

create index text_col_idx on table ((text ~ 'some|key|words|we|want'));

解釋分析

explain (analyze, verbose) select id from table where text ~ 'some|key|words|we|want';

結果

Seq Scan on public.table  (cost=0.00..405624.24 rows=5190575 width=4) (actual time=0.113..52582.259 rows=5113014 loops=1)
 Output: id
 Filter: ((table.text)::text ~ 'some|key|words|we|want'::text)
 Rows Removed by Filter: 4728206
Planning time: 10.938 ms
Execution time: 52766.607 ms

社區維基回答：

您的條件僅消除了表中大約 50% 的行。在這種情況下，Seq Scan 將比 Index Scan 更快。

例如，請參閱此 Stack Overflow 答案，其中使用者a_horse_with_no_name說：

如果SELECT返回表中所有行的大約 5-10% 以上，則順序掃描比索引掃描快得多。

這是因為索引掃描需要對每一行進行多次IO 操作（在索引中查找該行，然後從堆中檢索該行）。而順序掃描每行只需要一個 IO - 甚至更少，因為磁碟上的一個塊（頁）包含多行，因此可以通過單個 IO 操作獲取多行。

另請參閱文件中的僅索引掃描，其中包括：

原則上，僅索引掃描可以與表達式索引一起使用。

$$ … $$但是，PostgreSQL 的規劃器目前對這種情況不是很聰明。僅當查詢所需的所有列都可從索引中獲得時，它才認為查詢可以通過僅索引掃描執行。

一個單獨的想法是keywords在您的主要表格旁邊有一個表格，每個您感興趣的單詞有一個 ID，以及一個與特定文本相關的 ID，然後根據需要填充它。選擇顯然取決於許多其他只有您才能知道的約束。

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