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 authors (
id INT PRIMARY KEY,
fullname VARCHAR(255)
);
CREATE TABLE books (
id INT PRIMARY KEY,
title VARCHAR(255),
authorid INT REFERENCES authors(id)
);
INSERT INTO authors(id,fullname)
VALUES
(1, 'Jane Austen'),
(2, 'Mary Wollstonecraft Shelley'),
(3, 'Lewis Carroll'),
(4, 'Mark Twain'),
(5, 'Jules Verne')
;
INSERT INTO books(id,title,authorid)
VALUES
(1, 'Roughing It', 4),
(2, 'Alice’s Adventures in Wonderland', 3),
(3, 'Frankenstein', 2),
(4, 'The Time Machine',NULL),
(5, 'Sense and Sensibility', 1),
(6, 'The Hunting of the Snark', 3),
(7, 'The Innocents Abroad', 4),
(8, 'Pride and Prejudice', 1)
;
CREATE TABLE
CREATE TABLE
INSERT 0 5
INSERT 0 8
SELECT *
FROM books AS b FULL JOIN authors AS a ON b.authorid=a.id;
| id | title | authorid | id | fullname |
|---|---|---|---|---|
| 1 | Roughing It | 4 | 4 | Mark Twain |
| 2 | Alice’s Adventures in Wonderland | 3 | 3 | Lewis Carroll |
| 3 | Frankenstein | 2 | 2 | Mary Wollstonecraft Shelley |
| 4 | The Time Machine | null | null | null |
| 5 | Sense and Sensibility | 1 | 1 | Jane Austen |
| 6 | The Hunting of the Snark | 3 | 3 | Lewis Carroll |
| 7 | The Innocents Abroad | 4 | 4 | Mark Twain |
| 8 | Pride and Prejudice | 1 | 1 | Jane Austen |
| null | null | null | 5 | Jules Verne |
SELECT 9