postgres WITH RECURSIVE有多昂贵?

Question

我有一个产品系统，如果没有任何附加产品，每个类别可以有多个子类别层，如果没有附加任何子类别，我们可以为一个类别附加尽可能多的产品，如下面的例子:

PS:为了简单起见，我从两个表中删除了其他列/约束

CREATE TABLE categories (
id serial PRIMARY KEY,
name VARCHAR NOT NULL,
parent_id INT
);
CREATE TABLE products (
id serial PRIMARY KEY,
name VARCHAR NOT NULL,
category_id serial,
CONSTRAINT fk_category FOREIGN KEY(category_id) REFERENCES categories(id)
);
INSERT INTO categories (
id,
name,
parent_id
)
VALUES
(1, 'Electronic Devices', NULL),
(2, 'lap-top', 1),
(3, 'smart-phones', 1),
(4, 'headphones', 1),
(5, 'desctops', 1),
(6, '16inc laptops', 2),
(7, '13inc laptops', 2),
(8, 'smart phones', 3),
(9, 'phablets', 3),
(10, 'wireless headphones', 4),
(11, 'wired headphones', 4),
(12, 'onear headphones', 10),
(13, 'large headphones', 10);

INSERT into products (
id, name, category_id
) VALUES 
(1, 'mac-book-16 model 1', 6),
(2, 'mac-book-16 model 2', 6),
(3, 'mac-book-16 model 3', 6),
(4, 'mac-book-13 model 1', 7),
(5, 'mac-book-13 model 2', 7),
(6, 'mac-book-13 model 3', 7),
(7, 'mac-book-13 model 4', 7),
(8, 'iphone 10', 8),
(9, 'iphone 11', 8),
(10, 'iphone 12', 8),
(11, 'iphone 13', 8),
(12, 'iphone 14', 8),
(13, 'iphone 10 pro max', 9),
(14, 'iphone 11 pro max', 9),
(15, 'iphone 12 pro max', 9),
(16, 'iphone 13 pro max', 9),
(17, 'iphone 14 pro max', 9),
(18, 'galegxy note 8', 9),
(19, 'galegxy note 9', 9),
(20, 'galegxy note 10', 9),
(21, 'some headphones', 12),
(22, 'samsung galexypods', 12),
(23, 'apple earpods', 12),
(24, 'apple earpod pro', 13);

假设我想获得与"电子设备"相关的所有产品。我的计划如下:

WITH RECURSIVE products_in_category AS (
SELECT
p.*,
c.parent_id
FROM
products p
INNER join categories c on c.id = p.category_id
WHERE 
category_id = 1
UNION
SELECT
p2.*,
c2.parent_id
FROM
products p2
INNER join categories c2 on c2.id = p2.category_id
INNER JOIN products_in_category s ON s.category_id = c2.parent_id
) SELECT
*
FROM
products_in_category limit 25;

我期望看到产品中的所有24行，但是我得到0。

A:用with RECURSIVE可以吗?，如果是怎么回事?

B:这样做是一个好方法吗(我确实在我的id字段上创建索引)?它是可扩展的(当我们有500个类别&比如10000个产品)?如果不是，还有什么替代方案?

Answer 1

回答"why doesn't this work"的间接问句……

首先，你应该只递归结构中需要递归的部分;

• 首先在递归CTE
中构建类别树
• 将产品加入CTE之外的

第二，你期望的结果不需要递归查询，只需要一个连接。

SELECT
p.*,
c.parent_id
FROM
products   p
INNER join
categories c
ON c.id = p.category_id

获取任意类别(及其子类别)中的所有产品…

WITH RECURSIVE
recursed_category AS
(
SELECT
id     AS root_category_id,
name   AS root_category_name,
id     AS category_id,
name   AS category_name
FROM
categories
UNION ALL
SELECT
p.root_category_id,
p.root_category_name,
c.id,
c.name
FROM
recursed_category   AS p  -- parent
INNER JOIN
categories          AS c  -- child
ON c.parent_id = p.category_id
)
SELECT
c.*,
p.id    AS product_id,
p.name  AS product_name
FROM
recursed_category   AS c  -- category
INNER join
products            AS p  -- product
ON c.id = p.category_id
WHERE
c.root_category_id = ???

这将构建多个树，每个类别一个，并传递到每个类别的每个子节点。

然后在WHERE子句中，您可以指定哪个你真正感兴趣的。

由于SQL是声明式的，优化器将外部WHERE子句应用于CTE，并且而不是建造所有的树，然后扔掉一些。

然后，又是简单查询;只需在外部查询中获取类别树并连接产品。

注意:这明确地从一个类别开始，然后向下浏览。如果你想知道一个产品属于哪个类别，不要使用这个。类似地创建递归CTE，但从子节点开始，然后浏览到。否则，优化器将不会能够将where子句推到CTE。

Answer 2

在这个例子中，你也可以看到我的Git Repos:https://github.com/WalterMatsuda/WITH-RECURSIVE-x-SELECT

对LOOP和子查询进行相同操作的区别是:在具有5个层次结构级别的1000000行表中

For Loops and subqueries 
Query OK (execution time: 15.359 sec; total time: 15.422 sec)
For With Recursive 
Query OK (execution time: 62 ms; total time: 125 ms) 

都返回1667个值。您可以尝试比最简单的更有效的循环逻辑，但是WITH RECURSIVE使用递归。另一方面，当你不需要访问多个级别，只想重复一些东西而不需要另一个条件重复时，循环比递归更有效。因此，两者都应该在各自的角色中使用。