Postgres Minimum of two overlaps in array

Question

I need to search a large table (over 100million records) using quite a complex set of rules.

At present I have a table that looks like this

CREATE TABLE equipment (
     id serial primary key,
     description varchar,
     code integer,
     tags varchar[]
)

And I need to query based on

1) A specific list of codes (WHERE code IN (1,4,5,8)

2) An array of tags that are NOT in the tags array

3) An array of tags that are IN the tags array but at least two need to be present

SELECT * FROM equipment WHERE 
    code IN (1,3,6,7,8) 
    AND NOT ('{sports, outdoors, camping}' && tags)
    AND ('{tennis, squash, badminton, volleyball}' && tags)

But I do not know how to make that at least two of the values (tennis, squash, badminton, volleyball) overlap the tags array.

I am using Postgres 10.6

user330315 · Accepted Answer · 2019-04-17 12:43:10Z

1

I think you can only do that using by unnesting the tags and counting them:

SELECT e.*
FROM equipment e
WHERE code IN (1,3,6,7,8) 
  AND NOT ('{sports, outdoors, camping}' && tags)
  AND EXISTS (SELECT 1
              FROM unnest(e.tags) x(tg)
              WHERE x.tg = any ('{tennis, squash, badminton, volleyball}')
              HAVING count(*) >= 2);

If you have an index on the tags column then AND ('{tennis, squash, badminton, volleyball}' && tags) keeping your current condition:

AND ('{tennis, squash, badminton, volleyball}' && tags)

might improve the performance, as that should prevent unnesting the tags for rows where no match was found to begin with.

answered Apr 17, 2019 at 12:43

user330315

Sign up to request clarification or add additional context in comments.

1 Comment

tjmgis Over a year ago

Awesome, thanks v much. And yes i did have a GIN index on the tags so that extra AND helped

Collectives™ on Stack Overflow

Postgres Minimum of two overlaps in array

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related