with_without_cte.sql

-- child embed, cte better cost, almost half of subselect

-- cost=31864.78..31864.80
explain WITH pg_source AS (
  SELECT  "test"."clients".*, COALESCE((SELECT array_to_json(array_agg(row_to_json("projects".*))) FROM (SELECT  "test"."projects"."name" FROM  "test"."projects"  WHERE  "test"."projects"."client_id" = "test"."clients"."id"  ) "projects"), '[]') AS "projects" FROM  "test"."clients"    
) SELECT null AS total_result_set, 
         pg_catalog.count(_postgrest_t) AS page_total, 
         array[]::text[] AS header, 
         coalesce(array_to_json(array_agg(row_to_json(_postgrest_t))), '[]')::character varying AS body
FROM ( SELECT * FROM pg_source) _postgrest_t;

-- cost=63646.53..63646.55
explain SELECT null AS total_result_set, 
         pg_catalog.count(_postgrest_t) AS page_total, 
         array[]::text[] AS header, 
         coalesce(array_to_json(array_agg(row_to_json(_postgrest_t))), '[]')::character varying AS body
FROM ( 
  SELECT  "test"."clients".*, COALESCE((SELECT array_to_json(array_agg(row_to_json("projects".*))) FROM (SELECT  "test"."projects"."name" FROM  "test"."projects"  WHERE  "test"."projects"."client_id" = "test"."clients"."id"  ) "projects"), '[]') AS "projects" FROM  "test"."clients"    
) _postgrest_t;

-- many to many embed, cte better cost, almost half of subselect

-- cost=51284.80..51284.82
explain WITH pg_source AS (
  SELECT  "test"."tasks"."id", COALESCE ((SELECT array_to_json(array_agg(row_to_json("users".*))) FROM (SELECT  "test"."users"."id" FROM  "test"."users_tasks", "test"."users"  WHERE  "test"."users"."id" = "test"."users_tasks"."user_id" AND  "test"."tasks"."id" = "test"."users_tasks"."task_id"  ) "users"), '[]') AS "users" FROM  "test"."tasks"    
) SELECT null AS total_result_set, 
         pg_catalog.count(_postgrest_t) AS page_total, 
         array[]::text[] AS header, 
         coalesce(array_to_json(array_agg(row_to_json(_postgrest_t))), '[]')::character varying AS body
FROM ( SELECT * FROM pg_source) _postgrest_t;

-- cost=102490.60..102490.61
explain SELECT null AS total_result_set, 
       pg_catalog.count(_postgrest_t) AS page_total, 
       array[]::text[] AS header, 
       coalesce(array_to_json(array_agg(row_to_json(_postgrest_t))), '[]')::character varying AS body
FROM (
  SELECT  "test"."tasks"."id", COALESCE ((SELECT array_to_json(array_agg(row_to_json("users".*))) FROM (SELECT  "test"."users"."id" FROM  "test"."users_tasks", "test"."users"  WHERE  "test"."users"."id" = "test"."users_tasks"."user_id" AND  "test"."tasks"."id" = "test"."users_tasks"."task_id"  ) "users"), '[]') AS "users" FROM  "test"."tasks"    
) _postgrest_t;