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

On Mon, Sep 19, 2022 at 8:15 AM PG Bug reporting form <
noreply(at)postgresql(dot)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.