Postgres 12

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 mytable (
    id int,
    created_at timestamp,
    user_id int
);

INSERT INTO mytable VALUES
    (1, '2019-10-14 09:26:53.813', 1),
    (2, '2019-10-14 09:26:54.813', 1),
    (3, '2019-10-14 09:46:53.813', 1),
    (4, '2019-10-14 09:46:54.813', 2),
    (5, '2019-10-14 09:46:55.813', 1),
    (6, '2019-10-14 09:46:56.813', 1),
    (7, '2019-10-14 09:46:58.813', 2),
    (8, '2019-10-14 10:06:56.813', 1);

CREATE TABLE



INSERT 0 8


SELECT
    *,
    lag(created_at) OVER (PARTITION BY user_id ORDER BY created_at)
FROM
    mytable


id
created_at
user_id
lag


1
2019-10-14 09:26:53.813
1
null

2
2019-10-14 09:26:54.813
1
2019-10-14 09:26:53.813

3
2019-10-14 09:46:53.813
1
2019-10-14 09:26:54.813

5
2019-10-14 09:46:55.813
1
2019-10-14 09:46:53.813

6
2019-10-14 09:46:56.813
1
2019-10-14 09:46:55.813

8
2019-10-14 10:06:56.813
1
2019-10-14 09:46:56.813

4
2019-10-14 09:46:54.813
2
null

7
2019-10-14 09:46:58.813
2
2019-10-14 09:46:54.813



SELECT 8


SELECT
    *,
    created_at - lag(created_at, 1, created_at) OVER (PARTITION BY user_id ORDER BY created_at)
FROM
    mytable


id
created_at
user_id
?column?


1
2019-10-14 09:26:53.813
1
00:00:00

2
2019-10-14 09:26:54.813
1
00:00:01

3
2019-10-14 09:46:53.813
1
00:19:59

5
2019-10-14 09:46:55.813
1
00:00:02

6
2019-10-14 09:46:56.813
1
00:00:01

8
2019-10-14 10:06:56.813
1
00:20:00

4
2019-10-14 09:46:54.813
2
00:00:00

7
2019-10-14 09:46:58.813
2
00:00:04



SELECT 8


SELECT
    *,
    created_at 
        - lag(created_at, 1, created_at) OVER (PARTITION BY user_id ORDER BY created_at)
        > interval '10 minutes'
FROM
    mytable


id
created_at
user_id
?column?


1
2019-10-14 09:26:53.813
1
f

2
2019-10-14 09:26:54.813
1
f

3
2019-10-14 09:46:53.813
1
t

5
2019-10-14 09:46:55.813
1
f

6
2019-10-14 09:46:56.813
1
f

8
2019-10-14 10:06:56.813
1
t

4
2019-10-14 09:46:54.813
2
f

7
2019-10-14 09:46:58.813
2
f



SELECT 8


SELECT
    *,
    (created_at 
        - lag(created_at, 1, created_at) OVER (PARTITION BY user_id ORDER BY created_at)
        > interval '10 minutes')::int AS group_id
FROM
    mytable


id
created_at
user_id
group_id


1
2019-10-14 09:26:53.813
1
0

2
2019-10-14 09:26:54.813
1
0

3
2019-10-14 09:46:53.813
1
1

5
2019-10-14 09:46:55.813
1
0

6
2019-10-14 09:46:56.813
1
0

8
2019-10-14 10:06:56.813
1
1

4
2019-10-14 09:46:54.813
2
0

7
2019-10-14 09:46:58.813
2
0



SELECT 8


SELECT
    *,
    SUM(group_id) OVER (PARTITION BY user_id ORDER BY created_at)
FROM (
    SELECT
        *,
        (created_at 
            - lag(created_at, 1, created_at) OVER (PARTITION BY user_id ORDER BY created_at)
            > interval '10 minutes')::int AS group_id
    FROM
        mytable   
)s


id
created_at
user_id
group_id
sum


1
2019-10-14 09:26:53.813
1
0
0

2
2019-10-14 09:26:54.813
1
0
0

3
2019-10-14 09:46:53.813
1
1
1

5
2019-10-14 09:46:55.813
1
0
1

6
2019-10-14 09:46:56.813
1
0
1

8
2019-10-14 10:06:56.813
1
1
2

4
2019-10-14 09:46:54.813
2
0
0

