集成查询

改进多处逻辑，您可以将整个操作集成在一个查询中.包装成 SQL 函数是可选的:

Integrate query

Improving the logic in several places, you can integrate the whole operation in a single query. Wrapping into an SQL function is optional:

CREATE OR REPLACE FUNCTION f_elems(_action_id integer)
  RETURNS SETOF integer AS
$func$
   WITH RECURSIVE l AS (
      SELECT a.category_id, l.local_id
      FROM   action a
      JOIN   local  l USING (local_id)
      WHERE  a.action_id = $1

      UNION ALL 
      SELECT l.category_id, c.local_id
      FROM   l
      JOIN   local c ON c.parent_id = l.local_id  -- c for "child"
      )
   SELECT e.element_id
   FROM   l
   JOIN   element e USING (category_id, local_id);
$func$  LANGUAGE sql STABLE;

检索给定 action_id 的相同和子本地的所有 element_id.

Retrieves all element_id for same and child-locals of a given action_id.

调用:

SELECT * FROM f_elem(3);

element_id
-----------
6
7

db<>fiddle 这里
_{OLD sqlfiddle}

出于多种原因，这应该大大更快.最明显的是:

This should be substantially faster already for several reasons. The most obvious ones being:

• 用纯 SQL 替换 plpgsql 中的慢循环.
• 缩小递归查询的起始集.
• 删除不必要且众所周知的缓慢IN 构造.

我使用 SELECT * FROM ... 而不是 SELECT 调用，即使该行只有一列，以获取 SELECT 的列名code>OUT 参数 (element_id) 我在函数头中声明.

I am calling with SELECT * FROM ... instead of just SELECT, even though the row has only a single column, to get the column name of the OUT parameter (element_id) I declared in the function header.

action.action_id 上的索引由主键提供.

An index on action.action_id is provided by the primary key.

但您可能错过了 local.parent_id 上的索引.同时，将覆盖多列索引(Postgres 9.2+)与 parent_id 作为第一个元素，local_id 作为第二个元素.如果表 local 很大，这应该会有很大帮助.对于一张小桌子来说，没有那么多或根本没有:

But you may have missed the index on local.parent_id. While being at it, make that a covering multi-column index (Postgres 9.2+) with parent_id as first element and local_id as second. This should help a lot if the table local is big. Not so much or not at all for a small table:

CREATE INDEX l_mult_idx ON local(parent_id, local_id);

为什么?见:

• 复合索引是否也适用于第一个字段的查询?

最后，表上的多列索引element 应该有更多帮助:

Finally, a multi-column index on table element should help some more:

CREATE INDEX e_mult_idx ON element (category_id, local_id, element_id);

第三列element_id 仅用于使其成为覆盖索引.如果您的查询从表 element 中检索更多列，您可能需要向索引添加更多列或删除 element_id.要么会加快速度.

The third column element_id is only useful to make it a covering index. If your query retrieves more columns from table element, you may want to add more columns to the index or drop element_id. Either will make it faster.

如果您的表收到很少或没有更新，则提供共享相同类别的所有对 (action_id, element_id) 的预计算集的物化视图将使这快如闪电.将 (action_id, element_id)(按此顺序)设为主键.

If your tables receive few or no updates, a materialized view providing the pre-computed set of all pairs (action_id, element_id) sharing the same category would make this lightening-fast. Make (action_id, element_id) (in that order) the primary key.