Postgres 13

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 IF NOT EXISTS package (
  id SERIAL PRIMARY KEY,
  name TEXT NOT NULL UNIQUE
);

CREATE TABLE IF NOT EXISTS version (
  id SERIAL PRIMARY KEY,
  package_id INT NOT NULL REFERENCES package,
  version TEXT NOT NULL,
  
  UNIQUE(package_id, version)
);

CREATE TABLE IF NOT EXISTS dependency (
  version_id INT NOT NULL REFERENCES version,
  dependency_id INT NOT NULL REFERENCES version
);

INSERT INTO package (name) VALUES
  ('a'),('b'),('c'),('d'),('e'),('f'),('g'),('h'),('i'),('j');

INSERT INTO version (package_id, version) VALUES
  (1, '1.0.0'), -- 1
  (1, '2.0.0'), -- 2
  (1, '3.0.0'), -- 3
  (2, '1.0.0'), -- 4
  (2, '2.0.0'), -- 5
  (2, '3.0.0'), -- 6
  (3, '1.0.0'), -- 7
  (3, '2.0.0'), -- 8
  (3, '3.0.0'), -- 9
  (4, '1.0.0'), -- 10
  (4, '2.0.0'), -- 11
  (4, '3.0.0'), -- 12
  (5, '1.0.0'), -- 13
  (5, '2.0.0'), -- 14
  (5, '3.0.0'), -- 15
  (6, '1.0.0'), -- 16
  (6, '2.0.0'), -- 17
  (6, '3.0.0'), -- 18
  (7, '1.0.0'), -- 19
  (7, '2.0.0'), -- 20
  (7, '3.0.0'), -- 21
  (8, '1.0.0'), -- 22
  (8, '2.0.0'), -- 23
  (8, '3.0.0'), -- 24
  (9, '1.0.0'), -- 25
  (9, '2.0.0'), -- 26
  (9, '3.0.0'), -- 27
  (10, '1.0.0'), -- 28
  (10, '2.0.0'), -- 29
  (10, '3.0.0'); -- 30

INSERT INTO dependency (version_id, dependency_id) VALUES
  (1, 4), -- package 1, version 1.0.0 depends on package 2, version 1.0.0
  (1, 22), -- package 1, version 1.0.0 depends on package 8, version 1.0.0
  (4, 7), -- package 2, version 1.0.0 depends on package 3, version 1.0.0
  (22, 25), -- package 8, version 1.0.0 depends on package 9, version 1.0.0
  (25, 28); -- package 9, version 1.0.0 depends on package 10, version 1.0.0

CREATE TABLE

CREATE TABLE

CREATE TABLE

INSERT 0 10

INSERT 0 30

INSERT 0 5

package_id	name	version	l
1	a	1.0.0	1
2	b	1.0.0	2
3	c	1.0.0	3
8	h	1.0.0	2
9	i	1.0.0	3
10	j	1.0.0	4

SELECT 6