--bad code:
SELECT t.a, t.b, t.c
FROM foo t
where t.key not in (select pk from big_tbl)

--good code:
with a as
             (select pk from big_tbl intersect select key from foo)
SELECT t.a, t.b, t.c
from foo t
where t.key not in (select pk from a)

--sub-optimal code:
select dim1, dim2, dim3, sum(m1), sum(m2), ..., sum(m50)
from foo
group by 1, 2, 3

--good code
with a as
         (select dim1, dim2, dim3, sum(m1), sum(m2), ..., sum(m25)
          group by 1, 2, 3),
     b as
         (select dim1, dim2, dim3, sum(m26) m26_agg, sum(m27) m27_agg, ..., sum(m50) m50_agg
          group by 1, 2, 3)
select a.*, b.m26_agg, b.m27_agg, ..., b.m50_agg
from a, b
where a.dim(1, 2, 3) = b.dim(1, 2, 3) -- краткая форма записи для join-а по всем измерениям /  short form of a.dim1 = b.dim1 and ... and a.dim3 = b.dim3

CREATE OR REPLACE FUNCTION public.fn_foo()
RETURNS boolean
LANGUAGE plpgsql
AS $function$
    begin
      set gp_autostats_mode_in_functions = on_no_stats; -- безуслловный сбор статистики по таблицам, модифицируемым в функции / always gather stat for all tables modified by function
  -- Другие значения / other values: 
  --   none - не собирать статистику / don't gather stat, 
  --   on_change - собирать статистику если число измененных записей превысило заданный порог  / gather stat if  the number of modified records exceeds specified threshold
  ...

-- bad code
with recursive
                                agreement_chain as
                                (

                                select
                                    AGREEMENT_PK,
                                    PREVIOUS_AGREEMENT_PK,
                                    counter_rec,
                                from foo 
                                where is_first_agreement = 'y'
                                union all
                                select
                                    t1.AGREEMENT_PK, 
                                    t2.AGREEMENT_PK as PREVIOUS_AGREEMENT_PK,
                                    t2.counter_rec + 1,
                                from foo t1
                                inner join  agreement_chain t2  on t2.AGREEMENT_PK = t1.PREVIOUS_AGREEMENT_PK
                                                                and t2.counter_rec < 10 -- ограничение по рекурсии чтобы не уйти в бесконечный цикл / limitation on recursion to avoid an infinite loop
                                                              
                                )

                                select
                                    AGREEMENT_PK,
                                    PREVIOUS_AGREEMENT_PK
                                from agreement_chain 
                
-- good code
create table foo_mirror as select * from foo distribued by (PREVIOUS_AGREEMENT_PK);              
                
with recursive
                                agreement_chain as
                                (

                                select
                                    AGREEMENT_PK,
                                    PREVIOUS_AGREEMENT_PK,
                                    counter_rec,
                                from foo 
                                where is_first_agreement = 'y'
                                union all
                                select
                                    t1.AGREEMENT_PK, 
                                    t2.AGREEMENT_PK as PREVIOUS_AGREEMENT_PK,
                                    t2.counter_rec + 1,
                                from foo_mirror t1
                                inner join  agreement_chain t2  on t2.AGREEMENT_PK = t1.PREVIOUS_AGREEMENT_PK
                                                                and t2.counter_rec < 10
                                                              
                                )

                                select
                                    AGREEMENT_PK,
                                    PREVIOUS_AGREEMENT_PK
                                from agreement_chain

--bad code (метод соединения/ join method - Nested Loop )
select foo.*, d.date_from, d.date_to
from foo
         join dim_periods d on
    foo.dt between d.date_from d.and date_to


--good code ( вместо NL оптимизатор использует в итоге  Hash Join / Instead of NL optimizer finally uses Hash Join )
with a as 
(select distinct dt from foo),
x as ( a.dt, s.* from dim_periods s, a 
where a.dt between s.date_from s.and date_to ) -- используем NL лишь для малой доли от foo / use NL path only for tiny % of foo

select foo.*, x.date_from, x.date_to
from foo join x on foo.dt = x.dt

select deal_rk
      from fn_foo( p_dt_from :='2024-09-01', p_dt_to :='2024-09-02')
      where 1 = 1
        and src_cd = 'IMOEX'
        and coalesce(add_info_04, '&&&') not like '%NON_RESIDENT%'
        and id in (select risk_contract_id from foo) -- плохой фильтр если даже таблица пустая, но без статистики / bad filter if even the table is empty but without statistics

-- bad query
select a.*, b.address
from small_tbl a -- 7 млн строк ( 7 mln rows )
LEFT JOIN big_tbl b -- 1 млрд строк ( 1 bln rows )
on a.addr_fk = b.addr_pk
ERROR: Canceling query because of high VMEM usage.

-- friendly query
select a.*, b.address
from small_tbl a JOIN big_tbl b
on a.addr_fk = b.addr_pk

select a.*, b.address
from small_tbl a
left join big_tbl b
on a.addr_fk = b.addr_pk
where a.addr_fk is not null
union all
select a.*, null 
from small_tbl a
where a.addr_fk is null

create table public.foo   WITH (appendonly=true,orientation=column,compresstype=zstd,compresslevel=1)
as select generate_series(1,1000000) n; 

SQL> select pg_relation_size('public.foo')

pg_relation_size
------------------------------
3 209 024 байт

create table public.foo_2
as select 'public.foo'::text as tbl_nm;

SQL> select pg_relation_size(tbl_nm) from public.foo_2

pg_relation_size
------------------------------
4 640 байт

select pg_relation_size(t.tbl_nm) from
pg_class c, pg_namespace s, public.foo_2 t
where 1=1
and s.oid = c.relnamespace
and s.nspname || '.' || relname = t.tbl_nm

SQL> select sum(pg_relation_size(y.tbl_nm || decode(q.content, q.content, '')))
from public.foo_2 y
cross join gp_dist_random('gp_id') q

sum
------------------------------
3 209 024 байт