BUG #13464: Optimizer fails to use partial index on boolean when selected via "IS" operator.

The following bug has been logged on the website:

Bug reference: 13464
Logged by: Corey Huinker
Email address: corey(dot)huinker(at)gmail(dot)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(at)test# create table sample_table(x integer not null, y boolean
default false not null);
CREATE TABLE
Time: 6.025 ms
[local]:ubuntu(at)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(at)test# create index sample_table_i1 on sample_table(x);
CREATE INDEX
Time: 46.622 ms
[local]:ubuntu(at)test# create index sample_table_i2 on sample_table(x) where
y;
CREATE INDEX
Time: 16.076 ms
[local]:ubuntu(at)test# update sample_table set y = true where x % 100 = 0;
UPDATE 1000
Time: 47.548 ms
[local]:ubuntu(at)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(at)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(at)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)

corey(dot)huinker(at)gmail(dot)com writes:
> Correct index is used on "WHERE y" and "WHERE y = true" but not "WHERE y IS
> TRUE".

y IS TRUE is not actually equivalent to the other two conditions ...

regards, tom lane

Good to know. And yes, according to
http://www.postgresql.org/docs/9.4/static/functions-comparison.html IS TRUE
will never return null even if y is null.

I can't, however think of a case where the the partial index would lead to
an incorrect answer. Can you enlighten me?

On Tue, Jun 23, 2015 at 8:28 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> corey(dot)huinker(at)gmail(dot)com writes:
> > Correct index is used on "WHERE y" and "WHERE y = true" but not "WHERE y
> IS
> > TRUE".
>
> y IS TRUE is not actually equivalent to the other two conditions ...
>
> regards, tom lane
>