How to optimize query by preventing repetitive subquery

I have query that uses a subquery (select min/max(date) from stage.stage_net_subscription_010_filter_and_net_pds) to find min and max dates of a table.

It seems that the subqueries are repeated very often.

• Is there a way to rewrite the query without CTEs such that the subqueries do not get executed for every loop

• One other option to speed up the query is see, would be to use generate series in stead of selecting the dates from a table with dates. Any clue if that will make a difference?

Query:

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);

You can use a lateral cross join, although I don't understand why you want to avoid a 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

StackExchange Database Administrators Q#285191, answer score: 4