explain.depesz.com

PostgreSQL's explain analyze made readable

Result: VBWt

Settings
# exclusive inclusive rows x rows loops node
1. 6.736 2,187.933 ↑ 62.7 2,864 1

Sort (cost=15,576,805.87..15,577,254.60 rows=179,494 width=184) (actual time=2,187.787..2,187.933 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. 93.345 267.263 ↑ 143.6 97,000 1

Merge Join (cost=100,741.74..930,034.88 rows=13,929,284 width=136) (actual time=135.157..267.263 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.869 21.118 ↑ 5.1 9,700 1

Sort (cost=15,040.80..15,164.84 rows=49,617 width=80) (actual time=19.254..21.118 rows=9,700 loops=1)

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

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

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

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

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

Append (cost=0.00..764.14 rows=980 width=52) (actual time=0.030..1.078 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. 0.979 0.979 ↑ 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..0.979 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.998 11.027 ↑ 1.0 9,700 1

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

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

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

12. 0.006 0.006 ↓ 0.0 0 1

Seq Scan on stats_page_view (cost=0.00..0.00 rows=1 width=44) (actual time=0.005..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))
13. 6.037 6.037 ↑ 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.037 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. 13.755 152.800 ↑ 1.0 97,000 1

Materialize (cost=85,700.94..86,206.27 rows=101,065 width=76) (actual time=115.888..152.800 rows=97,000 loops=1)

15. 79.667 139.045 ↑ 1.0 97,000 1

Sort (cost=85,700.94..85,953.60 rows=101,065 width=76) (actual time=115.866..139.045 rows=97,000 loops=1)

  • Sort Key: stats_event.page_view_id
  • Sort Method: external merge Disk: 5496kB
16. 10.026 59.378 ↑ 1.0 97,000 1

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

17. 0.013 0.013 ↓ 0.0 0 1

Seq Scan on stats_event (cost=0.00..0.00 rows=1 width=76) (actual time=0.013..0.013 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))
18. 49.339 49.339 ↑ 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.025..49.339 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))
19. 3.125 2,181.197 ↑ 62.7 2,864 1

Merge Full Join (cost=14,617,912.04..14,621,990.93 rows=179,494 width=184) (actual time=2,177.328..2,181.197 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))
20. 2.403 1,604.969 ↑ 62.7 2,864 1

Sort (cost=10,305,521.51..10,305,970.24 rows=179,494 width=240) (actual time=1,604.787..1,604.969 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
21. 4.982 1,602.566 ↑ 62.7 2,864 1

Merge Full Join (cost=10,274,208.56..10,278,287.45 rows=179,494 width=240) (actual time=1,596.684..1,602.566 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))
22. 1.983 929.714 ↑ 62.7 2,864 1

Sort (cost=6,337,386.52..6,337,835.25 rows=179,494 width=112) (actual time=929.479..929.714 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
23. 0.769 927.731 ↑ 62.7 2,864 1

Subquery Scan on data_stats_session (cost=5,988,316.74..6,315,762.46 rows=179,494 width=112) (actual time=736.477..927.731 rows=2,864 loops=1)

24. 49.284 926.962 ↑ 62.7 2,864 1

GroupAggregate (cost=5,988,316.74..6,313,967.52 rows=179,494 width=208) (actual time=736.475..926.962 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: ()
25. 33.166 877.678 ↑ 33.1 42,092 1

Group (cost=5,988,316.74..6,280,831.70 rows=1,392,928 width=354) (actual time=736.437..877.678 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
26. 462.656 844.512 ↑ 143.6 97,000 1

Sort (cost=5,988,316.74..6,023,139.95 rows=13,929,284 width=354) (actual time=736.433..844.512 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: external merge Disk: 9320kB
27. 381.856 381.856 ↑ 143.6 97,000 1

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

28. 0.477 667.870 ↑ 62.7 2,864 1

Materialize (cost=3,936,822.04..3,937,719.51 rows=179,494 width=128) (actual time=667.196..667.870 rows=2,864 loops=1)

29. 1.810 667.393 ↑ 62.7 2,864 1

Sort (cost=3,936,822.04..3,937,270.78 rows=179,494 width=128) (actual time=667.191..667.393 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
30. 0.572 665.583 ↑ 62.7 2,864 1

Subquery Scan on data_stats_page_view (cost=3,621,424.74..3,914,495.98 rows=179,494 width=128) (actual time=389.825..665.583 rows=2,864 loops=1)

31. 58.141 665.011 ↑ 62.7 2,864 1

GroupAggregate (cost=3,621,424.74..3,912,701.04 rows=179,494 width=224) (actual time=389.824..665.011 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: ()
32. 54.720 606.870 ↑ 15.7 88,893 1

Group (cost=3,621,424.74..3,879,116.49 rows=1,392,928 width=124) (actual time=389.790..606.870 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
33. 485.665 552.150 ↑ 143.6 97,000 1

Sort (cost=3,621,424.74..3,656,247.95 rows=13,929,284 width=124) (actual time=389.786..552.150 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: external merge Disk: 7272kB
34. 66.485 66.485 ↑ 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.053..66.485 rows=97,000 loops=1)

35. 0.375 573.103 ↑ 62.7 2,864 1

Materialize (cost=4,312,390.53..4,313,288.00 rows=179,494 width=136) (actual time=572.534..573.103 rows=2,864 loops=1)

36. 1.467 572.728 ↑ 62.7 2,864 1

Sort (cost=4,312,390.53..4,312,839.27 rows=179,494 width=136) (actual time=572.530..572.728 rows=2,864 loops=1)

  • Sort Key: data_stats_event.day, data_stats_event.group1, data_stats_event.group2
  • Sort Method: quicksort Memory: 346kB
37. 0.500 571.261 ↑ 62.7 2,864 1

Subquery Scan on data_stats_event (cost=3,947,890.74..4,289,714.47 rows=179,494 width=136) (actual time=399.195..571.261 rows=2,864 loops=1)

38. 28.009 570.761 ↑ 62.7 2,864 1

GroupAggregate (cost=3,947,890.74..4,287,919.53 rows=179,494 width=232) (actual time=399.194..570.761 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: ()
39. 35.720 542.752 ↑ 14.4 97,000 1

Group (cost=3,947,890.74..4,240,405.70 rows=1,392,928 width=156) (actual time=399.170..542.752 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
40. 448.093 507.032 ↑ 143.6 97,000 1

Sort (cost=3,947,890.74..3,982,713.95 rows=13,929,284 width=156) (actual time=399.166..507.032 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: 7248kB
41. 58.939 58.939 ↑ 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.023..58.939 rows=97,000 loops=1)

Planning time : 1.164 ms
Execution time : 2,207.496 ms