The following bug has been logged on the website:
Bug reference: 13464
Logged by: Corey Huinker
Email address: corey.huinker@gmail.com
PostgreSQL version: 9.4.2
Operating system: Ubuntu 14.04
Description:
Correct index is used on "WHERE y" and "WHERE y = true" but not "WHERE y IS
TRUE".
[local]:ubuntu@test# create table sample_table(x integer not null, y boolean
default false not null);
CREATE TABLE
Time: 6.025 ms
[local]:ubuntu@test# insert into sample_table(x) select t.n from
generate_series(1,100000) as t(n);
INSERT 0 100000
Time: 143.815 ms
[local]:ubuntu@test# create index sample_table_i1 on sample_table(x);
CREATE INDEX
Time: 46.622 ms
[local]:ubuntu@test# create index sample_table_i2 on sample_table(x) where
y;
CREATE INDEX
Time: 16.076 ms
[local]:ubuntu@test# update sample_table set y = true where x % 100 = 0;
UPDATE 1000
Time: 47.548 ms
[local]:ubuntu@test# explain select * from sample_table where x < 10000 and
y = true;
QUERY PLAN
------------------------------------------------------------------------------------
Index Scan using sample_table_i2 on sample_table (cost=0.28..1.29 rows=1
width=5)
Index Cond: (x < 10000)
(2 rows)
Time: 1.321 ms
[local]:ubuntu@test# explain select * from sample_table where x < 10000 and
y is true;
QUERY PLAN
--------------------------------------------------------------------------------------
Index Scan using sample_table_i1 on sample_table (cost=0.29..252.14 rows=1
width=5)
Index Cond: (x < 10000)
Filter: (y IS TRUE)
(3 rows)
Time: 0.369 ms
[local]:ubuntu@test# explain select * from sample_table where x < 10000 and
y;
QUERY PLAN
------------------------------------------------------------------------------------
Index Scan using sample_table_i2 on sample_table (cost=0.28..1.29 rows=1
width=5)
Index Cond: (x < 10000)
(2 rows)