我需要帮助在 Postgres 中为大型数据集和/或可用于检索数据的后续查询设计更高效的存储模式。
情况是这样的。我有一个巨大的产品列表(超过 500 万)以及相关的子产品。
例如,这里有一些名义上的产品和子产品组。如您所见,子产品可能与一个或多个主要产品相关联。
Product1 Product2 Product3 Product4
-SubProA -SubProD -SubProC -SubProA
-SubProB -SubProE -SubProF -SubProD
-SubProC -SubProG
我需要做的是给定一个特定的子产品,有效地识别存在搜索子产品的所有其他独特的子产品。在示例中,如果我选择 SubProC,它应该检索 SubProA、SubProB 和 SubProF。
我最初的尝试包括创建一个产品表、一个子产品表,然后只是一个简单的两者之间的关系表。
Products
------------------
| ID | Name |
| 1 | Product 1 |
| 2 | Product 2 |
| 3 | Product 3 |
| 4 | Product 4 |
Sub-Products
----------------
| ID | Name |
| 1 | SubProA |
| 2 | SubProB |
| 3 | SubProC |
| 4 | SubProD |
| 5 | SubProE |
Relationships
---------------------------------
| ID | ProductID | SubProductID |
| 1 | 1 | 1 |
| 2 | 1 | 2 |
| 3 | 1 | 3 |
| 4 | 2 | 4 |
| 5 | 2 | 5 |
基本上我正在做的是对子产品进行简单查找以获取 ID,然后从关系表中查询其相应的 ProductID,然后循环遍历所有这些产品以捕获其他 SubProductID,同时与子产品表连接到获取子产品名称。
select ID from sub_products where name = $1;
result = select ProductID from relationships where SubProductID = $1;
for each ProductID in result:
select s.Name from sub_products s join relationships r on (s.ID = r.SubProductID) where r.ProductID = $1;
虽然这对于小型数据集来说已经足够好了,但由于所有单独的查找和循环,它对于数百万条记录来说非常慢。一些子产品可能与成千上万的产品相关联,但相关子产品的相应列表要小得多,我的简单关系表无法有效地捕捉到这一点。
那么,有没有更好的数据模式或者我可以使用的一组查询?