查詢中不使用表達式索引（正則表達式模式匹配）

在我的PostgreSQL 12.8數據庫中，我有一個相對簡單的表the_table，其中有一列value類型為varchar：

CREATE TABLE public.the_table (
   id uuid DEFAULT gen_random_uuid() NOT NULL,
   label character varying,
   value character varying,
   created_at timestamp without time zone NOT NULL,
   updated_at timestamp without time zone NOT NULL,
);

我想查詢具有格式化為電子郵件地址的值的所有行。查詢如下所示：SELECT * FROM the_table WHERE value ~ '^[a-zA-Z0-9.$%&*+/=?^_{|}~-]+@[a-zA-Z0-9.-]+\.[a-zA-Z0-9]+$'.

由於該表中有幾百萬行，我嘗試通過添加匹配的表達式索引來加速此查詢CREATE INDEX index_the_table_on_email_values ON the_table ((value ~ '^[a-zA-Z0-9.$%&*+/=?^_{|}~-]+@[a-zA-Z0-9.-]+\.[a-zA-Z0-9]+$'));

不幸的是，查詢計劃器不使用索引，而是對錶執行全盤掃描，這非常慢。

誰能幫我修復索引或告訴我還有哪些其他選擇？我已經考慮過生成的布爾列is_email。我可以向該生成的列添加索引並直接查詢它。但這似乎是原始問題的一種奇怪的解決方法，應該可以通過匹配索引來解決，對嗎？

公平地說，您對boolean錶達式的索引基本上也有效。

關鍵是：如果有很大比例的“電子郵件”行，那麼沒有索引會幫助（很多）——除了特殊情況。Postgres 通常會選擇更快的順序掃描。（我懷疑這是你的情況。）

如果只有很少的“電子郵件”行，那麼部分索引會更有效，因為它排除了大多數行開頭：

CREATE INDEX the_table_email_idx ON the_table ((true))
WHERE value ~ '^[a-zA-Z0-9.$%&*+/=?^_{|}~-]+@[a-zA-Z0-9.-]+\.[a-zA-Z0-9]+$';

(true)只是一個任意常數，因為沒有明顯的索引列。通常，您在“電子郵件過濾器”頂部有一個有用的索引列，可以替換該常量 - 使索引更加有用。有關的：

• IS NULL 上的 Postgres 部分索引不起作用

當然，生成列 is_email的想法也不錯。然後，您將使用該生成列上的條件創建部分索引。這有好處也有壞處。

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