explain.depesz.com

PostgreSQL's explain analyze made readable

Result: N3hY

Settings
# exclusive inclusive rows x rows loops node
1. 1.549 837.088 ↑ 419.3 2,864 1

Sort (cost=8,795,270.30..8,798,272.74 rows=1,200,975 width=184) (actual time=836.938..837.088 rows=2,864 loops=1)

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

CTE data

3. 12.824 385.190 ↑ 119.7 97,000 1

Gather Merge (cost=1,000,700.26..2,355,030.68 rows=11,607,736 width=136) (actual time=356.335..385.190 rows=97,000 loops=1)

  • Workers Planned: 2
  • Workers Launched: 0
4. 194.932 372.366 ↑ 59.8 97,000 1

Sort (cost=999,700.24..1,014,209.91 rows=5,803,868 width=136) (actual time=355.602..372.366 rows=97,000 loops=1)

  • Sort Key: _hyper_2_2_chunk.id, _hyper_3_3_chunk.id, _hyper_5_4_chunk.id
  • Sort Method: quicksort Memory: 28630kB
5. 101.005 177.434 ↑ 59.8 97,000 1

Parallel Hash Join (cost=8,938.07..347,676.78 rows=5,803,868 width=136) (actual time=16.160..177.434 rows=97,000 loops=1)

  • Hash Cond: (_hyper_5_4_chunk.page_view_id = _hyper_3_3_chunk.id)
  • Join Filter: ((_hyper_2_2_chunk.campaign_id >= 1) OR (((_hyper_5_4_chunk."values" ->> 'scrollTriggerValue'::text))::numeric <= '100'::numeric))
6. 9.540 60.726 ↓ 2.3 97,000 1

Parallel Append (cost=0.00..73,847.64 rows=42,111 width=76) (actual time=0.020..60.726 rows=97,000 loops=1)

7. 51.185 51.185 ↓ 2.3 97,000 1

Parallel Index Scan using _hyper_5_4_chunk_idx_event_property_id_and_created_at on _hyper_5_4_chunk (cost=0.43..73,637.09 rows=42,110 width=76) (actual time=0.018..51.185 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))
8. 0.001 0.001 ↓ 0.0 0 1

Parallel Seq Scan on stats_event (cost=0.00..0.00 rows=1 width=76) (actual time=0.001..0.001 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. 3.617 15.703 ↑ 2.1 9,700 1

Parallel Hash (cost=8,679.65..8,679.65 rows=20,674 width=80) (actual time=15.703..15.703 rows=9,700 loops=1)

  • Buckets: 65536 Batches: 1 Memory Usage: 1600kB
10. 3.727 12.086 ↑ 2.1 9,700 1

Parallel Hash Join (cost=762.37..8,679.65 rows=20,674 width=80) (actual time=1.404..12.086 rows=9,700 loops=1)

  • Hash Cond: (_hyper_3_3_chunk.session_id = _hyper_2_2_chunk.id)
11. 0.961 6.988 ↓ 2.3 9,700 1

Parallel Append (cost=0.00..7,604.15 rows=4,220 width=44) (actual time=0.023..6.988 rows=9,700 loops=1)

12. 6.027 6.027 ↓ 1.6 9,700 1

Parallel Index Scan using _hyper_3_3_chunk_idx_pageview_property_id_and_created_at on _hyper_3_3_chunk (cost=0.42..7,583.05 rows=5,956 width=44) (actual time=0.022..6.027 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))
13. 0.000 0.000 ↓ 0.0 0 1

Parallel Seq Scan on stats_page_view (cost=0.00..0.00 rows=1 width=44) (actual time=0.000..0.000 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))
14. 0.257 1.371 ↓ 2.4 970 1

Parallel Hash (cost=757.26..757.26 rows=409 width=52) (actual time=1.371..1.371 rows=970 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 104kB
15. 0.103 1.114 ↓ 2.4 970 1

Parallel Append (cost=0.00..757.26 rows=409 width=52) (actual time=0.031..1.114 rows=970 loops=1)

16. 1.010 1.010 ↓ 1.7 970 1

Parallel Index Scan using _hyper_2_2_chunk_idx_session_property_id_and_created_at on _hyper_2_2_chunk (cost=0.29..755.21 rows=576 width=52) (actual time=0.030..1.010 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))
17. 0.001 0.001 ↓ 0.0 0 1

