Planner choice on NULLs (9.1.5) with 2 conditional indexes - Mailing list pgsql-general

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