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(
   IDA       INTEGER  NOT NULL 
  ,DATETIMEA timestamp NOT NULL
);
INSERT INTO mytable(IDA,DATETIMEA) VALUES (1,'2020-03-16 13:15:00');
INSERT INTO mytable(IDA,DATETIMEA) VALUES (2,'2020-03-17 15:25:00');
INSERT INTO mytable(IDA,DATETIMEA) VALUES (3,'2020-03-18 17:10:00');
INSERT INTO mytable(IDA,DATETIMEA) VALUES (5,'2020-03-19 11:44:00');
INSERT INTO mytable(IDA,DATETIMEA) VALUES (5,'2020-03-20 12:55:00');
INSERT INTO mytable(IDA,DATETIMEA) VALUES (5,'2020-03-21 19:35:00');
INSERT INTO mytable(IDA,DATETIMEA) VALUES (7,'2020-03-22 10:13:00');
INSERT INTO mytable(IDA,DATETIMEA) VALUES (8,'2020-03-22 15:25:00');
INSERT INTO mytable(IDA,DATETIMEA) VALUES (8,'2020-03-28 12:12:00');
INSERT INTO mytable(IDA,DATETIMEA) VALUES (9,'2020-03-29 17:55:00');
INSERT INTO mytable(IDA,DATETIMEA) VALUES (10,'2020-03-30 11:54:00');
INSERT INTO mytable(IDA,DATETIMEA) VALUES (12,'2020-03-30 15:35:00');
INSERT INTO mytable(IDA,DATETIMEA) VALUES (12,'2020-03-31 13:19:00');

CREATE TABLE



INSERT 0 1



INSERT 0 1



INSERT 0 1



INSERT 0 1



INSERT 0 1



INSERT 0 1



INSERT 0 1



INSERT 0 1



INSERT 0 1



INSERT 0 1



INSERT 0 1



INSERT 0 1



INSERT 0 1


SELECT t.DATETIMEA::date date,
COUNT(t.*) "day",
(SELECT COUNT(w.*) FROM mytable w WHERE w.DATETIMEA::date BETWEEN t.DATETIMEA::date - 7 and t.DATETIMEA::date) week,
(SELECT COUNT(m.*) FROM mytable m WHERE m.DATETIMEA::date BETWEEN t.DATETIMEA::date - 30 and t.DATETIMEA::date) "month"
FROM mytable t
GROUP BY t.DATETIMEA::date
ORDER BY t.DATETIMEA::date

ERROR:  subquery uses ungrouped column "t.datetimea" from outer query
LINE 3: ...*) FROM mytable w WHERE w.DATETIMEA::date BETWEEN t.DATETIME...
                                                             ^


select
	datetimea::date date,
	count(*) "day",
	sum(count(*)) over(
		order by datetimea::date
		range between '7 day' preceding and current row
	) l7,
		sum(count(*)) over(
		order by datetimea::date
		range between '30 day' preceding and current row
	) l30
from mytable
group by datetimea::date
order by datetimea::date 


date
day
l7
l30


2020-03-16
1
1
1

2020-03-17
1
2
2

2020-03-18
1
3
3

2020-03-19
1
4
4

2020-03-20
1
5
5

2020-03-21
1
6
6

2020-03-22
2
8
8

2020-03-28
1
4
9

2020-03-29
1
4
10

2020-03-30
2
4
12

2020-03-31
1
5
13



SELECT 11

date	day	l7	l30
2020-03-16	1	1	1
2020-03-17	1	2	2
2020-03-18	1	3	3
2020-03-19	1	4	4
2020-03-20	1	5	5
2020-03-21	1	6	6
2020-03-22	2	8	8
2020-03-28	1	4	9
2020-03-29	1	4	10
2020-03-30	2	4	12
2020-03-31	1	5	13