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

Поиск
Список
Период
Сортировка
От David G. Johnston
Тема Re: BUG #17618: unnecessary filter column <> text even after adding index
Дата
Msg-id CAKFQuwaWi8MLBWazkXwM1gp6_e2G7LoNee_Za8ZKxfo-1K+2kA@mail.gmail.com
обсуждение исходный текст
Ответ на BUG #17618: unnecessary filter column <> text even after adding index  (PG Bug reporting form <noreply@postgresql.org>)
Список pgsql-bugs
Дерево обсуждения
On Mon, Sep 19, 2022 at 8:15 AM PG Bug reporting form <noreply@postgresql.org> wrote:

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'

Your index contains none of the fields in the original table so the system can never answer your inquiry using only the index.

You may find this to be informative:

https://www.postgresql.org/docs/devel/indexes-index-only-scans.html

Usually on a "status" field doing a few partial indexes gets you the best result.  The more statuses you need to be concerned about the more likely just scanning the table is going to win out in performance.  But if you do only care about a few the smaller index size will be of benefit to keep them in memory.  A covering index may be of use as well though for rapidly changing statuses tuple visibility is going to be a challenge.  In short, you seem to be providing a non-real situation and asking for advice that is situational in nature.

David J.

В списке pgsql-bugs по дате отправления:

Предыдущее
От: Sindy Senorita
Дата:
Сообщение: Re: BUG #17618: unnecessary filter column <> text even after adding index
Следующее
От: eponymous alias
Дата:
Сообщение: apparent loss of database access permissions