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 PulledTexts (
Id serial PRIMARY KEY,
BaseText TEXT,
CleanText TEXT
);

CREATE TABLE UniqueWords (
Id serial PRIMARY KEY,
WordText TEXT
);

CREATE TABLE WordTexts (
Id serial PRIMARY KEY,
TextIdId INTEGER REFERENCES PulledTexts,
WordIdId INTEGER REFERENCES UniqueWords
);

CREATE INDEX IX_WordTexts_TextIdId ON WordTexts (TextIdId);
CREATE INDEX IX_WordTexts_WordIdId ON WordTexts (WordIdId);


INSERT INTO PulledTexts (Id, BaseText, CleanText) VALUES
(1, 'automate business audit', null),
(2, 'audit trial', null),
(3, 'trial', null),
(4, 'audit', null),
(5, 'fresh report', null),
(6, 'fresh audit', null),
(7, 'automate this script', null),
(8, 'im trying here', null),
(9, 'automate this business', null),
(10, 'lateral', null);

INSERT INTO UniqueWords (Id, WordText) VALUES
(1, 'trial'),
(2, 'audit'),
(3, 'creation'),
(4, 'business'),
(5, 'automate');

INSERT INTO WordTexts (Id, TextIdId, WordIdId) VALUES
(1, 1, 2),
(2, 1, 4),
(3, 1, 5),
(4, 2, 1),
(5, 3, 1),
(6, 4, 2),
(7, 6, 2),
(8, 7, 5),
(9, 9, 4),
(10, 9, 5),
(11, 2, 2);
10 rows affected

5 rows affected

11 rows affected

SELECT *
FROM   PulledTexts
WHERE  Id IN (
   SELECT w.TextIdId
   FROM   WordTexts w  -- AS can be omitted for table alias
   LEFT   JOIN UniqueWords u ON w.WordIdId = u.Id  -- LEFT JOIN might be necessary here
   WHERE  u.WordText = 'automate'
   OR     w.TextIdId IN (
      SELECT w.TextIdId  -- AS and1 -- column alias only documentation, not visible
      FROM   WordTexts w
      JOIN   UniqueWords u ON w.WordIdId = u.Id  -- LEFT JOIN misleading here
      WHERE  u.WordText = 'audit'

      INTERSECT
      SELECT w.TextIdId  -- AS and2  -- but don't omit AS for column alias
      FROM   WordTexts w
      JOIN   UniqueWords u ON w.WordIdId = u.Id
      WHERE  u.WordText = 'trial'
      )
   );


id
basetext
cleantext


1
automate business audit
null

2
audit trial
null

7
automate this script
null

9
automate this business
null


SELECT *
FROM   PulledTexts
WHERE  Id IN (
   SELECT w.TextIdId
   FROM   WordTexts   w
   JOIN   UniqueWords u ON w.WordIdId = u.Id
   WHERE  u.WordText = 'automate'

   UNION
   SELECT w.TextIdId
   FROM   WordTexts w
   JOIN   UniqueWords u ON w.WordIdId = u.Id
   WHERE  u.WordText = 'audit'

   INTERSECT
   SELECT w.TextIdId
   FROM   WordTexts w
   JOIN   UniqueWords u ON w.WordIdId = u.Id
   WHERE  u.WordText = 'trial'
   );
   


id
basetext
cleantext


1
automate business audit
null

2
audit trial
null

7
automate this script
null

9
automate this business
null


SELECT *
FROM  (
   SELECT w.TextIdId AS Id
   FROM   WordTexts   w
   JOIN   UniqueWords u ON w.WordIdId = u.Id
   WHERE  u.WordText = 'automate'

   UNION
   SELECT w.TextIdId
   FROM   WordTexts w
   JOIN   UniqueWords u ON w.WordIdId = u.Id
   WHERE  u.WordText = 'audit'

   INTERSECT
   SELECT w.TextIdId
   FROM   WordTexts w
   JOIN   UniqueWords u ON w.WordIdId = u.Id
   WHERE  u.WordText = 'trial'
   ) w
JOIN   PulledTexts p USING (Id);


id
basetext
cleantext


1
automate business audit
null

2
audit trial
null

7
automate this script
null

9
automate this business
null


SELECT *
FROM  (
   SELECT w.TextIdId AS Id
   FROM   WordTexts   w
   JOIN   UniqueWords u ON w.WordIdId = u.Id
   WHERE  u.WordText = 'automate'

   UNION
   SELECT TextIdId
   FROM   WordTexts w1
   JOIN   WordTexts w2 USING (TextIdId)
   WHERE  w1.WordIdId = (SELECT Id FROM UniqueWords WHERE WordText = 'audit')
   AND    w2.WordIdId = (SELECT Id FROM UniqueWords WHERE WordText = 'trial')
   ) w
JOIN   PulledTexts p USING (Id);


id
basetext
cleantext


1
automate business audit
null

2
audit trial
null

7
automate this script
null

9
automate this business
null
id	basetext	cleantext
1	automate business audit	null
2	audit trial	null
7	automate this script	null
9	automate this business	null