Обсуждение: Suggest using boolean index with (bflag is true) condition for the query with (bflag = true) clause
Suggest using boolean index with (bflag is true) condition for the query with (bflag = true) clause
От
Евгений Плискин
Дата:
This is not a bug report but rather a suggestion. Suppose database contains an index on a boolean column like this: create table1(id int, amount float, best_record boolean); create index index1 on table1(best_record) where best_record is true; And suppose we issue a query like this: select * from table1 where best_record = true; The planner does not try to use an index for this query because an index condition (bflag is true) does not literally matchthe query (bflag = true). But is that reasonable? The index does not cover records where "best_record" is FALSE or NULL. And similarly the query is not interested in any records where "best_record" is FALSE or NULL. So why not use this index for this query? Thank you.
On Sun, 2022-09-18 at 18:24 +0300, Евгений Плискин wrote: > This is not a bug report but rather a suggestion. > > Suppose database contains an index on a boolean column like this: > create table1(id int, amount float, best_record boolean); > create index index1 on table1(best_record) where best_record is true; > > And suppose we issue a query like this: > select * from table1 where best_record = true; > > The planner does not try to use an index for this query because an index condition (bflag is true) does not literally matchthe query (bflag = true). > But is that reasonable? > The index does not cover records where "best_record" is FALSE or NULL. > And similarly the query is not interested in any records where "best_record" is FALSE or NULL. > So why not use this index for this query? Because the conditions are different: SELECT NULL = TRUE, NULL IS TRUE; ?column? │ ?column? ══════════╪══════════ │ f (1 row) The first result is NULL. Yours, Laurenz Albe -- Cybertec | https://www.cybertec-postgresql.com
Laurenz Albe <laurenz.albe@cybertec.at> writes: > On Sun, 2022-09-18 at 18:24 +0300, Евгений Плискин wrote: >> So why not use this index for this query? > Because the conditions are different: > SELECT NULL = TRUE, NULL IS TRUE; > ?column? │ ?column? > ══════════╪══════════ > │ f > (1 row) > The first result is NULL. Nonetheless, indxpath.c knows it can transform between "bool = true" and "bool IS TRUE" for the purpose of making an index search qualification, so it seems a bit odd that we fail to do the equivalent transformation when attempting to prove an index predicate. It'd be possible to improve this by adding some proof rules to predicate_implied_by_simple_clause: I think both "x => x IS TRUE" and the converse would be valid per the proof requirements, and if you wanted to gild the lily it'd likely be possible to handle some related cases like "x => x IS NOT FALSE". Whether it's worth the cycles isn't too clear to me, but we could argue about that if somebody submitted a patch. regards, tom lane