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

From: PG Bug reporting form <noreply(at)postgresql(dot)org>
To: pgsql-bugs(at)lists(dot)postgresql(dot)org
Cc: sindysenorita(at)gmail(dot)com
Subject: BUG #17618: unnecessary filter column <> text even after adding index
Date: 2022-09-19 14:28:23
Message-ID: 17618-7a2240bfaa7e84ae@postgresql.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

The following bug has been logged on the website:

Bug reference: 17618
Logged by: Sindy Senorita
Email address: sindysenorita(at)gmail(dot)com
PostgreSQL version: 13.7
Operating system: Ubuntu
Description:

Hi, I'm not sure if this is a bug or feature, but definitely not what I've
expected

So I have a table with "status" column which can contains 'valid',
'invalid', 'pending', 'unknown'.
A very simple table

CREATE TABLE public.test (
id varchar NOT NULL,
status varchar NOT NULL,
CONSTRAINT test__pkey PRIMARY KEY (id)
)
CREATE INDEX pending_test_4 ON public.test USING btree ((((status)::text <>
'invalid'::text)));

notice that I've created an index to guide statuses that is not 'invalid
my query is:
SELECT * FROM test WHERE status != 'invalid'

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
|

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
My questions are:
1. Is this a bug? or intended feature by design? If it is by design, I'd be
very happy to learn the rationale behind it.
2. Is there any way to skip/avoid the additional bitmap scan?
3. Could there be a better solution for my query. Suppose that the variants
of the status is unknown so query SELECT .. WHERE STATUS IN (all status
beside 'invalid') is not possible

Many thanks!
Sindy

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message Tom Lane 2022-09-19 15:24:12 Re: BUG #17618: unnecessary filter column <> text even after adding index
Previous Message Tom Lane 2022-09-17 14:51:27 Re: error: #error PostgreSQL does not have native spinlock support on this platform. error: unknown type name ‘slock_t’