7
2019-10-14 09:46:58.813
2
0
0



SELECT 8


SELECT
    user_id,
    group_id,
    first_value(created_at) OVER (PARTITION BY user_id, group_id ORDER BY created_at DESC) AS last_value,
    first_value(created_at) OVER (PARTITION BY user_id, group_id ORDER BY created_at) AS first_value
FROM (
    SELECT
        id, created_at, user_id,
        SUM(group_id) OVER (PARTITION BY user_id ORDER BY created_at) AS group_id
    FROM (
        SELECT
            *,
            (created_at 
                - lag(created_at, 1, created_at) OVER (PARTITION BY user_id ORDER BY created_at)
                > interval '10 minutes')::int AS group_id
        FROM
            mytable   
    )s
)s


user_id
group_id
last_value
first_value


1
0
2019-10-14 09:26:54.813
2019-10-14 09:26:53.813

1
0
2019-10-14 09:26:54.813
2019-10-14 09:26:53.813

1
1
2019-10-14 09:46:56.813
2019-10-14 09:46:53.813

1
1
2019-10-14 09:46:56.813
2019-10-14 09:46:53.813

1
1
2019-10-14 09:46:56.813
2019-10-14 09:46:53.813

1
2
2019-10-14 10:06:56.813
2019-10-14 10:06:56.813

2
0
2019-10-14 09:46:58.813
2019-10-14 09:46:54.813

2
0
2019-10-14 09:46:58.813
2019-10-14 09:46:54.813



SELECT 8


SELECT DISTINCT
    user_id,
    group_id,
    first_value(created_at) OVER (PARTITION BY user_id, group_id ORDER BY created_at DESC)
        - first_value(created_at) OVER (PARTITION BY user_id, group_id ORDER BY created_at)
FROM (
    SELECT
        id, created_at, user_id,
        SUM(group_id) OVER (PARTITION BY user_id ORDER BY created_at) AS group_id
    FROM (
        SELECT
            *,
            (created_at 
                - lag(created_at, 1, created_at) OVER (PARTITION BY user_id ORDER BY created_at)
                > interval '10 minutes')::int AS group_id
        FROM
            mytable   
    )s
)s


user_id
group_id
?column?


2
0
00:00:04

1
0
00:00:01

1
2
00:00:00

1
1
00:00:03



SELECT 4


SELECT 
    user_id,
    AVG(diff)
FROM (
    SELECT DISTINCT
        user_id,
        group_id,
        first_value(created_at) OVER (PARTITION BY user_id, group_id ORDER BY created_at DESC)
            - first_value(created_at) OVER (PARTITION BY user_id, group_id ORDER BY created_at) as diff
    FROM (
        SELECT
            id, created_at, user_id,
            SUM(group_id) OVER (PARTITION BY user_id ORDER BY created_at) AS group_id
        FROM (
            SELECT
                *,
                (created_at 
                    - lag(created_at, 1, created_at) OVER (PARTITION BY user_id ORDER BY created_at)
                    > interval '10 minutes')::int AS group_id
            FROM
                mytable   
        )s
    )s
)s
GROUP BY user_id


user_id
avg


2
00:00:04

1
00:00:01.333333



SELECT 2

id	created_at	user_id	lag
1	2019-10-14 09:26:53.813	1	null
2	2019-10-14 09:26:54.813	1	2019-10-14 09:26:53.813
3	2019-10-14 09:46:53.813	1	2019-10-14 09:26:54.813
5	2019-10-14 09:46:55.813	1	2019-10-14 09:46:53.813
6	2019-10-14 09:46:56.813	1	2019-10-14 09:46:55.813
8	2019-10-14 10:06:56.813	1	2019-10-14 09:46:56.813
4	2019-10-14 09:46:54.813	2	null
7	2019-10-14 09:46:58.813	2	2019-10-14 09:46:54.813

id	created_at	user_id	?column?
1	2019-10-14 09:26:53.813	1	00:00:00
2	2019-10-14 09:26:54.813	1	00:00:01
3	2019-10-14 09:46:53.813	1	00:19:59
5	2019-10-14 09:46:55.813	1	00:00:02
6	2019-10-14 09:46:56.813	1	00:00:01
8	2019-10-14 10:06:56.813	1	00:20:00
4	2019-10-14 09:46:54.813	2	00:00:00
7	2019-10-14 09:46:58.813	2	00:00:04