PostgreSQL忽略递归查询的索引。

Question

PostgreSQL忽略递归查询的索引。

postgresqlhierarchical-datarecursive-query

3

我有一张表示层级链接图的表（parent_id，child_id）。该表在父节点、子节点和两者上都有索引。该图可能包含循环，并且我需要检查它们（或者，也许我需要找到所有循环以消除它们）。

我需要递归查询一个节点的所有父节点。为此，我使用以下查询（应该保存在视图中）：

WITH RECURSIVE recursion(parent_id, child_id, node_id, path) AS (
     SELECT h.parent_id,
        h.child_id,
        h.child_id AS node_id,
        ARRAY[h.parent_id, h.child_id] AS path
       FROM hierarchy h
    UNION ALL
     SELECT h.parent_id,
        h.child_id,
        r.node_id,
        ARRAY[h.parent_id] || r.path 
       FROM hierarchy h JOIN recursion r ON h.child_id = r.parent_id
      WHERE NOT r.path @> ARRAY[h.parent_id]
    )
 SELECT parent_id,
    child_id,
    node_id,
    path
   FROM recursion
   where node_id = 883

对于这个查询，PostgreSQL将使用非常出色的计划：

"CTE Scan on recursion  (cost=2703799682.88..4162807558.70 rows=324223972 width=56)"
"  Filter: (node_id = 883)"
"  CTE recursion"
"    ->  Recursive Union  (cost=0.00..2703799682.88 rows=64844794481 width=56)"
"          ->  Seq Scan on hierarchy h  (cost=0.00..74728.61 rows=4210061 width=56)"
"          ->  Merge Join  (cost=10058756.99..140682906.47 rows=6484058442 width=56)"
"                Merge Cond: (h_1.child_id = r.parent_id)"
"                Join Filter: (NOT (r.path @> ARRAY[h_1.parent_id]))"
"                ->  Index Scan using hierarchy_idx_child on hierarchy h_1  (cost=0.43..256998.25 rows=4210061 width=16)"
"                ->  Materialize  (cost=10058756.56..10269259.61 rows=42100610 width=48)"
"                      ->  Sort  (cost=10058756.56..10164008.08 rows=42100610 width=48)"
"                            Sort Key: r.parent_id"
"                            ->  WorkTable Scan on recursion r  (cost=0.00..842012.20 rows=42100610 width=48)"

看起来Postgres不理解在第一个递归子查询中，对node_id的外部过滤器被应用于child_id。

我想我做错了什么。但具体是哪里错了呢？

- QwiglyDee

2

通常，在 UNION 的第一部分中会有一个条件：要么是顶级节点（没有父节点），要么是叶子节点（没有子节点），或者是您感兴趣的某个特定记录号。但是您的代码使用每个记录作为链式起始点。 - wildplasser

2

联合查询的第一个操作检索所有数据表中的行。没有索引能够帮助这个过程。 - user330315

我希望Postgres可以将外部过滤器与内部子查询合并。看来我太乐观了。 - QwiglyDee

Postgres通常不会将条件推入CTE中，例如在这里（http://modern-sql.com/feature/with/performance）中所述。但在这种情况下，它无论如何都不能这样做，因为它应该将“where node_id = 883”应用于联合的哪个部分？如果将其推送到两个部分，则会出现错误。 - user330315

感谢提供参考！ - QwiglyDee

2个回答

1

看起来你只需要将 WHERE node_id = 883 移动到 union 的第一部分：

WITH RECURSIVE recursion(parent_id, child_id, node_id, path) AS (
     SELECT h.parent_id,
        h.child_id,
        h.child_id AS node_id,
        ARRAY[h.parent_id, h.child_id] AS path
       FROM hierarchy h
      WHERE node_id = 883
    UNION ALL
     SELECT h.parent_id,
        h.child_id,
        r.node_id,
        ARRAY[h.parent_id] || r.path 
       FROM hierarchy h JOIN recursion r ON h.child_id = r.parent_id
      WHERE NOT r.path @> ARRAY[h.parent_id]
    )
 SELECT parent_id,
    child_id,
    node_id,
    path
   FROM recursion

- Roman Tkachuk

在这种情况下，我无法将查询保存到视图中，但只能保存到带有起始过滤器输入参数的函数中。 - QwiglyDee

2

函数是参数化视图 :-). 只有在非递归部分使用where语句，才能加快查询速度。 - Roman Tkachuk

是的，这个函数很可能确实是我在这里需要的。 - QwiglyDee

如果遍历的路径非常长，这将需要很长时间。我猜这是因为路径存储为表中每行的一部分所致。有没有什么解决方法？@RomanTkachuk - CRM

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- QwiglyDee · Accepted Answer

这里有一种更有效的方法来解决图遍历任务。

CREATE OR REPLACE FUNCTION public.terr_ancestors(IN bigint)
RETURNS TABLE(node_id bigint, depth integer, path bigint[]) AS
$BODY$
WITH RECURSIVE recursion(node_id, depth, path) AS (
  SELECT $1 as node_id, 0, ARRAY[$1] AS path
  UNION ALL
  SELECT h.parent_id as node_id, r.depth + 1, h.parent_id || r.path
    FROM hierarchy h JOIN recursion r ON h.child_id = r.node_id
    WHERE h.parent_id != ANY(path)
)
SELECT * FROM recursion
$BODY$

同样适用于后代元素。