EXPLAIN_ANALYZE

"Limit  (cost=6900944.76..6900945.40 rows=51 width=310) (actual time=236445.533..236445.567 rows=51 loops=1)"
"  ->  Sort  (cost=6900944.76..6900945.26 rows=200 width=310) (actual time=236445.531..236445.558 rows=51 loops=1)"
"        Sort Key: (min(count.""timestamp"")) DESC"
"        Sort Method: top-N heapsort  Memory: 4020kB"
"        ->  Nested Loop Left Join  (cost=6857145.13..6900937.12 rows=200 width=310) (actual time=191441.082..235780.106 rows=60797 loops=1)"
"              ->  Nested Loop Left Join  (cost=6857144.71..6900813.73 rows=200 width=108) (actual time=191441.062..234426.907 rows=60797 loops=1)"
"                    ->  GroupAggregate  (cost=6857144.28..6899155.23 rows=200 width=104) (actual time=191440.976..232361.857 rows=60797 loops=1)"
"                          Group Key: count.global_session_id"
"                          ->  Sort  (cost=6857144.28..6861345.03 rows=1680298 width=1372) (actual time=191439.421..193475.095 rows=1674396 loops=1)"
"                                Sort Key: count.global_session_id"
"                                Sort Method: external merge  Disk: 2445448kB"
"                                ->  Subquery Scan on count  (cost=5615324.40..5678335.57 rows=1680298 width=1372) (actual time=158960.581..164566.886 rows=1674396 loops=1)"
"                                      ->  WindowAgg  (cost=5615324.40..5661532.59 rows=1680298 width=1388) (actual time=158960.579..164158.664 rows=1674396 loops=1)"
"                                            ->  Sort  (cost=5615324.40..5619525.14 rows=1680298 width=1372) (actual time=158960.557..161528.067 rows=1674396 loops=1)"
"                                                  Sort Key: inner_sessions.distinct_id, inner_sessions.""timestamp"""
"                                                  Sort Method: external merge  Disk: 2450200kB"
"                                                  ->  Subquery Scan on inner_sessions  (cost=4415511.96..4436515.68 rows=1680298 width=1372) (actual time=134015.014..136329.420 rows=1674396 loops=1)"
"                                                        ->  Sort  (cost=4415511.96..4419712.70 rows=1680298 width=1452) (actual time=134015.009..135805.552 rows=1674396 loops=1)"
"                                                              Sort Key: posthog_event.""timestamp"" DESC"
"                                                              Sort Method: external merge  Disk: 2459624kB"
"                                                              ->  WindowAgg  (cost=3145665.78..3179271.74 rows=1680298 width=1452) (actual time=120278.212..125106.777 rows=1674396 loops=1)"
"                                                                    ->  Sort  (cost=3145665.78..3149866.53 rows=1680298 width=1364) (actual time=120278.162..122957.199 rows=1674396 loops=1)"
"                                                                          Sort Key: posthog_event.distinct_id, posthog_event.""timestamp"""
"                                                                          Sort Method: external merge  Disk: 2432312kB"
"                                                                          ->  Bitmap Heap Scan on posthog_event  (cost=62236.23..1972600.57 rows=1680298 width=1364) (actual time=686.900..103125.126 rows=1674396 loops=1)"
"                                                                                Recheck Cond: ((""timestamp"" >= '2021-01-01 00:00:00+00'::timestamp with time zone) AND (""timestamp"" <= '2021-01-02 00:00:00+00'::timestamp with time zone) AND (team_id = 1))"
"                                                                                Heap Blocks: exact=337249"
"                                                                                ->  Bitmap Index Scan on posthog_eve_timesta_1f6a8c_idx  (cost=0.00..61816.16 rows=1680298 width=0) (actual time=558.980..558.980 rows=1674396 loops=1)"
"                                                                                      Index Cond: ((""timestamp"" >= '2021-01-01 00:00:00+00'::timestamp with time zone) AND (""timestamp"" <= '2021-01-02 00:00:00+00'::timestamp with time zone) AND (team_id = 1))"
"                    ->  Index Scan using ""unique distinct_id for team"" on posthog_persondistinctid  (cost=0.42..8.28 rows=1 width=60) (actual time=0.032..0.032 rows=1 loops=60797)"
"                          Index Cond: ((team_id = 1) AND ((distinct_id)::text = (max((count.distinct_id)::text))))"
"              ->  Index Scan using posthog_person_pkey on posthog_person  (cost=0.42..0.62 rows=1 width=210) (actual time=0.021..0.021 rows=1 loops=60797)"
"                    Index Cond: (id = posthog_persondistinctid.person_id)"
"Planning Time: 4.223 ms"
"Execution Time: 237896.951 ms"

