explain.depesz.com

PostgreSQL's explain analyze made readable

Result: qg0v

Settings
# exclusive inclusive rows x rows loops node
1. 6.245 8,895.164 ↓ 716.0 2,864 1

Sort (cost=168,251.92..168,251.93 rows=4 width=184) (actual time=8,895.017..8,895.164 rows=2,864 loops=1)

  • Sort Key: (COALESCE(data_stats_session.day, (COALESCE((COALESCE((data."stats_page_view.created_at")::text, '-'::text)), '[TOTAL]'::text)), data_stats_event.day)) DESC, (COALESCE(data_stats_session.group1, (COALESCE((COALESCE((data."stats_session.user_agent_family")::text, '-'::text)), '[TOTAL]'::text)), data_stats_event.group1)), (COALESCE(data_stats_session.group2, (COALESCE((COALESCE((data."stats_event.event_id")::text, '-'::text)), '[TOTAL]'::text)), data_stats_event.group2))
  • Sort Method: quicksort Memory: 499kB
2.          

CTE data

3. 110.513 7,165.517 ↓ 97,000.0 97,000 1

Nested Loop (cost=1,000.00..168,251.07 rows=1 width=241) (actual time=3.391..7,165.517 rows=97,000 loops=1)

  • Join Filter: (((stats_session.campaign_id)::numeric >= '1'::numeric) OR (((_hyper_5_4_chunk."values" ->> 'scrollTriggerValue'::text))::numeric <= '100'::numeric))
4. 52.885 6,667.004 ↓ 8,083.3 97,000 1

Nested Loop (cost=1,000.00..168,220.97 rows=12 width=104) (actual time=3.373..6,667.004 rows=97,000 loops=1)

5. 13.467 6,226.119 ↓ 850.9 97,000 1

Gather (cost=1,000.00..167,939.68 rows=114 width=76) (actual time=3.352..6,226.119 rows=97,000 loops=1)

  • Workers Planned: 2
  • Workers Launched: 0
6. 10.223 6,212.652 ↓ 2,020.8 97,000 1

Parallel Append (cost=0.00..166,928.28 rows=48 width=76) (actual time=2.970..6,212.652 rows=97,000 loops=1)

7. 6,202.428 6,202.428 ↓ 2,063.8 97,000 1

Parallel Seq Scan on _hyper_5_4_chunk (cost=0.00..166,928.04 rows=47 width=76) (actual time=2.969..6,202.428 rows=97,000 loops=1)

  • Filter: (((created_at)::timestamp without time zone >= '2019-03-11 08:22:00'::timestamp without time zone) AND ((created_at)::timestamp without time zone <= '2019-05-10 08:22:59'::timestamp without time zone) AND ((property_id)::numeric = '1'::numeric))
  • Rows Removed by Filter: 4402999
8. 0.001 0.001 ↓ 0.0 0 1

Parallel Seq Scan on stats_event (cost=0.00..0.00 rows=1 width=76) (actual time=0.001..0.001 rows=0 loops=1)

  • Filter: (((created_at)::timestamp without time zone >= '2019-03-11 08:22:00'::timestamp without time zone) AND ((created_at)::timestamp without time zone <= '2019-05-10 08:22:59'::timestamp without time zone) AND ((property_id)::numeric = '1'::numeric))
9. 0.000 388.000 ↑ 2.0 1 97,000

Custom Scan (ConstraintAwareAppend) (cost=0.00..2.45 rows=2 width=44) (actual time=0.003..0.004 rows=1 loops=97,000)

  • Hypertable: stats_page_view
  • Chunks left after exclusion: 1
10. 97.000 388.000 ↑ 2.0 1 97,000

Append (cost=0.00..2.45 rows=2 width=44) (actual time=0.003..0.004 rows=1 loops=97,000)

11. 291.000 291.000 ↑ 1.0 1 97,000

Index Scan using "3_3_stats_page_view_id" on _hyper_3_3_chunk (cost=0.42..2.44 rows=1 width=44) (actual time=0.003..0.003 rows=1 loops=97,000)

  • Index Cond: (id = _hyper_5_4_chunk.page_view_id)
  • Filter: (((created_at)::timestamp without time zone >= '2019-03-11 08:22:00'::timestamp without time zone) AND ((created_at)::timestamp without time zone <= '2019-05-10 08:22:59'::timestamp without time zone) AND ((property_id)::numeric = '1'::numeric))
12. 97.000 388.000 ↑ 2.0 1 97,000

Custom Scan (ConstraintAwareAppend) (cost=0.00..2.46 rows=2 width=157) (actual time=0.003..0.004 rows=1 loops=97,000)

  • Hypertable: stats_session
  • Chunks left after exclusion: 1
13. 0.000 291.000 ↑ 2.0 1 97,000

Append (cost=0.00..2.46 rows=2 width=157) (actual time=0.003..0.003 rows=1 loops=97,000)

