Postgres中的分层父查询

我正在从Oracle迁移到Postgresql。我正在尝试将一些Oracle层次查询转换为Postgres。例如，在Oracle中，以逗号分隔（包括子项）并包括id_to_start_withI的所有ID的形式返回一个逗号分隔的有序列表，我将执行以下操作：

SELECT LISTAGG(id_something, ',') WITHIN GROUP (ORDER BY id_something) AS somethings FROM(
SELECT DISTINCT D.id_something
FROM something_table D
START WITH D.id_something = :id_to_start_with
CONNECT BY D.id_something_parent = PRIOR D.id_something
)

Postgres中的等效项似乎是：

WITH RECURSIVE the_somethings(id_something) AS (
SELECT id_something
FROM something_table
WHERE id_something = $id_to_start_with
UNION ALL
SELECT D.id_something
FROM the_somethings DR
JOIN something_table D ON DR.id_something = D.id_something_parent
)
SELECT string_agg(temp_somethings.id_something::TEXT, ',') AS somethings
FROM (
SELECT id_something
FROM the_somethings
ORDER BY id_something
) AS temp_somethings

同样，如果我想返回上面所有ID（即父母）（包括id_to_start_withI ）的逗号分隔的有序列表，我将在Oracle中执行以下操作：

SELECT LISTAGG(id_something, ',') WITHIN GROUP (ORDER BY id_something) AS somethings FROM(
SELECT DISTINCT D.id_something
FROM something_table D
START WITH D.id_something = :id_to_start_with
CONNECT BY D.id_something = PRIOR D.id_something_parent
)

Postgres中的等效项似乎是：

WITH RECURSIVE the_somethings(id_something, path) AS (
SELECT id_something
, id_something::TEXT as path
FROM something_table
WHERE id_something_parent IS NULL
UNION ALL
SELECT D.id_something
, (DR.path || ',' || D.id_something::TEXT) as path
FROM something_table D
JOIN the_somethings DR ON DR.id_something = D.id_something_parent
)
SELECT path
FROM the_somethings
WHERE id_something = $id_to_start_with
ORDER BY id_something

我的问题与上一个Postgres查询有关。对我来说，这似乎效率极低，我想知道是否有更好的方法来编写它。也就是说，在Oracle中，查询将查找的父级id_to_start_with，然后是父级的父级，依此类推直至根。

另一方面，Postgres查询会获得每个可能的根到子路径的组合，然后将所有东西扔掉，除了id_to_start_with我要寻找的一个根。可能要创建大量数据，只是将其丢弃（除了我要查找的一行）。

有没有一种方法可以获取一个逗号分隔的有序列表，该列表的所有特定父级id_to_start_with在Postgres中的表现与在Oracle中一样好？

编辑：从Oracle和Postgres添加说明计划。

Oracle解释计划输出

Postgres解释分析输出

CTE Scan on the_somethings  (cost=62.27..74.66 rows=3 width=76) (actual time=0.361..0.572 rows=1 loops=1)
  Filter: (id_something = 1047)
  Rows Removed by Filter: 82
  CTE the_somethings
    ->  Recursive Union  (cost=0.00..62.27 rows=551 width=76) (actual time=0.026..0.433 rows=83 loops=1)
          ->  Seq Scan on something_table  (cost=0.00..2.83 rows=1 width=8) (actual time=0.023..0.034 rows=1 loops=1)
                Filter: (id_something_parent IS NULL)
                Rows Removed by Filter: 82
          ->  Hash Join  (cost=0.33..4.84 rows=55 width=76) (actual time=0.028..0.065 rows=16 loops=5)
                Hash Cond: (d.id_something_parent = dr.id_something)
                ->  Seq Scan on something_table d  (cost=0.00..2.83 rows=83 width=16) (actual time=0.002..0.012 rows=83 loops=5)
                ->  Hash  (cost=0.20..0.20 rows=10 width=76) (actual time=0.009..0.009 rows=17 loops=5)
                      Buckets: 1024  Batches: 1  Memory Usage: 10kB
                      ->  WorkTable Scan on the_somethings dr  (cost=0.00..0.20 rows=10 width=76) (actual time=0.001..0.004 rows=17 loops=5)
Planning time: 0.407 ms
Execution time: 0.652 ms

这是基于下面Jakub的答案的最终查询。

WITH RECURSIVE the_somethings(id_something, path, level, orig_id, id_something_parent) AS (

SELECT id_something

, id_something::TEXT as path

, 0 as level

, id_something AS orig_id

, id_something_parent

FROM something_table

WHERE id_something IN (1047, 448)

UNION ALL

SELECT D.id_something

, (D.id_something::TEXT || ',' || DR.path) as path

, DR.level + 1 as level

, DR.orig_id as orig_id

, D.id_something_parent

FROM something_table D

JOIN the_somethings DR ON D.id_something = DR.id_something_parent

)

SELECT DISTINCT ON(orig_id) orig_id, path

FROM the_somethings

ORDER BY orig_id, level DESC
;