Postgres 10

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 users (id int, name text, createdAt timestamp);

insert into users values
(1, 'Timothee', '2018-03-01 13:02:20.904+00');

create table friends (id int, userId int, friendId int, createdAt timestamp);

insert into friends values
(1, 1, 234, '2018-03-20 15:41:51.779+00'),
(2, 1, 254, '2018-03-20 16:16:34.698+00'),
(3, 1, 288, '2018-03-15 15:17:39.907+00'),
(4, 1, 293, '2018-03-07 16:15:49.379+00');
1 rows affected

4 rows affected

select generate_series(createdAt, current_date, 
                       (date_part('day', current_date::timestamp - createdAt::timestamp) / 10)::int * '1 day'::interval) ddate
       , (date_part('day', current_date::timestamp - createdAt::timestamp) / 10)::int * '1 day'::interval as ddays                
from   users
where  id = 1


ddate
ddays


2018-03-01 13:02:20.904
147 days

2018-07-26 13:02:20.904
147 days

2018-12-20 13:02:20.904
147 days

2019-05-16 13:02:20.904
147 days

2019-10-10 13:02:20.904
147 days

2020-03-05 13:02:20.904
147 days

2020-07-30 13:02:20.904
147 days

2020-12-24 13:02:20.904
147 days

2021-05-20 13:02:20.904
147 days

2021-10-14 13:02:20.904
147 days


with x as
(
    select generate_series(createdAt, current_date, 
           (date_part('day', current_date::timestamp - createdAt::timestamp) / 10)::int * '1 day'::interval) ddate
           , (date_part('day', current_date::timestamp - createdAt::timestamp) / 10)::int * '1 day'::interval as ddays
    from   users
    where  id = 1
)
select ddate,
       (select count(*)
        from   friends
        where  createdAt >= ddate
        and    createdAt <  ddate + ddays) friends
from x;


ddate
friends


2018-03-01 13:02:20.904
4

2018-07-26 13:02:20.904
0

2018-12-20 13:02:20.904
0

2019-05-16 13:02:20.904
0

2019-10-10 13:02:20.904
0

2020-03-05 13:02:20.904
0

2020-07-30 13:02:20.904
0

2020-12-24 13:02:20.904
0

2021-05-20 13:02:20.904
0

2021-10-14 13:02:20.904
0


with x as
(
    select generate_series(createdAt, current_date, 
           (date_part('day', current_date::timestamp - createdAt::timestamp) / 10)::int * '1 day'::interval) ddate
           , (date_part('day', current_date::timestamp - createdAt::timestamp) / 10)::int * '1 day'::interval as ddays
    from   users
    where  id = 1
)
select ddate,
       count(friends.*) as friends
from   x
left join   friends
on     createdAt >= ddate
and    createdAt <  ddate + ddays
group by ddate
order by ddate;


ddate
friends


2018-03-01 13:02:20.904
4

2018-07-26 13:02:20.904
0

2018-12-20 13:02:20.904
0

2019-05-16 13:02:20.904
0

2019-10-10 13:02:20.904
0

2020-03-05 13:02:20.904
0

2020-07-30 13:02:20.904
0

2020-12-24 13:02:20.904
0

2021-05-20 13:02:20.904
0

2021-10-14 13:02:20.904
0


with x as
(
    select generate_series(createdAt, current_date, 
           (date_part('day', current_date::timestamp - createdAt::timestamp) / 10)::int * '1 day'::interval) ddate
           , (date_part('day', current_date::timestamp - createdAt::timestamp) / 10)::int * '1 day'::interval as ddays
    from   users
    where  id = 1
)
select ddate,
       count(friends.*) as friends,
       sum(count(friends.*)) over (order by ddate) as friends_total
from   x
left join   friends
on     createdAt >= ddate
and    createdAt <  ddate + ddays
group by ddate
order by ddate;


ddate
friends
friends_total


2018-03-01 13:02:20.904
4
4

2018-07-26 13:02:20.904
0
4

2018-12-20 13:02:20.904
0
4

2019-05-16 13:02:20.904
0
4

2019-10-10 13:02:20.904
0
4

2020-03-05 13:02:20.904
0
4

2020-07-30 13:02:20.904
0
4

2020-12-24 13:02:20.904
0
4

2021-05-20 13:02:20.904
0
4

2021-10-14 13:02:20.904
0
4

ddate	ddays
2018-03-01 13:02:20.904	147 days
2018-07-26 13:02:20.904	147 days
2018-12-20 13:02:20.904	147 days
2019-05-16 13:02:20.904	147 days
2019-10-10 13:02:20.904	147 days
2020-03-05 13:02:20.904	147 days
2020-07-30 13:02:20.904	147 days
2020-12-24 13:02:20.904	147 days
2021-05-20 13:02:20.904	147 days
2021-10-14 13:02:20.904	147 days