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 &gt;= (select min(date) from stage.stage_net_subscription_010_filter_and_net_pds)
  and   date &lt;= (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 &quot;date&quot;, 
       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 &gt;= mm.min_date
  and   date &lt;= mm.max_date