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?.

create table events
(
    correlation_id char(26) not null,
    user_id        bigint,
    task_id        bigint not null,
    location_id    bigint,
    type           bigint not null,
    created_at     timestamp(6) with time zone not null,
    constraint events_correlation_id_created_at_user_id_unique
        unique (correlation_id, created_at, user_id)
);
create table tasks
(
    id                bigserial primary key,
    name              text not null,
    inprogress_status bigint
);
insert into tasks (id, name, inprogress_status)
values (58, 'Opening Type 1', 1),
       (97, 'Closing Type 1', 2),
       (99, 'Opening Type 2', 1);
3 rows affected

insert into events (correlation_id, user_id, task_id, location_id, type, created_at)
values  ('01CN4HP4AN0000000000000001', 4, 58, 30, 0, '2018-08-17 18:17:15.348629+00'),
        ('01CN4HP4AN0000000000000001', 4, 58, 30, 1, '2018-08-17 18:17:22.852299+00'),
        ('01CN4HP4AN0000000000000001', 4, 58, 30, 99, '2018-08-17 18:17:25.535593+00'),
        ('01CN4J9SZ80000000000000003', 4, 97, 30, 0, '2018-08-17 18:28:00.104093+00'),
        ('01CN4J9SZ80000000000000003', 4, 97, 30, 99, '2018-08-17 18:29:09.01684+00'),
        ('01CN4JC1430000000000000004', 4, 99, 30, 0, '2018-08-17 18:29:12.963264+00'),
        ('01CN4JC1430000000000000004', 4, 99, 30, 99, '2018-08-17 18:32:09.272632+00'),
        ('01CN4KJCDY0000000000000005', 139, 97, 30, 0, '2018-08-17 18:50:09.725668+00'),
        ('01CN4KJCDY0000000000000005', 139, 97, 30, 3, '2018-08-17 18:50:11.842+00'),
        ('01CN4KJCDY0000000000000005', 139, 97, 30, 99, '2018-08-17 18:51:42.240895+00'),
        ('01CNC4G1Y40000000000000008', 139, 99, 30, 0, '2018-08-20 17:00:40.26043+00'),
        ('01CNC4G1Y40000000000000008', 139, 99, 30, 99, '2018-08-20 17:00:47.583501+00');
12 rows affected

SELECT e.created_at::DATE, e.location_id, e.task_id
     , CASE t.inprogress_status WHEN 2 THEN 'CLOSE' WHEN 1 THEN 'OPEN' END AS task_type
     , e.correlation_id
     , json_object_agg(e.type, json_build_object('timestamp', e.created_at, 'user_id', e.user_id)) AS events
FROM events e
JOIN tasks t on e.task_id = t.id
WHERE e.type IN (0, 99)
AND t.inprogress_status IN (1, 2)
group by created_at::DATE, location_id, task_id, correlation_id, inprogress_status
ORDER BY 1, 2, 3;


created_at
location_id
task_id
task_type
correlation_id
events


2018-08-17
30
58
OPEN
01CN4HP4AN0000000000000001
{ "99" : {"timestamp" : "2018-08-17T19:17:25.535593+01:00", "user_id" : 4}, "0" : {"timestamp" : "2018-08-17T19:17:15.348629+01:00", "user_id" : 4} }

2018-08-17
30
97
CLOSE
01CN4J9SZ80000000000000003
{ "0" : {"timestamp" : "2018-08-17T19:28:00.104093+01:00", "user_id" : 4}, "99" : {"timestamp" : "2018-08-17T19:29:09.01684+01:00", "user_id" : 4} }

2018-08-17
30
97
CLOSE
01CN4KJCDY0000000000000005
{ "99" : {"timestamp" : "2018-08-17T19:51:42.240895+01:00", "user_id" : 139}, "0" : {"timestamp" : "2018-08-17T19:50:09.725668+01:00", "user_id" : 139} }

2018-08-17
30
99
OPEN
01CN4JC1430000000000000004
{ "0" : {"timestamp" : "2018-08-17T19:29:12.963264+01:00", "user_id" : 4}, "99" : {"timestamp" : "2018-08-17T19:32:09.272632+01:00", "user_id" : 4} }

2018-08-20
30
99
OPEN
01CNC4G1Y40000000000000008
{ "99" : {"timestamp" : "2018-08-20T18:00:47.583501+01:00", "user_id" : 139}, "0" : {"timestamp" : "2018-08-20T18:00:40.26043+01:00", "user_id" : 139} }


