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?.
select version();
| version |
|---|
| PostgreSQL 14.1 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.5.0 20210514 (Red Hat 8.5.0-4), 64-bit |
CREATE TABLE task_states (
id INT PRIMARY KEY,
label TEXT NOT NULL
);
INSERT INTO task_states (id, label) VALUES
(1, 'Assigns'),
(2, 'In Progress'),
(3, 'Completed');
3 rows affected
CREATE TABLE tasks (
id INT PRIMARY KEY,
title TEXT NOT NULL,
state_id INT REFERENCES task_states,
inserted_at TIMESTAMP NOT NULL
);
INSERT INTO tasks (id, title, state_id, inserted_at) VALUES
(1, 'First Task', 1, '2022-05-05 19:16:44'),
(2, 'Second Task', 1, '2022-05-07 18:54:40'),
(3, 'Third Task', 1, '2022-05-07 19:18:28'),
(4, 'Fourth Task', 1, '2022-05-10 15:28:57');
4 rows affected
CREATE TABLE task_logs (
id INT PRIMARY KEY,
event TEXT NOT NULL,
target TEXT NOT NULL,
value TEXT NOT NULL,
task_id INT REFERENCES tasks,
inserted_at TIMESTAMP NOT NULL
);
INSERT INTO task_logs (id, event, target, value, task_id, inserted_at) VALUES
(1, 'changed', 'state', 'Assigns', 1, '2022-05-05 19:16:44'),
(2, 'changed', 'state', 'In Progress', 1, '2022-05-06 11:43:14'),
(3, 'changed', 'state', 'Assigns', 2, '2022-05-07 18:54:40'),
(4, 'changed', 'state', 'Assigns', 3, '2022-05-07 19:18:28'),
(5, 'changed', 'state', 'Completed', 1, '2022-05-08 12:11:38'),
(6, 'changed', 'state', 'In Progress', 2, '2022-05-09 09:22:53'),
(7, 'changed', 'state', 'Assigns', 4, '2022-05-10 15:28:57'),
(8, 'changed', 'state', 'Completed', 2, '2022-05-11 11:21:53'),
(9, 'changed', 'state', 'In Progress', 3, '2022-05-11 17:42:02');
9 rows affected
-- gets # of tasks in each state ON '2022-05-10'
SELECT date('2022-05-10'), state.id as state_id, state.label, count(sub.id)
FROM (
SELECT DISTINCT ON (t.id) t.id, logs.value
FROM tasks t
INNER JOIN task_logs logs ON logs.task_id = t.id
WHERE date(logs.inserted_at) <= date('2022-05-10') AND logs.target = 'state'
ORDER BY t.id, logs.inserted_at DESC
) sub
RIGHT JOIN task_states state ON state.label = sub.value
GROUP BY state.id
ORDER BY state.id;
-- gets # of tasks in each state ON '2022-05-11'
SELECT date('2022-05-11'), state.id as state_id, state.label, count(sub.id)
FROM (
SELECT DISTINCT ON (t.id) t.id, logs.value
FROM tasks t
INNER JOIN task_logs logs ON logs.task_id = t.id
WHERE date(logs.inserted_at) <= date('2022-05-11') AND logs.target = 'state'
ORDER BY t.id, logs.inserted_at DESC
) sub
RIGHT JOIN task_states state ON state.label = sub.value
GROUP BY state.id
ORDER BY state.id;
-- DESIRED RESULT IS SOMETHING SIMILAR TO THE QUERY ABOVE REPRODUCED
-- FOR EACH DATE IN A SERIES OF DATES
| date | state_id | label | count |
|---|---|---|---|
| 2022-05-10 | 1 | Assigns | 2 |
| 2022-05-10 | 2 | In Progress | 1 |
| 2022-05-10 | 3 | Completed | 1 |
| date | state_id | label | count |
|---|---|---|---|
| 2022-05-11 | 1 | Assigns | 1 |
| 2022-05-11 | 2 | In Progress | 1 |
| 2022-05-11 | 3 | Completed | 2 |
-- DESIRED RESULT:
-- | date | state_id | label | count |
-- | ---------- | -------- | ----------- | ----- |
-- | 2022-05-05 | 1 | Assigns | 1 |
-- | 2022-05-05 | 2 | In Progress | 0 |
-- | 2022-05-05 | 3 | Complete | 0 |
-- | 2022-05-06 | 1 | Assigns | 0 |
-- | 2022-05-06 | 2 | In Progress | 1 |
-- | 2022-05-06 | 3 | Complete | 0 |
-- | 2022-05-07 | 1 | Assigns | 2 |
-- | 2022-05-07 | 2 | In Progress | 1 |
-- | 2022-05-07 | 3 | Complete | 0 |
-- | 2022-05-08 | 1 | Assigns | 2 |
-- | 2022-05-08 | 2 | In Progress | 0 |
-- | 2022-05-08 | 3 | Complete | 1 |
-- | 2022-05-09 | 1 | Assigns | 1 |
-- | 2022-05-09 | 2 | In Progress | 1 |
-- | 2022-05-09 | 3 | Complete | 1 |
-- | 2022-05-10 | 1 | Assigns | 2 |
-- | 2022-05-10 | 2 | In Progress | 1 |
-- | 2022-05-10 | 3 | Complete | 1 |
-- | 2022-05-11 | 1 | Assigns | 1 |
-- | 2022-05-11 | 2 | In Progress | 1 |
-- | 2022-05-11 | 3 | Complete | 2 |