Postgres 16

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

CREATE TABLE keyword (topic_id int, keyword text);
INSERT INTO keyword VALUES
  (1, 'basketball')
, (2, 'music')
, (3, 'food')
, (4, 'war')
RETURNING *;

CREATE TABLE post (post_id int, content text);
INSERT INTO post VALUES
  (1, 'A typhoon warning has been issued in southern Japan')
, (2, 'We are going to play neither basketball nor volleyball')
, (3, 'I am indulging in both the delightful music and delectable food')
, (4, 'That basketball player fouled again')
, (5, 'Basketballs stop wars!')                    -- added to illustrate diff!
RETURNING *;

CREATE TABLE




topic_id
keyword


1
basketball

2
music

3
food

4
war



INSERT 0 4



CREATE TABLE




post_id
content


1
A typhoon warning has been issued in southern Japan

2
We are going to play neither basketball nor volleyball

3
I am indulging in both the delightful music and delectable food

4
That basketball player fouled again

5
Basketballs stop wars!



INSERT 0 5


-- Naive query without index support
-- as simple and as fast as it gets
SELECT post_id, COALESCE(string_agg(topic_id::text, ',' ), 'Vague!') AS topic
FROM  (
   SELECT p.post_id, k.topic_id
   FROM   post p 
   LEFT   JOIN keyword k ON p.content ~* ('\m' || k.keyword || '\M')
   ORDER  BY p.post_id, k.topic_id
   ) sub
GROUP  BY post_id
ORDER  BY post_id;


post_id
topic


1
Vague!

2
1

3
2,3

4
1

5
Vague!



SELECT 5


-- Index support for non-trivial cardinalities
CREATE INDEX posts_content_fts_simple_idx ON post USING gin (to_tsvector('simple', content));

SELECT post_id, COALESCE(topics, 'Vague!') AS topics
FROM  (
   SELECT post_id, string_agg(topic_id::text, ',') AS topics
   FROM  (
      SELECT p.post_id, k.topic_id
      FROM   keyword k
      JOIN   post    p ON to_tsvector('simple', p.content) @@ to_tsquery('simple', k.keyword)
      ORDER  BY p.post_id, k.topic_id
      ) sub
   GROUP  BY post_id
   ) sub1
RIGHT  JOIN post p USING (post_id)
ORDER  BY post_id;

CREATE INDEX




post_id
topics


1
Vague!

2
1

3
2,3

4
1

5
Vague!



SELECT 5


-- The same with stemming for English language words
-- Note the matches for the added post 5!
CREATE INDEX posts_content_fts_en_idx ON post USING gin (to_tsvector('english', content));

SELECT post_id, COALESCE(topics, 'Vague!') AS topics
FROM  (
   SELECT post_id, string_agg(topic_id::text, ',') AS topics
   FROM  (
      SELECT p.post_id, k.topic_id
      FROM   keyword k
      JOIN   post    p ON to_tsvector('english', p.content) @@ to_tsquery('english', k.keyword)
      ORDER  BY p.post_id, k.topic_id
      ) sub
   GROUP  BY post_id
   ) sub1
RIGHT  JOIN post p USING (post_id)
ORDER  BY post_id;

CREATE INDEX




post_id
topics


1
Vague!

2
1

3
2,3

4
1

5
1,4



SELECT 5

post_id	content
1	A typhoon warning has been issued in southern Japan
2	We are going to play neither basketball nor volleyball
3	I am indulging in both the delightful music and delectable food
4	That basketball player fouled again
5	Basketballs stop wars!