Hi everyone, I am using a partial functional index on a table where F(a) = a. Querying whre F(a) = a hits the index as expected. However the reverse statement a = F(a) does not. I have verified this in 9.3.4.
Is this a deficiency with the query planner, or are these not actually equivalent? I've been stumped on it. -Brian Dunavant Test script to display behavior below: -- Setup the test data CREATE OR REPLACE FUNCTION public.return_if_even(v_id integer) returns integer LANGUAGE sql AS $$ SELECT case when v_id % 2 = 1 then 0 else v_id end; $$; create table public.partial_functional_index_test as select id from generate_series(1,1000000) AS s(id); create index partial_functional_idx ON public.partial_functional_index_test USING btree ( public.return_if_even(id) ) WHERE public.return_if_even(id) = id; -- This will hit the index explain analyze select count(1) from public.partial_functional_index_test where public.return_if_even(id) = id; -- This will not hit the index explain analyze select count(1) from public.partial_functional_index_test where id = public.return_if_even(id); -- To work around it, I can index both ways: drop index partial_functional_idx; create index partial_functional_idx ON public.partial_functional_index_test USING btree ( public.return_if_even(id) ) WHERE public.return_if_even(id) = id OR id = public.return_if_even(id); -- Now both versions will hit the index explain analyze select count(1) from public.partial_functional_index_test where public.return_if_even(id) = id; explain analyze select count(1) from public.partial_functional_index_test where id = public.return_if_even(id); -- Cleanup test data drop table public.partial_functional_index_test; drop function public.return_if_even(integer);
