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.
-- 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);
