Skip to main content
Database Administrators

Return to Answer

I believe adding follower_count on the actual table is normalizing the data, not denormalizing it.
Source Link
András Váczi
András Váczi
  • 31.6k
  • 13
  • 101
  • 149

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 normalize and put follower_count as a proper column on the actual table and enjoy the performance benefits.

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 normalize and put follower_count as a column on the actual table and enjoy the performance benefits.

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 put follower_count as a proper column on the actual table and enjoy the performance benefits.

I believe adding follower_count on the actual table is normalizing the data, not denormalizing it.
Source Link
edit approved Oct 1, 2019 at 16:08
Bwvolleyball
edit approved Oct 1, 2019 at 16:08
Bwvolleyball
  • 103
  • 4

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 denormalizenormalize and put follower_count as a column on the actual table and enjoy the performance benefits.

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.

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 normalize and put follower_count as a column on the actual table and enjoy the performance benefits.

Source Link
Evan Carroll
Evan Carroll
  • 64.7k
  • 49
  • 251
  • 496

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.