Postgres 14

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'),
(4, 'changed', 'state', 'Assigns', 2, '2022-05-07 18:54:40'),
(5, 'changed', 'state', 'Assigns', 3, '2022-05-07 19:18:28'),
(6, 'changed', 'state', 'Completed', 1, '2022-05-08 12:11:38'),
(7, 'changed', 'state', 'In Progress', 2, '2022-05-09 09:22:53'),
(8, 'changed', 'state', 'Assigns', 4, '2022-05-10 15:28:57'),
(9, 'changed', 'state', 'Completed', 2, '2022-05-11 11:21:53'),
(10, '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     |
CREATE OR REPLACE FUNCTION build_daily_log_agg(_interval_days TEXT)
  RETURNS TABLE ("date" TEXT,
                 state_id INTEGER, 
                 state_label TEXT,
                 "count" INTEGER)
  LANGUAGE plpgsql AS
$func$

DECLARE dt RECORD;
BEGIN 

    CREATE TEMPORARY TABLE daily_log_agg (
        "date" TEXT, 
        state_id INTEGER, 
        state_label TEXT, 
        "count" INTEGER
    );
    

    FOR dt IN SELECT dates FROM generate_series( 
            current_date - _interval_days::interval,
            current_date, '1 day' 
        ) AS dates LOOP
        
        INSERT INTO daily_log_agg ("date", state_id, state_label, "count")
        SELECT dt.dates AS "date",
               state.id AS state_id, 
               state.label, 
               COUNT(sub.id) AS "count"
        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) <= dt.dates
              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;
        
   END LOOP; 
   
   RETURN QUERY
   SELECT * FROM daily_log_agg;
END
$func$;


SELECT * FROM build_daily_log_agg('12 days');


date
state_id
state_label
count


2022-05-05 00:00:00
1
Assigns
1

2022-05-05 00:00:00
2
In Progress
0

2022-05-05 00:00:00
3
Completed
0

2022-05-06 00:00:00
1
Assigns
0

2022-05-06 00:00:00
2
In Progress
1

2022-05-06 00:00:00
3
Completed
0

2022-05-07 00:00:00
1
Assigns
2

2022-05-07 00:00:00
2
In Progress
1

2022-05-07 00:00:00
3
Completed
0

2022-05-08 00:00:00
1
Assigns
2

2022-05-08 00:00:00
2
In Progress
0

2022-05-08 00:00:00
3
Completed
1

2022-05-09 00:00:00
1
Assigns
1

2022-05-09 00:00:00
2
In Progress
1

2022-05-09 00:00:00
3
Completed
1

2022-05-10 00:00:00
1
Assigns
2

2022-05-10 00:00:00
2
In Progress
1

2022-05-10 00:00:00
3
Completed
1

2022-05-11 00:00:00
1
Assigns
1

2022-05-11 00:00:00
2
In Progress
1

2022-05-11 00:00:00
3
Completed
2

2022-05-12 00:00:00
1
Assigns
1

2022-05-12 00:00:00
2
In Progress
1

2022-05-12 00:00:00
3
Completed
2

2022-05-13 00:00:00
1
Assigns
1

2022-05-13 00:00:00
2
In Progress
1

2022-05-13 00:00:00
3
Completed
2

2022-05-14 00:00:00
1
Assigns
1

2022-05-14 00:00:00
2
In Progress
1

2022-05-14 00:00:00
3
Completed
2

2022-05-15 00:00:00
1
Assigns
1

2022-05-15 00:00:00
2
In Progress
1

2022-05-15 00:00:00
3
Completed
2

2022-05-16 00:00:00
1
Assigns
1

2022-05-16 00:00:00
2
In Progress
1

2022-05-16 00:00:00
3
Completed
2

2022-05-17 00:00:00
1
Assigns
1

2022-05-17 00:00:00
2
In Progress
1

2022-05-17 00:00:00
3
Completed
2

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

date	state_id	state_label	count
2022-05-05 00:00:00	1	Assigns	1
2022-05-05 00:00:00	2	In Progress	0
2022-05-05 00:00:00	3	Completed	0
2022-05-06 00:00:00	1	Assigns	0
2022-05-06 00:00:00	2	In Progress	1
2022-05-06 00:00:00	3	Completed	0
2022-05-07 00:00:00	1	Assigns	2
2022-05-07 00:00:00	2	In Progress	1
2022-05-07 00:00:00	3	Completed	0
2022-05-08 00:00:00	1	Assigns	2
2022-05-08 00:00:00	2	In Progress	0
2022-05-08 00:00:00	3	Completed	1
2022-05-09 00:00:00	1	Assigns	1
2022-05-09 00:00:00	2	In Progress	1
2022-05-09 00:00:00	3	Completed	1
2022-05-10 00:00:00	1	Assigns	2
2022-05-10 00:00:00	2	In Progress	1
2022-05-10 00:00:00	3	Completed	1
2022-05-11 00:00:00	1	Assigns	1
2022-05-11 00:00:00	2	In Progress	1
2022-05-11 00:00:00	3	Completed	2
2022-05-12 00:00:00	1	Assigns	1
2022-05-12 00:00:00	2	In Progress	1
2022-05-12 00:00:00	3	Completed	2
2022-05-13 00:00:00	1	Assigns	1
2022-05-13 00:00:00	2	In Progress	1
2022-05-13 00:00:00	3	Completed	2
2022-05-14 00:00:00	1	Assigns	1
2022-05-14 00:00:00	2	In Progress	1
2022-05-14 00:00:00	3	Completed	2
2022-05-15 00:00:00	1	Assigns	1
2022-05-15 00:00:00	2	In Progress	1
2022-05-15 00:00:00	3	Completed	2
2022-05-16 00:00:00	1	Assigns	1
2022-05-16 00:00:00	2	In Progress	1
2022-05-16 00:00:00	3	Completed	2
2022-05-17 00:00:00	1	Assigns	1
2022-05-17 00:00:00	2	In Progress	1
2022-05-17 00:00:00	3	Completed	2