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 mytable(
   id       INTEGER  NOT NULL PRIMARY KEY 
  ,job_type VARCHAR(1) NOT NULL
  ,run_dt   DATE  NOT NULL
);
INSERT INTO mytable(id,job_type,run_dt) VALUES (1,'A','2020-05-25');
INSERT INTO mytable(id,job_type,run_dt) VALUES (2,'A','2020-05-25');
INSERT INTO mytable(id,job_type,run_dt) VALUES (3,'A','2020-05-25');
INSERT INTO mytable(id,job_type,run_dt) VALUES (4,'A','2020-05-25');
INSERT INTO mytable(id,job_type,run_dt) VALUES (5,'A','2020-05-25');
INSERT INTO mytable(id,job_type,run_dt) VALUES (6,'A','2020-05-24');
INSERT INTO mytable(id,job_type,run_dt) VALUES (7,'A','2020-05-24');
INSERT INTO mytable(id,job_type,run_dt) VALUES (8,'A','2020-05-23');
INSERT INTO mytable(id,job_type,run_dt) VALUES (9,'B','2020-05-25');
INSERT INTO mytable(id,job_type,run_dt) VALUES (10,'B','2020-05-25');
INSERT INTO mytable(id,job_type,run_dt) VALUES (11,'B','2020-05-25');
INSERT INTO mytable(id,job_type,run_dt) VALUES (12,'B','2020-05-25');
INSERT INTO mytable(id,job_type,run_dt) VALUES (13,'B','2020-05-25');
INSERT INTO mytable(id,job_type,run_dt) VALUES (14,'B','2020-05-25');
INSERT INTO mytable(id,job_type,run_dt) VALUES (15,'B','2020-05-24');
INSERT INTO mytable(id,job_type,run_dt) VALUES (16,'B','2020-05-24');
1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

select '2020-01-03' - date '2020-01-01'


?column?


2


select
	id,
	job_type,
	date(min_run_dt + (max_run_dt - min_run_dt + 1) * (rn - 1) / cnt * interval '1 day') run_dt
from (
	select 
		t.*, 
		min(run_dt) over(partition by job_type) min_run_dt , 
		max(run_dt) over(partition by job_type) max_run_dt, 
		count(*) over(partition by job_type) cnt,
		row_number() over(partition by job_type order by run_dt) rn
	from mytable t
) t
order by job_type, run_dt


id
job_type
run_dt


8
A
2020-05-23

6
A
2020-05-23

7
A
2020-05-23

4
A
2020-05-24

1
A
2020-05-24

5
A
2020-05-24

2
A
2020-05-25

3
A
2020-05-25

16
B
2020-05-24

15
B
2020-05-24

11
B
2020-05-24

12
B
2020-05-24

13
B
2020-05-25

14
B
2020-05-25

9
B
2020-05-25

10
B
2020-05-25

id	job_type	run_dt
8	A	2020-05-23
6	A	2020-05-23
7	A	2020-05-23
4	A	2020-05-24
1	A	2020-05-24
5	A	2020-05-24
2	A	2020-05-25
3	A	2020-05-25
16	B	2020-05-24
15	B	2020-05-24
11	B	2020-05-24
12	B	2020-05-24
13	B	2020-05-25
14	B	2020-05-25
9	B	2020-05-25
10	B	2020-05-25