explain.depesz.com

PostgreSQL's explain analyze made readable

Result: EXQ1

Settings
# exclusive inclusive rows x rows loops node
1. 0.000 0.000 ↓ 0.0

Sort (cost=10,909,977.62..10,913,560.44 rows=1,433,129 width=184) (actual rows= loops=)

  • Sort Key: (COALESCE(data_stats_session.day, data_stats_page_view.day, data_stats_event.day)), (COALESCE(data_stats_session.group1, data_stats_page_view.group1, data_stats_event.group1)), (COALESCE(data_stats_session.group2, data_stats_page_view.group2, data_stats_event.group2))
2.          

CTE data

3. 0.000 0.000 ↓ 0.0

Merge Join (cost=96,913.74..925,954.22 rows=13,929,284 width=136) (actual rows= loops=)

  • Merge Cond: (stats_page_view.id = stats_event.page_view_id)
  • Join Filter: ((stats_session.campaign_id >= 1) OR (((stats_event."values" ->> 'scrollTriggerValue'::text))::numeric <= '100'::numeric))
4. 0.000 0.000 ↓ 0.0

Sort (cost=13,780.80..13,904.84 rows=49,617 width=80) (actual rows= loops=)

  • Sort Key: stats_page_view.id
5. 0.000 0.000 ↓ 0.0

Merge Join (cost=9,161.88..9,911.03 rows=49,617 width=80) (actual rows= loops=)

  • Merge Cond: (stats_session.id = stats_page_view.session_id)
6. 0.000 0.000 ↓ 0.0

Sort (cost=812.83..815.28 rows=980 width=52) (actual rows= loops=)

  • Sort Key: stats_session.id
7. 0.000 0.000 ↓ 0.0

Append (cost=0.00..764.14 rows=980 width=52) (actual rows= loops=)

8. 0.000 0.000 ↓ 0.0

Seq Scan on stats_session (cost=0.00..0.00 rows=1 width=262) (actual rows= loops=)

  • Filter: ((created_at >= '2019-03-11 12:06:00+00'::timestamp with time zone) AND (created_at <= '2019-05-10 12:06:59+00'::timestamp with time zone) AND (property_id = 1))
9. 0.000 0.000 ↓ 0.0

Index Scan using _hyper_2_2_chunk_idx_session_property_id_and_created_at on _hyper_2_2_chunk (cost=0.29..759.24 rows=979 width=52) (actual rows= loops=)

  • Index Cond: ((property_id = 1) AND (created_at >= '2019-03-11 12:06:00+00'::timestamp with time zone) AND (created_at <= '2019-05-10 12:06:59+00'::timestamp with time zone))
10. 0.000 0.000 ↓ 0.0

Sort (cost=8,349.05..8,374.36 rows=10,126 width=44) (actual rows= loops=)

  • Sort Key: stats_page_view.session_id
11. 0.000 0.000 ↓ 0.0

Append (cost=0.00..7,675.38 rows=10,126 width=44) (actual rows= loops=)

12. 0.000 0.000 ↓ 0.0

Seq Scan on stats_page_view (cost=0.00..0.00 rows=1 width=44) (actual rows= loops=)

  • Filter: ((created_at >= '2019-03-11 12:06:00+00'::timestamp with time zone) AND (created_at <= '2019-05-10 12:06:59+00'::timestamp with time zone) AND (property_id = 1))
13. 0.000 0.000 ↓ 0.0

Index Scan using _hyper_3_3_chunk_idx_pageview_property_id_and_created_at on _hyper_3_3_chunk (cost=0.42..7,624.75 rows=10,125 width=44) (actual rows= loops=)

  • Index Cond: ((property_id = 1) AND (created_at >= '2019-03-11 12:06:00+00'::timestamp with time zone) AND (created_at <= '2019-05-10 12:06:59+00'::timestamp with time zone))
14. 0.000 0.000 ↓ 0.0

Sort (cost=83,132.94..83,385.60 rows=101,065 width=76) (actual rows= loops=)

  • Sort Key: stats_event.page_view_id
15. 0.000 0.000 ↓ 0.0

Append (cost=0.00..74,731.95 rows=101,065 width=76) (actual rows= loops=)

16. 0.000 0.000 ↓ 0.0

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

  • Filter: ((created_at >= '2019-03-11 12:06:00+00'::timestamp with time zone) AND (created_at <= '2019-05-10 12:06:59+00'::timestamp with time zone) AND (property_id = 1))
17. 0.000 0.000 ↓ 0.0

Index Scan using _hyper_5_4_chunk_idx_event_property_id_and_created_at on _hyper_5_4_chunk (cost=0.43..74,226.63 rows=101,064 width=76) (actual rows= loops=)

  • Index Cond: ((property_id = 1) AND (created_at >= '2019-03-11 12:06:00+00'::timestamp with time zone) AND (created_at <= '2019-05-10 12:06:59+00'::timestamp with time zone))
18. 0.000 0.000 ↓ 0.0

Merge Full Join (cost=9,748,254.90..9,764,702.68 rows=1,433,129 width=184) (actual rows= loops=)

  • Merge Cond: ((data_stats_event.day = (COALESCE(data_stats_session.day, data_stats_page_view.day))) AND (data_stats_event.group1 = (COALESCE(data_stats_session.group1, data_stats_page_view.group1))) AND (data_stats_event.group2 = (COALESCE(data_stats_session.group2, data_stats_page_view.group2))))
