"Peter J. Holzer" <hjp-pgsql@hjp.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