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

CREATE TABLE tbl (id int, path_id text, node text);
INSERT INTO tbl VALUES
  (1, 'p1', 'n1')
, (2, 'p1', 'n2')
, (3, 'p1', 'n3')
, (4, 'p2', 'n1')
, (5, 'p2', 'n2')
, (6, 'p2', 'n4')
RETURNING *;

CREATE TABLE

id
path_id
node

1
p1
n1

2
p1
n2

3
p1
n3

4
p2
n1

5
p2
n2

6
p2
n4

INSERT 0 6

SELECT jsonb_object_agg(node, parent) AS edges
FROM  (
   SELECT node, lag(node) OVER (PARTITION BY path_id ORDER BY id) AS parent
   FROM   tbl
   ORDER  BY parent NULLS FIRST, node  -- ORDER BY optional
   ) sub;

edges

{"n1": null, "n2": "n1", "n3": "n2", "n4": "n2"}

SELECT 1

-- jsonb_object_agg_strict() eliminates root node etry pointing to null
SELECT jsonb_object_agg_strict(node, parent) AS edges
FROM  (
   SELECT node, lag(node) OVER (PARTITION BY path_id ORDER BY id) AS parent
   FROM   tbl
   ORDER  BY parent NULLS FIRST, node  -- ORDER BY optional
   ) sub;

edges

{"n2": "n1", "n3": "n2", "n4": "n2"}

SELECT 1

-- legacy JSOB solution

SELECT node, lag(node) OVER (PARTITION BY path_id ORDER BY id) AS parent
FROM   tbl
ORDER  BY parent NULLS FIRST, node;  -- ORDER BY optional

SELECT DISTINCT
       node, lag(node) OVER (PARTITION BY path_id ORDER BY id) AS parent
FROM   tbl
ORDER  BY parent NULLS FIRST, node;  -- ORDER BY optional

SELECT json_agg(sub) AS edges
FROM  (
   SELECT DISTINCT
          node, lag(node) OVER (PARTITION BY path_id ORDER BY id) AS parent
   FROM   tbl
   ORDER  BY parent NULLS FIRST, node  -- ORDER BY optional
   ) sub;

node
parent

n1
null

n1
null

n2
n1

n2
n1

n3
n2

n4
n2

SELECT 6

node
parent

n1
null

n2
n1

n3
n2

n4
n2

SELECT 4

edges

[{"node":"n1","parent":null}, 
 {"node":"n2","parent":"n1"}, 
 {"node":"n3","parent":"n2"}, 
 {"node":"n4","parent":"n2"}]

SELECT 1

node	parent
n1	null
n1	null
n2	n1
n2	n1
n3	n2
n4	n2