/*
 set work_mem = '32MB';
 set effective_cache_size = '24GB';
 set random_page_cost = 1.1; --ssd
 set shared_buffers = 8GB
 set fsync = 'on';
 set synchronous_commit = 'on';
 set max_parallel_workers_per_gather = 0;
 set temp_buffers = '80MB';
 set enable_partitionwise_aggregate = 'on';
 
 */

CREATE OR REPLACE
FUNCTION public.add_partitions(_schema TEXT,
_table TEXT,
_values TEXT[]) RETURNS void LANGUAGE plpgsql AS $function$ DECLARE n TEXT;
parent_table TEXT = quote_ident(_schema) || '.' || quote_ident(_table);
BEGIN FOR n IN
SELECT
	'CREATE TABLE IF NOT EXISTS ' || quote_ident(_schema) || '.' || quote_ident(_table || ':' || v) || ' (LIKE ' || parent_table || ' INCLUDING ALL);

ALTER TABLE ' || parent_table || ' ATTACH PARTITION ' || quote_ident(_schema) || '.' || quote_ident(_table || ':' || v) || ' FOR VALUES IN (' || quote_literal(v) || ');'
FROM
	UNNEST(_values) v
WHERE
	NOT EXISTS(
	SELECT
		*
	FROM
		pg_type t
	INNER JOIN pg_namespace n ON
		t.typnamespace = n.oid
	INNER JOIN LATERAL (
		SELECT
			1
		FROM
			pg_partition_tree(parent_table)
		WHERE
			relid = (quote_ident(_schema) || '.' || quote_ident(_table || ':' || v))::REGCLASS
			AND isleaf ) p ON
		TRUE
	WHERE
		n.nspname = _schema
		AND t.typname = _table || ':' || v ) LOOP EXECUTE n;
END LOOP;
END;
$function$ ;

CREATE TYPE public.mytype1 AS (
    a numeric(22,2),
    b numeric(22,2),
    c numeric(22,2),
    d numeric(22,2)
);

CREATE TYPE public.mytype2 AS (
    x date,
    y numeric(22,2),
    z bigint[]
);

CREATE TYPE public.sometype AS (
	a date,
	b date,
	c mytype1,
	d mytype1,
	e mytype1,
	f mytype1,
	g mytype1,
	h mytype1);

CREATE TYPE public.someenum AS ENUM (
	'A',
	'B'
);


DROP TABLE IF EXISTS public.mytable;

CREATE TABLE public.mytable (
	dt date NOT NULL,
	id int8 NOT NULL,
	status int4 NOT NULL,
	col1 public.mytype1 NULL,
	col2 public.mytype2 NOT NULL,
	a int4 NOT NULL,
	b date NULL,
	c date NULL,
	d int4 NOT NULL,
	e public.mytype1 NOT NULL,
	f public.mytype1 NOT NULL,
	g public.mytype1 NOT NULL,
	h public.mytype2 NULL,
	i public.mytype2 NULL,
	j public.mytype2 NULL,
	k date NOT NULL,
	l int4 NOT NULL,
	m int4 NOT NULL,
	n public.mytype2 NULL,
	o date NULL,
	p int8 NULL,
	q public.sometype NULL,
	r public.sometype NULL,
	s public.sometype NULL,
	t public.sometype NULL,
	u public.sometype NULL,
	v public.sometype NULL,
	w public.mytype1 NOT NULL,
	x public.someenum NOT NULL,
	y bool NOT NULL,
	z public.mytype1 NOT NULL,
	a1 int4 NOT NULL,
	b1 numeric(22,2) NULL,
	c1 sometype NULL,
	d1 int4 NOT NULL,
	e1 int4 NOT NULL,
	f1 int4 NOT NULL,
	g1 mytype1 NOT NULL,
	h1 int4 NOT NULL,
	i1 int4 NOT NULL,
	j1 int4 NOT NULL,
	k1 numeric(22,2) NULL,
	l1 numeric(22,2) NULL,
	m1 int8 NOT NULL,
	n1 int4 NOT NULL,
	o1 public.mytype1 NOT NULL,
	p1 int4 NOT NULL,
	q1 int8 NOT NULL,
	r1 int8 NOT NULL,
	s1 int8 NOT NULL
)
PARTITION BY LIST (dt);


SELECT public.add_partitions('public','mytable',array_agg(dt::date::text)) 
FROM generate_series('2020-11-15','2020-12-15','1 day'::INTERVAL) AS dates(dt);


