explain.depesz.com

PostgreSQL's explain analyze made readable

Result: iaW

Settings
# exclusive inclusive rows x rows loops node
1. 1.353 1,941.568 ↑ 62.7 2,864 1

Sort (cost=10,634,487.74..10,634,936.47 rows=179,494 width=184) (actual time=1,941.417..1,941.568 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. 91.867 236.667 ↑ 143.6 97,000 1

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

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

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

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

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

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

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

Append (cost=0.00..764.14 rows=980 width=52) (actual time=0.031..1.038 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. 0.923 0.923 ↑ 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.024..0.923 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.727 10.226 ↑ 1.0 9,700 1

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

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

Append (cost=0.00..7,675.38 rows=10,126 width=44) (actual time=0.026..6.499 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.003..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.535 5.535 ↑ 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.535 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. 68.878 125.093 ↑ 1.0 97,000 1

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

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

Append (cost=0.00..74,731.95 rows=101,065 width=76) (actual time=0.037..56.215 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. 46.573 46.573 ↑ 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..46.573 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.835 1,940.215 ↑ 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,937.030..1,940.215 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.172 1,395.265 ↑ 62.7 2,864 1

Sort (cost=6,635,320.77..6,635,769.51 rows=179,494 width=240) (actual time=1,395.100..1,395.265 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. 2.797 1,393.093 ↑ 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,389.925..1,393.093 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. 1.931 838.936 ↑ 62.7 2,864 1

Sort (cost=3,719,682.52..3,720,131.25 rows=179,494 width=112) (actual time=838.759..838.936 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.664 837.005 ↑ 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=731.458..837.005 rows=2,864 loops=1)

23. 53.600 836.341 ↑ 62.7 2,864 1

GroupAggregate (cost=3,376,572.74..3,702,223.52 rows=179,494 width=208) (actual time=731.456..836.341 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. 39.062 782.741 ↑ 33.1 42,092 1

Group (cost=3,376,572.74..3,669,087.70 rows=1,392,928 width=354) (actual time=731.422..782.741 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. 374.166 743.679 ↑ 143.6 97,000 1

Sort (cost=3,376,572.74..3,411,395.95 rows=13,929,284 width=354) (actual time=731.417..743.679 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. 369.513 369.513 ↑ 143.6 97,000 1

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

27. 1.475 551.360 ↑ 62.7 2,864 1

Sort (cost=2,896,344.04..2,896,792.78 rows=179,494 width=128) (actual time=551.157..551.360 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.550 549.885 ↑ 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.605..549.885 rows=2,864 loops=1)

29. 39.419 549.335 ↑ 62.7 2,864 1

GroupAggregate (cost=2,587,608.74..2,878,885.04 rows=179,494 width=224) (actual time=456.603..549.335 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. 40.166 509.916 ↑ 15.7 88,893 1

Group (cost=2,587,608.74..2,845,300.49 rows=1,392,928 width=124) (actual time=456.577..509.916 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. 416.594 469.750 ↑ 143.6 97,000 1

Sort (cost=2,587,608.74..2,622,431.95 rows=13,929,284 width=124) (actual time=456.572..469.750 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. 53.156 53.156 ↑ 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.008..53.156 rows=97,000 loops=1)

33. 1.568 542.115 ↑ 62.7 2,864 1

Sort (cost=3,053,918.53..3,054,367.27 rows=179,494 width=136) (actual time=541.921..542.115 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.491 540.547 ↑ 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=458.342..540.547 rows=2,864 loops=1)

35. 25.812 540.056 ↑ 62.7 2,864 1

GroupAggregate (cost=2,696,430.74..3,036,459.53 rows=179,494 width=232) (actual time=458.341..540.056 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. 41.671 514.244 ↑ 14.4 97,000 1

Group (cost=2,696,430.74..2,988,945.70 rows=1,392,928 width=156) (actual time=458.318..514.244 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.705 472.573 ↑ 143.6 97,000 1

Sort (cost=2,696,430.74..2,731,253.95 rows=13,929,284 width=156) (actual time=458.312..472.573 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. 54.868 54.868 ↑ 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.007..54.868 rows=97,000 loops=1)