Postgres 11

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 "Request"
(
"RequestId" bigserial not null primary key,
"Created Time" date not null,
"Completed Time" date NULL
);
insert into "Request"("Created Time", "Completed Time")
select d, d+t*interval '1 day' from (SELECT generate_series('2019-01-01', '2019-03-31', interval '1 day')::date d, (30*random()+2)::int t) x
90 rows affected

select 
	mc.mc, 
	count(case when to_char("Created Time", 'mm')::int=mc.mc then "RequestId" end) utworzonych, 
	count(case when to_char("Completed Time", 'mm')::int=mc.mc then "RequestId" end) zamknietych
from 
	(select 1 mc union select 2 union select 3 union select 4 union select 5 union select 6 union select 1+6 union select 2+6 union select 3+6 union select 4+6 union select 5+6 union select 6+6) mc
	left join
	"Request" r
		on mc.mc in (to_char("Created Time", 'mm')::int, to_char("Completed Time", 'mm')::int)
group by mc.mc
order by mc.mc


mc
utworzonych
zamknietych


1
31
12

2
28
34

3
31
26

4
0
18

5
0
0

6
0
0

7
0
0

8
0
0

9
0
0

10
0
0

11
0
0

12
0
0