Postgres 15

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 emp (empno, ename,    job,        mgr,   hiredate,     sal, comm, deptno, email, other_info) as values
(7369, 'SMITH',  'CLERK',     7902, '1980-12-17',  800, NULL,   20,'SMITH@acme.com', '{"skills":["accounting"]}'),
(7499, 'ALLEN',  'SALESMAN',  7698, '1981-02-20', 1600,  300,   30,'ALLEN@acme.com', null),
(7521, 'WARD',   'SALESMAN',  7698, '1981-02-22', 1250,  500,   30,'WARD@compuserve.com', null),
(7566, 'JONES',  'MANAGER',   7839, '1981-04-02', 2975, NULL,   20,'JONES@gmail.com', null),
(7654, 'MARTIN', 'SALESMAN',  7698, '1981-09-28', 1250, 1400,   30,'MARTIN@acme.com', null),
(7698, 'BLAKE',  'MANAGER',   7839, '1981-05-01', 2850, NULL,   30,'BLAKE@hotmail.com', null),
(7782, 'CLARK',  'MANAGER',   7839, '1981-06-09', 2450, NULL,   10,'CLARK@acme.com', '{"skills":["C","C++","SQL"]}'),
(7788, 'SCOTT',  'ANALYST',   7566, '1982-12-09', 3000, NULL,   20,'SCOTT@acme.com', '{"cat":"tiger"}'),
(7839, 'KING',   'PRESIDENT', NULL, '1981-11-17', 5000, NULL,   10,'KING@aol.com', null),
(7844, 'TURNER', 'SALESMAN',  7698, '1981-09-08', 1500,    0,   30,'TURNER@acme.com', null),
(7876, 'ADAMS',  'CLERK',     7788, '1983-01-12', 1100, NULL,   20,'ADAMS@acme.org', null),
(7900, 'JAMES',  'CLERK',     7698, '1981-12-03',  950, NULL,   30,'JAMES@acme.org', null),
(7902, 'FORD',   'ANALYST',   7566, '1981-12-03', 3000, NULL,   20,'FORD@acme.com', '{"skills":["SQL","CQL"]}'),
(7934, 'MILLER', 'CLERK',     7782, '1982-01-23', 1300, NULL,   10,'MILLER@acme.com', null)
;

SELECT 14


ALTER TABLE emp ADD PRIMARY KEY (empno)
;
ALTER TABLE emp ADD FOREIGN KEY (mgr) REFERENCES emp (empno)
;

ALTER TABLE



ALTER TABLE


-- (1) query to find the root
select emp.empno,emp.ename,emp.mgr
from emp where mgr is null
;


empno
ename
mgr


7839
KING
null



SELECT 1


with
-- (2) define the root as as levelN (which is level 0 until we add recursion)
levelN as (
-- (1) query to find the root
select emp.empno,emp.ename,emp.mgr
from emp where mgr is null
) 
-- (2) query this level
select * from levelN
;


empno
ename
mgr


7839
KING
null



SELECT 1


with 
-- (2) define the root as as levelN which is level 0 until we add recursion
levelN as (
-- (1) query to find the root
select emp.empno,emp.ename,emp.mgr
from emp where mgr is null
) 
-- (3) join employees with levelN to get level N+1 (which is level 1 here)
select emp.empno,emp.ename,emp.mgr,mgr.ename mgr_name
from emp
join levelN mgr on mgr.empno=emp.mgr
;


empno
ename
mgr
mgr_name


7566
JONES
7839
KING

7698
BLAKE
7839
KING

7782
CLARK
7839
KING



SELECT 3


with 
-- (2) define the root as as levelN which is level0 until we add recursion
levelN as (
-- (1) query to find the root      --(4) add all columns
select emp.empno,emp.ename,emp.mgr,'' as mgr
from emp where mgr is null
) 
-- (4) concatenate level 0 on top of level l
select * from levelN  
union all
-- (3) join employees with level n to get level n+1 (which is level 1 here)
select emp.empno,emp.ename,emp.mgr,mgr.ename mgr_name
from emp
join levelN mgr on mgr.empno=emp.mgr
;


empno
ename
mgr
mgr


7839
KING
null


7566
JONES
7839
KING

7698
BLAKE
7839
KING

7782
CLARK
7839
KING



SELECT 4


-- (5) change to recursive 
with recursive
-- (2) define the root as as levelN which is level0 until we add recursion
levelN as (
-- (1) query to find the root      --(4) add all columns
select emp.empno,emp.ename,emp.mgr,'' as mgr
from emp where mgr is null
-- (5) move second branch (level 1) here
union all
-- (3) join employees with level n to get level n+1 (which is level 1 here)
select emp.empno,emp.ename,emp.mgr,mgr.ename mgr_name
from emp
join levelN mgr on mgr.empno=emp.mgr
) 
-- (4) concatenate level 0 on top of level l
select * from levelN  
;


empno
ename
mgr
mgr


7839
KING
null


7566
JONES
7839
KING

7698
BLAKE
7839
KING

7782
CLARK
7839
KING

7499
ALLEN
7698
BLAKE

7521
WARD
7698
BLAKE

7654
MARTIN
7698
BLAKE

7788
SCOTT
7566
JONES

7844
TURNER
7698
BLAKE

7900
JAMES
7698
BLAKE

7902
FORD
7566
JONES

7934
MILLER
7782
CLARK

7369
SMITH
7902
FORD

7876
ADAMS
7788
SCOTT



SELECT 14


explain
-- (5) change to recursive 
with recursive
-- (2) define the root as as levelN which is level0 until we add recursion
levelN as (
-- (1) query to find the root      --(4) add all columns
select emp.empno,emp.ename,emp.mgr,'' as mgr
from emp where mgr is null
-- (5) move second branch (level 1) here
union all
-- (3) join employees with level n to get level n+1 (which is level 1 here)
select emp.empno,emp.ename,emp.mgr,mgr.ename mgr_name
from emp
join levelN mgr on mgr.empno=emp.mgr
) 
-- (4) concatenate level 0 on top of level l
select * from levelN  
;


QUERY PLAN


CTE Scan on leveln  (cost=19.34..21.36 rows=101 width=72)

  CTE leveln

    ->  Recursive Union  (cost=0.00..19.34 rows=101 width=72)

          ->  Seq Scan on emp  (cost=0.00..1.14 rows=1 width=72)

                Filter: (mgr IS NULL)

          ->  Hash Join  (cost=0.33..1.62 rows=10 width=72)

                Hash Cond: (emp_1.mgr = mgr.empno)

                ->  Seq Scan on emp emp_1  (cost=0.00..1.14 rows=14 width=40)

                ->  Hash  (cost=0.20..0.20 rows=10 width=36)

                      ->  WorkTable Scan on leveln mgr  (cost=0.00..0.20 rows=10 width=36)



EXPLAIN
empno	ename	mgr	mgr
7839	KING	null
7566	JONES	7839	KING
7698	BLAKE	7839	KING
7782	CLARK	7839	KING