Postgres 15

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

DROP TABLE IF EXISTS event_log CASCADE;
CREATE TABLE event_log (
	  ts timestamptz
	, name varchar
);

INSERT INTO event_log ( ts, name ) VALUES
  ('2024-07-01T12:00:00.000Z', 'Alice')
, ('2024-07-01T12:01:00.000Z', 'Alice')
, ('2024-07-01T12:02:00.000Z', 'Alice')
, ('2024-07-01T12:03:00.000Z', 'Alice')
, ('2024-07-01T12:04:00.000Z', 'Alice')
, ('2024-07-01T12:05:00.000Z', 'Alice')
, ('2024-07-01T12:12:00.000Z', 'Alice')
, ('2024-07-01T12:14:00.000Z', 'Alice')
, ('2024-07-01T12:17:00.000Z', 'Alice')
, ('2024-07-01T12:21:00.000Z', 'Alice')
, ('2024-07-01T12:26:00.000Z', 'Alice')
, ('2024-07-01T12:30:00.000Z', 'Alice')
, ('2024-07-01T12:00:00.000Z', 'Bob')
, ('2024-07-01T12:02:00.000Z', 'Bob')
, ('2024-07-01T12:05:00.000Z', 'Bob')
, ('2024-07-01T12:09:00.000Z', 'Bob')
, ('2024-07-01T12:14:00.000Z', 'Bob')
, ('2024-07-01T12:20:00.000Z', 'Bob')
, ('2024-07-01T12:25:00.000Z', 'Bob')
, ('2024-07-01T12:35:00.000Z', 'Bob')
, ('2024-07-01T12:44:00.000Z', 'Bob')
, ('2024-07-01T12:48:00.000Z', 'Bob')
, ('2024-07-01T13:05:00.000Z', 'Bob')
;

DROP TABLE



CREATE TABLE



INSERT 0 23


select * from event_log order by ts desc


ts
name


2024-07-01 14:05:00+01
Bob

2024-07-01 13:48:00+01
Bob

2024-07-01 13:44:00+01
Bob

2024-07-01 13:35:00+01
Bob

2024-07-01 13:30:00+01
Alice

2024-07-01 13:26:00+01
Alice

2024-07-01 13:25:00+01
Bob

2024-07-01 13:21:00+01
Alice

2024-07-01 13:20:00+01
Bob

2024-07-01 13:17:00+01
Alice

2024-07-01 13:14:00+01
Bob

2024-07-01 13:14:00+01
Alice

2024-07-01 13:12:00+01
Alice

2024-07-01 13:09:00+01
Bob

2024-07-01 13:05:00+01
Alice

2024-07-01 13:05:00+01
Bob

2024-07-01 13:04:00+01
Alice

2024-07-01 13:03:00+01
Alice

2024-07-01 13:02:00+01
Bob

2024-07-01 13:02:00+01
Alice

2024-07-01 13:01:00+01
Alice

2024-07-01 13:00:00+01
Bob

2024-07-01 13:00:00+01
Alice



SELECT 23


with inputs as (
	select
		  '5 minutes'::interval threshold -- change this to however long you want your session timeouts to be
		, '1 minute'::interval min_session_duration -- if there is only one event in the session, how long do you want to say it lasted?
)
, prep as (
	select
		  ts
		, name
		, row_number() 
            over (partition by name order by ts) row_num
		, coalesce(
            ts - lag(ts) 
              over (partition by name order by ts asc) <= inputs.threshold
            , false) is_connected
	from
		event_log
	cross
		join inputs
)
, session_detail as (
	select
		  ts
		, name
		, max(row_num) 
            filter (where NOT is_connected) 
            over (partition by name order by ts asc) session_seq
	from prep
)
, sessions as (
	select
		  row_number() 
            over (partition by name order by min(ts), session_seq) session_seq
		, name
		, count(*) event_count
		, min(ts) start_ts
		, max(ts) end_ts
		, greatest(inputs.min_session_duration, max(ts) - min(ts)) session_duration
	from session_detail
	cross
		join inputs
	group by
		  session_seq
		, name
		, inputs.min_session_duration
)
select
	  session_seq
	, concat_ws('-', name, session_seq) unique_id
	, name
	, event_count
	, start_ts
	, end_ts
	, session_duration
from sessions;





session_seq
unique_id
name
event_count
start_ts
end_ts
session_duration


1
Alice-1
Alice
6
2024-07-01 13:00:00+01
2024-07-01 13:05:00+01
00:05:00

2
Alice-2
Alice
6
2024-07-01 13:12:00+01
2024-07-01 13:30:00+01
00:18:00

1
Bob-1
Bob
5
2024-07-01 13:00:00+01
2024-07-01 13:14:00+01
00:14:00

2
Bob-2
Bob
2
2024-07-01 13:20:00+01
2024-07-01 13:25:00+01
00:05:00

3
Bob-3
Bob
1
2024-07-01 13:35:00+01
2024-07-01 13:35:00+01
00:01:00

4
Bob-4
Bob
2
2024-07-01 13:44:00+01
2024-07-01 13:48:00+01
00:04:00

5
Bob-5
Bob
1
2024-07-01 14:05:00+01
2024-07-01 14:05:00+01
00:01:00



SELECT 7

ts	name
2024-07-01 14:05:00+01	Bob
2024-07-01 13:48:00+01	Bob
2024-07-01 13:44:00+01	Bob
2024-07-01 13:35:00+01	Bob
2024-07-01 13:30:00+01	Alice
2024-07-01 13:26:00+01	Alice
2024-07-01 13:25:00+01	Bob
2024-07-01 13:21:00+01	Alice
2024-07-01 13:20:00+01	Bob
2024-07-01 13:17:00+01	Alice
2024-07-01 13:14:00+01	Bob
2024-07-01 13:14:00+01	Alice
2024-07-01 13:12:00+01	Alice
2024-07-01 13:09:00+01	Bob
2024-07-01 13:05:00+01	Alice
2024-07-01 13:05:00+01	Bob
2024-07-01 13:04:00+01	Alice
2024-07-01 13:03:00+01	Alice
2024-07-01 13:02:00+01	Bob
2024-07-01 13:02:00+01	Alice
2024-07-01 13:01:00+01	Alice
2024-07-01 13:00:00+01	Bob
2024-07-01 13:00:00+01	Alice

session_seq	unique_id	name	event_count	start_ts	end_ts	session_duration
1	Alice-1	Alice	6	2024-07-01 13:00:00+01	2024-07-01 13:05:00+01	00:05:00
2	Alice-2	Alice	6	2024-07-01 13:12:00+01	2024-07-01 13:30:00+01	00:18:00
1	Bob-1	Bob	5	2024-07-01 13:00:00+01	2024-07-01 13:14:00+01	00:14:00
2	Bob-2	Bob	2	2024-07-01 13:20:00+01	2024-07-01 13:25:00+01	00:05:00
3	Bob-3	Bob	1	2024-07-01 13:35:00+01	2024-07-01 13:35:00+01	00:01:00
4	Bob-4	Bob	2	2024-07-01 13:44:00+01	2024-07-01 13:48:00+01	00:04:00
5	Bob-5	Bob	1	2024-07-01 14:05:00+01	2024-07-01 14:05:00+01	00:01:00