explain.depesz.com

PostgreSQL's explain analyze made readable

Result: jY9f

Settings
# exclusive inclusive rows x rows loops node
1. 1.482 1,099.370 ↑ 500.4 2,864 1

Sort (cost=15,411,954.10..15,415,536.93 rows=1,433,129 width=184) (actual time=1,099.217..1,099.370 rows=2,864 loops=1)

  • Sort Key: (COALESCE(data_stats_session.day, data_stats_page_view.day, (COALESCE((COALESCE((data."stats_event.created_at")::text, '-'::text)), '[TOTAL]'::text)))), (COALESCE(data_stats_session.group1, data_stats_page_view.group1, (COALESCE((COALESCE((data."stats_session.user_agent_family")::text, '-'::text)), '[TOTAL]'::text)))), (COALESCE(data_stats_session.group2, data_stats_page_view.group2, (COALESCE((COALESCE((data."stats_event.event_id")::text, '-'::text)), '[TOTAL]'::text))))
  • Sort Method: quicksort Memory: 450kB
2.          

CTE data

3. 91.294 261.659 ↑ 143.6 97,000 1

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

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

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

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

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

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

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

Append (cost=0.00..764.14 rows=980 width=52) (actual time=0.029..1.017 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.921 0.921 ↑ 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.921 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.946 10.454 ↑ 1.0 9,700 1

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

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

Append (cost=0.00..7,675.38 rows=10,126 width=44) (actual time=0.027..6.508 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. 5.565 5.565 ↑ 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.565 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.695 150.210 ↑ 1.0 97,000 1

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

15. 78.722 136.515 ↑ 1.0 97,000 1

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

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

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

17. 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))
18. 47.606 47.606 ↑ 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.026..47.606 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. 2.340 1,097.888 ↑ 500.4 2,864 1

Hash Full Join (cost=13,476,139.43..14,189,820.50 rows=1,433,129 width=184) (actual time=1,013.280..1,097.888 rows=2,864 loops=1)

  • Hash Cond: (((COALESCE((COALESCE((data."stats_event.created_at")::text, '-'::text)), '[TOTAL]'::text)) = COALESCE(data_stats_session.day, data_stats_page_view.day)) AND ((COALESCE((COALESCE((data."stats_session.user_agent_family")::text, '-'::text)), '[TOTAL]'::text)) = COALESCE(data_stats_session.group1, data_stats_page_view.group1)) AND ((COALESCE((COALESCE((data."stats_event.event_id")::text, '-'::text)), '[TOTAL]'::text)) = COALESCE(data_stats_session.group2, data_stats_page_view.group2)))
20. 31.340 496.368 ↑ 500.4 2,864 1

