Selecting primary from a mapping row SQL

Question

I have a table that references a bunch of articles, the table contains tags for those articles. Like this:

tag text
article_id bigint

I want to select all article_ids with a set of tags, say tag1, tag2, tag3 but the article could also have tag4, tag5 as well attached to it.

I know this will work:

SELECT article_id 
FROM tag WHERE tag='tag1' 
INTERSECT 
SELECT article_id 
FROM tag 
WHERE tag='tag2' 
INTERSECT 
SELECT article_id 
FROM tag 
WHERE tag='tag3'

And so will this:

SELECT article_id 
FROM tag 
WHERE tag IN ('tag1','tag2','tag3') 
GROUP BY article_id 
HAVING count(*) = 3

But I am not sure that that is the most efficient way to do this. I have also been playing with the below, but cannot get it to work currently.

SELECT array_agg(tag) as arr,
       article_id 
FROM tag 
GROUP BY article_id 
HAVING arr = {tag1,tag2,tag3}

This just seemed like a common issue that others would encounter, I was wondering if the INTERSECT is the most efficient query in this case. It is for PostgreSQL.

Answer 1

Personally I like the second option. But you should use PostgreSQL's tools, along with some test queries, to see which is the most efficient.

The reason the third doesn't work as written is because you need specify a sort order for array_agg(): how to make array_agg() work like group_concat() from mySQL

Answer 2

I assume you want more columns from article than just the article_id. Doesn't change the query style much, though.

What you are dealing with is called relational division. And there is quite a number of ways to skin this cat as can be seen under this related question - including advise on indexing and performance tests.

My personal favorites (and very likely among the fastest) are:

SELECT a.*
FROM   article a
JOIN   tag x USING (article_id)
JOIN   tag y USING (article_id)
JOIN   tag z USING (article_id)
WHERE  x.tag = 'tag1'
AND    y.tag = 'tag1'
AND    z.tag = 'tag3';

Or:

SELECT a.*
FROM   article a
WHERE  EXISTS (
   SELECT *
   FROM   tag x
   JOIN   tag y USING (article_id)
   JOIN   tag z USING (article_id)
   WHERE  x.article_id = a.article_id
   AND    x.tag = 'tag1'
   AND    y.tag = 'tag2'
   AND    z.tag = 'tag3'
   );

@David already explained why your third version cannot function. But you should never use it anyway because it cannot use indexes and will be slower by an order of magnitude than any of the other methods here.

Answer 3

In your last variant you are not interested in array equality. You are interested in array containment, HAVING arr contain all the three tags.

And array1 contains array2 is array1 @> array2 in PostgreSQL.

Still I expect Erwin's answer to win performance-wise thanks to indexing.