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 ad_treenodemm(
  ad_tree_id int NOT NULL
, node_id int NOT NULL
, parent_id int NOT NULL
, CONSTRAINT ad_treenodemm_pkey PRIMARY KEY (ad_tree_id, node_id)
);

INSERT INTO ad_treenodemm VALUES
  (1,1,0)
, (1,2,1), (1,3,2), (1,4,3)
, (1,5,1), (1,6,5)           -- delete this branch ...
         , (1,7,5), (1,8,7)
, (2,11,0), (2,12,11), (2,13,12)
          , (2,15,11), (2,16,15)
RETURNING *;

CREATE TABLE




ad_tree_id
node_id
parent_id


1
1
0

1
2
1

1
3
2

1
4
3

1
5
1

1
6
5

1
7
5

1
8
7

2
11
0

2
12
11

2
13
12

2
15
11

2
16
15



INSERT 0 13


WITH RECURSIVE x AS (
   SELECT ad_tree_id, node_id
   FROM   ad_treenodemm
   WHERE (ad_tree_id, node_id) = (1,5)

   UNION  ALL
   SELECT a.ad_tree_id, a.node_id
   FROM   x
   JOIN   ad_treenodemm a ON a.parent_id = x.node_id
   )
DELETE FROM ad_treenodemm a
USING  x
WHERE (a.ad_tree_id, a.node_id)
    = (x.ad_tree_id, x.node_id)
RETURNING a.*;


ad_tree_id
node_id
parent_id


1
5
1

1
6
5

1
7
5

1
8
7



DELETE 4


SELECT * FROM ad_treenodemm;


ad_tree_id
node_id
parent_id


1
1
0

1
2
1

1
3
2

1
4
3

2
11
0

2
12
11

2
13
12

2
15
11

2
16
15



SELECT 9