3

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?

2
  • How many rows does the table influencers hold?
    – Marco
    May 15, 2017 at 11:25
  • @Marco around 500k
    – borjagvo
    May 18, 2017 at 10:49

1 Answer 1

5

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);
0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.