在PostgreSQL中将视图连接到快速子查询/CTE的糟糕性能

我有一个PostgreSQL(v15)DB视图,它汇总了单个组织的每个用户的一堆数据,以显示每个用户所欠/支付的费用等数据的报告。这是以组织ID和日期范围作为输入来执行的,并执行亚秒,这对于这个用例(UI报告)来说非常快。到现在为止,一直都还不错。

对于一些组织,我还需要生成THOSE摘要的摘要,即 组 为父组织按用户、按组织汇总相同数据的组织数。在高层,我试图首先选择目标组织(它应该产生一组<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;

连接的视图相当复杂,也依赖于一些子视图,但在隔离执行时速度非常快。这个 member_orgs CTE本身也非常快(~30ms),并且总是导致<150条记录。如上所示,如果我在特定的ID上加入这两个(作为测试),那么整个查询速度非常快(~150ms)。然而,当在CTE和视图之间的柱上连接时(我需要做的是),整体性能下降到40+ 秒 .

我觉得我一定错过了一些愚蠢的东西,因为我不明白将视图连接到一组133条记录(在我正在调试的真实案例中)是如何戏剧性地爆炸时间的。我的理解是,CTE将实现其输出,允许外部联接仅处理该结果集,我认为这是非常有效的。我可以编写应用程序代码来运行CTE,然后迭代ID并单独执行外部查询133次,所用时间远少于此查询所用的时间。

请原谅庞大的查询计划,因为真正的查询(具有底层视图)非常复杂,但这些查询是用上面显示的简化查询示例的稍微复杂的版本创建的(尽管其逻辑相同)。两次运行之间的唯一区别是使用特定的ID,而不是在列上连接,正如上面的示例代码所示。

• Fast version (通过特定ID)-在44ms内执行
• Slow version (ID列上的联接)-在41秒内执行

提前谢谢,如果我能提供任何额外的细节,请告诉我。