explain.depesz.com

PostgreSQL's explain analyze made readable

Result: VxkQ

Settings
# exclusive inclusive rows x rows loops node
1. 1.421 1,801.397 ↑ 500.4 2,864 1

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

  • 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))
  • Sort Method: quicksort Memory: 499kB
2.          

CTE data

3. 109.759 263.848 ↑ 143.6 97,000 1

Merge Join (cost=96,913.74..925,954.22 rows=13,929,284 width=136) (actual time=135.829..263.848 rows=97,000 loops=1)

  • 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. 6.204 22.689 ↑ 5.1 9,700 1

Sort (cost=13,780.80..13,904.84 rows=49,617 width=80) (actual time=20.542..22.689 rows=9,700 loops=1)

  • Sort Key: stats_page_view.id
  • Sort Method: quicksort Memory: 1749kB
5. 2.716 16.485 ↑ 5.1 9,700 1

Merge Join (cost=9,161.88..9,911.03 rows=49,617 width=80) (actual time=12.867..16.485 rows=9,700 loops=1)

  • Merge Cond: (stats_session.id = stats_page_view.session_id)
6. 0.473 1.695 ↑ 1.0 970 1

Sort (cost=812.83..815.28 rows=980 width=52) (actual time=1.570..1.695 rows=970 loops=1)

  • Sort Key: stats_session.id
  • Sort Method: quicksort Memory: 161kB
7. 0.122 1.222 ↑ 1.0 970 1

Append (cost=0.00..764.14 rows=980 width=52) (actual time=0.033..1.222 rows=970 loops=1)

8. 0.006 0.006 ↓ 0.0 0 1

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

  • 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. 1.094 1.094 ↑ 1.0 970 1

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 time=0.027..1.094 rows=970 loops=1)

  • 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. 4.243 12.074 ↑ 1.0 9,700 1

Sort (cost=8,349.05..8,374.36 rows=10,126 width=44) (actual time=11.290..12.074 rows=9,700 loops=1)

  • Sort Key: stats_page_view.session_id
  • Sort Method: quicksort Memory: 1142kB
11. 1.060 7.831 ↑ 1.0 9,700 1

Append (cost=0.00..7,675.38 rows=10,126 width=44) (actual time=0.028..7.831 rows=9,700 loops=1)

12. 0.005 0.005 ↓ 0.0 0 1

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

  • 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. 6.766 6.766 ↑ 1.0 9,700 1

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 time=0.023..6.766 rows=9,700 loops=1)

  • 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. 70.088 131.400 ↑ 1.0 97,000 1

Sort (cost=83,132.94..83,385.60 rows=101,065 width=76) (actual time=115.185..131.400 rows=97,000 loops=1)

  • Sort Key: stats_event.page_view_id
  • Sort Method: quicksort Memory: 10651kB
15. 10.213 61.312 ↑ 1.0 97,000 1

Append (cost=0.00..74,731.95 rows=101,065 width=76) (actual time=0.037..61.312 rows=97,000 loops=1)

16. 0.012 0.012 ↓ 0.0 0 1

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

  • 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. 51.087 51.087 ↑ 1.0 97,000 1

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 time=0.024..51.087 rows=97,000 loops=1)

  • 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. 3.215 1,799.976 ↑ 500.4 2,864 1

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

  • 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. 1.693 679.611 ↑ 500.4 2,864 1

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

  • Sort Key: data_stats_event.day, data_stats_event.group1, data_stats_event.group2
  • Sort Method: quicksort Memory: 346kB
20. 0.504 677.918 ↑ 500.4 2,864 1

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

21. 35.639 677.414 ↑ 500.4 2,864 1

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

  • 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. 249.784 641.775 ↑ 143.6 97,000 1

Sort (cost=2,529,931.90..2,564,755.11 rows=13,929,284 width=148) (actual time=627.862..641.775 rows=97,000 loops=1)

  • 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))
  • Sort Method: quicksort Memory: 16642kB
23. 391.991 391.991 ↑ 143.6 97,000 1

CTE Scan on data (cost=0.00..278,585.68 rows=13,929,284 width=148) (actual time=135.837..391.991 rows=97,000 loops=1)

24. 2.393 1,117.150 ↑ 62.7 2,864 1

Sort (cost=6,635,320.77..6,635,769.51 rows=179,494 width=240) (actual time=1,116.934..1,117.150 rows=2,864 loops=1)

  • 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))
  • Sort Method: quicksort Memory: 857kB
25. 2.857 1,114.757 ↑ 62.7 2,864 1

Merge Full Join (cost=6,616,026.56..6,619,656.71 rows=179,494 width=240) (actual time=1,111.536..1,114.757 rows=2,864 loops=1)

  • 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. 2.085 552.777 ↑ 62.7 2,864 1

Sort (cost=3,719,682.52..3,720,131.25 rows=179,494 width=112) (actual time=552.603..552.777 rows=2,864 loops=1)

  • Sort Key: (COALESCE(data_stats_session.day)), (COALESCE(data_stats_session.group1)), (COALESCE(data_stats_session.group2))
  • Sort Method: quicksort Memory: 500kB
27. 0.876 550.692 ↑ 62.7 2,864 1

Subquery Scan on data_stats_session (cost=3,376,572.74..3,704,018.46 rows=179,494 width=112) (actual time=439.179..550.692 rows=2,864 loops=1)

28. 53.942 549.816 ↑ 62.7 2,864 1

GroupAggregate (cost=3,376,572.74..3,702,223.52 rows=179,494 width=208) (actual time=439.177..549.816 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: ()
29. 42.736 495.874 ↑ 33.1 42,092 1

Group (cost=3,376,572.74..3,669,087.70 rows=1,392,928 width=354) (actual time=439.129..495.874 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
30. 399.350 453.138 ↑ 143.6 97,000 1

Sort (cost=3,376,572.74..3,411,395.95 rows=13,929,284 width=354) (actual time=439.123..453.138 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: quicksort Memory: 16713kB
31. 53.788 53.788 ↑ 143.6 97,000 1

CTE Scan on data data_1 (cost=0.00..417,878.52 rows=13,929,284 width=354) (actual time=0.007..53.788 rows=97,000 loops=1)

32. 1.670 559.123 ↑ 62.7 2,864 1

Sort (cost=2,896,344.04..2,896,792.78 rows=179,494 width=128) (actual time=558.923..559.123 rows=2,864 loops=1)

  • Sort Key: data_stats_page_view.day, data_stats_page_view.group1, data_stats_page_view.group2
  • Sort Method: quicksort Memory: 346kB
33. 0.715 557.453 ↑ 62.7 2,864 1

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

34. 42.784 556.738 ↑ 62.7 2,864 1

GroupAggregate (cost=2,587,608.74..2,878,885.04 rows=179,494 width=224) (actual time=456.968..556.738 rows=2,864 loops=1)

  • 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. 41.750 513.954 ↑ 15.7 88,893 1

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

  • 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. 414.438 472.204 ↑ 143.6 97,000 1

Sort (cost=2,587,608.74..2,622,431.95 rows=13,929,284 width=124) (actual time=456.939..472.204 rows=97,000 loops=1)

  • 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
  • Sort Method: quicksort Memory: 16713kB
37. 57.766 57.766 ↑ 143.6 97,000 1

CTE Scan on data data_2 (cost=0.00..417,878.52 rows=13,929,284 width=124) (actual time=0.008..57.766 rows=97,000 loops=1)

Planning time : 1.429 ms
Execution time : 1,805.401 ms