Postgres 13

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 "User"
(
  UserId INT   NOT NULL
 ,Login  TEXT  NOT NULL
 ,CONSTRAINT PK_User PRIMARY KEY (UserId)
 ,CONSTRAINT AK_User UNIQUE (Login)
)
;

INSERT INTO "User"
VALUES
  (1,'UserA')
 ,(2,'UserB')
 ,(3,'UserC')
;

CREATE TABLE UserItem
(
  UserId      INT   NOT NULL
 ,Updated_At  DATE  NOT NULL
 ,CONSTRAINT FK_UserItem_Belongs_To_User FOREIGN KEY (UserId) REFERENCES "User" (UserId)
)
;

INSERT INTO UserItem
VALUES
  (1,'2020-01-01')
 ,(1,'2020-01-01')
 ,(1,'2020-01-02')
 ,(2,'2020-01-01')
 ,(2,'2020-01-03')
 ,(3,'2020-04-05')
;

SELECT
  U.Login
 ,t.Date::date AS Day
 ,COUNT(UserItem.UserId) AS Count
 ,AVG(COUNT(UserItem.UserId)) OVER (PARTITION BY U.Login ORDER By t.Date ROWS BETWEEN 3 PRECEDING AND 3 FOLLOWING) AS RollingAvg
FROM
  "User" U
CROSS JOIN
  generate_series(date '2020-01-01' - 3, date '2020-01-01' + 3, interval '1' DAY) AS t(date)
LEFT JOIN
  UserItem UserItem
    ON UserItem.UserId = U.UserId
        AND UserItem.Updated_At = t.Date
WHERE
  U.UserId IN
    (
      SELECT
        UserId
      FROM
        UserItem
      WHERE
        Updated_At >= date '2020-01-01' - 3 
        AND Updated_At <= date '2020-01-01' + 3
    )
GROUP BY
  U.Login
 ,t.Date
ORDER BY
  U.Login
 ,t.Date

3 rows affected

6 rows affected

login	day	count	rollingavg
UserA	2019-12-29	0	0.50000000000000000000
UserA	2019-12-30	0	0.60000000000000000000
UserA	2019-12-31	0	0.50000000000000000000
UserA	2020-01-01	2	0.42857142857142857143
UserA	2020-01-02	1	0.50000000000000000000
UserA	2020-01-03	0	0.60000000000000000000
UserA	2020-01-04	0	0.75000000000000000000
UserB	2019-12-29	0	0.25000000000000000000
UserB	2019-12-30	0	0.20000000000000000000
UserB	2019-12-31	0	0.33333333333333333333
UserB	2020-01-01	1	0.28571428571428571429
UserB	2020-01-02	0	0.33333333333333333333
UserB	2020-01-03	1	0.40000000000000000000
UserB	2020-01-04	0	0.50000000000000000000