Postgres 12

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 orders (
  "id" INTEGER,
  "description" VARCHAR(18),
  "parent_id" VARCHAR(4),
  "cost" DECIMAL(8,2)
);

INSERT INTO orders
  ("id", "description", "parent_id", "cost")
VALUES
  ('1', 'Radiology', NULL, '0.00'),
  ('2', 'Lab Tests', NULL, '0.00'),
  ('3', 'Normal Radiology', '1', '0.00'),
  ('4', 'Resonance', '1', '100.00'),
  ('1100', 'Cerebral Resonance', '4', '200.00'),
  ('1900', 'Blood Tests', '2', '10.00'),
  ('2044', 'Calcium', '2', '50.00');
7 rows affected

WITH RECURSIVE hierarchy AS (
    SELECT  id, 1 AS rown, CAST(description AS TEXT) AS parent_list, id as parent
    FROM    orders
    WHERE   parent_id is null
    UNION  
    SELECT  c.id
    ,rown + 1 as rown
    ,CAST(repeat('    ', rown) || ' --> ' || c.description as text) as parent_list
    ,parent
    FROM orders c
    INNER JOIN hierarchy c2 ON CAST(c.parent_id AS INTEGER) = c2.id )
SELECT id,parent_list FROM hierarchy
ORDER BY parent DESC,rown


id
parent_list


2
Lab Tests

1900
     --> Blood Tests

2044
     --> Calcium

1
Radiology

4
     --> Resonance

3
     --> Normal Radiology

1100
         --> Cerebral Resonance


WITH RECURSIVE hierarchy AS (
    SELECT  id, CAST(description AS TEXT) AS parent_list, 1 AS rown, id as parent
    FROM    orders
    WHERE   parent_id is null
    UNION  
    SELECT  c.id
    ,CAST(c2.parent_list || ' --> ' || c.description as text) as parent_list
    ,rown + 1 as rwon
    ,parent
    FROM orders c
    INNER JOIN hierarchy c2 ON CAST(c.parent_id AS INTEGER) = c2.id )
SELECT  id, parent_list
FROM    hierarchy
GROUP BY id, parent_list
ORDER BY parent_list;


id
parent_list


2
Lab Tests

1900
Lab Tests --> Blood Tests

2044
Lab Tests --> Calcium

1
Radiology

3
Radiology --> Normal Radiology

4
Radiology --> Resonance

1100
Radiology --> Resonance --> Cerebral Resonance

id	parent_list
2	Lab Tests
1900	--> Blood Tests
2044	--> Calcium
1	Radiology
4	--> Resonance
3	--> Normal Radiology
1100	--> Cerebral Resonance

id	parent_list
2	Lab Tests
1900	Lab Tests --> Blood Tests
2044	Lab Tests --> Calcium
1	Radiology
3	Radiology --> Normal Radiology
4	Radiology --> Resonance
1100	Radiology --> Resonance --> Cerebral Resonance