unnecessary_subplans.sql

CREATE TABLE users_table (user_id int, time timestamp, value_1 int, value_2 int, value_3 float, value_4 bigint);
SELECT create_distributed_table('users_table', 'user_id');

INSERT INTO users_table
SELECT i,
       now(),
       i,
       i,
       i,
       i
FROM generate_series(0, 100000)i;

WITH y AS
  (SELECT pc.value_1
   FROM users_table pc
   WHERE pc.value_4 in
       (SELECT value_4
        FROM users_table p
        GROUP BY p.value_4
        HAVING count(*) > 0) ) SELECT * FROM y;


 CREATE OR REPLACE FUNCTION the_function_has_the_same_query() RETURNS TABLE(datasource int) LANGUAGE PLPGSQL AS $function$
 declare
         tz text;
         comp_name text;
         begin
          --   select local_time_zone from pbi.tenant_id_info where tenant_id = tenant_idd into tz;
          --   select abbreviation_tbl_name from pbi.tenant_id_info where tenant_id = tenant_idd into comp_name;
         return query
WITH y AS
  (SELECT pc.value_1
   FROM users_table pc
   WHERE pc.value_4 in
       (SELECT value_4
        FROM users_table p
        GROUP BY p.value_4
        HAVING count(*) > 0) ) SELECT * FROM y;

            end;
 $function$ ;




--  the same query generates way too many intermediate results 
SET citus.log_remote_commands TO ON;
SELECT the_function_has_the_same_query();