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_post_location'::text) -> 'administrative_area_level_3_id'::text))
TABLESPACE pg_default;
CREATE INDEX ig_last_post_location_country
ON public.influencers USING btree
(((ig -> 'last_post_location'::text) -> 'country_id'::text))
TABLESPACE pg_default;
CREATE INDEX ig_last_post_location_locality
ON public.influencers USING btree
(((ig -> 'last_post_location'::text) -> 'locality_id'::text))
TABLESPACE pg_default;
CREATE UNIQUE INDEX ig_unique_id
ON public.influencers USING btree
((ig ->> 'id'::text) COLLATE pg_catalog."default")
TABLESPACE pg_default;
CREATE TRIGGER influencers_search_vector_update
BEFORE INSERT OR UPDATE
ON public.influencers
FOR EACH ROW
EXECUTE PROCEDURE tsvector_update_trigger(s, e, a, r, c, h, _, v, e, c, t, o, r, \, 0, 0, 0, p, g, _, c, a, t, a, l, o, g, ., s, i, m, p, l, e, \, 0, 0, 0, s, e, a, r, c, h, _, f, i, e, l, d, \, 0, 0, 0);
CREATE TRIGGER update_search_field_influencers_on_insert
BEFORE INSERT
ON public.influencers
FOR EACH ROW
EXECUTE PROCEDURE update_influencer_search_field();
CREATE TRIGGER update_search_field_influencers_on_update
BEFORE UPDATE
ON public.influencers
FOR EACH ROW
EXECUTE PROCEDURE update_influencer_search_field();
What's wrong?
Best Answer
Index
idx_btree_ig_id
will be used only if you writeWHERE
part likeig ->> 'id' = '"4878142508"'
. (I assume thatig
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"}'