Postgres 12

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

Help with an interesting Postgres question: Why isn't an Index Only Scan used on a partition accessed via the parent table?.

Create table t (col1 int,col2 varchar(100),col3 varchar(100), 
col4 varchar(100), col5 varchar(100), col6 date) ;

Create table t2 (col1 int,col2 varchar(100),col3 varchar(100), 
col4 varchar(100), col5 varchar(100), col6 date) ;

Insert into t
Values (110010,'axbc','ORT.01','+','ACTIVE','2020-06-07');
Insert into t
Values (110011,'axbe','ORT.01','+','ACTIVE','2020-06-08');
Insert into t
Values (110012,'axbc','ORT.01','+','ACTIVE','2020-06-09');
Insert into t
Values (110013,'axbe','ORT.01','+','ACTIVE','2020-06-10');
Insert into t
Values (110014,'axbc','ORT.01','+','ACTIVE','2020-06-11');
Insert into t
Values (110015,'axbe','ORT.01','+','ACTIVE','2020-06-12');
Insert into t
Values (110016,'axbc','ORT.01','+','ACTIVE','2020-06-13');

Insert into t2
Select col1,col2,col3,col4,col5, col6 from t;

Insert into t2

Select col1,col2,col3,col4,col5, col6 + interval '7' day as col6 from t
Union
Select col1,col2,col3,col4,col5, col6 + interval '14' day as col6 from t
Union
Select col1,col2,col3,col4,col5, col6 + interval '21' day as col6 from t
Union
Select col1,col2,col3,col4,col5, col6 + interval '28' day as col6 from t;

Select * from t2 order by col6

1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

7 rows affected

28 rows affected

col1	col2	col3	col4	col5	col6
110010	axbc	ORT.01	+	ACTIVE	2020-06-07
110011	axbe	ORT.01	+	ACTIVE	2020-06-08
110012	axbc	ORT.01	+	ACTIVE	2020-06-09
110013	axbe	ORT.01	+	ACTIVE	2020-06-10
110014	axbc	ORT.01	+	ACTIVE	2020-06-11
110015	axbe	ORT.01	+	ACTIVE	2020-06-12
110016	axbc	ORT.01	+	ACTIVE	2020-06-13
110010	axbc	ORT.01	+	ACTIVE	2020-06-14
110011	axbe	ORT.01	+	ACTIVE	2020-06-15
110012	axbc	ORT.01	+	ACTIVE	2020-06-16
110013	axbe	ORT.01	+	ACTIVE	2020-06-17
110014	axbc	ORT.01	+	ACTIVE	2020-06-18
110015	axbe	ORT.01	+	ACTIVE	2020-06-19
110016	axbc	ORT.01	+	ACTIVE	2020-06-20
110010	axbc	ORT.01	+	ACTIVE	2020-06-21
110011	axbe	ORT.01	+	ACTIVE	2020-06-22
110012	axbc	ORT.01	+	ACTIVE	2020-06-23
110013	axbe	ORT.01	+	ACTIVE	2020-06-24
110014	axbc	ORT.01	+	ACTIVE	2020-06-25
110015	axbe	ORT.01	+	ACTIVE	2020-06-26
110016	axbc	ORT.01	+	ACTIVE	2020-06-27
110010	axbc	ORT.01	+	ACTIVE	2020-06-28
110011	axbe	ORT.01	+	ACTIVE	2020-06-29
110012	axbc	ORT.01	+	ACTIVE	2020-06-30
110013	axbe	ORT.01	+	ACTIVE	2020-07-01
110014	axbc	ORT.01	+	ACTIVE	2020-07-02
110015	axbe	ORT.01	+	ACTIVE	2020-07-03
110016	axbc	ORT.01	+	ACTIVE	2020-07-04
110010	axbc	ORT.01	+	ACTIVE	2020-07-05
110011	axbe	ORT.01	+	ACTIVE	2020-07-06
110012	axbc	ORT.01	+	ACTIVE	2020-07-07
110013	axbe	ORT.01	+	ACTIVE	2020-07-08
110014	axbc	ORT.01	+	ACTIVE	2020-07-09
110015	axbe	ORT.01	+	ACTIVE	2020-07-10
110016	axbc	ORT.01	+	ACTIVE	2020-07-11