INSERT INTO public.mytable
SELECT 
	--dt::date,
	'2020-12-09'::date,
	id,
	(random()*4)::int+1 AS status,
	(random()*1000,random()*1000,random()*1000,random()*1000)::public.mytype1 AS col1, 
	(current_date-(random()*365)::int,random()*1000,array_fill(1, ARRAY[18]))::public.mytype2 AS col2,
	0,current_date,current_date,0,
	(random()*1000,random()*1000,random()*1000,random()*1000)::public.mytype1,
	(random()*1000,random()*1000,random()*1000,random()*1000)::public.mytype1,
	(random()*1000,random()*1000,random()*1000,random()*1000)::public.mytype1,
	(current_date-(random()*365)::int,random()*1000,array_fill(1, ARRAY[18]))::mytype2,
	(current_date-(random()*365)::int,random()*1000,array_fill(1, ARRAY[18]))::mytype2,
	(current_date-(random()*365)::int,random()*1000,array_fill(1, ARRAY[18]))::mytype2,
	current_date,0,0,
	(current_date-(random()*365)::int,random()*1000,array_fill(1, ARRAY[18]))::mytype2,
	current_date,0,
	(current_date,current_date,
	(random()*1000,random()*1000,random()*1000,random()*1000)::public.mytype1,
	(random()*1000,random()*1000,random()*1000,random()*1000)::public.mytype1,
	(random()*1000,random()*1000,random()*1000,random()*1000)::public.mytype1,
	(random()*1000,random()*1000,random()*1000,random()*1000)::public.mytype1,
	(random()*1000,random()*1000,random()*1000,random()*1000)::public.mytype1,
	(random()*1000,random()*1000,random()*1000,random()*1000)::public.mytype1)::public.sometype,
	(current_date,current_date,
	(random()*1000,random()*1000,random()*1000,random()*1000)::public.mytype1,
	(random()*1000,random()*1000,random()*1000,random()*1000)::public.mytype1,
	(random()*1000,random()*1000,random()*1000,random()*1000)::public.mytype1,
	(random()*1000,random()*1000,random()*1000,random()*1000)::public.mytype1,
	(random()*1000,random()*1000,random()*1000,random()*1000)::public.mytype1,
	(random()*1000,random()*1000,random()*1000,random()*1000)::public.mytype1)::public.sometype,
	(current_date,current_date,
	(random()*1000,random()*1000,random()*1000,random()*1000)::public.mytype1,
	(random()*1000,random()*1000,random()*1000,random()*1000)::public.mytype1,
	(random()*1000,random()*1000,random()*1000,random()*1000)::public.mytype1,
	(random()*1000,random()*1000,random()*1000,random()*1000)::public.mytype1,
	(random()*1000,random()*1000,random()*1000,random()*1000)::public.mytype1,
	(random()*1000,random()*1000,random()*1000,random()*1000)::public.mytype1)::public.sometype,
	(current_date,current_date,
	(random()*1000,random()*1000,random()*1000,random()*1000)::public.mytype1,
	(random()*1000,random()*1000,random()*1000,random()*1000)::public.mytype1,
	(random()*1000,random()*1000,random()*1000,random()*1000)::public.mytype1,
	(random()*1000,random()*1000,random()*1000,random()*1000)::public.mytype1,
	(random()*1000,random()*1000,random()*1000,random()*1000)::public.mytype1,
	(random()*1000,random()*1000,random()*1000,random()*1000)::public.mytype1)::public.sometype,
	(current_date,current_date,
	(random()*1000,random()*1000,random()*1000,random()*1000)::public.mytype1,
	(random()*1000,random()*1000,random()*1000,random()*1000)::public.mytype1,
	(random()*1000,random()*1000,random()*1000,random()*1000)::public.mytype1,
	(random()*1000,random()*1000,random()*1000,random()*1000)::public.mytype1,
	(random()*1000,random()*1000,random()*1000,random()*1000)::public.mytype1,
	(random()*1000,random()*1000,random()*1000,random()*1000)::public.mytype1)::public.sometype,
	(current_date,current_date,
	(random()*1000,random()*1000,random()*1000,random()*1000)::public.mytype1,
	(random()*1000,random()*1000,random()*1000,random()*1000)::public.mytype1,
	(random()*1000,random()*1000,random()*1000,random()*1000)::public.mytype1,
	(random()*1000,random()*1000,random()*1000,random()*1000)::public.mytype1,
	(random()*1000,random()*1000,random()*1000,random()*1000)::public.mytype1,
	(random()*1000,random()*1000,random()*1000,random()*1000)::public.mytype1)::public.sometype,
	(random()*1000,random()*1000,random()*1000,random()*1000)::public.mytype1,
	'A'::public.someenum,
	TRUE,
	(random()*1000,random()*1000,random()*1000,random()*1000)::public.mytype1,
	0,0,
	(current_date,current_date,
	(random()*1000,random()*1000,random()*1000,random()*1000)::public.mytype1,
	(random()*1000,random()*1000,random()*1000,random()*1000)::public.mytype1,
	(random()*1000,random()*1000,random()*1000,random()*1000)::public.mytype1,
	(random()*1000,random()*1000,random()*1000,random()*1000)::public.mytype1,
	(random()*1000,random()*1000,random()*1000,random()*1000)::public.mytype1,
	(random()*1000,random()*1000,random()*1000,random()*1000)::public.mytype1)::public.sometype,
	0,0,0,
	(random()*1000,random()*1000,random()*1000,random()*1000)::public.mytype1,
	0,0,0,0,0,0,0,
	(random()*1000,random()*1000,random()*1000,random()*1000)::public.mytype1,
	0,0,0,0
