create sequence public.sq_1;

create table public.sq_1_tst
as select nextval('public.sq_1') sq, generate_series(1,1000000) n;
1,000,000 rows affected in 3 m 11 s 203 ms

create sequence public.sq_1000 cache 1000;

create table public.sq_1000_tst
as select nextval('public.sq_1000') sq, generate_series(1,1000000) n;
1,000,000 rows affected in 1 s 156 ms

SQL> select pg_backend_pid(), nextval('public.sq_1000')
pg_backend_pid | nextval
---------------+--------
11309          | 1001001

SQL> select pg_backend_pid(), nextval('public.sq_1000')
pg_backend_pid | nextval
---------------+--------
12208          | 1002001

create table public.tst1   WITH (appendonly=true,orientation=column,compresstype=zstd,compresslevel=1)
as select generate_series(1,1000) n1;

create table public.tst2   WITH (appendonly=true,orientation=column,compresstype=zstd,compresslevel=1)
as select t1.n1, t1.n1*t2.n1 n2 from
public.tst1 t1, public.tst1 t2

create table public.tst3   WITH (appendonly=true,orientation=column,compresstype=zstd,compresslevel=1)
as select t1.n1, t1.n2, t1.n2*t2.n1 n3  from
public.tst2 t1, public.tst1 t2
distributed by (n1,n2,n3);

SQL> select reltuples from pg_class c, pg_namespace s where relname = 'tst3'
and s.nspname = 'public'
and s.oid = c.relnamespace;
reltuples
--------------
1 000 000 000

SQL>select pg_get_table_distributedby('public.tst3'::regclass)
pg_get_table_distributedby
------------------------------------------
DISTRIBUTED BY (n1, n2, n3)

SQL>select count(distinct n1)  from  public.tst3
count
--------
1 000
execution: 6 m 30 s 459 ms

SQL> select count(*)
from (
         select n1
         from (
                  select n1, n2, n3
                  from dbg.tst3
                  group by 1, 2, 3) a
         group by 1) b;
count
---------
1 000
execution: 15 s 386 ms

delete
from scott.calls
where rowid not in
      (select min(rowid) from scott.calls group by phone_num)

create table public.foo as
select generate_series(1, 1000) n;

SQL> select count(distinct ctid)
from public.foo;
count
-----
67

SQL> select count(distinct ctid::text || '.' || gp_segment_id)
from public.foo
count
-----
1000

with a as
    (
        select string_agg(pg_get_functiondef(p.oid), ',' order by 1) fn 
        from pg_proc p,
             pg_namespace n
        where p.pronamespace = n.oid
          and n.nspname = 'etl'
        group by p.oid
    )
select fn from a
where upper(fn)  like '%DROP%CASCADE%'
limit 5

select reltuples, s.nspname, *
from pg_class c,
     pg_namespace s
where relname = 'foo'
  and s.oid = c.relnamespace;

select reltuples, s.nspname, *
from pg_class c,
     pg_namespace s
where relname = 'foo'
  and s.oid = c.relnamespace;

CREATE OR REPLACE FUNCTION public.estimate_cost(query text)
RETURNS json
LANGUAGE plpgsql
AS $function$
DECLARE
    query_explain  text;
    explanation       json;
BEGIN
query_explain :=e'EXPLAIN (ANALYZE, FORMAT JSON) ' || query;
EXECUTE query_explain INTO explanation;
RETURN explanation;
END;
$function$

create table public.tst_plan (query_plan json, id int) DISTRIBUTED BY (id)

 
insert into public.tst_plan
select *, 1 from public.estimate_cost_0('select generate_series(1,10000)')

SQL> select (query_plan->0)->>'Execution Time' as exec_tm from public.tst_plan where id = 1
exec_tm
-------
1.444

select query_plan from public.tst_plan where id = 1
query_plan
—————————————————————————————-
[
  {
    "Plan": {
      "Node Type": "Result",
      "Startup Cost": 0.00,
      "Total Cost": 0.00,
      "Plan Rows": 1,
      "Plan Width": 4,
      "Actual Startup Time": 0.055,
      "Actual Total Time": 0.770,
      "Actual Rows": 10000,
      "Actual Loops": 1,
      "Plans": [
        {
          "Node Type": "Result",
          "Parent Relationship": "Outer",
          "Startup Cost": 0.00,
          "Total Cost": 0.00,
          "Plan Rows": 1,
          "Plan Width": 1,
          "Actual Startup Time": 0.024,
          "Actual Total Time": 0.024,
          "Actual Rows": 1,
          "Actual Loops": 1
        }
      ]
    },
    "Planning Time": 2.391,
    "Triggers": [
    ],
    "Slice statistics": [
      {
        "Slice": 0,
        "Executor Memory": 57512
      }
    ],
    "Statement statistics": {
      "Memory used": 311296
    },
    "Settings": {
      "Optimizer": "Pivotal Optimizer (GPORCA)"
    },
    "Execution Time": 1.444
  }
]

CREATE OR REPLACE FUNCTION public.estimate_cost(query text)
RETURNS json
LANGUAGE plpgsql
AS $function$
DECLARE
    query_explain  text;
    explanation       json;
BEGIN
query_explain :=e'EXPLAIN (ANALYZE, FORMAT JSON) ' || query;
EXECUTE query_explain INTO explanation;
RETURN explanation;
END;
$function$

create table public.tst_plan (query_plan json, id int) DISTRIBUTED BY (id)

insert into public.tst_plan
select *, 1 from public.estimate_cost_0('select generate_series(1,10000)')

SQL> select (query_plan->0)->>'Execution Time' as exec_tm from public.tst_plan where id = 1
exec_tm
-------
1.444

select query_plan from public.tst_plan where id = 1
query_plan
—————————————————————————————-
[
  {
    "Plan": {
      "Node Type": "Result",
      "Startup Cost": 0.00,
      "Total Cost": 0.00,
      "Plan Rows": 1,
      "Plan Width": 4,
      "Actual Startup Time": 0.055,
      "Actual Total Time": 0.770,
      "Actual Rows": 10000,
      "Actual Loops": 1,
      "Plans": [
        {
          "Node Type": "Result",
          "Parent Relationship": "Outer",
          "Startup Cost": 0.00,
          "Total Cost": 0.00,
          "Plan Rows": 1,
          "Plan Width": 1,
          "Actual Startup Time": 0.024,
          "Actual Total Time": 0.024,
          "Actual Rows": 1,
          "Actual Loops": 1
        }
      ]
    },
    "Planning Time": 2.391,
    "Triggers": [
    ],
    "Slice statistics": [
      {
        "Slice": 0,
        "Executor Memory": 57512
      }
    ],
    "Statement statistics": {
      "Memory used": 311296
    },
    "Settings": {
      "Optimizer": "Pivotal Optimizer (GPORCA)"
    },
    "Execution Time": 1.444
  }
]