BUG #17618: unnecessary filter column <> text even after adding index
От | PG Bug reporting form |
---|---|
Тема | BUG #17618: unnecessary filter column <> text even after adding index |
Дата | |
Msg-id | 17618-7a2240bfaa7e84ae@postgresql.org обсуждение исходный текст |
Ответы |
Re: BUG #17618: unnecessary filter column <> text even after adding index
Re: BUG #17618: unnecessary filter column <> text even after adding index |
Список | pgsql-bugs |
The following bug has been logged on the website: Bug reference: 17618 Logged by: Sindy Senorita Email address: sindysenorita@gmail.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
В списке pgsql-bugs по дате отправления: