Re: BUG #17618: unnecessary filter column <> text even after adding index

On Mon, Sep 19, 2022 at 11:24 AM Tom Lane <tgl@sss.pgh.pa.us> wrote:

>
> > The plan has used the index condition just right, but it still perform
> > aditional bitmap heap scan just to filter for a clause that exactly match
> > the index. And worse, it double the query cost
>
> The filter condition is required because the bitmap produced by the index
> can be lossy, ie it might identify more rows than actually satisfy the
> condition.  BitmapHeapNext will only actually apply the condition if
> the index reports that that happened, so in practice for this sort of
> query the filter condition probably never gets rechecked.
>

You are describing a Recheck, but attributing its properties to a Filter.
I think that the filter condition always gets checked.  It does so if I
create a tattler function which raises a notice every time it is called and
then build an index on a Boolean expression over that function (but of
course that inevitably does change the code paths a bit).

I don't know about being a bug, but it is at least a mild mal-feature that
boolean index columns/expressions can't be dealt with better, and have to
be handed in a filter rather than in a recheck.

Cheers,

Jeff