Query not using index with jsonb - Postgres

For some reason it seems as Postgres is not using the index we created. This is the query I'm testing:

SELECT  "public"."influencers".* 
FROM "public"."influencers" 
WHERE (ig -> 'id' @> '"4878142508"') 
LIMIT 1

And after running EXPLAIN:

->  Seq Scan on influencers  (cost=0.00..32800.14 rows=216 width=1110)

Which indicates (as I see it) that no index is being used.

This is our database and indexes created:

CREATE TABLE public.influencers
(
    id integer NOT NULL DEFAULT nextval('influencers_id_seq'::regclass),
    location jsonb,
    gender text COLLATE pg_catalog."default",
    birthdate timestamp without time zone,
    ig jsonb,
    contact_info jsonb,
    created_at timestamp without time zone DEFAULT now(),
    updated_at timestamp without time zone DEFAULT now(),
    categories text[] COLLATE pg_catalog."default",
    search_field text COLLATE pg_catalog."default",
    search_vector tsvector,
    ig_updated_at timestamp without time zone,
    CONSTRAINT influencers_pkey PRIMARY KEY (id),
    CONSTRAINT ig_id_must_exist CHECK (ig ? 'id'::text),
    CONSTRAINT ig_username_must_exist CHECK (ig ? 'username'::text)
)

And the indexes created (relevant index first - the one I think it should be used):

```
CREATE INDEX idx_btree_ig_id
ON public.influencers USING btree
((ig ->> 'id'::text) COLLATE pg_catalog."default")
TABLESPACE pg_default;

CREATE INDEX ads_search_idx
ON public.influencers USING gin
(search_vector)
TABLESPACE pg_default;

CREATE INDEX ig_last_post_location_aal1
ON public.influencers USING btree
(((ig -> 'last_post_location'::text) -> 'administrative_area_level_1_id'::text))
TABLESPACE pg_default;

CREATE INDEX ig_last_post_location_aal2
ON public.influencers USING btree
(((ig -> 'last_post_location'::text) -> 'administrative_area_level_2_id'::text))
TABLESPACE pg_default;

CREATE INDEX ig_last_post_location_aal3
ON public.influencers USING btree
(((ig -> 'last_

Index idx_btree_ig_id will be used only if you write WHERE part like ig ->> 'id' = '"4878142508"'. (I assume that ig has structure like {"id": "4878142508", ...})

If you want to use operator @> you need to create gin index on that column, then you can use this index like so: ig @> '{"id":"4878142508"}'

CREATE INDEX idx_gin_ig_id ON public.influencers USING gin (ig jsonb_path_ops);


StackExchange Database Administrators Q#173584, answer score: 5