explain.depesz.com

PostgreSQL's explain analyze made readable

Result: giy4

Settings
# exclusive inclusive rows x rows loops node
1. 6.322 1,949.696 ↑ 62.7 2,864 1

Sort (cost=10,634,487.74..10,634,936.47 rows=179,494 width=184) (actual time=1,949.542..1,949.696 rows=2,864 loops=1)

  • Sort Key: (COALESCE(data_stats_session.day, data_stats_page_view.day, data_stats_event.day)) DESC, (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. 89.157 230.169 ↑ 143.6 97,000 1

Merge Join (cost=96,913.74..925,954.22 rows=13,929,284 width=136) (actual time=125.852..230.169 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. 5.504 20.614 ↑ 5.1 9,700 1

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

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

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

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

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

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

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

8. 0.004 0.004 ↓ 0.0 0 1

Seq Scan on stats_session (cost=0.00..0.00 rows=1 width=262) (actual time=0.004..0.004 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.005 1.005 ↑ 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.025..1.005 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. 3.964 10.830 ↑ 1.0 9,700 1

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

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

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

12. 0.004 0.004 ↓ 0.0 0 1

Seq Scan on stats_page_view (cost=0.00..0.00 rows=1 width=44) (actual time=0.004..0.004 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. 5.860 5.860 ↑ 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.022..5.860 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. 62.973 120.398 ↑ 1.0 97,000 1

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

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

Append (cost=0.00..74,731.95 rows=101,065 width=76) (actual time=0.091..57.425 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. 47.257 47.257 ↑ 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.078..47.257 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. 2.915 1,943.374 ↑ 62.7 2,864 1

Merge Full Join (cost=9,689,239.31..9,692,869.46 rows=179,494 width=184) (actual time=1,939.804..1,943.374 rows=2,864 loops=1)

  • Merge Cond: (((COALESCE(data_stats_session.day, data_stats_page_view.day)) = data_stats_event.day) AND ((COALESCE(data_stats_session.group1, data_stats_page_view.group1)) = data_stats_event.group1) AND ((COALESCE(data_stats_session.group2, data_stats_page_view.group2)) = data_stats_event.group2))
19. 2.786 1,392.013 ↑ 62.7 2,864 1

Sort (cost=6,635,320.77..6,635,769.51 rows=179,494 width=240) (actual time=1,391.549..1,392.013 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
20. 3.263 1,389.227 ↑ 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,385.505..1,389.227 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))
21. 2.811 850.462 ↑ 62.7 2,864 1

Sort (cost=3,719,682.52..3,720,131.25 rows=179,494 width=112) (actual time=850.228..850.462 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
22. 0.757 847.651 ↑ 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=715.065..847.651 rows=2,864 loops=1)

23. 71.905 846.894 ↑ 62.7 2,864 1

GroupAggregate (cost=3,376,572.74..3,702,223.52 rows=179,494 width=208) (actual time=715.064..846.894 rows=2,864 loops=1)

  • Group Key: (COALESCE((data."stats_session.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_session.created_at")::text, '-'::text)), (COALESCE((data."stats_session.user_agent_family")::text, '-'::text))
  • Group Key: (COALESCE((data."stats_session.created_at")::text, '-'::text))
  • Group Key: ()
24. 43.935 774.989 ↑ 33.1 42,092 1

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

  • Group Key: (COALESCE((data."stats_session.created_at")::text, '-'::text)), (COALESCE((data."stats_session.user_agent_family")::text, '-'::text)), (COALESCE((data."stats_event.event_id")::text, '-'::text)), data."stats_session.user_agent_family", data."stats_session.id", data."stats_session.user_id", data."stats_session.created_at
25. 393.418 731.054 ↑ 143.6 97,000 1

Sort (cost=3,376,572.74..3,411,395.95 rows=13,929,284 width=354) (actual time=715.022..731.054 rows=97,000 loops=1)

  • Sort Key: (COALESCE((data."stats_session.created_at")::text, '-'::text)), (COALESCE((data."stats_session.user_agent_family")::text, '-'::text)), (COALESCE((data."stats_event.event_id")::text, '-'::text)), data."stats_session.user_agent_family", data."stats_session.id", data."stats_session.user_id", data."stats_session.created_at
  • Sort Method: quicksort Memory: 16713kB
26. 337.636 337.636 ↑ 143.6 97,000 1

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

27. 1.932 535.502 ↑ 62.7 2,864 1

Sort (cost=2,896,344.04..2,896,792.78 rows=179,494 width=128) (actual time=535.266..535.502 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
28. 0.513 533.570 ↑ 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=433.939..533.570 rows=2,864 loops=1)

29. 43.154 533.057 ↑ 62.7 2,864 1

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

  • Group Key: (COALESCE((data_1."stats_page_view.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_page_view.created_at")::text, '-'::text)), (COALESCE((data_1."stats_session.user_agent_family")::text, '-'::text))
  • Group Key: (COALESCE((data_1."stats_page_view.created_at")::text, '-'::text))
  • Group Key: ()
30. 42.093 489.903 ↑ 15.7 88,893 1

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

  • Group Key: (COALESCE((data_1."stats_page_view.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_page_view.runtime_active", data_1."stats_page_view.id", data_1."stats_page_view.created_at
31. 382.947 447.810 ↑ 143.6 97,000 1

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

  • Sort Key: (COALESCE((data_1."stats_page_view.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_page_view.runtime_active", data_1."stats_page_view.id", data_1."stats_page_view.created_at
  • Sort Method: quicksort Memory: 16713kB
32. 64.863 64.863 ↑ 143.6 97,000 1

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

33. 1.525 548.446 ↑ 62.7 2,864 1

Sort (cost=3,053,918.53..3,054,367.27 rows=179,494 width=136) (actual time=548.246..548.446 rows=2,864 loops=1)

  • Sort Key: data_stats_event.day, data_stats_event.group1, data_stats_event.group2
  • Sort Method: quicksort Memory: 346kB
34. 0.474 546.921 ↑ 62.7 2,864 1

Subquery Scan on data_stats_event (cost=2,696,430.74..3,038,254.47 rows=179,494 width=136) (actual time=460.287..546.921 rows=2,864 loops=1)

35. 26.764 546.447 ↑ 62.7 2,864 1

GroupAggregate (cost=2,696,430.74..3,036,459.53 rows=179,494 width=232) (actual time=460.286..546.447 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: ()
36. 45.843 519.683 ↑ 14.4 97,000 1

Group (cost=2,696,430.74..2,988,945.70 rows=1,392,928 width=156) (actual time=460.266..519.683 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
37. 417.456 473.840 ↑ 143.6 97,000 1

Sort (cost=2,696,430.74..2,731,253.95 rows=13,929,284 width=156) (actual time=460.197..473.840 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: quicksort Memory: 16713kB
38. 56.384 56.384 ↑ 143.6 97,000 1

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

Planning time : 1.177 ms
Execution time : 1,954.634 ms