Обсуждение: BUG #13464: Optimizer fails to use partial index on boolean when selected via "IS" operator.
BUG #13464: Optimizer fails to use partial index on boolean when selected via "IS" operator.
От
corey.huinker@gmail.com
Дата:
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 >