Postgresql
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 發佈時,它應該包含錯誤修復。