I have this Postgres function. It forces text to be lowercase and all nulls to become empty strings, so that I more cleanly perform searches for things that don't match something, etc:
CREATE OR REPLACE FUNCTION magic_text(txt text) RETURNS text IMMUTABLE PARALLEL SAFE LANGUAGE SQL AS $$ SELECT lower(coalesce(txt,'')) $$;
This function is heavily used in many different query types, so I created all of the varieties of text index for it:
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)
These indexes are interfering somehow with some (but not all) kinds of complex regular expression searches. I haven't been able to determine if specific regex symbols or features cause the problem.
Here's an example (explain.depesz):
SELECT card_name FROM magic_cards WHERE magic_text(oracle_text) ~ '***:(?n)eldrazi\ (?!scion)';
This returns nothing, and according to the query planner, it performs a bitmap index scan on
This expanded query also scans the same index, and fails to find anything (explain.depesz):
SELECT card_name FROM magic_cards WHERE lower(coalesce(oracle_text, '')) ~ '***:(?n)eldrazi\ (?!scion)';
However, if I force Postgres to not use my index, this query has results! (explain.depesz) The following has multiple rows of results and performs a sequential scan on the table. All I did below was change the coalesce fallback to
⌘, which shouldn't affect the results:
SELECT card_name FROM magic_cards WHERE lower(coalesce(oracle_text, '⌘')) ~ '***:(?n)eldrazi\ (?!scion)';
Why does using the index change the results for regular expression searches?
(Postgres 9.6.1, pg_trgm 1.3)