2

We have a table containing around 500k rows. The database table is supposed to grow to million of records.

This is how the table looks like:

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 these are some of the queries we need to perform efficiently:

SELECT  "public"."influencers".*
FROM "public"."influencers"
WHERE (ig->'follower_count' IS NOT NULL)
ORDER BY (ig->'follower_count') DESC
LIMIT 9
OFFSET 0

SELECT *
FROM "public"."influencers"
WHERE (ig->'follower_count' >= '5000')
LIMIT 9 

SELECT SUM(CAST(ig ->> 'follower_count' AS integer))
FROM "public"."influencers"
WHERE (ig->'follower_count' >= '5000')
  AND (ig->'follower_count' <= '10000')
  AND (ig->'follower_count' IS NOT NULL)

ig -> follower_count are numeric values.

I read that GIN indexes are mainly intended for searching through composite items (text) so I'm guessing the best index to use would be a BTREE. Would that be correct?

  • I think a GIN index could work, but are all your queries really just filtering by follower_count? If so, I would first try doing a regular B-Tree on ig->>'follower_count' and see how that does. – CalZ May 17 '17 at 12:12
2

GIN indexes do not work with any jsonb operators EXCEPT ? ?& ?| @>. Clearly, your use of comparison operators >= and <= are not on that list. And all of those operators that it could help are also not on the list (meaning the index won't do anything) afaik.

That means needing indexed comparison operators, you'll need a btree.

CREATE INDEX ON public.influencers ((ig->'follower_count'));

All of that said, I think this is silly. Just denormalize and put follower_count on the actual table and enjoy the performance benefits.

  • Thanks Evan. However, do you mean taking out the field and putting it at the same level of 'ig'? What would it be the difference between that and having it indexed inside a jsonb? (in terms of performance). – borjagvo May 17 '17 at 15:47
  • jsonb stores internally as an inflated numeric type. even if you index it as an int, afaik it's getting stored as a super-fat numeric type on the row. And, pulling down that requires you to CAST(AS int) which you would not otherwise need to do. – Evan Carroll May 17 '17 at 15:53
  • If I understand correctly, then you just would recommend store text or boolean inside a jsonb field? What are the disadvantages of storing it as numeric? How much does that impact on the performance? I guess is a function of the dataset size... – borjagvo May 17 '17 at 15:59
  • 1
    No, it's far better than text. At least you can run a comparison operator on it. It's just far worse than int. Generally, my rule of thumb for jsonb is to use it only on things you do not query on, that need to be extended and would otherwise result in an EAV table. You're querying on jsonb and trying to impose a schema. I don't believe that's what it's good at. Ideally, no constraints, and no b-trees on jsonb. If they're there in my work it's because of an oversight in design, not by intent. – Evan Carroll May 17 '17 at 16:03

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.