19. 0.000 0.000 ↓ 0.0

Sort (cost=3,112,934.12..3,116,516.94 rows=1,433,129 width=136) (actual rows= loops=)

  • Sort Key: data_stats_event.day, data_stats_event.group1, data_stats_event.group2
20. 0.000 0.000 ↓ 0.0

Subquery Scan on data_stats_event (cost=2,529,931.90..2,910,409.40 rows=1,433,129 width=136) (actual rows= loops=)

21. 0.000 0.000 ↓ 0.0

GroupAggregate (cost=2,529,931.90..2,896,078.11 rows=1,433,129 width=232) (actual rows= loops=)

  • Group Key: (COALESCE((data."stats_event.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_event.created_at")::text, '-'::text)), (COALESCE((data."stats_session.user_agent_family")::text, '-'::text))
  • Group Key: (COALESCE((data."stats_event.created_at")::text, '-'::text))
  • Group Key: ()
22. 0.000 0.000 ↓ 0.0

Sort (cost=2,529,931.90..2,564,755.11 rows=13,929,284 width=148) (actual rows= loops=)

  • Sort Key: (COALESCE((data."stats_event.created_at")::text, '-'::text)), (COALESCE((data."stats_session.user_agent_family")::text, '-'::text)), (COALESCE((data."stats_event.event_id")::text, '-'::text))
23. 0.000 0.000 ↓ 0.0

CTE Scan on data (cost=0.00..278,585.68 rows=13,929,284 width=148) (actual rows= loops=)

24. 0.000 0.000 ↓ 0.0

Sort (cost=6,635,320.77..6,635,769.51 rows=179,494 width=240) (actual rows= loops=)

  • Sort Key: (COALESCE(data_stats_session.day, data_stats_page_view.day)), (COALESCE(data_stats_session.group1, data_stats_page_view.group1)), (COALESCE(data_stats_session.group2, data_stats_page_view.group2))
25. 0.000 0.000 ↓ 0.0

Merge Full Join (cost=6,616,026.56..6,619,656.71 rows=179,494 width=240) (actual rows= loops=)

  • Merge Cond: (((COALESCE(data_stats_session.day)) = data_stats_page_view.day) AND ((COALESCE(data_stats_session.group1)) = data_stats_page_view.group1) AND ((COALESCE(data_stats_session.group2)) = data_stats_page_view.group2))
26. 0.000 0.000 ↓ 0.0

Sort (cost=3,719,682.52..3,720,131.25 rows=179,494 width=112) (actual rows= loops=)

  • Sort Key: (COALESCE(data_stats_session.day)), (COALESCE(data_stats_session.group1)), (COALESCE(data_stats_session.group2))
27. 0.000 0.000 ↓ 0.0

Subquery Scan on data_stats_session (cost=3,376,572.74..3,704,018.46 rows=179,494 width=112) (actual rows= loops=)

28. 0.000 0.000 ↓ 0.0

GroupAggregate (cost=3,376,572.74..3,702,223.52 rows=179,494 width=208) (actual rows= loops=)

  • 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: ()
29. 0.000 0.000 ↓ 0.0

Group (cost=3,376,572.74..3,669,087.70 rows=1,392,928 width=354) (actual rows= loops=)

  • 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
30. 0.000 0.000 ↓ 0.0

Sort (cost=3,376,572.74..3,411,395.95 rows=13,929,284 width=354) (actual rows= loops=)

  • 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
31. 0.000 0.000 ↓ 0.0

CTE Scan on data data_1 (cost=0.00..417,878.52 rows=13,929,284 width=354) (actual rows= loops=)

32. 0.000 0.000 ↓ 0.0

Sort (cost=2,896,344.04..2,896,792.78 rows=179,494 width=128) (actual rows= loops=)

  • Sort Key: data_stats_page_view.day, data_stats_page_view.group1, data_stats_page_view.group2
33. 0.000 0.000 ↓ 0.0

Subquery Scan on data_stats_page_view (cost=2,587,608.74..2,880,679.98 rows=179,494 width=128) (actual rows= loops=)

34. 0.000 0.000 ↓ 0.0

GroupAggregate (cost=2,587,608.74..2,878,885.04 rows=179,494 width=224) (actual rows= loops=)

  • Group Key: (COALESCE((data_2."stats_page_view.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_page_view.created_at")::text, '-'::text)), (COALESCE((data_2."stats_session.user_agent_family")::text, '-'::text))
  • Group Key: (COALESCE((data_2."stats_page_view.created_at")::text, '-'::text))
  • Group Key: ()
35. 0.000 0.000 ↓ 0.0

Group (cost=2,587,608.74..2,845,300.49 rows=1,392,928 width=124) (actual rows= loops=)

  • Group Key: (COALESCE((data_2."stats_page_view.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_page_view.runtime_active", data_2."stats_page_view.id", data_2."stats_page_view.created_at
36. 0.000 0.000 ↓ 0.0

Sort (cost=2,587,608.74..2,622,431.95 rows=13,929,284 width=124) (actual rows= loops=)

  • Sort Key: (COALESCE((data_2."stats_page_view.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_page_view.runtime_active", data_2."stats_page_view.id", data_2."stats_page_view.created_at
37. 0.000 0.000 ↓ 0.0

CTE Scan on data data_2 (cost=0.00..417,878.52 rows=13,929,284 width=124) (actual rows= loops=)