Question about partial functional indexes and the query planner

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