Thread: BUG #9817: Broken index detection in case of functions with variadic array parameters
BUG #9817: Broken index detection in case of functions with variadic array parameters
From
dmitry-ryabov@mail.ru
Date:
The following bug has been logged on the website: Bug reference: 9817 Logged by: Dmitry Ryabov Email address: dmitry-ryabov@mail.ru PostgreSQL version: 9.3.4 Operating system: Any Description: -- HOW REPRODUCE: CREATE TABLE test ( id INTEGER NOT NULL, value TEXT NOT NULL, PRIMARY KEY(id) ); -- just test function CREATE FUNCTION test_value_func ( text, variadic text [] ) RETURNS bigint AS $body$ select sum(position(unnest in $1)) from unnest($2) $body$ LANGUAGE 'sql' IMMUTABLE CALLED ON NULL INPUT SECURITY INVOKER; truncate test; insert into test select generate_series(1, 1000000), md5(random()::text); CREATE INDEX test_idx ON public.test USING btree ((test_value_func(value, 'a', 'b'))); -- index scan used select * from test where test_value_func(value, 'a', 'b') > 60; DROP INDEX test_idx; -- but after restore from backup index created like this CREATE INDEX test_idx ON public.test USING btree ((test_value_func(value, VARIADIC ARRAY['a'::text, 'b'::text]))); -- BUG! index scan isn't used select * from test where test_value_func(value, 'a', 'b') > 60; -- index scan used select * from test where test_value_func(value, VARIADIC ARRAY['a'::text, 'b'::text])>60 DROP INDEX test_idx; CREATE INDEX test_idx ON public.test USING btree ((test_value_func(value, 'a', 'b'))); -- index scan used select * from test where test_value_func(value, 'a', 'b') > 60; -- BUG! index scan isn't used select * from test where test_value_func(value, VARIADIC ARRAY['a'::text, 'b'::text])>60 -- affected version: 9.3.4 -- not affected version: 9.1.13 - index scan used in both cases DROP TABLE test; DROP FUNCTION test_value_func(text, variadic text []);
Re: BUG #9817: Broken index detection in case of functions with variadic array parameters
From
Tom Lane
Date:
dmitry-ryabov@mail.ru writes: > -- BUG! index scan isn't used > select * from test where test_value_func(value, 'a', 'b') > 60; > -- index scan used > select * from test where test_value_func(value, VARIADIC ARRAY['a'::text, > 'b'::text])>60 I've committed a fix for this. Thanks for the report! regards, tom lane