From: Tom Lane <tgl@sss.pgh.pa.us>
To: sindysenorita@gmail.com
Cc: pgsql-bugs@lists.postgresql.org
Subject: Re: BUG #17618: unnecessary filter column <> text even after adding index
Date: Mon, 19 Sep 2022 11:24:12 -0400
Message-ID: <349136.1663601052@sss.pgh.pa.us> (raw)
In-Reply-To: <17618-7a2240bfaa7e84ae@postgresql.org>
References: <17618-7a2240bfaa7e84ae@postgresql.org>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-bugs@postgresql.org
Cc: tgl@sss.pgh.pa.us, sindysenorita@gmail.com, pgsql-bugs@lists.postgresql.org
Subject: Re: BUG #17618: unnecessary filter column <> text even after adding index
In-Reply-To: <349136.1663601052@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox