Re: 9.5alpha1: Partial index not used

"Peter J. Holzer" <hjp-pgsql(at)hjp(dot)at> writes:
> Consider this table:
> ...
> "concept_start_idx" btree (start) WHERE start IS NOT NULL

> and this query:

> select id as IdValue, canonicalname as Description, null as IsLeaf from public.concept where start;

> Clearly this should be able to use the partial index (if start is true
> it is also not null)

As you surmise, there's no proof rule for that.

> and since there are only 3 out of 3 million rows in
> result it would also be beneficial (and PostgreSQL 9.1 did use the
> index).

... and there wasn't in 9.1 either. I get a seqscan from examples like
this in every branch back to 8.3, which is as far back as I can test
conveniently.

This is the exact test case I'm using:

create table foo as select null::bool as start, generate_series(1,100000) as x;
update foo set start = true where x < 10;
create index foos on foo (start) where start is not null;
analyze foo;
explain select * from foo where start;
explain select * from foo where start = true;
explain select * from foo where start is not null;

Only the last case produces use of the index. I agree that it'd be better
if they all did, but I'm disinclined to consider it a bug fix unless you
can show a specific case in which there's a performance regression from
older releases.

regards, tom lane