Performance issues with @> operator in postgres 14

Question

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.

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

See:

• Check if any of a given array of values are present in a Postgres array