Postgres 12

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 phrases ( id INT, collection TEXT );
INSERT INTO phrases VALUES
( 1 , 'apple tree, large orange   '),
( 2 , 'kiwi, apple tree           '), 
( 3 , 'large orange, melon        '),
( 4 , 'large orange, banana beach ');
4 rows affected

CREATE TABLE pattern ( id INT, words VARCHAR(32) );
INSERT INTO pattern VALUES
( 1 , 'apple tree'),
( 2 , 'large orange'),
( 3 , 'large orange');
3 rows affected

SELECT DISTINCT words, COUNT(DISTINCT phrases.id) cnt
FROM phrases
JOIN pattern ON POSITION(words IN collection) > 0
GROUP BY words;


words
cnt


apple tree
2

large orange
3