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

I see, quick google search takes me to BitmapHeapNext implementation here
https://doxygen.postgresql.org/nodeBitmapHeapscan_8c_source.html#l00072. I
hope this is what you mean
Noted. Thanks for the explanation

Cheers

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

> PG Bug reporting form <noreply@postgresql.org> writes:
> > When I run explain analyze on that with SET enable_seqscan = off, I got
> > QUERY PLAN
>
> >                                            |
> >
> ------------------------------------------------------------------------------------------------------------------------+
> > Bitmap Heap Scan on test  (cost=4.62..8.37 rows=120 width=160) (actual
> > time=0.088..0.134 rows=117 loops=1)              |
> >   Filter: ((status)::text <> 'invalid'::text)
>
> >                                            |
> >   Heap Blocks: exact=3
>
> >                                            |
> >   ->  Bitmap Index Scan on pending_test_4  (cost=0.00..4.59 rows=60
> width=0)
> > (actual time=0.073..0.073 rows=117 loops=1)|
> >         Index Cond: (((status)::text <> 'invalid'::text) = true)
>
> >                                            |
> > Planning Time: 0.222 ms
>
> >                                            |
> > Execution Time: 0.172 ms
>
> >                                            |
>
>
> This is exactly what is expected; it's not a bug.
>
> > 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.
>
> The "doubled cost" has nothing whatever to do with the filter condition;
> most of that is concerned with the number of disk pages touched.  It
> might help you to read
>
> https://www.postgresql.org/docs/current/using-explain.html
>
>                         regards, tom lane
>