Postgres 16

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?.
-- Single table

CREATE TABLE employees (
    id integer NOT NULL PRIMARY KEY,
    name text NOT NULL,
    manager_id integer REFERENCES employees(id)
);

INSERT INTO employees VALUES (0, 'Big Boss', NULL);
INSERT INTO employees VALUES (1, 'Marc', 0);
INSERT INTO employees VALUES (2, 'Jeanne', 0);
INSERT INTO employees VALUES (3, 'Pascale', 0);
INSERT INTO employees VALUES (4, 'Rachid', 2);
INSERT INTO employees VALUES (5, 'Elizabeth', 4);


CREATE TABLE



INSERT 0 1



INSERT 0 1



INSERT 0 1



INSERT 0 1



INSERT 0 1



INSERT 0 1


WITH RECURSIVE subordinates(id, name, manager_id, breadcrumbs) AS
      (SELECT e0.id
            , e0.name
            , e0.manager_id
            , e0.name
       FROM employees AS e0
       WHERE id = 2
 
       UNION ALL
 
       SELECT e0.id
            , e0.name
            , e0.manager_id
            , concat_ws(' > ', s1.breadcrumbs, e0.name)
       FROM employees AS e0
            INNER JOIN subordinates AS s1 ON s1.id = e0.manager_id)
 
   SELECT *
   FROM subordinates
   ;


id
name
manager_id
breadcrumbs


2
Jeanne
0
Jeanne

4
Rachid
2
Jeanne > Rachid

5
Elizabeth
4
Jeanne > Rachid > Elizabeth



SELECT 3


WITH RECURSIVE managers(id, name, manager_id, breadcrumbs) AS
      (SELECT e0.id
            , e0.name
            , e0.manager_id
            , e0.name 
       FROM employees AS e0
       WHERE e0.id = 5
 
       UNION ALL
 
       SELECT e0.id
            , e0.name
            , e0.manager_id
            , concat_ws(' > ', m1.breadcrumbs, e0.name)
       FROM employees AS e0
            INNER JOIN managers AS m1 ON m1.manager_id = e0.id)
 
   SELECT *
   FROM managers;


id
name
manager_id
breadcrumbs


5
Elizabeth
4
Elizabeth

4
Rachid
2
Elizabeth > Rachid

2
Jeanne
0
Elizabeth > Rachid > Jeanne

0
Big Boss
null
Elizabeth > Rachid > Jeanne > Big Boss



SELECT 4


-- Multi-table

CREATE TABLE employees2 (
    id integer NOT NULL PRIMARY KEY,
    name text NOT NULL
);


CREATE TABLE employees_managers (
    id integer NOT NULL PRIMARY KEY,
    employee_id integer NOT NULL REFERENCES employees2(id),
    manager_id integer NOT NULL REFERENCES employees2(id)
);

INSERT INTO employees2 VALUES (0, 'Big Boss');
INSERT INTO employees2 VALUES (1, 'Marc');
INSERT INTO employees2 VALUES (2, 'Jeanne');
INSERT INTO employees2 VALUES (3, 'Pascale');
INSERT INTO employees2 VALUES (4, 'Rachid');
INSERT INTO employees2 VALUES (5, 'Elizabeth');

INSERT INTO employees_managers VALUES (0, 1, 0);
INSERT INTO employees_managers VALUES (1, 2, 0);
INSERT INTO employees_managers VALUES (2, 3, 0);
INSERT INTO employees_managers VALUES (3, 4, 2);
INSERT INTO employees_managers VALUES (4, 5, 4);

CREATE TABLE



CREATE TABLE



INSERT 0 1



INSERT 0 1



INSERT 0 1



INSERT 0 1



INSERT 0 1



INSERT 0 1



INSERT 0 1



INSERT 0 1



INSERT 0 1



INSERT 0 1



INSERT 0 1


WITH RECURSIVE subordinates2(  employee_id, name, manager_id, breadcrumbs) AS
      (SELECT e0.id AS employee_id
            , e0.name
            , em1.manager_id
            , e0.name
       FROM employees2 AS e0
            INNER JOIN employees_managers AS em1 ON e0.id = em1.employee_id
       WHERE e0.id = 2
 
       UNION ALL
 
       SELECT e0.id
            , e0.name
            , em1.manager_id
            , concat_ws(' > ', s2.breadcrumbs, e0.name)
       FROM employees2 AS e0
            INNER JOIN employees_managers AS em1 ON e0.id = em1.employee_id
            INNER JOIN subordinates2 AS s2 ON s2.employee_id = em1.manager_id)
 
    CYCLE employee_id SET is_cycle USING path
 
   SELECT s2.employee_id
        , s2.name
        , s2.manager_id
        , s2.breadcrumbs
   FROM subordinates2 AS s2
   ;


employee_id
name
manager_id
breadcrumbs


2
Jeanne
0
Jeanne

4
Rachid
2
Jeanne > Rachid

5
Elizabeth
4
Jeanne > Rachid > Elizabeth



SELECT 3


 WITH RECURSIVE managers2(  employee_id, name, manager_id, breadcrumbs) AS
      (SELECT e0.id AS employee_id
            , e0.name
            , em1.manager_id
            , e0.name
       FROM employees2 AS e0
            INNER JOIN employees_managers AS em1 ON e0.id = em1.employee_id
       WHERE e0.id = 5
 
       UNION ALL
 
       SELECT e0.id AS employee_id
            , e0.name
            , em1.manager_id
            , concat_ws(' > ', m2.breadcrumbs, e0.name)
       FROM employees2 AS e0
            INNER JOIN employees_managers AS em1 ON e0.id = em1.employee_id
            INNER JOIN managers2 AS m2 ON em1.employee_id = m2.manager_id)
 
    CYCLE employee_id SET is_cycle USING path
 
   SELECT m2.employee_id
        , m2.name
        , m2.manager_id
        , m2.breadcrumbs
   FROM managers2 AS m2
 
   UNION ALL
 
   SELECT e0.id AS employee_id
        , e0.name
        , NULL
        , concat_ws(' > ', m2.breadcrumbs, e0.name)
   FROM employees2 AS e0
        LEFT JOIN employees_managers AS em1 ON e0.id = em1.employee_id
        INNER JOIN managers2 as m2 on m2.manager_id = e0.id
   WHERE em1.manager_id IS NULL;


employee_id
name
manager_id
breadcrumbs


5
Elizabeth
4
Elizabeth

4
Rachid
2
Elizabeth > Rachid

2
Jeanne
0
Elizabeth > Rachid > Jeanne

0
Big Boss
null
Elizabeth > Rachid > Jeanne > Big Boss



SELECT 4
id	name	manager_id	breadcrumbs
2	Jeanne	0	Jeanne
4	Rachid	2	Jeanne > Rachid
5	Elizabeth	4	Jeanne > Rachid > Elizabeth