PostgreSQL で関数インデックスに使っているストアドファンクションを REPLACE する

当たり前かもしれませんが PostgreSQL で関数インデックスに使っているストアドファンクションを REPLACE して中身を書き換えてしまうとクエリの結果が正しくなくなることがあります。

-- テーブルを作る
create table t (id serial not null primary key, str text);

--- 'Aa' の行と適当に100万行入れる
insert into t (str) values ('Aa');
insert into t (str) select generate_series(1, 1000000, 1)::text;

-- ストアドファンクションを作る
-- 関数インデックスで使うためには IMMUTABLE も必要
create or replace function my_func(_text text) returns text as $$ begin return upper(_text); end $$ LANGUAGE plpgsql IMMUTABLE;

-- インデックスが無いので遅い
select * from t where my_func(str) = my_func('aA');
-- (1 rows)
-- Time: 754.790 ms

-- 関数インデックスを作る
create index idx on t (my_func(str));

-- インデックスがあるので早い
select * from t where my_func(str) = my_func('aA');
-- (1 rows)
-- Time: 0.751 ms

-- ストアドファンクションを同じ内容で REPLACE する
create or replace function my_func(_text text) returns text as $$ begin return upper(_text); end $$ LANGUAGE plpgsql IMMUTABLE;

-- 特に変わらない
select * from t where my_func(str) = my_func('aA');
-- (1 rows)
-- Time: 0.565 ms

-- ストアドファンクションを異なる内容に REPLACE する
create or replace function my_func(_text text) returns text as $$ begin return lower(_text); end $$ LANGUAGE plpgsql IMMUTABLE;

-- 正しい結果が得られない
select * from t where my_func(str) = my_func('aA');
-- (0 rows)
-- Time: 0.565 ms

-- 新しい行は新しいストアドでインデックス化されるので大丈夫
insert into t (str) values ('Bb');
select my_func(str), my_func('bB') from t where my_func(str) = my_func('bB');
-- (1 rows)
-- Time: 0.963 ms

-- インデックスを再構築する
reindex index idx;

-- 正しい結果が得られる
select * from t where my_func(str) = my_func('aA');
-- (1 rows)
-- Time: 0.916 ms

IMMUTABLE としたストアドファンクションは単にその関数の参照透過性だけではなく、関数に依存するすべてのデータベースオブジェクトのライフサイクル全体にわたって不変である必要がある、ということなのかも。

https://www.postgresql.jp/document/15/html/xfunc-volatility.html https://stackoverflow.com/a/17610486

ちなみに MySQL ではどうなるかなと試そうと思ったのですが MySQL ではそもそも関数インデックスや生成列にストアドは使えませんでした。

https://dev.mysql.com/doc/refman/8.0/ja/create-index.html サブクエリー、パラメータ、変数、ストアドファンクションおよびユーザー定義関数は使用できません。

ところで PostgreSQL の pg_catalog.pg_index テーブルに indisvalid という列があるのですが、

https://www.postgresql.jp/document/15/html/catalog-pg-index.html

ストアドを REPLACE などするときっとここが false になってクエリで使用できなくなるのでは、と予想していたのですが、全然そんなことありませんでした。

↓を見るに、関数がインライン展開可能な SQL ならインデックスが不使用になるのかも？

https://www.postgresql.org/message-id/21420.1373644747%40sss.pgh.pa.us

試してみました。

-- テーブルを作る
create table t (id serial not null primary key, str text);

--- 'Aa' の行と適当に100万行入れる
insert into t (str) values ('Aa');
insert into t (str) select generate_series(1, 1000000, 1)::text;

-- ストアドファンクションを作る
create or replace function my_func(_text text) returns text as $$ select upper(_text) $$ LANGUAGE sql IMMUTABLE;

-- 関数インデックスを作る
create index idx on t (my_func(str));

-- インデックスがあるので早い
select * from t where my_func(str) = my_func('aA');
-- (1 rows)
-- Time: 0.791 ms

-- ストアドファンクションを異なる内容に REPLACE する
create or replace function my_func(_text text) returns text as $$ select lower(_text) $$ LANGUAGE sql IMMUTABLE;

-- インデックスが使われていないので遅い
select * from t where my_func(str) = my_func('aA');
-- (0 rows)
-- Time: 232.930 ms

explain select * from t where my_func(str) = my_func('aA');
--  Seq Scan on t  (cost=0.00..20405.01 rows=5000 width=10)
--    Filter: (lower(str) = 'aa'::text)

--- indisvalid は true のまま
select indisvalid from pg_index where indrelid = to_regclass('t') and indexrelid = to_regclass('idx');
-- indisvalid | t

reindex index idx;

-- インデックスが使われる
select * from t where my_func(str) = my_func('aA');
-- (0 rows)
-- Time: 0.751 ms

どうやらストアドがインライン展開可能ならインデックスが使われなくなるようなのです。 ただ、最初に予想していた indisvalid は true のままでした。これとは別のどこかに有効/無効の情報はあるのかもしれません。