DROP INDEX IF EXISTS ix1,ix2,ix3,ix4,ix5,ix6;

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

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


--partitioned (parent) table, targeting single partition:
EXPLAIN (ANALYZE, COSTS, VERBOSE, BUFFERS, SETTINGS)
SELECT 
	dt,
	SUM(COALESCE((col1).a + (col1).b + (col1).c + (col1).d, 0)) AS expected,
	SUM(COALESCE((col2).y, 0)) AS repayments
FROM 
	public.mytable
WHERE 
	dt = '2020-12-09'
	AND status IN (1,2,3,4)
GROUP BY 
	dt;
	

--querying the partition directly instead:
EXPLAIN (ANALYZE, COSTS, VERBOSE, BUFFERS, SETTINGS)
SELECT 
	dt,
	SUM(COALESCE((col1).a + (col1).b + (col1).c + (col1).d, 0)) AS expected,
	SUM(COALESCE((col2).y, 0)) AS repayments
FROM 
	public."mytable:2020-12-09"
WHERE 
	dt = '2020-12-09'
	AND status IN (1,2,3,4)
GROUP BY 
	dt;