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 users (
  user_id  serial PRIMARY KEY
, username text NOT NULL
);

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


CREATE TABLE log (
  user_id  int                          -- possibly with a FK to users
, log_date date
, payload  int
);

INSERT INTO log(log_date, user_id, payload)
SELECT '2010-01-01'::date + g/50        -- asc 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 log_combo_covering_idx
ON log (user_id, log_date DESC NULLS LAST, payload);


-- type for simple query
CREATE TYPE combo AS (log_date date, payload int);

CREATE TABLE



INSERT 0 1000



CREATE TABLE



INSERT 0 100000



CREATE INDEX



CREATE TYPE


-- Without table "users"

-- 1a rCTE with LATERAL
WITH RECURSIVE cte AS (
   (  -- parentheses required
   SELECT user_id, log_date, payload
   FROM   log
   WHERE  log_date <= '2015-01-07'
   AND    user_id > 990                 -- limitfor demo
   ORDER  BY user_id, log_date DESC NULLS LAST
   LIMIT  1
   )
   UNION ALL
   SELECT l.*
   FROM   cte c
   CROSS  JOIN LATERAL (
      SELECT l.user_id, l.log_date, l.payload
      FROM   log l
      WHERE  l.user_id > c.user_id      -- lateral reference
      AND    l.log_date <= '2015-01-07' -- repeat condition
      ORDER  BY l.user_id, l.log_date DESC NULLS LAST
      LIMIT  1
      ) l
   )
TABLE  cte
ORDER  BY user_id;


user_id
log_date
payload


991
2014-12-25
673

992
2015-01-01
528

993
2014-12-20
730

994
2014-11-21
404

995
2014-12-25
640

996
2014-12-18
563

997
2014-12-29
154

998
2015-01-02
578

999
2014-12-12
340

1000
2014-12-13
220



SELECT 10


-- 1b Correlated subquery
WITH RECURSIVE cte AS (
   (                                           -- parentheses required
   SELECT l AS my_row                          -- whole row
   FROM   log l
   WHERE  log_date <= '2015-01-07'
   AND    user_id > 990                        -- limit for demo
   ORDER  BY user_id, log_date DESC NULLS LAST
   LIMIT  1
   )
   UNION ALL
   SELECT (SELECT l                            -- whole row
           FROM   log l
           WHERE  l.user_id > (c.my_row).user_id
           AND    l.log_date <= '2015-01-07'
           ORDER  BY l.user_id, l.log_date DESC NULLS LAST
           LIMIT  1)
   FROM   cte c
   WHERE  (c.my_row).user_id IS NOT NULL       -- note parentheses
   )
SELECT (my_row).*                              -- decompose row
FROM   cte
WHERE  (my_row).user_id IS NOT NULL
ORDER  BY (my_row).user_id;


user_id
log_date
payload


991
2014-12-25
673

992
2015-01-01
528

993
2014-12-20
730

994
2014-11-21
404

995
2014-12-25
640

996
2014-12-18
563

997
2014-12-29
154

998
2015-01-02
578

999
2014-12-12
340

1000
2014-12-13
220



SELECT 10


-- With table "users"

-- 2a JOIN LATERAL
SELECT u.user_id, l.log_date, l.payload
FROM   users u
CROSS  JOIN LATERAL (
   SELECT l.log_date, l.payload
   FROM   log l
   WHERE  l.user_id = u.user_id         -- lateral reference
   AND    l.log_date <= '2015-01-07'
   ORDER  BY l.log_date DESC NULLS LAST
   LIMIT  1
   ) l
WHERE   u.user_id > 990;                -- limit for demo


user_id
log_date
payload


991
2014-12-25
673

992
2015-01-01
528

993
2014-12-20
730

994
2014-11-21
404

995
2014-12-25
640

996
2014-12-18
563

997
2014-12-29
154

998
2015-01-02
578

999
2014-12-12
340

1000
2014-12-13
220



SELECT 10


-- 2b Correlated subquery
SELECT user_id, (combo1).*              -- note parentheses
FROM (
   SELECT u.user_id
        , (SELECT (l.log_date, l.payload)::combo
           FROM   log l
           WHERE  l.user_id = u.user_id
           AND    l.log_date <= '2015-01-07'
           ORDER  BY l.log_date DESC NULLS LAST
           LIMIT  1) AS combo1
   FROM   users u
   WHERE  u.user_id > 990               -- for demo
   ) sub;
   


user_id
log_date
payload


991
2014-12-25
673

992
2015-01-01
528

993
2014-12-20
730

994
2014-11-21
404

995
2014-12-25
640

996
2014-12-18
563

997
2014-12-29
154

998
2015-01-02
578

999
2014-12-12
340

1000
2014-12-13
220



SELECT 10
user_id	log_date	payload
991	2014-12-25	673
992	2015-01-01	528
993	2014-12-20	730
994	2014-11-21	404
995	2014-12-25	640
996	2014-12-18	563
997	2014-12-29	154
998	2015-01-02	578
999	2014-12-12	340
1000	2014-12-13	220