9.5alpha1: Partial index not used

Consider this table:

wdsold=> \d concept
Table "public.concept"
Column | Type | Modifiers
-------------------+-----------------------------+------------------------------------------------------
id | integer | not null default nextval('concept_id_seq'::regclass)
canonicalname | character varying |
description | character varying |
start | boolean |
hidden | boolean |
sortorder | integer |
valid_from | timestamp without time zone | not null default now()
from_job_queue_id | integer |
Indexes:
"concept_pkey" PRIMARY KEY, btree (id)
"concept_canonicalname_idx" btree (canonicalname)
"concept_start_idx" btree (start) WHERE start IS NOT NULL
Referenced by:
TABLE "facttablemetadata" CONSTRAINT "facttablemetadata_dimension_fkey" FOREIGN KEY (dimension) REFERENCES concept(id)
TABLE "facttablemetadata" CONSTRAINT "facttablemetadata_member_fkey" FOREIGN KEY (member) REFERENCES concept(id)
TABLE "relation" CONSTRAINT "relation_child_fkey" FOREIGN KEY (child) REFERENCES concept(id) DEFERRABLE
TABLE "relation" CONSTRAINT "relation_parent_fkey" FOREIGN KEY (parent) REFERENCES concept(id) DEFERRABLE
TABLE "term" CONSTRAINT "term_concept_id_fkey" FOREIGN KEY (concept_id) REFERENCES concept(id) DEFERRABLE

wdsold=> select start, count(*) from concept group by start order by start;
start | count
-------+---------
t | 3
| 3431866
(2 rows)

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

However, it PostgreSQL 9.5 doesn't use it:

wdsold=> explain analyze select id as IdValue, canonicalname as Description, null as IsLeaf from public.concept where start;
QUERY PLAN
--------------------------------------------------------------------------------------------------------
Seq Scan on concept (cost=0.00..81659.03 rows=1 width=27) (actual time=0.026..955.889 rows=3 loops=1)
Filter: start
Rows Removed by Filter: 3431866
Planning time: 0.193 ms
Execution time: 955.926 ms
(5 rows)

Even if I try to force it:

wdsold=> set enable_seqscan to off;
SET
Time: 0.540 ms
wdsold=> explain analyze select id as IdValue, canonicalname as Description, null as IsLeaf from public.concept where start;
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------
Seq Scan on concept (cost=10000000000.00..10000099078.69 rows=1 width=27) (actual time=0.014..948.738 rows=3 loops=1)
Filter: start
Rows Removed by Filter: 3431866
Planning time: 0.060 ms
Execution time: 948.777 ms
(5 rows)

So it obviously thinks that it can't use the index.

However, if I create a full index:

wdsold=> create index on concept(start);
CREATE INDEX
Time: 5899.635 ms
wdsold=> explain analyze select id as IdValue, canonicalname as Description, null as IsLeaf from public.concept where start;
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------
Index Scan using concept_start_idx on concept (cost=0.43..3.05 rows=1 width=27) (actual time=0.501..0.535 rows=3 loops=1)
Index Cond: (start = true)
Filter: start
Planning time: 0.731 ms
Execution time: 0.577 ms
(5 rows)

it is used, and also if I create a partial index just on true values:

wdsold=> create index on concept(start) where start;
CREATE INDEX
Time: 937.267 ms
wdsold=> explain analyze select id as IdValue, canonicalname as Description, null as IsLeaf from public.concept where start;
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------
Index Scan using concept_start_idx on concept (cost=0.13..2.75 rows=1 width=27) (actual time=0.028..0.033 rows=3 loops=1)
Index Cond: (start = true)
Planning time: 0.499 ms
Execution time: 0.073 ms
(4 rows)

it is also used.

So I think the problem is that PostgreSQL 9.5alpha1 doesn't consider
true to be a subset of the non-null values for the purpose of index
selection.

hp

--
_ | Peter J. Holzer | I want to forget all about both belts and
|_|_) | | suspenders; instead, I want to buy pants
| | | hjp(at)hjp(dot)at | that actually fit.
__/ | http://www.hjp.at/ | -- http://noncombatant.org/