Usign PostgreSQL Ltree extenstion

ltree-example.sql

CREATE EXTENSION ltree;
CREATE TABLE test(
    id serial primary key,
    path ltree
);

INSERT INTO test (path) VALUES
('1'),
('1.1'),
('1.1.1'),
('1.1.2'),
('1.2');

query-example.sql

SELECT
    lft.id as id,
    array_remove(array_agg(chlds.id ORDER BY chlds.path), NULL) as children_ids,
    prnts.id as parent_id
FROM test lft
LEFT JOIN test chlds
    ON lft.path = subpath(chlds.path, 0, -1)
LEFT JOIN test prnts
    ON subpath(lft.path, 0, -1) = prnts.path
GROUP BY lft.id, prnts.id

 id | children_ids | parent_id 
----+--------------+-----------
  1 | {2,5,8}      |          
  2 | {3,4}        |         1
  3 | {}           |         2
  4 | {}           |         2
  5 | {6,7}        |         1
  6 | {}           |         5
  7 | {}           |         5
  8 | {}           |         1
(8 rows)

query-plan-with-btree-index.sql

CREATE INDEX test_path_btree_idx ON test USING BTREE (path);
ANALYZE;

EXPLAIN
SELECT
    lft.id as id,
    array_remove(array_agg(chlds.id ORDER BY chlds.path), NULL) as children_ids,
    prnts.id as parent_id
FROM test lft
LEFT JOIN test chlds
    ON lft.path = subpath(chlds.path, 0, -1)
LEFT JOIN test prnts
    ON subpath(lft.path, 0, -1) = prnts.path
GROUP BY lft.id, prnts.id

                                          QUERY PLAN                                           
-----------------------------------------------------------------------------------------------
 GroupAggregate  (cost=4.24..4.44 rows=8 width=40)
   Group Key: lft.id, prnts.id
   ->  Sort  (cost=4.24..4.26 rows=8 width=36)
         Sort Key: lft.id, prnts.id
         ->  Merge Left Join  (cost=3.92..4.12 rows=8 width=36)
               Merge Cond: ((subpath(lft.path, 0, '-1'::integer)) = prnts.path)
               ->  Sort  (cost=2.72..2.74 rows=8 width=56)
                     Sort Key: (subpath(lft.path, 0, '-1'::integer))
                     ->  Merge Left Join  (cost=2.40..2.60 rows=8 width=56)
                           Merge Cond: (lft.path = (subpath(chlds.path, 0, '-1'::integer)))
                           ->  Sort  (cost=1.20..1.22 rows=8 width=28)
                                 Sort Key: lft.path
                                 ->  Seq Scan on test lft  (cost=0.00..1.08 rows=8 width=28)
                           ->  Sort  (cost=1.20..1.22 rows=8 width=28)
                                 Sort Key: (subpath(chlds.path, 0, '-1'::integer))
                                 ->  Seq Scan on test chlds  (cost=0.00..1.08 rows=8 width=28)
               ->  Sort  (cost=1.20..1.22 rows=8 width=28)
                     Sort Key: prnts.path
                     ->  Seq Scan on test prnts  (cost=0.00..1.08 rows=8 width=28)
(19 rows)

query-plan-without-index.sql

EXPLAIN
SELECT
    lft.id as id,
    array_remove(array_agg(chlds.id ORDER BY chlds.path), NULL) as children_ids,
    prnts.id as parent_id
FROM test lft
LEFT JOIN test chlds
    ON lft.path = subpath(chlds.path, 0, -1)
LEFT JOIN test prnts
    ON subpath(lft.path, 0, -1) = prnts.path
GROUP BY lft.id, prnts.id

                                            QUERY PLAN                                             
---------------------------------------------------------------------------------------------------
 GroupAggregate  (cost=5849.39..7129.54 rows=51206 width=40)
   Group Key: lft.id, prnts.id
   ->  Sort  (cost=5849.39..5977.41 rows=51206 width=44)
         Sort Key: lft.id, prnts.id
         ->  Merge Right Join  (cost=938.42..1844.05 rows=51206 width=44)
               Merge Cond: (prnts.path = (subpath(lft.path, 0, '-1'::integer)))
               ->  Sort  (cost=88.17..91.35 rows=1270 width=36)
                     Sort Key: prnts.path
                     ->  Seq Scan on test prnts  (cost=0.00..22.70 rows=1270 width=36)
               ->  Sort  (cost=850.25..870.41 rows=8064 width=72)
                     Sort Key: (subpath(lft.path, 0, '-1'::integer))
                     ->  Merge Left Join  (cost=176.34..327.01 rows=8064 width=72)
                           Merge Cond: (lft.path = (subpath(chlds.path, 0, '-1'::integer)))
                           ->  Sort  (cost=88.17..91.35 rows=1270 width=36)
                                 Sort Key: lft.path
                                 ->  Seq Scan on test lft  (cost=0.00..22.70 rows=1270 width=36)
                           ->  Sort  (cost=88.17..91.35 rows=1270 width=36)
                                 Sort Key: (subpath(chlds.path, 0, '-1'::integer))
                                 ->  Seq Scan on test chlds  (cost=0.00..22.70 rows=1270 width=36)
(19 rows)