WITH grouped_events AS (
    SELECT e.created_at::DATE AS created_date,
        location_id,
        task_id,
        CASE t.inprogress_status WHEN 2 THEN 'CLOSE' WHEN 1 THEN 'OPEN' END AS task_type,
        jsonb_build_object('id', e.correlation_id) ||
                jsonb_object_agg(type, jsonb_build_object('timestamp', e.created_at, 'user_id', user_id)) AS events
    FROM events e
    JOIN tasks t on e.task_id = t.id
    WHERE type IN (0, 99)
    AND inprogress_status IN (1, 2)
    GROUP BY e.created_at::DATE, location_id, task_id, correlation_id, t.inprogress_status
)
SELECT created_date, location_id, json_object_agg(task_type, events) AS events
FROM grouped_events
GROUP BY 1, 2
ORDER BY 1, 2


created_date
location_id
events


2018-08-17
30
{ "OPEN" : {"0": {"user_id": 4, "timestamp": "2018-08-17T19:17:15.348629+01:00"}, "99": {"user_id": 4, "timestamp": "2018-08-17T19:17:25.535593+01:00"}, "id": "01CN4HP4AN0000000000000001"}, "CLOSE" : {"0": {"user_id": 4, "timestamp": "2018-08-17T19:28:00.104093+01:00"}, "99": {"user_id": 4, "timestamp": "2018-08-17T19:29:09.01684+01:00"}, "id": "01CN4J9SZ80000000000000003"}, "CLOSE" : {"0": {"user_id": 139, "timestamp": "2018-08-17T19:50:09.725668+01:00"}, "99": {"user_id": 139, "timestamp": "2018-08-17T19:51:42.240895+01:00"}, "id": "01CN4KJCDY0000000000000005"}, "OPEN" : {"0": {"user_id": 4, "timestamp": "2018-08-17T19:29:12.963264+01:00"}, "99": {"user_id": 4, "timestamp": "2018-08-17T19:32:09.272632+01:00"}, "id": "01CN4JC1430000000000000004"} }

2018-08-20
30
{ "OPEN" : {"0": {"user_id": 139, "timestamp": "2018-08-20T18:00:40.26043+01:00"}, "99": {"user_id": 139, "timestamp": "2018-08-20T18:00:47.583501+01:00"}, "id": "01CNC4G1Y40000000000000008"} }


WITH grouped_events1 AS (
    SELECT e.created_at::DATE AS created_date,
        location_id,
        task_id,
        CASE t.inprogress_status WHEN 2 THEN 'CLOSE' WHEN 1 THEN 'OPEN' END AS task_type,
        jsonb_build_object('id', e.correlation_id) ||
                jsonb_object_agg(type, jsonb_build_object('timestamp', e.created_at, 'user_id', user_id)) AS events
    FROM events e
    JOIN tasks t on e.task_id = t.id
    WHERE type IN (0, 99)
    AND inprogress_status IN (1, 2)
    GROUP BY e.created_at::DATE, location_id, task_id, correlation_id, t.inprogress_status
), grouped_events2 AS (
    SELECT created_date, location_id, task_type, json_agg(events) AS events
    FROM grouped_events1
    GROUP BY 1, 2, 3
)
SELECT created_date, location_id, json_object_agg(task_type, events) AS events
FROM grouped_events2
GROUP BY 1, 2
ORDER BY 1, 2


created_date
location_id
events


2018-08-17
30
{ "CLOSE" : [{"0": {"user_id": 4, "timestamp": "2018-08-17T19:28:00.104093+01:00"}, "99": {"user_id": 4, "timestamp": "2018-08-17T19:29:09.01684+01:00"}, "id": "01CN4J9SZ80000000000000003"}, {"0": {"user_id": 139, "timestamp": "2018-08-17T19:50:09.725668+01:00"}, "99": {"user_id": 139, "timestamp": "2018-08-17T19:51:42.240895+01:00"}, "id": "01CN4KJCDY0000000000000005"}], "OPEN" : [{"0": {"user_id": 4, "timestamp": "2018-08-17T19:17:15.348629+01:00"}, "99": {"user_id": 4, "timestamp": "2018-08-17T19:17:25.535593+01:00"}, "id": "01CN4HP4AN0000000000000001"}, {"0": {"user_id": 4, "timestamp": "2018-08-17T19:29:12.963264+01:00"}, "99": {"user_id": 4, "timestamp": "2018-08-17T19:32:09.272632+01:00"}, "id": "01CN4JC1430000000000000004"}] }

2018-08-20
30
{ "OPEN" : [{"0": {"user_id": 139, "timestamp": "2018-08-20T18:00:40.26043+01:00"}, "99": {"user_id": 139, "timestamp": "2018-08-20T18:00:47.583501+01:00"}, "id": "01CNC4G1Y40000000000000008"}] }