GroupAggregate (cost=3,726,979.90..4,093,126.11 rows=1,433,129 width=232) (actual time=414.081..496.368 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: ()
21. 368.519 465.028 ↑ 143.6 97,000 1

Sort (cost=3,726,979.90..3,761,803.11 rows=13,929,284 width=148) (actual time=414.062..465.028 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: external merge Disk: 6392kB
22. 96.509 96.509 ↑ 143.6 97,000 1

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

23. 0.886 599.180 ↓ 4.2 2,548 1

Hash (cost=9,749,149.01..9,749,149.01 rows=601 width=240) (actual time=599.180..599.180 rows=2,548 loops=1)

  • Buckets: 4096 (originally 1024) Batches: 1 (originally 1) Memory Usage: 277kB
24. 1.672 598.294 ↓ 4.2 2,548 1

Merge Full Join (cost=9,749,136.98..9,749,149.01 rows=601 width=240) (actual time=596.356..598.294 rows=2,548 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))
25. 0.600 459.184 ↓ 1.4 857 1

Sort (cost=6,058,015.56..6,058,017.06 rows=601 width=112) (actual time=459.117..459.184 rows=857 loops=1)

  • Sort Key: (COALESCE(data_stats_session.day)), (COALESCE(data_stats_session.group1)), (COALESCE(data_stats_session.group2))
  • Sort Method: quicksort Memory: 146kB
26. 0.170 458.584 ↓ 1.4 857 1

Subquery Scan on data_stats_session (cost=6,057,972.80..6,057,987.82 rows=601 width=112) (actual time=456.439..458.584 rows=857 loops=1)

27. 1.886 458.414 ↓ 1.4 857 1

GroupAggregate (cost=6,057,972.80..6,057,981.81 rows=601 width=208) (actual time=456.437..458.414 rows=857 loops=1)

  • Group Key: agg_stats_session.day, agg_stats_session.group1, agg_stats_session.group2
  • Group Key: agg_stats_session.day, agg_stats_session.group1
  • Group Key: agg_stats_session.day
  • Group Key: ()
28. 2.270 456.528 ↓ 4.8 970 1

Sort (cost=6,057,972.80..6,057,973.30 rows=200 width=128) (actual time=456.411..456.528 rows=970 loops=1)

  • Sort Key: agg_stats_session.day, agg_stats_session.group1, agg_stats_session.group2
  • Sort Method: quicksort Memory: 161kB
29. 0.202 454.258 ↓ 4.8 970 1

Subquery Scan on agg_stats_session (cost=5,988,316.74..6,057,965.16 rows=200 width=128) (actual time=429.973..454.258 rows=970 loops=1)

30. 7.359 454.056 ↓ 4.8 970 1

Unique (cost=5,988,316.74..6,057,963.16 rows=200 width=354) (actual time=429.971..454.056 rows=970 loops=1)

31. 72.737 446.697 ↑ 143.6 97,000 1

Sort (cost=5,988,316.74..6,023,139.95 rows=13,929,284 width=354) (actual time=429.970..446.697 rows=97,000 loops=1)

  • Sort Key: data_1."stats_session.id
  • Sort Method: external merge Disk: 7376kB
32. 373.960 373.960 ↑ 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=131.728..373.960 rows=97,000 loops=1)

33. 5.967 137.438 ↓ 4.2 2,512 1

Sort (cost=3,691,121.42..3,691,122.92 rows=601 width=128) (actual time=137.232..137.438 rows=2,512 loops=1)

  • Sort Key: data_stats_page_view.day, data_stats_page_view.group1, data_stats_page_view.group2
  • Sort Method: quicksort Memory: 315kB
34. 0.406 131.471 ↓ 4.2 2,512 1

Subquery Scan on data_stats_page_view (cost=3,621,424.74..3,691,093.68 rows=601 width=128) (actual time=129.826..131.471 rows=2,512 loops=1)

35. 11.768 131.065 ↓ 4.2 2,512 1

MixedAggregate (cost=3,621,424.74..3,691,087.67 rows=601 width=224) (actual time=129.824..131.065 rows=2,512 loops=1)

  • Hash 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))
  • Hash Key: (COALESCE((data_2."stats_page_view.created_at")::text, '-'::text)), (COALESCE((data_2."stats_session.user_agent_family")::text, '-'::text))
  • Hash Key: (COALESCE((data_2."stats_page_view.created_at")::text, '-'::text))
  • Group Key: ()
36. 8.700 119.297 ↓ 48.5 9,700 1

Unique (cost=3,621,424.74..3,691,071.16 rows=200 width=124) (actual time=94.333..119.297 rows=9,700 loops=1)

37. 52.128 110.597 ↑ 143.6 97,000 1

Sort (cost=3,621,424.74..3,656,247.95 rows=13,929,284 width=124) (actual time=94.332..110.597 rows=97,000 loops=1)

  • Sort Key: data_2."stats_page_view.id
  • Sort Method: external merge Disk: 6408kB
38. 58.469 58.469 ↑ 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.037..58.469 rows=97,000 loops=1)