Postgres 14

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 public.menu_resource (
    id int4 NOT NULL,
    "name" varchar NOT NULL,
    res_type int4 NOT NULL,
    created_time int8 NOT NULL,
    updated_time int8 NOT NULL,
    remark varchar NULL,
    "path" varchar NOT NULL,
    parent_id int4 NOT NULL,
    component varchar NULL,
    sort int4 NOT NULL,
    name_zh varchar NOT NULL,
    tree_id_path varchar NULL,
    CONSTRAINT auth_resource_id_seq PRIMARY KEY (id)
);

CREATE TABLE


INSERT INTO public.menu_resource
(id, "name", res_type, created_time, updated_time, remark, "path", parent_id, component, sort, name_zh, tree_id_path)
VALUES(13, 'job', 1, 1632389739938, 1632389739938, NULL, '/app/job', 1, NULL, 2, '求职管理', '5-1-13');
INSERT INTO public.menu_resource
(id, "name", res_type, created_time, updated_time, remark, "path", parent_id, component, sort, name_zh, tree_id_path)
VALUES(14, 'interview', 1, 1632389739938, 1632389739938, NULL, '/app/job/interview', 13, NULL, 2, '面试列表', '5-1-13-14');
INSERT INTO public.menu_resource
(id, "name", res_type, created_time, updated_time, remark, "path", parent_id, component, sort, name_zh, tree_id_path)
VALUES(15, 'menu', 1, 1632389739938, 1632389739938, NULL, '/privilege/menu', 2, './permission/menu', 0, '菜单列表', NULL);
INSERT INTO public.menu_resource
(id, "name", res_type, created_time, updated_time, remark, "path", parent_id, component, sort, name_zh, tree_id_path)
VALUES(3, 'cruise', 1, 1632389739938, 1632389739938, NULL, '/app/cruise', 1, NULL, 2, 'Cruise', NULL);
INSERT INTO public.menu_resource
(id, "name", res_type, created_time, updated_time, remark, "path", parent_id, component, sort, name_zh, tree_id_path)
VALUES(5, '系统菜单', 1, 1632389739938, 1632389739938, NULL, '/demo', 0, NULL, 0, '系统菜单', NULL);
INSERT INTO public.menu_resource
(id, "name", res_type, created_time, updated_time, remark, "path", parent_id, component, sort, name_zh, tree_id_path)
VALUES(4, 'channel', 1, 1632389739938, 1632389739938, NULL, '/app/cruise/channel', 3, './apps/cruise/channel', 3, '频道', NULL);
INSERT INTO public.menu_resource
(id, "name", res_type, created_time, updated_time, remark, "path", parent_id, component, sort, name_zh, tree_id_path)
VALUES(1, 'app', 1, 1632389739938, 1632389739938, NULL, '/app', 5, NULL, 1, '应用', NULL);
INSERT INTO public.menu_resource
(id, "name", res_type, created_time, updated_time, remark, "path", parent_id, component, sort, name_zh, tree_id_path)
VALUES(2, 'privilege', 1, 1632389739938, 1632389739938, NULL, '/privilege', 5, NULL, 4, '权限管理', NULL);
INSERT INTO public.menu_resource
(id, "name", res_type, created_time, updated_time, remark, "path", parent_id, component, sort, name_zh, tree_id_path)
VALUES(6, 'role', 1, 1632389739938, 1632389739938, NULL, '/privilege/role', 2, './permission/role', 5, '角色列表', NULL);
INSERT INTO public.menu_resource
(id, "name", res_type, created_time, updated_time, remark, "path", parent_id, component, sort, name_zh, tree_id_path)
VALUES(7, 'article', 1, 1632389739938, 1632389739938, NULL, '/app/cruise/article', 3, './apps/cruise/article', 3, '文章', NULL);
INSERT INTO public.menu_resource
(id, "name", res_type, created_time, updated_time, remark, "path", parent_id, component, sort, name_zh, tree_id_path)
VALUES(8, 'user', 1, 1632389739938, 1632389739938, NULL, '/privilege/user', 2, './permission/user', 5, '用户列表', NULL);
INSERT INTO public.menu_resource
(id, "name", res_type, created_time, updated_time, remark, "path", parent_id, component, sort, name_zh, tree_id_path)
VALUES(9, 'welcome', 1, 1632389739938, 1632389739938, NULL, '/welcome', 5, './Welcome', 0, '欢迎', NULL);
INSERT INTO public.menu_resource
(id, "name", res_type, created_time, updated_time, remark, "path", parent_id, component, sort, name_zh, tree_id_path)
VALUES(10, 'overview', 1, 1632389739938, 1632389739938, NULL, '/app/overview', 1, NULL, 2, '应用概览', NULL);
INSERT INTO public.menu_resource
(id, "name", res_type, created_time, updated_time, remark, "path", parent_id, component, sort, name_zh, tree_id_path)
VALUES(11, 'product', 1, 1632389739938, 1632389739938, NULL, '/app/overview/product', 10, NULL, 2, '产品列表', NULL);
INSERT INTO public.menu_resource
(id, "name", res_type, created_time, updated_time, remark, "path", parent_id, component, sort, name_zh, tree_id_path)
VALUES(12, 'list', 1, 1632389739938, 1632389739938, NULL, '/app/overview/list', 10, NULL, 2, '应用列表', NULL);

INSERT 0 1



INSERT 0 1



INSERT 0 1



INSERT 0 1



INSERT 0 1



INSERT 0 1



INSERT 0 1



INSERT 0 1



INSERT 0 1



INSERT 0 1



INSERT 0 1



INSERT 0 1



INSERT 0 1



INSERT 0 1



INSERT 0 1


create table tmp (id int, parent_id int, tree_path_id text);

CREATE TABLE


insert into tmp (id, parent_id, tree_path_id)
with recursive t (id, parent_id, tree_path_id) as (
    select id, parent_id, '' as tree_path_id 
    from public.menu_resource 
    where parent_id = 0
    
    union all
    
    select mr.id, mr.parent_id, t.tree_path_id || '-' || mr.parent_id::text
    from public.menu_resource mr
    join t
        on mr.parent_id = t.id
) 
select id, parent_id, tree_path_id from t;

INSERT 0 15


update public.menu_resource x
    set tree_id_path = (select tree_path_id from tmp where id = x.id);

UPDATE 15


select id, parent_id, tree_id_path from public.menu_resource;


id
parent_id
tree_id_path


13
1
-5-1

14
13
-5-1-13

15
2
-5-2

3
1
-5-1

5
0


4
3
-5-1-3

1
5
-5

2
5
-5

6
2
-5-2

7
3
-5-1-3

8
2
-5-2

9
5
-5

10
1
-5-1

11
10
-5-1-10

12
10
-5-1-10



SELECT 15
id	parent_id	tree_id_path
13	1	-5-1
14	13	-5-1-13
15	2	-5-2
3	1	-5-1
5	0
4	3	-5-1-3
1	5	-5
2	5	-5
6	2	-5-2
7	3	-5-1-3
8	2	-5-2
9	5	-5
10	1	-5-1
11	10	-5-1-10
12	10	-5-1-10