FROM 
	generate_series(1::bigint,2000000,1) AS ids(id);
--	, generate_series('2020-11-15','2020-12-15','1 day'::INTERVAL) AS dates(dt);


ALTER TABLE public.mytable ADD PRIMARY KEY (id,dt);





--big&slow:
CREATE INDEX ix1 ON public.mytable (col1,col2) WHERE status IN (1,2,3,4);
--cost higher than ix1, so ix1 needs to be dropped before ix2 will be used (but faster than ix1):
CREATE INDEX ix2 ON public.mytable (COALESCE((col1).a + (col1).b + (col1).c + (col1).d, 0),COALESCE((col2).y, 0),col1,col2) WHERE status IN (1,2,3,4);
--cost too high, won't be used unless I drop ix1,ix2 and set enable_seqscan and enable_bitmapscan to off:
CREATE INDEX ix3 ON public.mytable (COALESCE((col1).a + (col1).b + (col1).c + (col1).d, 0),COALESCE((col2).y, 0)) WHERE status IN (1,2,3,4);

--ix4, ix5 and ix6 are just some variants I tried
CREATE INDEX ix4 ON public.mytable (((col1).a),((col1).b),((col1).c),((col1).d),((col2).y),col1,col2) WHERE status IN (1,2,3,4);

CREATE INDEX ix5 ON public.mytable (((col1).a),((col1).b),((col1).c),((col1).d),((col2).y)) WHERE status IN (1,2,3,4);

CREATE INDEX ix6 ON public.mytable (COALESCE((col1).a + (col1).b + (col1).c + (col1).d, 0),COALESCE((col2).y, 0)) INCLUDE (col1,col2) WHERE status IN (1,2,3,4);


VACUUM ANALYZE public.mytable;
VACUUM ANALYZE public."mytable:2020-12-09";


SELECT 
	pg_size_pretty(pg_total_relation_size('public."mytable:2020-12-09_col1_col2_idx"')) ix1,
	pg_size_pretty(pg_total_relation_size('public."mytable:2020-12-09_coalesce_coalesce1_col1_col2_idx"')) ix2,
	pg_size_pretty(pg_total_relation_size('public."mytable:2020-12-09_coalesce_coalesce1_idx"')) ix3,
	pg_size_pretty(pg_total_relation_size('public."mytable:2020-12-09_a_b_c_d_y_col1_col2_idx"')) ix4,
	pg_size_pretty(pg_total_relation_size('public."mytable:2020-12-09_a_b_c_d_y_idx"')) ix5,
	pg_size_pretty(pg_total_relation_size('public."mytable:2020-12-09_coalesce_coalesce1_col1_col2_idx1"')) ix6,
	pg_size_pretty(pg_total_relation_size('public."mytable:2020-12-09_pkey"')) pkey,
	pg_size_pretty(pg_total_relation_size('mytable:2020-12-09')) part_with_ix,
	pg_size_pretty(pg_relation_size('mytable:2020-12-09')) part;

--ix1	    ix2	    ix3	    ix4	    ix5	    ix6	    pkey	part_with_ix	part
--266 MB	280 MB	53 MB	327 MB	98 MB	280 MB	60 MB	6902 MB			3906 MB



--the query
EXPLAIN (ANALYZE, COSTS, VERBOSE, BUFFERS, SETTINGS)
SELECT
	SUM(COALESCE((col1).a + (col1).b + (col1).c + (col1).d, 0)) AS val1,
	SUM(COALESCE((col2).y, 0)) AS val2
FROM
	public."mytable:2020-12-09"
WHERE
	status IN (1,
	2,
	3,
	4);