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 (
  user_id  int GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY
, username text NOT NULL
);

INSERT INTO users (username)
SELECT 'usr_'::text || g
FROM   generate_series(1, 1000) g;      -- 1000 users


CREATE TABLE user_msg_log (
  aggr_date     date
, user_id       int                     -- possibly with a FK to users
, running_total int
);

INSERT INTO user_msg_log(aggr_date, user_id, running_total)
SELECT '2010-01-01'::date + g/50        -- ascending dates with ~ 10 % exceptions
     + CASE WHEN random() > .9 THEN (random()^3 * 100)::int ELSE 0 END
     , 1 + trunc(random() * 1000)::int  -- user_id 1 - 1000
     , (random() * 1000)::int     
FROM   generate_series(1, 100000) g;    -- 100k log entries

CREATE INDEX user_msg_log_combo_covering_idx
ON user_msg_log (user_id, aggr_date DESC NULLS LAST, running_total);


-- type for simple query
CREATE TYPE combo AS (aggr_date date, running_total int);
1000 rows affected

100000 rows affected

-- Without table "users"

-- Recursive CTE with LATERAL
WITH RECURSIVE cte AS (
   (
   SELECT user_id, aggr_date, running_total
   FROM   user_msg_log
   WHERE  aggr_date <= '2015-01-07'
   AND    user_id > 990  -- limit to 10 users for demo
   ORDER  BY user_id, aggr_date DESC NULLS LAST
   LIMIT  1
   )
   UNION ALL
   SELECT u.user_id, u.aggr_date, u.running_total
   FROM   cte c
   ,      LATERAL (
      SELECT user_id, aggr_date, running_total
      FROM   user_msg_log
      WHERE  user_id > c.user_id  -- lateral reference
      AND    aggr_date <= '2015-01-07'
      ORDER  BY user_id, aggr_date DESC NULLS LAST
      LIMIT  1
      ) u
   )
SELECT user_id, aggr_date, running_total
FROM   cte
ORDER  BY user_id;


user_id
aggr_date
running_total


991
2014-12-18
879

992
2014-12-31
463

993
2014-11-25
204

994
2014-12-28
173

995
2014-11-22
639

996
2014-12-25
458

997
2014-12-26
848

998
2014-12-25
594

999
2015-01-05
443

1000
2015-01-06
247


-- Recursive CTE with correlated subquery (using whole table type)
WITH RECURSIVE cte AS (
   (  -- parentheses required
   SELECT u  -- whole row
   FROM   user_msg_log u
   WHERE  aggr_date <= '2015-01-07'
   AND    user_id > 990  -- limit to 10 users for demo
   ORDER  BY user_id, aggr_date DESC NULLS LAST
   LIMIT  1
   )
   UNION ALL
   SELECT (SELECT u1
           FROM   user_msg_log u1
           WHERE  user_id > (c.u).user_id
           AND    aggr_date <= '2015-01-07'
           ORDER  BY user_id, aggr_date DESC NULLS LAST
           LIMIT  1)
   FROM   cte c
   WHERE  c.u IS NOT NULL
   )
SELECT (u).user_id, (u).aggr_date, (u).running_total
FROM   cte
WHERE  u IS NOT NULL
ORDER  BY (u).user_id;


user_id
aggr_date
running_total


991
2014-12-18
879

992
2014-12-31
463

993
2014-11-25
204

994
2014-12-28
173

995
2014-11-22
639

996
2014-12-25
458

997
2014-12-26
848

998
2014-12-25
594

999
2015-01-05
443

1000
2015-01-06
247


-- With table "users"

-- JOIN LATERAL
SELECT u.user_id, l.aggr_date, l.running_total
FROM   users u
CROSS  JOIN LATERAL (
   SELECT aggr_date, running_total
   FROM   user_msg_log
   WHERE  user_id = u.user_id  -- lateral reference
   AND    aggr_date <= '2015-01-07'
   ORDER  BY aggr_date DESC NULLS LAST
   LIMIT  1
   ) l
WHERE   user_id > 990;


user_id
aggr_date
running_total


991
2014-12-18
879

992
2014-12-31
463

993
2014-11-25
204

994
2014-12-28
173

995
2014-11-22
639

996
2014-12-25
458

997
2014-12-26
848

998
2014-12-25
594

999
2015-01-05
443

1000
2015-01-06
247


-- Correalted subquery
SELECT user_id, (my_combo).aggr_date, (my_combo).running_total  -- note the parentheses
FROM (
   SELECT user_id
        , (SELECT (aggr_date, running_total)::combo
           FROM   user_msg_log
           WHERE  user_id = u.user_id
           AND    aggr_date <= '2015-01-07'
           ORDER  BY aggr_date DESC NULLS LAST
           LIMIT  1) AS my_combo
   FROM   users u
   WHERE  user_id > 990
   ) sub;


user_id
aggr_date
running_total


991
2014-12-18
879

992
2014-12-31
463

993
2014-11-25
204

994
2014-12-28
173

995
2014-11-22
639

996
2014-12-25
458

997
2014-12-26
848

998
2014-12-25
594

999
2015-01-05
443

1000
2015-01-06
247
user_id	aggr_date	running_total
991	2014-12-18	879
992	2014-12-31	463
993	2014-11-25	204
994	2014-12-28	173
995	2014-11-22	639
996	2014-12-25	458
997	2014-12-26	848
998	2014-12-25	594
999	2015-01-05	443
1000	2015-01-06	247