Suggest using boolean index with (bflag is true) condition for the query with (bflag = true) clause

From: Евгений Плискин <eugene(dot)pliskin(at)gmail(dot)com>
To: pgsql-general(at)lists(dot)postgresql(dot)org
Subject: Suggest using boolean index with (bflag is true) condition for the query with (bflag = true) clause
Date: 2022-09-18 15:24:35
Message-ID: 519561082.20220918182435@gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: 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 match the 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.

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Matthias Apitz 2022-09-18 16:36:39 Re: Mysterious performance degradation in exceptional cases
Previous Message Adrian Klaver 2022-09-18 14:47:32 Re: Mysterious performance degradation in exceptional cases