Parallel Seq Scan on stats_session (cost=0.00..0.00 rows=1 width=262) (actual time=0.001..0.001 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. 3.084 835.539 ↑ 419.3 2,864 1

Merge Full Join (cost=6,306,949.86..6,318,966.52 rows=1,200,975 width=184) (actual time=831.762..835.539 rows=2,864 loops=1)

  • Merge Cond: (((COALESCE(data_stats_session.day, (COALESCE((COALESCE((data."stats_page_view.created_at")::text, '-'::text)), '[TOTAL]'::text)))) = data_stats_event.day) AND ((COALESCE(data_stats_session.group1, (COALESCE((COALESCE((data."stats_session.user_agent_family")::text, '-'::text)), '[TOTAL]'::text)))) = data_stats_event.group1) AND ((COALESCE(data_stats_session.group2, (COALESCE((COALESCE((data."stats_event.event_id")::text, '-'::text)), '[TOTAL]'::text)))) = data_stats_event.group2))
19. 9.310 685.050 ↓ 4.2 2,546 1

Sort (cost=5,055,919.90..5,055,921.40 rows=601 width=240) (actual time=684.623..685.050 rows=2,546 loops=1)

  • Sort Key: (COALESCE(data_stats_session.day, (COALESCE((COALESCE((data."stats_page_view.created_at")::text, '-'::text)), '[TOTAL]'::text)))), (COALESCE(data_stats_session.group1, (COALESCE((COALESCE((data."stats_session.user_agent_family")::text, '-'::text)), '[TOTAL]'::text)))), (COALESCE(data_stats_session.group2, (COALESCE((COALESCE((data."stats_event.event_id")::text, '-'::text)), '[TOTAL]'::text))))
  • Sort Method: quicksort Memory: 557kB
20. 1.405 675.740 ↓ 4.2 2,546 1

Hash Full Join (cost=4,997,691.21..5,055,892.16 rows=601 width=240) (actual time=672.676..675.740 rows=2,546 loops=1)

  • Hash Cond: (((COALESCE((COALESCE((data."stats_page_view.created_at")::text, '-'::text)), '[TOTAL]'::text)) = COALESCE(data_stats_session.day)) AND ((COALESCE((COALESCE((data."stats_session.user_agent_family")::text, '-'::text)), '[TOTAL]'::text)) = COALESCE(data_stats_session.group1)) AND ((COALESCE((COALESCE((data."stats_event.event_id")::text, '-'::text)), '[TOTAL]'::text)) = COALESCE(data_stats_session.group2)))
21. 12.371 135.783 ↓ 4.2 2,511 1

MixedAggregate (cost=2,141,073.67..2,199,128.87 rows=601 width=224) (actual time=134.111..135.783 rows=2,511 loops=1)

  • Hash Key: (COALESCE((data."stats_page_view.created_at")::text, '-'::text)), (COALESCE((data."stats_session.user_agent_family")::text, '-'::text)), (COALESCE((data."stats_event.event_id")::text, '-'::text))
  • Hash Key: (COALESCE((data."stats_page_view.created_at")::text, '-'::text)), (COALESCE((data."stats_session.user_agent_family")::text, '-'::text))
  • Hash Key: (COALESCE((data."stats_page_view.created_at")::text, '-'::text))
  • Group Key: ()
22. 10.786 123.412 ↓ 48.5 9,700 1

Unique (cost=2,141,073.67..2,199,112.35 rows=200 width=124) (actual time=102.265..123.412 rows=9,700 loops=1)

23. 58.176 112.626 ↑ 119.7 97,000 1

Sort (cost=2,141,073.67..2,170,093.01 rows=11,607,736 width=124) (actual time=102.262..112.626 rows=97,000 loops=1)

  • Sort Key: data."stats_page_view.id
  • Sort Method: quicksort Memory: 16713kB
24. 54.450 54.450 ↑ 119.7 97,000 1

CTE Scan on data (cost=0.00..348,232.08 rows=11,607,736 width=124) (actual time=0.006..54.450 rows=97,000 loops=1)

25. 0.300 538.552 ↓ 1.4 850 1

Hash (cost=2,856,607.02..2,856,607.02 rows=601 width=112) (actual time=538.552..538.552 rows=850 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 82kB
26. 0.147 538.252 ↓ 1.4 850 1

Subquery Scan on data_stats_session (cost=2,856,592.00..2,856,607.02 rows=601 width=112) (actual time=536.366..538.252 rows=850 loops=1)

27. 1.699 538.105 ↓ 1.4 850 1

GroupAggregate (cost=2,856,592.00..2,856,601.01 rows=601 width=208) (actual time=536.365..538.105 rows=850 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.053 536.406 ↓ 4.8 970 1

Sort (cost=2,856,592.00..2,856,592.50 rows=200 width=128) (actual time=536.340..536.406 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.217 534.353 ↓ 4.8 970 1

Subquery Scan on agg_stats_session (cost=2,798,543.67..2,856,584.35 rows=200 width=128) (actual time=517.279..534.353 rows=970 loops=1)

30. 9.508 534.136 ↓ 4.8 970 1

Unique (cost=2,798,543.67..2,856,582.35 rows=200 width=354) (actual time=517.276..534.136 rows=970 loops=1)

31. 50.382 524.628 ↑ 119.7 97,000 1

Sort (cost=2,798,543.67..2,827,563.01 rows=11,607,736 width=354) (actual time=517.273..524.628 rows=97,000 loops=1)

  • Sort Key: data_1."stats_session.id
  • Sort Method: quicksort Memory: 16713kB
32. 474.246 474.246 ↑ 119.7 97,000 1

CTE Scan on data data_1 (cost=0.00..348,232.08 rows=11,607,736 width=354) (actual time=356.350..474.246 rows=97,000 loops=1)

33. 7.244 147.405 ↑ 419.3 2,864 1

Sort (cost=1,251,029.96..1,254,032.40 rows=1,200,975 width=136) (actual time=147.127..147.405 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.508 140.161 ↑ 419.3 2,864 1

Subquery Scan on data_stats_event (cost=0.00..1,129,756.86 rows=1,200,975 width=136) (actual time=131.148..140.161 rows=2,864 loops=1)

35. 82.394 139.653 ↑ 419.3 2,864 1

MixedAggregate (cost=0.00..1,117,747.11 rows=1,200,975 width=232) (actual time=131.147..139.653 rows=2,864 loops=1)

  • Hash 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)
  • Hash Key: COALESCE((data_2."stats_event.created_at")::text, '-'::text), COALESCE((data_2."stats_session.user_agent_family")::text, '-'::text)
  • Hash Key: COALESCE((data_2."stats_event.created_at")::text, '-'::text)
  • Group Key: ()
36. 57.259 57.259 ↑ 119.7 97,000 1

CTE Scan on data data_2 (cost=0.00..232,154.72 rows=11,607,736 width=148) (actual time=0.009..57.259 rows=97,000 loops=1)