14. 291.000 291.000 ↑ 1.0 1 97,000

Index Scan using "2_2_stats_session_id" on _hyper_2_2_chunk (cost=0.41..2.45 rows=1 width=52) (actual time=0.003..0.003 rows=1 loops=97,000)

  • Index Cond: (id = stats_page_view.session_id)
  • Filter: (((created_at)::timestamp without time zone >= '2019-03-11 08:22:00'::timestamp without time zone) AND ((created_at)::timestamp without time zone <= '2019-05-10 08:22:59'::timestamp without time zone) AND ((property_id)::numeric = '1'::numeric))
15. 2.472 8,888.919 ↓ 716.0 2,864 1

Hash Full Join (cost=0.55..0.81 rows=4 width=184) (actual time=8,752.060..8,888.919 rows=2,864 loops=1)

  • Hash Cond: ((COALESCE(data_stats_session.day, (COALESCE((COALESCE((data."stats_page_view.created_at")::text, '-'::text)), '[TOTAL]'::text))) = data_stats_event.day) AND (COALESCE(data_stats_session.group1, (COALESCE((COALESCE((data."stats_session.user_agent_family")::text, '-'::text)), '[TOTAL]'::text))) = data_stats_event.group1) AND (COALESCE(data_stats_session.group2, (COALESCE((COALESCE((data."stats_event.event_id")::text, '-'::text)), '[TOTAL]'::text))) = data_stats_event.group2))
16. 2.141 1,027.481 ↓ 716.0 2,864 1

Hash Full Join (cost=0.29..0.48 rows=4 width=240) (actual time=893.084..1,027.481 rows=2,864 loops=1)

  • Hash Cond: (((COALESCE((COALESCE((data."stats_page_view.created_at")::text, '-'::text)), '[TOTAL]'::text)) = COALESCE(data_stats_session.day)) AND ((COALESCE((COALESCE((data."stats_session.user_agent_family")::text, '-'::text)), '[TOTAL]'::text)) = COALESCE(data_stats_session.group1)) AND ((COALESCE((COALESCE((data."stats_event.event_id")::text, '-'::text)), '[TOTAL]'::text)) = COALESCE(data_stats_session.group2)))
17. 39.760 475.161 ↓ 716.0 2,864 1

GroupAggregate (cost=0.04..0.14 rows=4 width=224) (actual time=342.887..475.161 rows=2,864 loops=1)

  • Group Key: (COALESCE((data."stats_page_view.created_at")::text, '-'::text)), (COALESCE((data."stats_session.user_agent_family")::text, '-'::text)), (COALESCE((data."stats_event.event_id")::text, '-'::text))
  • Group Key: (COALESCE((data."stats_page_view.created_at")::text, '-'::text)), (COALESCE((data."stats_session.user_agent_family")::text, '-'::text))
  • Group Key: (COALESCE((data."stats_page_view.created_at")::text, '-'::text))
  • Group Key: ()
18. 33.558 435.401 ↓ 88,893.0 88,893 1

Group (cost=0.04..0.07 rows=1 width=124) (actual time=342.861..435.401 rows=88,893 loops=1)

  • Group Key: (COALESCE((data."stats_page_view.created_at")::text, '-'::text)), (COALESCE((data."stats_session.user_agent_family")::text, '-'::text)), (COALESCE((data."stats_event.event_id")::text, '-'::text)), data."stats_page_view.runtime_active", data."stats_page_view.id", data."stats_page_view.created_at
19. 343.729 401.843 ↓ 97,000.0 97,000 1

Sort (cost=0.04..0.05 rows=1 width=124) (actual time=342.857..401.843 rows=97,000 loops=1)

  • Sort Key: (COALESCE((data."stats_page_view.created_at")::text, '-'::text)), (COALESCE((data."stats_session.user_agent_family")::text, '-'::text)), (COALESCE((data."stats_event.event_id")::text, '-'::text)), data."stats_page_view.runtime_active", data."stats_page_view.id", data."stats_page_view.created_at
  • Sort Method: external merge Disk: 7272kB
20. 58.114 58.114 ↓ 97,000.0 97,000 1

CTE Scan on data (cost=0.00..0.03 rows=1 width=124) (actual time=0.020..58.114 rows=97,000 loops=1)

21. 1.306 550.179 ↓ 716.0 2,864 1

Hash (cost=0.18..0.18 rows=4 width=112) (actual time=550.179..550.179 rows=2,864 loops=1)

  • Buckets: 4096 (originally 1024) Batches: 1 (originally 1) Memory Usage: 279kB
22. 0.544 548.873 ↓ 716.0 2,864 1

Subquery Scan on data_stats_session (cost=0.04..0.18 rows=4 width=112) (actual time=400.096..548.873 rows=2,864 loops=1)

23. 49.338 548.329 ↓ 716.0 2,864 1

