CREATE TABLE public.article (
    id uuid NOT NULL,
    tags _text NULL,
    CONSTRAINT article_pkey PRIMARY KEY (id)

I have the requirement to load articles that have a tag which is in a large list (at least 300 tags) from the DB:

SELECT * FROM public.cms_article where tags @> ARRAY['my_long_extra_long_tag_1', '...', '...', 'my_long_extra_long_tag_300'];

But I have a performance issue. Does anyone have experience with such requirement? Which index would be the best? The number of articles is less than 5000.

  • Did you try a GIN index on the array column? Please include EXPLAIN (ANALYZE, BUFFERS) output. Nov 8 at 13:15
  • thanks, GIN index helps Nov 10 at 9:18
  • another question: is there reasonable number of maximum tags: I want to create composite tags. 1000, 10000? Nov 10 at 9:21
  • Experiment. It will get slower and slower. Where is your pain point? Nov 10 at 9:28
  • I am currently working on a concept how to search for articles with automatically composed tags. and here the question arises how many tags I can use. if I have for instance 5 Tags and combine them, I have more than 300 hundred permutations. for 7 tagsmore than 13000. which tag number would still practicable to handle Nov 13 at 10:14

1 Answer 1


articles that have a tag which is in a large list

This should translate to the overlap operator &&, not the containment operator @>:

... WHERE tags && ARRAY['my_extra_long_tag_1', '...', 'my_extra_long_tag_300'];

Support that with a GIN index like:

CREATE INDEX ON public.article USING gin (tags);


  • Thanks,I need to take a closer look at the link with they overlap operator. :-) Nov 10 at 9:24

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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