Planner choice on NULLs (9.1.5) with 2 conditional indexes

Hello,

here is a small test case to reproduce an issue from our production
system:

- one table with 2 columns (sid, ua) containing a lot of nulls
- two indexes with the same condition:
i_sid : on (sid) WHERE sid IS NOT NULL
i_ua : on (ua,sid) WHERE sid IS NOT NULL

and a query with the clause "WHERE sid IS NOT NULL and ua IS NULL"

It is quite evident that the second index is better as it allows to
resolve the 2 conditions of the queries,
but this seems to be an issue for the planner that prefers the first
index

beat regards,

Marc Mamin

create table ptest (sid int, ua int);

insert into ptest
select null,null from generate_series (1,100000);

insert into ptest
select s%100,s%50 from generate_series (1,10000) s;

insert into ptest
select s%100,null from generate_series (1,1000) s;

create index i_sid on ptest(sid) WHERE sid IS NOT NULL;

create index i_ua on ptest(ua,sid) WHERE sid IS NOT NULL;

-- BEFORE ANALYZE:
------------------
explain analyze
select count(*) from ptest WHERE sid IS NOT NULL and ua IS NULL

http://explain.depesz.com/s/1n3

Aggregate (cost=402.71..402.72 rows=1 width=0) (actual
time=0.297..0.297 rows=1 loops=1)
-> Bitmap Heap Scan on ptest (cost=11.91..401.33 rows=552 width=0)
(actual time=0.146..0.235 rows=1000 loops=1)
Recheck Cond: ((ua IS NULL) AND (sid IS NOT NULL))
-> Bitmap Index Scan on i_ua (cost=0.00..11.77 rows=552
width=0) (actual time=0.140..0.140 rows=1000 loops=1)
Index Cond: ((ua IS NULL) AND (sid IS NOT NULL))
Total runtime: 0.331 ms

-- ANALYZED:
------------
analyze ptest

explain analyze
select count(*) from ptest WHERE sid IS NOT NULL and ua IS NULL

http://explain.depesz.com/s/s6c

Aggregate (cost=711.59..711.60 rows=1 width=0) (actual
time=1.842..1.842 rows=1 loops=1)
-> Bitmap Heap Scan on ptest (cost=184.00..686.67 rows=9970 width=0)
(actual time=1.677..1.780 rows=1000 loops=1)
Recheck Cond: (sid IS NOT NULL)
Filter: (ua IS NULL)
-> Bitmap Index Scan on i_sid (cost=0.00..181.50 rows=10967
width=0) (actual time=0.826..0.826 rows=11000 loops=1)
Index Cond: (sid IS NOT NULL)
Total runtime: 1.873 ms

I have also tried it with the best possible statistics, but the planner
still choose the single column index:

ALTER TABLE ptest ALTER sid SET STATISTICS 10000;
ALTER TABLE ptest ALTER ua SET STATISTICS 10000;
analyze ptest;

explain analyze
select count(*) from ptest WHERE sid IS NOT NULL and ua IS NULL

http://explain.depesz.com/s/Vjy9