Question about partial functional indexes and the query planner

[Brian Dunavant <brian@xxxxxxxxxx>]

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