Postgres 11

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

-- schema
CREATE TABLE article (
  id bigint PRIMARY KEY,
  title text NOT NULL,
  score int NOT NULL
);
CREATE TABLE tag (
  id bigint PRIMARY KEY,
  name text NOT NULL
);
CREATE TABLE article_tag (
  article_id bigint NOT NULL REFERENCES article (id),
  tag_id bigint NOT NULL REFERENCES tag (id),
  PRIMARY KEY (article_id, tag_id)
);
CREATE INDEX ON article (score);

-- sample data
INSERT INTO article (id, title, score) VALUES
  (1, 'Sample Article A', 200),
  (2, 'Sample Article B', 350);
INSERT INTO tag (id, name) VALUES
  (1, 'Technology'),
  (2, 'News'),
  (3, 'Startups'),
  (4, 'Programming'),
  (5, 'Investing');
INSERT INTO article_tag (article_id, tag_id) VALUES
  (1, 1),
  (1, 2),
  (1, 3),
  (1, 4),
  (2, 1),
  (2, 2),
  (2, 5);

CREATE TABLE



CREATE TABLE



CREATE TABLE



CREATE INDEX



INSERT 0 2



INSERT 0 5



INSERT 0 7


-- Faster LATERAL
SELECT a.id, a.title, a.score, tags.names
FROM   article a
LEFT   JOIN LATERAL (
   SELECT ARRAY (
      SELECT t.name
      FROM   article_tag a_t 
      JOIN   tag t ON t.id = a_t.tag_id
      WHERE  a_t.article_id = a.id
      )
  ) AS tags(names) ON true
ORDER  BY a.score DESC
LIMIT  10;


id
title
score
names


2
Sample Article B
350
{Technology,News,Investing}

1
Sample Article A
200
{Technology,News,Startups,Programming}



SELECT 2


-- Even faster correlated subquery
SELECT a.id, a.title, a.score
     , ARRAY (
         SELECT t.name
         FROM   article_tag a_t 
         JOIN   tag t ON t.id = a_t.tag_id
         WHERE  a_t.article_id = a.id
      ) AS names
FROM   article a
ORDER  BY a.score DESC
LIMIT  10;


id
title
score
names


2
Sample Article B
350
{Technology,News,Investing}

1
Sample Article A
200
{Technology,News,Startups,Programming}



SELECT 2

id	title	score	names
2	Sample Article B	350	{Technology,News,Investing}
1	Sample Article A	200	{Technology,News,Startups,Programming}