PostgreSQL WITH RECURSIVE分类商品查询问题及性能咨询

背景 #

我有一个商品系统，规则如下：

• 若分类下未关联商品，则可拥有多层子分类；
• 若分类下无子分类，则可关联任意数量的商品。

数据库结构与数据 #

注：为简化展示，已删除两张表的其他字段/约束

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);

问题描述 #

想要获取"Electronic Devices"（ID=1）分类下的所有商品，尝试了以下WITH RECURSIVE查询语句：

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个商品的场景）？若不合理，有哪些替代方案？

A. 可以用WITH RECURSIVE实现，正确写法如下 #

原查询的问题在于起始条件直接查找category_id=1的商品，但根据系统规则，有子分类的分类不会关联商品，导致起始查询返回空，递归自然无结果。正确思路是先递归找出目标分类下的所有子分类（含自身），再关联商品表获取对应商品。

正确查询语句 #

WITH RECURSIVE category_tree AS (
    -- 起始节点：目标分类
    SELECT id FROM categories WHERE id = 1
    UNION ALL
    -- 递归获取所有子分类
    SELECT c.id 
    FROM categories c
    INNER JOIN category_tree ct ON c.parent_id = ct.id
)
-- 关联商品表，获取所有子分类下的商品
SELECT p.*
FROM products p
INNER JOIN category_tree ct ON p.category_id = ct.id;

逻辑说明 #

1. category_tree递归CTE先找出ID=1的分类及其所有层级的子分类ID；
2. 将商品表与该分类ID集合关联，即可得到目标分类下的所有商品，返回24条预期结果。

B. 实现合理性与可扩展性分析 #

修正后实现的合理性与扩展性 #

修正后的WITH RECURSIVE实现是合理的，在500个分类、10000个商品的场景下具备良好可扩展性：

• 递归查询分类树的时间复杂度为O(n)（n为分类数量），500个分类的递归开销极小；
• 商品表关联分类ID集合时，若products.category_id上有单独索引（建议创建，而非仅依赖主键索引），查询效率会非常高；
• PostgreSQL对递归CTE的优化成熟，这类层级查询的性能表现稳定。

替代方案 #

如果分类层级极深（如超过100层）或数据量极大（百万级分类/商品），可考虑以下方案：

1. 预计算分类路径：在categories表新增path字段（如用ltree类型或字符串拼接路径，例如1/2/6），新增分类时同步更新路径。查询时直接通过path LIKE '1/%'筛选所有子分类，再关联商品表。该方式查询速度更快，但需维护路径一致性（新增/修改分类时要更新）。
2. 嵌套集模型（Nested Set）：用左、右值存储分类结构，查询子分类时只需筛选lft > 父分类lft AND rgt < 父分类rgt的分类。适合频繁查询子分类的场景，但修改分类结构（新增、删除）的复杂度较高。

内容的提问来源于stack exchange，提问作者kouroshtajalliepour