在postgres中递归查询中的Order

Question

我有以下查询：

with recursive chain(id, weight, depth) as (
select rpCore.itemID2, rpCore.weight, 1 as depth from relations rpCore where itemID1 = 1048663
union 
select r.itemID2, c.weight + r.weight, c.depth + 1from chain c
inner join relations r
on (r.itemID1=id)
where c.depth < 3
order by c.weight + r.weight asc limit 10
) select * from chain order by weight asc limit 10;

如果运行它，将得到以下错误：

ERROR: ORDER BY in a recursive query is not implemented
SQL state: 0A000
Character: 313

由于权重的结构方式(都是正的，并加在一起)，如果我能够使查询的递归部分对自身进行排序和限制，查询的外部部分将返回我需要的结果，但似乎不能按递归查询的递归部分执行顺序。

有什么方法来模拟这种行为吗？如果删除限制语句，则查询将运行，但由于有大量行(由于最后的限制语句而没有必要)，查询运行得非常慢。

Answer 1

正如我理解您的问题一样，您希望为递归CTE关系添加一个限制，以防止它在过多的结果上递归，这会使查询变得缓慢。

答案是你不能这么做。这不仅是因为它没有在PostgreSQL中实现，而且是因为您所要求的内容在逻辑上是不可能的。

当PostgreSQL在每一组相关记录上递归时，它累积了一组事实上按深度排列的集合。这不是您想要的顺序，这是由一个不相关的列，“权重”。因此，Postgres无法在不先积累完整递归数据集的情况下根据权重进行限制。因此，即使支持CTE中的ORDER语句，它也不会提高您的性能。

我还将指出，在任何UNION查询中，ORDER应用于结果数据集，而不是应用于UNION的每个部分。因此，不能仅将其应用于“递归部分”。

当然，您可以像在此查询中已经完成的那样，提前深度终止递归。将UNION改为UNION ALL也将提高性能。

从语法上讲，只需添加第二个CTE即可实现所需的内容：

  with recursive chain(id, weight, depth) as (
  select rpCore.itemID2, rpCore.weight, 1 as depth 
  from relations rpCore 
  where itemID1 = 1048663
  union all
  select r.itemID2, c.weight + r.weight, c.depth + 1
  from chain c
  inner join relations r
  on (r.itemID1=id)
  where c.depth < 3
  ),
  chain_limit as (
  SELECT * FROM chain
  ORDER BY weight LIMIT 10
  )
  select * from chain_limit;

然而，这并不能帮助您实现性能目标。