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 Books
(bookID integer PRIMARY KEY,
title varchar(100),
pages integer);
CREATE TABLE Prequels
(bookID INTEGER REFERENCES Books(bookID),
prequelID INTEGER REFERENCES Books(bookID),
PRIMARY KEY (bookID,prequelID));
INSERT INTO BOOKS (bookID,title,pages) VALUES
(80429,'A Game of Thrones',292),
(41121,'A Clash of Kings',160),
(29287,'A Storm of Swords',160),
(17696,'A Feast for Crows',292),
(3947,'A Dance with Dragons',101);
INSERT INTO Prequels (bookID,prequelID) VALUES
(41121,80429),
(29287,41121),
(17696,29287),
(3947,17696);
5 rows affected
4 rows affected
WITH RECURSIVE series AS (
SELECT CONCAT( a.title) as str, a.prequelID
FROM ( Prequels NATURAL JOIN Books) AS a
UNION
SELECT CONCAT(t.title, ' -> ', str) as str, t.prequelID
FROM (Books NATURAL JOIN Prequels) as t
INNER JOIN series AS s ON s.prequelID = t.bookID
)
SELECT s1.str as series
FROM series AS s1
LEFT JOIN series AS s2
ON s2.str ~ s1.str
AND s2.str <> s1.str
WHERE s2.str IS NULL
ORDER BY series;
| series |
|---|
| A Clash of Kings -> A Storm of Swords -> A Feast for Crows -> A Dance with Dragons |
SELECT 'A Clash of Kings -> A Storm of Swords -> A Feast for Crows -> A Dance with Dragons' ~ 'A Clash of Kings -> A Storm of Swords'
| ?column? |
|---|
| t |
SELECT title ~ ' $' FROM Books
| ?column? |
|---|
| f |
| f |
| f |
| f |
| f |