Postgres 16

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create table test (id int, broker text,affiliate text, leads int);
insert into test values
  (1, 'broker1','aff1',1)
 ,(2, 'broker1','aff2',2)
 ,(3, 'broker2','aff2',4);

insert into test 
select g,
       'broker'||(random()*4e3)::int,
       'aff'||(random()*1e3)::int,
       (random()*40)::int
from generate_series(4,2e4,1)_(g);

select * from test tablesample bernoulli(.2) limit 8;

CREATE TABLE



INSERT 0 3



INSERT 0 19997




id
broker
affiliate
leads


366
broker2389
aff797
19

2304
broker290
aff948
7

2346
broker2593
aff159
24

2374
broker3719
aff410
23

3097
broker3494
aff962
37

4395
broker3415
aff933
9

5595
broker1230
aff168
30

6333
broker1855
aff81
20



SELECT 8


create function report2_1(arg_group text default '', arg_where text default '')
returns setof json language plpgsql as
$$
begin
  return query execute format(
    'select to_json(t) from (select %s sum(leads) from test %s %s) t', 
    case when arg_group = '' then '' else arg_group || ',' end,
    case when arg_where = '' then '' else 'where ' || arg_where end,
    case when arg_group = '' then '' else 'group by ' || arg_group end
   ); 
end; 
$$;

CREATE FUNCTION


DROP FUNCTION IF EXISTS report2_1_1(TEXT,TEXT);
create function report2_1_1(arg_group text default '', arg_where text default '')
returns setof json language plpgsql as
$$
begin
  return query execute format(
    'select to_json(t) from (select %s sum(leads) from test %s %s) t', 
    case when arg_group <>'' then arg_group  || ','       end,
    case when arg_where <>'' then 'where '   || arg_where end,
    case when arg_group <>'' then 'group by '|| arg_group end
   ); 
end; 
$$;

DROP FUNCTION



CREATE FUNCTION


DROP FUNCTION IF EXISTS report2_1_2(TEXT,TEXT);
create function report2_1_2(arg_group text default '', arg_where text default '')
returns setof json language plpgsql as
$$
begin
  return query execute format(
    'select to_json(t) from (select %s sum(leads) from test %s %s) t', 
    nullif(arg_group,'') || ',',
    'where '   || nullif(arg_where,''),
    'group by '|| nullif(arg_group,'')
   ); 
end; 
$$;

DROP FUNCTION



CREATE FUNCTION


DROP FUNCTION IF EXISTS report2_1_3(TEXT,TEXT);
create function report2_1_3(arg_group text default null, arg_where text default 'true')
returns setof json language plpgsql as
$$
begin
  return query execute format(
    'select to_json(t) from (select %s sum(leads) from test where %s %s) t', 
    arg_group || ',',
    arg_where,
    'group by ' || arg_group
   ); 
end; 
$$;

DROP FUNCTION



CREATE FUNCTION


create table timings (attempt_n int, variant text, started timestamp, finished timestamp);


do $f$
declare i int; started_ timestamp; finished_ timestamp; variant_ text;
begin
for i in 1..100 loop foreach variant_ in array array['report2_1',
                                                    'report2_1_1',
                                                    'report2_1_2',
                                                    'report2_1_3'] loop
   started_=clock_timestamp();
   execute format($c$select %s('broker',arg_where=>'mod(id,2)=0');$c$, variant_);
   finished_=clock_timestamp();
   insert into timings select i,variant_,started_,finished_;
   started_=clock_timestamp();
   execute format($c$select %s(arg_where=>$w$mod(id,2)=0$w$);$c$,variant_);
   finished_=clock_timestamp();
   insert into timings select i,variant_,started_,finished_;
   started_=clock_timestamp();
   execute format($c$select %s('broker');$c$,variant_);
   finished_=clock_timestamp();
   insert into timings select i,variant_,started_,finished_;
   started_=clock_timestamp();
   execute format($c$select %s();$c$, variant_);
   finished_=clock_timestamp();
   insert into timings select i,variant_,started_,finished_;
   started_=clock_timestamp();
end loop; end loop; 
end $f$;
select variant,
       avg(finished-started)"avg execution time - lower is better" 
from timings group by variant order by 2 asc;

CREATE TABLE



DO




variant
avg execution time - lower is better


report2_1_2
00:00:00.007505

report2_1_3
00:00:00.007506

report2_1
00:00:00.007518

report2_1_1
00:00:00.007528



SELECT 4
id	broker	affiliate	leads
366	broker2389	aff797	19
2304	broker290	aff948	7
2346	broker2593	aff159	24
2374	broker3719	aff410	23
3097	broker3494	aff962	37
4395	broker3415	aff933	9
5595	broker1230	aff168	30
6333	broker1855	aff81	20
variant	avg execution time - lower is better
report2_1_2	00:00:00.007505
report2_1_3	00:00:00.007506
report2_1	00:00:00.007518
report2_1_1	00:00:00.007528