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 |