时间戳

首页 > 解决方案 > Postgres 11.7 中具有太多连接或多态关系的表的设计问题

postgresql - Postgres 11.7 中具有太多连接或多态关系的表的设计问题

问题描述

我得到了一张我不知道如何设计的桌子。我希望有一些设计建议或正确方向的指示。该表被调用edge，旨在存储一些事件跟踪，以及链接到许多可能的查找表的 ID。省略除 ID 之外的所有内容，这是表包含的所有 UUID：

ID
InvID
OrgID
FacilityID

FromAssemblyID
FromAssociatedTo
FromAssociatedToID
FromClinicID
FromFacilityDepartmentID
FromFacilityID
FromFacilityLocationID
FromScanAtFacilityID
FromScanID
FromSCaseID
FromSterilizerLoadID
FromWasherLoadID
FromWebUserID

ToAssemblyID
ToAssociatedTo
ToAssociatedToID
ToClinicID
ToFacilityDepartmentID
ToFacilityID
ToFacilityLocationID
ToNodeDTS
ToScanAtFacilityID
ToScanID
ToSCaseID
ToSterilizerLoadID
ToUserName
ToWasherLoadID
ToWebUserID

可能加入的 ID 数量庞大。我记得读过 Postgres 规划器在你有十几个连接时会放弃。这个想法是有太多的排列需要探索，以至于计划时间很快就会超过查询时间。如果你把它归结为，“from”和“to”链接在所有这些字段中只会有一个键值。因此，实现为多态/混杂关系，如下所示：

ID
InvID
OrgID
FacilityID
FromID
FromType
ToID
ToType
ToWebUserID

这张桌子将是巨大的，所以速度是/将是一个考虑因素。

我鼓励作者不要使用多态设计，尽管它的吸引力是显而易见的。（我喜欢 Karwin 的SQL Antipatterns书。）但是现在，面对将近三打 ID，我有点难过。

这类问题有通用解决方案吗？即，您在哪里有一个像这样的中央表，可以连接到各种可能的表？我没有数据仓库背景，但这看起来有点像。（此表的作者读过 Kimball 的书，但也没有做过任何数据仓库实现。）

重要提示：我们JOIN用于查找可能会更改的相关值，我们不会使用它来更改结果集的大小。只是假装它会一直如此LEFT JOIN。

考虑到这一点，我想到的是跳过加入From和ToID，而是使用自定义函数调用从相关表中查找所需的值。喜欢（伪代码）

GetUserName(uuid) : citext
...and os on for other values of interest in this and other tables...

当 UUID 为 0000 等时，该函数将返回 ''。

我很欣赏这不是 SO 历史上最清晰的问题，我希望能在富有成果的方向上得到指点。

标签： postgresqljoinpolymorphismdata-warehouse

解决方案

这有点“过早优化”（这是邪恶的根源）的味道，基于您“记得阅读”的内容，所以也许对连接优化的一些启示会有所帮助。

我在此类问题中遵循的一条经验法则是对事物进行建模，以便您的查询变得简单自然。经验表明，这通常会带来良好的性能。

我假设您显示的表是星型模式的事实表，并且外键指向许多维度表，因此您的查询看起来像

SELECT ...
FROM fact
   JOIN dim1 ON fact.dim1_id = dim1.id
   JOIN dim2 ON fact.dim3_id = dim2.id
   JOIN dim3 ON fact.dim3_id = dim3.id
   ...
WHERE dim1.col1 = ...
  AND dim2.col2 BETWEEN ... AND ...
  AND dim3.col3 < ...
  ...

现在 PostgreSQL 默认只考虑前八个表 ( ) 的所有连接排列，join_collapse_limit其余的表只是按照它们在查询中出现的顺序连接。

此外，如果表的数量达到 12 ( geqo_threshold) 的阈值，遗传查询优化器将接管，该组​​件通过随机选择的执行计划（真的！）通过突变和适者生存来模拟进化，因此并不总是出现为相同的查询提供相同的执行计划。

因此，我的建议是以前七个维度表是最有可能最显着减少结果行数的方式编写查询（基于WHERE条件）。您也可以增加join_collapse_limit，因为如果您的查询无论如何都需要很长时间才能运行，您可以轻松地让计划者花更多时间考虑最佳计划。

然后，您将设置geqo = off禁用基因查询优化器。

如果您根据这些原则设计查询，您应该能够在不弄乱数据模型的情况下获得良好的执行计划。

推荐阅读