Eu tenho uma visualização de banco de dados PostgreSQL (v15) que acumula um monte de dados por usuário para uma única organização, para exibir um relatório de dados como taxas devidas/pagas por usuário, etc. insere e executa em menos de um segundo, o que é perfeitamente rápido para este caso de uso (um relatório de UI). Até agora tudo bem.
Para algumas organizações, também preciso produzir um resumo DESSES resumos, ou seja, um rollup para grupos de organizações que resuma os mesmos dados por usuário, por organização, para uma organização mãe. Alto nível, estou tentando selecionar primeiro as organizações de destino (que devem produzir um conjunto de <150 linhas) e, em seguida, unir minha visualização de organização única existente a esse conjunto para adicionar os dados agregados da visualização original, mas por coleta organização.
Embora a consulta real lide com muito mais colunas e agregações na saída, esta versão reduzida resume a lógica principal da consulta:
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;
A visualização que está sendo unida é bastante complexa e também depende de algumas subvisualizações, mas quando executada isoladamente é muito rápida. O member_orgsCTE também é muito rápido por si só (~30ms) e sempre resulta em <150 registros. Conforme mostrado acima, se eu juntar os dois em IDs específicos (como teste), a consulta geral será extremamente rápida (~150ms). No entanto, ao unir as colunas entre o CTE e a visualização (o que preciso fazer), o desempenho geral cai para mais de 40 segundos .
Sinto que devo estar perdendo alguma coisa boba, pois não entendo como juntar a visualização a um conjunto de 133 registros (no caso real, estou depurando) poderia explodir o tempo de forma tão dramática. Meu entendimento era que o CTE materializaria sua saída, permitindo que a junção externa funcionasse apenas naquele conjunto de resultados, o que considero muito eficiente. Eu poderia escrever o código do aplicativo para executar o CTE, depois iterar sobre os IDs e executar a consulta externa individualmente 133 vezes em muito menos tempo do que esta consulta está demorando.
Por favor, perdoe os enormes planos de consulta, pois as consultas reais (com visualizações subjacentes) são bastante complicadas, mas foram criadas com uma versão um pouco mais complexa do exemplo de consulta reduzida mostrado acima (embora a lógica seja a mesma). A única diferença entre as duas execuções foi o uso de IDs específicos, em vez da união em colunas, exatamente como mostrado no código de exemplo acima.
- Versão rápida (por IDs específicos) - executada em 44ms
- Versão lenta (junção em colunas de ID) - executada em mais de 41 segundos
Agradecemos antecipadamente e deixe-me saber se posso fornecer mais detalhes.