Postgres 14

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

Help with an interesting Postgres question: Why isn't an Index Only Scan used on a partition accessed via the parent table?.

CREATE TABLE tbl (id int, words text);
INSERT INTO tbl VALUES
  (1, 'The employee was fired today')
, (2, 'He likes chocolate a lot')
, (3, 'She eat chocolate today')
, (4, 'Car was stolen yesterday')
;

CREATE INDEX tbl_words_gin_idx ON tbl USING gin (string_to_array(words, ' '));
4 rows affected

SELECT *
FROM   tbl t
, LATERAL (
   SELECT string_agg(tag, ', ') AS tags, count(*) AS tag_count
   FROM  (
      SELECT unnest(string_to_array(words, ' ')) tag
      INTERSECT ALL
      SELECT unnest ('{today, likes, eat}'::text[])
      ) i
   ) ct
WHERE  string_to_array(t.words, ' ') && '{today, likes, eat}';


id
words
tags
tag_count


1
The employee was fired today
today
1

2
He likes chocolate a lot
likes
1

3
She eat chocolate today
eat, today
2


-- simpler with text[] in the table:
CREATE TABLE tbl1 (id int, words text[]);
INSERT INTO tbl1 VALUES
  (1, string_to_array('The employee was fired today', ' '))
, (2, string_to_array('He likes chocolate a lot'    , ' '))
, (3, string_to_array('She eat chocolate today'     , ' '))
, (4, string_to_array('Car was stolen yesterday'    , ' '))
;

CREATE INDEX tbl1_words_gin_idx ON tbl1 USING gin (words);
4 rows affected

SELECT *
FROM   tbl1 t
, LATERAL (
   SELECT string_agg(tag, ', ') AS tags, count(*) AS tag_count
   FROM  (
      SELECT unnest(words) tag
      INTERSECT ALL
      SELECT unnest ('{today, likes, eat}'::text[])
      ) i
   ) ct
WHERE  t.words && '{today, likes, eat}';


id
words
tags
tag_count


1
{The,employee,was,fired,today}
today
1

2
{He,likes,chocolate,a,lot}
likes
1

3
{She,eat,chocolate,today}
eat, today
2

id	words	tags	tag_count
1	The employee was fired today	today	1
2	He likes chocolate a lot	likes	1
3	She eat chocolate today	eat, today	2

id	words	tags	tag_count
1	{The,employee,was,fired,today}	today	1
2	{He,likes,chocolate,a,lot}	likes	1
3	{She,eat,chocolate,today}	eat, today	2