skip to Main Content

I have a PostgreSQL (v15) DB view that rolls up a bunch of data per user for a single organization, to display a report of data like fees owed/paid per user, etc. This is executed with an org ID and date range as inputs, and performs sub-second, which is perfectly fast for this use case (a UI report). So far so good.

For some orgs, I also need to produce a summary of THOSE summaries, i.e. a rollup for groups of organizations that summarizes the same data per user, per organization, for a parent organization. High-level, I am trying to select the target orgs first (which should produce a set of < 150 rows), then join my existing single-org view to that set to add in the aggregated data from the original view, but per collected organization.

While the real query deals with a lot more columns and aggregations in the output, this reduced version summarizes the core query logic:

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;

The view being joined is fairly complex, and relies on some sub-views as well, but when executed in isolation it’s very fast. The member_orgs CTE is also very fast on its own (~30ms), and always results in < 150 records. As shown above, if I join the two on specific IDs (as a test), the overall query is extremely fast (~150ms). However, when joining on the columns between the CTE and the view (what I need to do), overall performance tanks to 40+ seconds.

I feel like I must be missing something silly, as I don’t understand how joining the view to a set of 133 records (in the real case I’m debugging) could explode the time so dramatically. My understanding was that the CTE would materialize its output, allowing the outer join to work on just that result set, which I would think to be very efficient. I could write application code to run the CTE, then iterate over the IDs and execute the outer query individually 133 times in far less time than this query is taking.

Please pardon the huge query plans, as the real queries (with underlying views) are pretty complicated, but these were created with a slightly more complex version of the reduced query example shown above (though the logic of it is the same). The only difference between the two runs was using specific IDs, vs joining on columns, exactly as shown in the example code above.

Thanks in advance, and let me know if I can provide any additional details.

2

Answers


  1. Chosen as BEST ANSWER

    We ended up converting these views into a series of materialized CTEs, and inverting some of the dependencies between them to reduce duplication. Originally the base DB views were intended to be reusable in a way that ultimately became more of a hinderance than benefit, and so for the sake of performance, we ended up with a (large) block of raw SQL that we're executing directly from the Rails code. Not ideal for maintenance purposes, but we're now getting sub-second SQL execution for all use cases.


  2. `select
        *
    from
        (
        select
            distinct member_orgs.billing_period_id,     member_orgs.billing_period_started_on,
            member_orgs.billing_period_ended_on,
            member_orgs.organization_name,
            payments_received_count
        from
            member_orgs) m
    left join (
        select
            billing_period_id,
            organization_id .
       SUM(case when details.received_amount > 0 then 1 else 0 end) as payments_received_count
        from
            per_athlete_fee_details_view
        group by
            billing_period_id,
            organization_id )details
       on
        details.billing_period_id = m.billing_period_id
        and details.organization_id = m.organization_id;
    

    `

    Login or Signup to reply.
Please signup or login to give your own answer.
Back To Top
Search