问题在于对数据库的查询需要极长的时间(最多 400 ms)
SELECT * FROM production_category AS cc
INNER JOIN LATERAL (
SELECT cc.id AS id, SUM(products) AS p_count FROM (
SELECT cc.id AS parent_id, category_id, COUNT(id) AS products FROM production_product_categories WHERE category_id IN (
SELECT id FROM production_category WHERE lft <= cc.rght AND lft >= cc.lft AND tree_id = cc.tree_id)
AND product_id IN ( SELECT id FROM production_product WHERE manufacturer_id = 15 )
GROUP BY category_id
) AS sub_cc
GROUP BY parent_id ) AS cp USING(id)
WHERE cp.p_count > 0;
结构如下:
有一个制造商(manufacturer),他的产品(production_product)和任何产品都可以属于一个或多个类别(production_category)。也可能有类别中没有该制造商的产品。
任务是选择包含该制造商产品的类别。
所以问题出现了:要么我对查询太聪明了,要么应该对数据库进行非规范化并将制造商的类别信息存储在单独的表中(确切关系(制造商 ID,类别 ID))。
PostgreSQL (9.3.4)
Nested Loop (cost=207.57..129375.37 rows=42 width=223)
-> Seq Scan on production_category cc (cost=0.00..25.20 rows=620 width=191)
-> Subquery Scan on cp (cost=207.57..208.62 rows=1 width=36)
Filter: (cc.id = cp.id)
-> HashAggregate (cost=207.57..208.09 rows=42 width=12)
Filter: (sum((count(production_product_categories.id))) > 0::numeric)
-> HashAggregate (cost=206.41..206.83 rows=42 width=8)
-> Nested Loop Semi Join (cost=9.23..206.20 rows=42 width=8)
-> Nested Loop (cost=8.94..106.81 rows=45 width=12)
-> Bitmap Heap Scan on production_category (cost=4.31..12.78 rows=1 width=4)
Recheck Cond: ((lft <= cc.rght) AND (lft >= cc.lft))
Filter: (tree_id = cc.tree_id)
-> Bitmap Index Scan on production_category_caf7cc51 (cost=0.00..4.31 rows=3 width=0)
Index Cond: ((lft <= cc.rght) AND (lft >= cc.lft))
-> Bitmap Heap Scan on production_product_categories (cost=4.64..93.58 rows=45 width=12)
Recheck Cond: (category_id = production_category.id)
-> Bitmap Index Scan on production_product_categories_b583a629 (cost=0.00..4.62 rows=45 width=0)
Index Cond: (category_id = production_category.id)
-> Index Scan using production_product_pkey on production_product (cost=0.29..2.20 rows=1 width=4)
Index Cond: (id = production_product_categories.product_id)
Filter: (manufacturer_id = 15)
我认为它应该是这样的:
太糟糕了,现在没有什么可检查的。
在我看来,以下请求的结果不错(大约 100 毫秒):
我想最好在一个请求中完成。