GroupAggregate (cost=0.04..0.14 rows=4 width=208) (actual time=400.094..548.329 rows=2,864 loops=1)

  • Group Key: (COALESCE((data_1."stats_session.created_at")::text, '-'::text)), (COALESCE((data_1."stats_session.user_agent_family")::text, '-'::text)), (COALESCE((data_1."stats_event.event_id")::text, '-'::text))
  • Group Key: (COALESCE((data_1."stats_session.created_at")::text, '-'::text)), (COALESCE((data_1."stats_session.user_agent_family")::text, '-'::text))
  • Group Key: (COALESCE((data_1."stats_session.created_at")::text, '-'::text))
  • Group Key: ()
24. 31.973 498.991 ↓ 42,092.0 42,092 1

Group (cost=0.04..0.07 rows=1 width=354) (actual time=400.059..498.991 rows=42,092 loops=1)

  • Group Key: (COALESCE((data_1."stats_session.created_at")::text, '-'::text)), (COALESCE((data_1."stats_session.user_agent_family")::text, '-'::text)), (COALESCE((data_1."stats_event.event_id")::text, '-'::text)), data_1."stats_session.user_agent_family", data_1."stats_session.id", data_1."stats_session.user_id", data_1."stats_session.created_at
25. 402.810 467.018 ↓ 97,000.0 97,000 1

Sort (cost=0.04..0.05 rows=1 width=354) (actual time=400.056..467.018 rows=97,000 loops=1)

  • Sort Key: (COALESCE((data_1."stats_session.created_at")::text, '-'::text)), (COALESCE((data_1."stats_session.user_agent_family")::text, '-'::text)), (COALESCE((data_1."stats_event.event_id")::text, '-'::text)), data_1."stats_session.user_agent_family", data_1."stats_session.id", data_1."stats_session.user_id", data_1."stats_session.created_at
  • Sort Method: external merge Disk: 9328kB
26. 64.208 64.208 ↓ 97,000.0 97,000 1

CTE Scan on data data_1 (cost=0.00..0.03 rows=1 width=354) (actual time=0.524..64.208 rows=97,000 loops=1)

27. 1.089 7,858.966 ↓ 716.0 2,864 1

Hash (cost=0.20..0.20 rows=4 width=136) (actual time=7,858.965..7,858.966 rows=2,864 loops=1)

  • Buckets: 4096 (originally 1024) Batches: 1 (originally 1) Memory Usage: 256kB
28. 0.504 7,857.877 ↓ 716.0 2,864 1

Subquery Scan on data_stats_event (cost=0.04..0.20 rows=4 width=136) (actual time=7,694.791..7,857.877 rows=2,864 loops=1)

29. 46.912 7,857.373 ↓ 716.0 2,864 1

GroupAggregate (cost=0.04..0.16 rows=4 width=232) (actual time=7,694.789..7,857.373 rows=2,864 loops=1)

  • Group Key: (COALESCE((data_2."stats_event.created_at")::text, '-'::text)), (COALESCE((data_2."stats_session.user_agent_family")::text, '-'::text)), (COALESCE((data_2."stats_event.event_id")::text, '-'::text))
  • Group Key: (COALESCE((data_2."stats_event.created_at")::text, '-'::text)), (COALESCE((data_2."stats_session.user_agent_family")::text, '-'::text))
  • Group Key: (COALESCE((data_2."stats_event.created_at")::text, '-'::text))
  • Group Key: ()
30. 35.887 7,810.461 ↓ 97,000.0 97,000 1

Group (cost=0.04..0.07 rows=1 width=156) (actual time=7,694.766..7,810.461 rows=97,000 loops=1)

  • Group Key: (COALESCE((data_2."stats_event.created_at")::text, '-'::text)), (COALESCE((data_2."stats_session.user_agent_family")::text, '-'::text)), (COALESCE((data_2."stats_event.event_id")::text, '-'::text)), data_2."stats_event.event_id", data_2."stats_event.values->>'scrollTriggerValue'", data_2."stats_event.id", data_2."stats_event.created_at
31. 440.241 7,774.574 ↓ 97,000.0 97,000 1

Sort (cost=0.04..0.05 rows=1 width=156) (actual time=7,694.761..7,774.574 rows=97,000 loops=1)

  • Sort Key: (COALESCE((data_2."stats_event.created_at")::text, '-'::text)), (COALESCE((data_2."stats_session.user_agent_family")::text, '-'::text)), (COALESCE((data_2."stats_event.event_id")::text, '-'::text)), data_2."stats_event.event_id", data_2."stats_event.values->>'scrollTriggerValue'", data_2."stats_event.id", data_2."stats_event.created_at
  • Sort Method: external merge Disk: 7264kB
32. 7,334.333 7,334.333 ↓ 97,000.0 97,000 1

CTE Scan on data data_2 (cost=0.00..0.03 rows=1 width=156) (actual time=3.398..7,334.333 rows=97,000 loops=1)

Planning time : 1.019 ms
Execution time : 8,911.773 ms