created_at	location_id	task_id	task_type	correlation_id	events
2018-08-17	30	58	OPEN	01CN4HP4AN0000000000000001	{ "99" : {"timestamp" : "2018-08-17T19:17:25.535593+01:00", "user_id" : 4}, "0" : {"timestamp" : "2018-08-17T19:17:15.348629+01:00", "user_id" : 4} }
2018-08-17	30	97	CLOSE	01CN4J9SZ80000000000000003	{ "0" : {"timestamp" : "2018-08-17T19:28:00.104093+01:00", "user_id" : 4}, "99" : {"timestamp" : "2018-08-17T19:29:09.01684+01:00", "user_id" : 4} }
2018-08-17	30	97	CLOSE	01CN4KJCDY0000000000000005	{ "99" : {"timestamp" : "2018-08-17T19:51:42.240895+01:00", "user_id" : 139}, "0" : {"timestamp" : "2018-08-17T19:50:09.725668+01:00", "user_id" : 139} }
2018-08-17	30	99	OPEN	01CN4JC1430000000000000004	{ "0" : {"timestamp" : "2018-08-17T19:29:12.963264+01:00", "user_id" : 4}, "99" : {"timestamp" : "2018-08-17T19:32:09.272632+01:00", "user_id" : 4} }
2018-08-20	30	99	OPEN	01CNC4G1Y40000000000000008	{ "99" : {"timestamp" : "2018-08-20T18:00:47.583501+01:00", "user_id" : 139}, "0" : {"timestamp" : "2018-08-20T18:00:40.26043+01:00", "user_id" : 139} }

created_date	location_id	events
2018-08-17	30	{ "OPEN" : {"0": {"user_id": 4, "timestamp": "2018-08-17T19:17:15.348629+01:00"}, "99": {"user_id": 4, "timestamp": "2018-08-17T19:17:25.535593+01:00"}, "id": "01CN4HP4AN0000000000000001"}, "CLOSE" : {"0": {"user_id": 4, "timestamp": "2018-08-17T19:28:00.104093+01:00"}, "99": {"user_id": 4, "timestamp": "2018-08-17T19:29:09.01684+01:00"}, "id": "01CN4J9SZ80000000000000003"}, "CLOSE" : {"0": {"user_id": 139, "timestamp": "2018-08-17T19:50:09.725668+01:00"}, "99": {"user_id": 139, "timestamp": "2018-08-17T19:51:42.240895+01:00"}, "id": "01CN4KJCDY0000000000000005"}, "OPEN" : {"0": {"user_id": 4, "timestamp": "2018-08-17T19:29:12.963264+01:00"}, "99": {"user_id": 4, "timestamp": "2018-08-17T19:32:09.272632+01:00"}, "id": "01CN4JC1430000000000000004"} }
2018-08-20	30	{ "OPEN" : {"0": {"user_id": 139, "timestamp": "2018-08-20T18:00:40.26043+01:00"}, "99": {"user_id": 139, "timestamp": "2018-08-20T18:00:47.583501+01:00"}, "id": "01CNC4G1Y40000000000000008"} }

created_date	location_id	events
2018-08-17	30	{ "CLOSE" : [{"0": {"user_id": 4, "timestamp": "2018-08-17T19:28:00.104093+01:00"}, "99": {"user_id": 4, "timestamp": "2018-08-17T19:29:09.01684+01:00"}, "id": "01CN4J9SZ80000000000000003"}, {"0": {"user_id": 139, "timestamp": "2018-08-17T19:50:09.725668+01:00"}, "99": {"user_id": 139, "timestamp": "2018-08-17T19:51:42.240895+01:00"}, "id": "01CN4KJCDY0000000000000005"}], "OPEN" : [{"0": {"user_id": 4, "timestamp": "2018-08-17T19:17:15.348629+01:00"}, "99": {"user_id": 4, "timestamp": "2018-08-17T19:17:25.535593+01:00"}, "id": "01CN4HP4AN0000000000000001"}, {"0": {"user_id": 4, "timestamp": "2018-08-17T19:29:12.963264+01:00"}, "99": {"user_id": 4, "timestamp": "2018-08-17T19:32:09.272632+01:00"}, "id": "01CN4JC1430000000000000004"}] }
2018-08-20	30	{ "OPEN" : [{"0": {"user_id": 139, "timestamp": "2018-08-20T18:00:40.26043+01:00"}, "99": {"user_id": 139, "timestamp": "2018-08-20T18:00:47.583501+01:00"}, "id": "01CNC4G1Y40000000000000008"}] }