Início / dba / Perguntas / 285191
Accepted
Bart Jonk
Asked: 2021-02-12 01:43:52 +0800 CST

Como otimizar a consulta evitando subconsultas repetitivas

  • 772

Eu tenho uma consulta que usa uma subconsulta ( select min/max(date) from stage.stage_net_subscription_010_filter_and_net_pds) para encontrar datas mínimas e máximas de uma tabela.

Parece que as subconsultas são repetidas com muita frequência.

  1. Existe uma maneira de reescrever a consulta sem CTEs , de modo que as subconsultas não sejam executadas para cada loop
  2. Uma outra opção para agilizar a consulta é ver, seria usar gerar série ao invés de selecionar as datas de uma tabela com datas. Alguma pista se isso vai fazer a diferença?

Consulta:

SELECT 
         dim_date.date AS date
         , stage_net_subscription_020_classes.client_id AS client_id
         , stage_net_subscription_020_classes.product_id AS product_id
  FROM star.dim_date dim_date
  CROSS JOIN stage.stage_net_subscription_020_classes stage_net_subscription_020_classes
  where date >= (select min(date) from stage.stage_net_subscription_010_filter_and_net_pds)
  and   date <= (select max(date) from stage.stage_net_subscription_010_filter_and_net_pds);
postgresql postgresql-performance

2 respostas

  1. Best Answer

    Você pode usar uma junção cruzada lateral, embora eu não entenda por que você deseja evitar um CTE:

    SELECT dim_date.date AS "date", 
       stage_net_subscription_020_classes.client_id AS client_id,
       stage_net_subscription_020_classes.product_id AS product_id
FROM star.dim_date dim_date
  CROSS JOIN stage.stage_net_subscription_020_classes
  CROSS JOIN LATERAL (
    select min(date) as min_date, max(date) as max_date 
    from stage.stage_net_subscription_010_filter_and_net_pds
  ) as mm
  where date >= mm.min_date
  and   date <= mm.max_date
    • 4 
  2. WITH cte AS (select MIN(date) mindate
                  , MAX(date) maxdate
             from stage.stage_net_subscription_010_filter_and_net_pds)
SELECT dim_date.date AS date
     , stage_net_subscription_020_classes.client_id AS client_id
     , stage_net_subscription_020_classes.product_id AS product_id
FROM star.dim_date dim_date
CROSS JOIN stage.stage_net_subscription_020_classes stage_net_subscription_020_classes
CROSS JOIN cte
WHERE dim_date.date BETWEEN cte.mindate AND cte.maxdate;
    • 2

relate perguntas