Postgres 11

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 tree
(
  id INT PRIMARY KEY
, parent_id INT 
, name text
);

CREATE TABLE


INSERT INTO tree VALUES 
(2,  12,   'Node 2'),
(3,  2,    'Node 3'),
(4,  6,    'Node 4'),
(5,  1,    'Node 5'),
(6,  1,    'Node 6'),
(7,  11,   'Node 7'),
(8,  6,    'Node 8'),
(10, 7,    'Node 10'),
(11, 6,    'Node 11'),
(12, 5,    'Node 12'),
(17, 14,   'Node 17');

INSERT 0 11


CREATE FUNCTION get_parent(node_id int)
RETURNS integer AS
$$
  WITH RECURSIVE get_parent AS
  (
      SELECT 
          t1.id, 
          t1.parent_id, 
          t1.name, 
          0 AS level
      FROM 
          tree t1
      WHERE 
          t1.id = node_id
      UNION ALL
      SELECT 
          t2.id, 
          t2.parent_id, 
          t2.name,
          level+1
      FROM 
          tree t2 
      INNER JOIN 
          get_parent ON get_parent.parent_id = t2.id
  )
  SELECT
      id
  FROM
      get_parent
  ORDER BY
      level DESC
  LIMIT 1 ;
$$
LANGUAGE SQL;

CREATE FUNCTION


select get_parent(7);


get_parent


6



SELECT 1


select get_parent(1);


get_parent


null



SELECT 1


WITH RECURSIVE childs AS
(
    SELECT 
        t1.id, 
        t1.parent_id, 
        t1.name
    FROM 
        tree t1
    WHERE 
        t1.id = get_parent(7)
    UNION ALL
    SELECT 
        t2.id, 
        t2.parent_id, 
        t2.name
    FROM 
        tree t2 
    INNER JOIN 
        childs ON childs.id = t2.parent_id
)
SELECT
    id,
    parent_id,
    name
FROM
    childs;


id
parent_id
name


6
1
Node 6

4
6
Node 4

8
6
Node 8

11
6
Node 11

7
11
Node 7

10
7
Node 10



SELECT 6