Re: BUG #17618: unnecessary filter column <> text even after adding index - Mailing list pgsql-bugs

From Tom Lane
Subject Re: BUG #17618: unnecessary filter column <> text even after adding index
Date
Msg-id 349136.1663601052@sss.pgh.pa.us
Whole thread Raw
In response to BUG #17618: unnecessary filter column <> text even after adding index  (PG Bug reporting form <noreply@postgresql.org>)
Responses Re: BUG #17618: unnecessary filter column <> text even after adding index
Re: BUG #17618: unnecessary filter column <> text even after adding index
List pgsql-bugs
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



pgsql-bugs by date:

Previous
From: PG Bug reporting form
Date:
Subject: BUG #17618: unnecessary filter column <> text even after adding index
Next
From: Sindy Senorita
Date:
Subject: Re: BUG #17618: unnecessary filter column <> text even after adding index