Suggest using boolean index with (bflag is true) condition for the query with (bflag = true) clause - Mailing list pgsql-general

From Евгений Плискин
Subject Suggest using boolean index with (bflag is true) condition for the query with (bflag = true) clause
Date
Msg-id 519561082.20220918182435@gmail.com
Whole thread Raw
Responses Re: Suggest using boolean index with (bflag is true) condition for the query with (bflag = true) clause
List pgsql-general
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.




pgsql-general by date:

Previous
From: Adrian Klaver
Date:
Subject: Re: Mysterious performance degradation in exceptional cases
Next
From: Matthias Apitz
Date:
Subject: Re: Mysterious performance degradation in exceptional cases