query

SELECT
                *
            FROM (
                SELECT
                    global_session_id,
                    properties,
                    start_time,
                    end_time,
                    length,
                    sessions.distinct_id,
                    event_count,
                    events
                FROM (
                    SELECT
                        global_session_id,
                        count(1) as event_count,
                        MAX(distinct_id) as distinct_id,
                        EXTRACT('EPOCH' FROM (MAX(timestamp) - MIN(timestamp))) AS length,
                        MIN(timestamp) as start_time,
                        MAX(timestamp) as end_time,
                        array_agg(json_build_object( 'id', id, 'event', event, 'timestamp', timestamp, 'properties', properties, 'elements_hash', elements_hash) ORDER BY timestamp) as events
                    FROM
                        (            SELECT *,                SUM(new_session) OVER (ORDER BY distinct_id, timestamp) AS global_session_id,                SUM(new_session) OVER (PARTITION BY distinct_id ORDER BY timestamp) AS user_session_id                FROM (SELECT id, team_id, distinct_id, event, elements_hash, timestamp, properties, CASE WHEN EXTRACT('EPOCH' FROM (timestamp - previous_timestamp)) >= (60 * 30)                    OR previous_timestamp IS NULL                     THEN 1 ELSE 0 END AS new_session                     FROM (SELECT "posthog_event"."id", "posthog_event"."created_at", "posthog_event"."team_id", "posthog_event"."event", "posthog_event"."distinct_id", "posthog_event"."properties", "posthog_event"."timestamp", "posthog_event"."elements_hash", "posthog_event"."elements", (SELECT U0."person_id" FROM "posthog_persondistinctid" U0 WHERE (U0."distinct_id" = "posthog_event"."distinct_id" AND U0."team_id" = 1) LIMIT 1) AS "person_id", LAG("posthog_event"."timestamp", 1) OVER (PARTITION BY "posthog_event"."distinct_id" ORDER BY "posthog_event"."timestamp" ASC) AS "previous_timestamp", LAG("posthog_event"."event", 1) OVER (PARTITION BY "posthog_event"."distinct_id" ORDER BY "posthog_event"."timestamp" ASC) AS "previous_event" FROM "posthog_event" WHERE ("posthog_event"."team_id" = 1 AND "posthog_event"."timestamp" >= '2021-01-01T00:00:00+00:00'::timestamptz AND "posthog_event"."timestamp" <= '2021-01-02T00:00:00+00:00'::timestamptz) ORDER BY "posthog_event"."timestamp" DESC) AS inner_sessions                ) AS outer_sessions) as count
                    GROUP BY 1
                ) as sessions
                LEFT OUTER JOIN
                    posthog_persondistinctid ON posthog_persondistinctid.distinct_id = sessions.distinct_id AND posthog_persondistinctid.team_id = 1
                LEFT OUTER JOIN
                    posthog_person ON posthog_person.id = posthog_persondistinctid.person_id
                ORDER BY
                    start_time DESC
            ) as ordered_sessions
            OFFSET 0
            LIMIT 51;