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 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
      b.bookId,
      1 as lvl,
      p.prequelID,
      CONCAT(b.title)   AS series
    FROM
      books      b
    LEFT JOIN
      prequels   p
        ON p.bookid = b.bookid
    WHERE NOT EXISTS (
      SELECT 1
      FROM Prequels p
      WHERE p.prequelID = b.bookId
    )
    
    UNION ALL
    
    SELECT
      b.bookid,
      lvl+1,
      p.prequelID,
      CONCAT(b.title, ' -> ', s.series)
    FROM
      series     s
    INNER JOIN
      books      b
        ON b.bookid = s.prequelid
    LEFT JOIN
      prequels   p
        ON p.bookid = b.bookid
)
SELECT
  s.series
FROM


series


A Game of Thrones -> A Clash of Kings -> A Storm of Swords -> A Feast for Crows -> A Dance with Dragons