Postgres 函式索引不適用於正則表達式

我有這個 Postgres 功能。它強製文本為小寫，所有空值變為空字元串，以便我更清晰地搜尋不匹配的內容等：

CREATE OR REPLACE FUNCTION magic_text(txt text) RETURNS text
IMMUTABLE PARALLEL SAFE
LANGUAGE SQL AS $$
 SELECT lower(coalesce(txt,''))
$$;

這個函式在許多不同的查詢類型中被大量使用，所以我為它創建了所有種類的文本索引：

CREATE INDEX index_magic_cards_on_oracle_text_magic 
ON magic_cards 
USING BTREE (magic_text(oracle_text)) WITH (fillfactor = 100);

CREATE INDEX index_magic_cards_on_oracle_text_magic_text_pattern 
ON magic_cards 
USING BTREE (magic_text(oracle_text) text_pattern_ops) WITH (fillfactor = 100);

CREATE INDEX index_magic_cards_on_oracle_text_magic_gist_trgm 
ON magic_cards 
USING GIST (magic_text(oracle_text) gist_trgm_ops);

CREATE INDEX index_magic_cards_on_oracle_text_magic_gin_trgm 
ON magic_cards 
USING GIN (magic_text(oracle_text) gin_trgm_ops)

這些索引以某種方式乾擾了某些（但不是全部）複雜的正則表達式搜尋。我無法確定特定的正則表達式符號或功能是否會導致問題。

這是一個範例（explain.depesz）：

SELECT card_name 
FROM magic_cards 
WHERE magic_text(oracle_text) ~ '***:(?n)eldrazi\ (?!scion)';

這不會返回任何內容，並且根據查詢計劃器，它會在index_magic_cards_on_oracle_text_magic_gin_trgm.

此擴展查詢還掃描相同的索引，但找不到任何內容（explain.depesz）：

SELECT card_name 
FROM magic_cards 
WHERE lower(coalesce(oracle_text, '')) ~ '***:(?n)eldrazi\ (?!scion)';

但是，如果我強制 Postgres 不使用我的索引，這個查詢就會有結果！( explain.depesz ) 下面有多行結果，對錶進行順序掃描。我在下面所做的只是將合併備份更改為⌘，這不應該影響結果：

SELECT card_name 
FROM magic_cards 
WHERE lower(coalesce(oracle_text, '⌘')) ~ '***:(?n)eldrazi\ (?!scion)';

為什麼使用索引會更改正則表達式搜尋的結果？

（Postgres 9.6.1，pg_trgm 1.3）

對我來說，這看起來像是 pg_trgm 中的一個實時錯誤。我可以去掉很多東西，仍然用這個簡單的測試案例重現它：

create table foobar (x text);
insert into foobar values ('eldrazi scion'),('eldrazi scio');
create extension pg_trgm ;
create index  on foobar using gin (x gin_trgm_ops);
select * from foobar where x  ~ 'eldrazi (?!scion)';  -- returns 1 row
set enable_seqscan TO off;
select * from foobar where x  ~ 'eldrazi (?!scion)'; -- returns 0 rows

請注意，最近修復了一個類似的錯誤（在尚未發布的程式碼中，送出 16500d2278ab3dd），但該修復並未修復此錯誤。

我自己報告了這個錯誤，作為錯誤 #14623。

現在已經修復了。當 9.6.3 發佈時，它應該包含錯誤修復。

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