Postgresql

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

  • April 14, 2017

我有這個 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