我有一个 PostgreSQL (v15) 数据库视图,它汇总单个组织的每个用户的大量数据,以显示数据报告,例如每个用户所欠/支付的费用等。这是使用组织 ID 和日期范围执行的输入并执行亚秒级的操作,对于此用例(UI 报告)而言,速度非常快。到目前为止,一切都很好。
对于某些组织,我还需要生成这些摘要的摘要,即组织组的汇总,其中汇总了父组织的每个用户、每个组织的相同数据。高级别,我尝试首先选择目标组织(应生成一组 < 150 行),然后将我现有的单一组织视图加入到该组中,以添加原始视图中的聚合数据,但每个收集的数据组织。
虽然真正的查询在输出中处理更多的列和聚合,但这个简化版本总结了核心查询逻辑:
WITH member_orgs AS (
-- CTE returns 133 rows in ~30ms
SELECT
bp.id AS billing_period_id,
bp.started_on AS billing_period_started_on,
bp.ended_on AS billing_period_ended_on,
org.name AS organization_name,
bp.organization_id
FROM billing_periods bp
JOIN organizations org
ON org.id = bp.organization_id
WHERE
bp.paid_by_organization_id = 123
AND (
bp.started_on >= '2023-07-01'
AND bp.ended_on <= '2024-06-30'
)
AND bp.organization_id != 123
)
SELECT
member_orgs.billing_period_id,
member_orgs.billing_period_started_on,
member_orgs.billing_period_ended_on,
member_orgs.organization_name,
-- this is one example aggregation, the real query has more of these:
SUM(CASE WHEN details.received_amount > 0 THEN 1 ELSE 0 END) AS payments_received_count
FROM member_orgs
LEFT JOIN per_athlete_fee_details_view details
-- SLOW (~40 SECONDS):
-- ON details.billing_period_id = member_orgs.billing_period_id
-- AND details.organization_id = member_orgs.organization_id
-- FAST (~150ms):
ON details.billing_period_id = 1234
AND details.organization_id = 3456
GROUP BY
member_orgs.billing_period_id,
member_orgs.billing_period_started_on,
member_orgs.billing_period_ended_on,
member_orgs.organization_name;
所连接的视图相当复杂,并且还依赖于一些子视图,但是当单独执行时,速度非常快。CTEmember_orgs
本身也非常快(约 30 毫秒),并且始终生成 < 150 条记录。如上所示,如果我将两个特定 ID 连接起来(作为测试),则整体查询速度非常快(约 150 毫秒)。然而,当连接 CTE 和视图之间的列时(我需要做的),整体性能下降到 40秒以上。
我觉得我一定错过了一些愚蠢的事情,因为我不明白如何将视图连接到一组 133 条记录(在我正在调试的实际情况下)会如此戏剧性地增加时间。我的理解是,CTE 将具体化其输出,允许外部联接仅处理该结果集,我认为这是非常有效的。我可以编写应用程序代码来运行 CTE,然后迭代 ID 并单独执行外部查询 133 次,所需时间远少于此查询所花费的时间。
请原谅庞大的查询计划,因为真正的查询(带有底层视图)非常复杂,但这些查询是使用上面所示的简化查询示例的稍微复杂的版本创建的(尽管其逻辑是相同的)。两次运行之间的唯一区别是使用特定的 ID,而不是在列上连接,完全如上面的示例代码所示。
提前致谢,如果我可以提供任何其他详细信息,请告诉我。
我们最终将这些视图转换为一系列具体化的 CTE,并反转它们之间的一些依赖关系以减少重复。最初,基本数据库视图的目的是可重用,但最终成为一种阻碍而不是好处,因此为了性能,我们最终得到了一个(大)原始 SQL 块,我们直接从中执行Rails 代码。对于维护目的来说并不理想,但我们现在可以为所有用例实现亚秒级 SQL 执行。