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.5 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.5.0 20210514 (Red Hat 8.5.0-10), 64-bit |
SELECT 1
CREATE TABLE labels(user_id INT, session_id INT,
start_time TIMESTAMP,mode TEXT);
INSERT INTO labels (user_id,session_id,start_time,mode)
VALUES (48,652,'2016-04-01 00:47:00+01','foot'),
(9,656,'2016-04-01 00:03:39+01','car'),(9,657,'2016-04-01 00:26:51+01','car'),
(9,658,'2016-04-01 00:45:19+01','car'),(46,663,'2016-04-01 00:13:12+01','car');
CREATE TABLE
INSERT 0 5
CREATE TABLE raw_data(user_id INT, session_id INT,timestamp TIMESTAMP) ;
INSERT INTO raw_data(user_id, session_id,timestamp)
VALUES (8,652,'2016-04-01 00:46:11.638+01'),(8,652,'2016-04-01 00:47:00.566+01'),
(8,652,'2016-04-01 00:48:06.383+01'),(9,656,'2016-04-01 00:14:17.707+01'),
(9,656,'2016-04-01 00:15:18.664+01'),(9,656,'2016-04-01 00:16:19.687+01'),
(9,656,'2016-04-01 00:24:20.691+01'),(9,656,'2016-04-01 00:25:23.681+01'),
(9,657,'2016-04-01 00:24:50.842+01'),(9,657,'2016-04-01 00:26:51.096+01'),
(9,657,'2016-04-01 00:37:54.092+01');
CREATE TABLE
INSERT 0 11
select * from raw_data; select * from labels
| user_id | session_id | timestamp |
|---|---|---|
| 8 | 652 | 2016-04-01 00:46:11.638 |
| 8 | 652 | 2016-04-01 00:47:00.566 |
| 8 | 652 | 2016-04-01 00:48:06.383 |
| 9 | 656 | 2016-04-01 00:14:17.707 |
| 9 | 656 | 2016-04-01 00:15:18.664 |
| 9 | 656 | 2016-04-01 00:16:19.687 |
| 9 | 656 | 2016-04-01 00:24:20.691 |
| 9 | 656 | 2016-04-01 00:25:23.681 |
| 9 | 657 | 2016-04-01 00:24:50.842 |
| 9 | 657 | 2016-04-01 00:26:51.096 |
| 9 | 657 | 2016-04-01 00:37:54.092 |
SELECT 11
| user_id | session_id | start_time | mode |
|---|---|---|---|
| 48 | 652 | 2016-04-01 00:47:00 | foot |
| 9 | 656 | 2016-04-01 00:03:39 | car |
| 9 | 657 | 2016-04-01 00:26:51 | car |
| 9 | 658 | 2016-04-01 00:45:19 | car |
| 46 | 663 | 2016-04-01 00:13:12 | car |
SELECT 5
select session_id
,timestamp
,user_id
,start_time
,count(diff) over() as number_of_sessions_with_problem
from (
select session_id
,timestamp
,labels.user_id
,start_time
,case when lead(timestamp) over(partition by session_id order by timestamp)-timestamp > '00:05:00.000' then 1 end as diff
from raw_data join labels using(session_id)
) t
where diff = 1
| session_id | timestamp | user_id | start_time | number_of_sessions_with_problem |
|---|---|---|---|---|
| 656 | 2016-04-01 00:16:19.687 | 9 | 2016-04-01 00:03:39 | 2 |
| 657 | 2016-04-01 00:26:51.096 | 9 | 2016-04-01 00:26:51 | 2 |
SELECT 2