explain.depesz.com

PostgreSQL's explain analyze made readable

Result: ZQnO

Settings
# exclusive inclusive rows x rows loops node
1. 1.520 1,143.839 ↑ 447.9 3,200 1

Sort (cost=11,061,098.92..11,064,681.74 rows=1,433,129 width=184) (actual time=1,143.649..1,143.839 rows=3,200 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: 547kB
2.          

CTE data

3. 90.285 232.079 ↑ 143.6 97,000 1

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

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

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

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

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

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

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

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

8. 0.005 0.005 ↓ 0.0 0 1

Seq Scan on stats_session (cost=0.00..0.00 rows=1 width=262) (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))
9. 0.922 0.922 ↑ 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.922 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.886 10.498 ↑ 1.0 9,700 1

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

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

Append (cost=0.00..7,675.38 rows=10,126 width=44) (actual time=0.027..6.612 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.634 5.634 ↑ 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..5.634 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. 63.394 121.666 ↑ 1.0 97,000 1

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

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

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

16. 0.009 0.009 ↓ 0.0 0 1

Seq Scan on stats_event (cost=0.00..0.00 rows=1 width=76) (actual time=0.009..0.009 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. 48.658 48.658 ↑ 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.022..48.658 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.218 1,142.319 ↑ 447.9 3,200 1

Merge Full Join (cost=9,899,376.20..9,915,823.98 rows=1,433,129 width=184) (actual time=1,138.714..1,142.319 rows=3,200 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. 2.078 461.361 ↑ 500.4 2,864 1

Sort (cost=3,562,557.41..3,566,140.23 rows=1,433,129 width=136) (actual time=461.190..461.361 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.484 459.283 ↑ 500.4 2,864 1

Subquery Scan on data_stats_event (cost=2,965,223.90..3,360,032.69 rows=1,433,129 width=136) (actual time=416.833..459.283 rows=2,864 loops=1)

21. 29.443 458.799 ↑ 500.4 2,864 1

GroupAggregate (cost=2,965,223.90..3,345,701.40 rows=1,433,129 width=366) (actual time=416.832..458.799 rows=2,864 loops=1)

  • Group Key: data."stats_event.created_at", data."stats_session.user_agent_family", data."stats_event.event_id
  • Group Key: data."stats_event.created_at", data."stats_session.user_agent_family
  • Group Key: data."stats_event.created_at
  • Group Key: ()
22. 125.024 429.356 ↑ 143.6 97,000 1

Sort (cost=2,965,223.90..3,000,047.11 rows=13,929,284 width=278) (actual time=416.798..429.356 rows=97,000 loops=1)

  • Sort Key: data."stats_event.created_at", data."stats_session.user_agent_family", data."stats_event.event_id
  • Sort Method: quicksort Memory: 10651kB
23. 304.332 304.332 ↑ 143.6 97,000 1

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

24. 2.818 677.740 ↑ 56.1 3,200 1

Sort (cost=6,336,818.78..6,337,267.52 rows=179,494 width=240) (actual time=677.513..677.740 rows=3,200 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: 887kB
25. 3.063 674.922 ↑ 60.3 2,976 1

Merge Full Join (cost=6,317,524.57..6,321,154.72 rows=179,494 width=240) (actual time=671.495..674.922 rows=2,976 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.126 332.616 ↑ 62.7 2,864 1

Sort (cost=3,172,140.92..3,172,589.65 rows=179,494 width=112) (actual time=332.448..332.616 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: 501kB
27. 0.674 330.490 ↑ 62.7 2,864 1

Subquery Scan on data_stats_session (cost=2,910,811.90..3,156,476.86 rows=179,494 width=112) (actual time=225.240..330.490 rows=2,864 loops=1)

28. 52.877 329.816 ↑ 62.7 2,864 1

GroupAggregate (cost=2,910,811.90..3,154,681.92 rows=179,494 width=342) (actual time=225.237..329.816 rows=2,864 loops=1)

  • Group Key: data_1."stats_session.created_at", data_1."stats_session.user_agent_family", data_1."stats_event.event_id
  • Group Key: data_1."stats_session.created_at", data_1."stats_session.user_agent_family
  • Group Key: data_1."stats_session.created_at
  • Group Key: ()
29. 37.104 276.939 ↑ 33.1 42,092 1

Group (cost=2,910,811.90..3,119,751.16 rows=1,392,928 width=488) (actual time=225.186..276.939 rows=42,092 loops=1)

  • Group Key: data_1."stats_session.created_at", data_1."stats_session.user_agent_family", data_1."stats_event.event_id", data_1."stats_session.id", data_1."stats_session.user_id
30. 222.307 239.835 ↑ 143.6 97,000 1

Sort (cost=2,910,811.90..2,945,635.11 rows=13,929,284 width=262) (actual time=225.178..239.835 rows=97,000 loops=1)

  • Sort Key: data_1."stats_session.created_at", data_1."stats_session.user_agent_family", data_1."stats_event.event_id", data_1."stats_session.id", data_1."stats_session.user_id
  • Sort Method: quicksort Memory: 16609kB
31. 17.528 17.528 ↑ 143.6 97,000 1

CTE Scan on data data_1 (cost=0.00..278,585.68 rows=13,929,284 width=262) (actual time=0.003..17.528 rows=97,000 loops=1)

32. 1.558 339.243 ↑ 60.3 2,976 1

Sort (cost=3,145,383.65..3,145,832.39 rows=179,494 width=128) (actual time=339.036..339.243 rows=2,976 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. 1.037 337.685 ↑ 62.7 2,864 1

Subquery Scan on data_stats_page_view (cost=2,883,605.90..3,129,719.59 rows=179,494 width=128) (actual time=248.475..337.685 rows=2,864 loops=1)

34. 38.612 336.648 ↑ 62.7 2,864 1

GroupAggregate (cost=2,883,605.90..3,127,924.65 rows=179,494 width=358) (actual time=248.473..336.648 rows=2,864 loops=1)

  • Group Key: data_2."stats_page_view.created_at", data_2."stats_session.user_agent_family", data_2."stats_event.event_id
  • Group Key: data_2."stats_page_view.created_at", data_2."stats_session.user_agent_family
  • Group Key: data_2."stats_page_view.created_at
  • Group Key: ()
35. 35.878 298.036 ↑ 15.7 88,893 1

Group (cost=2,883,605.90..3,092,545.16 rows=1,392,928 width=258) (actual time=248.444..298.036 rows=88,893 loops=1)

  • Group Key: data_2."stats_page_view.created_at", data_2."stats_session.user_agent_family", data_2."stats_event.event_id", data_2."stats_page_view.runtime_active", data_2."stats_page_view.id
36. 234.322 262.158 ↑ 143.6 97,000 1

Sort (cost=2,883,605.90..2,918,429.11 rows=13,929,284 width=250) (actual time=248.439..262.158 rows=97,000 loops=1)

  • Sort Key: data_2."stats_page_view.created_at", data_2."stats_session.user_agent_family", data_2."stats_event.event_id", data_2."stats_page_view.runtime_active", data_2."stats_page_view.id
  • Sort Method: quicksort Memory: 11531kB
37. 27.836 27.836 ↑ 143.6 97,000 1

CTE Scan on data data_2 (cost=0.00..278,585.68 rows=13,929,284 width=250) (actual time=0.003..27.836 rows=97,000 loops=1)

Planning time : 1.155 ms
Execution time : 1,147.880 ms