Обсуждение: 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.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)

corey.huinker@gmail.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@sss.pgh.pa.us> wrote:

> corey.huinker@gmail.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
>