优化Postgres中的SCD2连接

Question

现在，我正在为一个应用程序开发一个DB模式，它需要对几组不同的数值数据进行版本控制，这些数据必须结合在一起才能在视图中使用。应用程序要求我们能够“回顾”并在任意时间点找到这些多个变量的状态。

我构建了几个视图定义，使用子查询获取任意连接条件下的各种输入，并根据time_created字段检查版本。这不是一个严格的SCD2实现，但它是接近的。

您可以在这里看到一个与我们的模式类似(尽管非常简化)的实现：DB Fiddle链接。真正的模式有6个子查询/联接。

对于我的目的来说，这很好，只是当我们开始将它扩展到数千条“购买”记录时，我们就会产生很大的查询成本。对2000+行的查询采用~1s，这不适合我们的应用程序。

想到的第一个解决方案是创建一个表，该表将“购买”连接到其各种版本的输入表，在创建采购时由应用程序创建。这应该更有效率，但需要更多的模式复杂性。

有人能给我建议如何最好地规划我的模式/查询，以便对这种设置最优吗？

Answer 1

我在这个问题上找到了一个比较令人满意的解决办法。我觉得忽略这一点有点愚蠢，但你可以把它归结为代表我进行“过早优化”的情况。按照维基百科中的学术例子，我在我的SCD表中省略了end_date列。我认为这并不是特别必要，因为要“倒带”表并找到正确的SCD行，所需做的就是查找第一行，其start_date小于或等于表排序时的查询日期。但这需要一个表排序才能正常工作。

我的查询中的主要瓶颈是根据时间戳对一个中等大的( 2500+行)表进行排序。通过添加“end_date”列并将查询更改为使用(简化的示例)，我设法解决了这个问题：

LEFT JOIN LATERAL ( SELECT * FROM tax t
  WHERE p.time_created >= t.time_created AND p.time_created <= COALESCE(t.time_ended, '9999-12-30'::date)) current_tax ON true
....

这将查询从~1秒减少到<100 to。我尝试为2500+行表中的字段添加索引，但这似乎对查询时间没有太大影响，但可能我在那里犯了一些错误。无论如何，这似乎更加优化。

更新后的DBFiddle可以看到这里。

对于如何更好地表示这个值，或者如何在time_ended列中更好地考虑time_ended值，我会有一些建议。