explain.depesz.com

PostgreSQL's explain analyze made readable

Result: NLU0

Settings
# exclusive inclusive rows x rows loops node
1. 16.689 1,428.138 ↑ 1.6 2,864 1

Sort (cost=99,535.64..99,547.23 rows=4,637 width=192) (actual time=1,427.779..1,428.138 rows=2,864 loops=1)

  • Sort Key: (CASE COALESCE(data_stats_session.day, data_stats_page_view.day, data_stats_event.day) WHEN '[TOTAL]'::text THEN 0 ELSE 1 END), (COALESCE(data_stats_session.day, data_stats_page_view.day, data_stats_event.day)) DESC, (CASE COALESCE(data_stats_session.group1, data_stats_page_view.group1, data_stats_event.group1) WHEN '[TOTAL]'::text THEN 0 ELSE 1 END), (COALESCE(data_stats_session.group1, data_stats_page_view.group1, data_stats_event.group1)), (CASE COALESCE(data_stats_session.group2, data_stats_page_view.group2, data_stats_event.group2) WHEN '[TOTAL]'::text THEN 0 ELSE 1 END), (COALESCE(data_stats_session.group2, data_stats_page_view.group2, data_stats_event.group2))
  • Sort Method: quicksort Memory: 499kB
2.          

CTE data

3. 36.333 271.297 ↓ 4.4 97,000 1

Gather (cost=76,345.85..87,237.10 rows=22,180 width=136) (actual time=97.822..271.297 rows=97,000 loops=1)

  • Workers Planned: 2
  • Workers Launched: 0
4. 95.325 234.964 ↓ 10.5 97,000 1

Parallel Hash Join (cost=75,345.85..84,019.10 rows=9,242 width=136) (actual time=97.149..234.964 rows=97,000 loops=1)

  • Hash Cond: (_hyper_3_3_chunk.session_id = _hyper_2_2_chunk.id)
  • Join Filter: ((_hyper_2_2_chunk.campaign_id >= 1) OR (((_hyper_5_4_chunk."values" ->> 'scrollTriggerValue'::text))::numeric <= '100'::numeric))
5. 45.804 138.302 ↓ 28.6 97,000 1

Parallel Hash Join (cost=74,583.48..82,825.61 rows=3,395 width=104) (actual time=95.599..138.302 rows=97,000 loops=1)

  • Hash Cond: (_hyper_3_3_chunk.id = _hyper_5_4_chunk.page_view_id)
6. 1.027 6.914 ↓ 2.3 9,700 1

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

7. 5.886 5.886 ↓ 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.025..5.886 rows=9,700 loops=1)

  • Index Cond: ((property_id = 1) AND (created_at >= '2019-03-14 06:59:00+00'::timestamp with time zone) AND (created_at <= '2019-05-13 06:59:59+00'::timestamp with time zone))
8. 0.001 0.001 ↓ 0.0 0 1

Parallel Seq Scan on stats_page_view (cost=0.00..0.00 rows=1 width=44) (actual time=0.001..0.001 rows=0 loops=1)

  • Filter: ((created_at >= '2019-03-14 06:59:00+00'::timestamp with time zone) AND (created_at <= '2019-05-13 06:59:59+00'::timestamp with time zone) AND (property_id = 1))
9. 27.576 85.584 ↓ 2.3 97,000 1

Parallel Hash (cost=73,528.20..73,528.20 rows=41,862 width=76) (actual time=85.583..85.584 rows=97,000 loops=1)

  • Buckets: 65536 Batches: 4 Memory Usage: 2496kB
10. 9.355 58.008 ↓ 2.3 97,000 1

Parallel Append (cost=0.00..73,528.20 rows=41,862 width=76) (actual time=0.016..58.008 rows=97,000 loops=1)

11. 48.652 48.652 ↓ 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,318.89 rows=41,861 width=76) (actual time=0.015..48.652 rows=97,000 loops=1)

  • Index Cond: ((property_id = 1) AND (created_at >= '2019-03-14 06:59:00+00'::timestamp with time zone) AND (created_at <= '2019-05-13 06:59:59+00'::timestamp with time zone))
12. 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.000..0.001 rows=0 loops=1)

  • Filter: ((created_at >= '2019-03-14 06:59:00+00'::timestamp with time zone) AND (created_at <= '2019-05-13 06:59:59+00'::timestamp with time zone) AND (property_id = 1))
13. 0.259 1.337 ↓ 2.4 970 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 104kB
14. 0.093 1.078 ↓ 2.4 970 1

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

15. 0.984 0.984 ↓ 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.026..0.984 rows=970 loops=1)

  • Index Cond: ((property_id = 1) AND (created_at >= '2019-03-14 06:59:00+00'::timestamp with time zone) AND (created_at <= '2019-05-13 06:59:59+00'::timestamp with time zone))
16. 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-14 06:59:00+00'::timestamp with time zone) AND (created_at <= '2019-05-13 06:59:59+00'::timestamp with time zone) AND (property_id = 1))
17. 3.425 1,411.449 ↑ 1.6 2,864 1

Merge Full Join (cost=11,928.57..12,016.18 rows=4,637 width=192) (actual time=1,407.541..1,411.449 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))
18. 2.254 1,249.133 ↓ 4.4 2,864 1

Sort (cost=9,656.57..9,658.18 rows=645 width=240) (actual time=1,248.914..1,249.133 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
19. 3.032 1,246.879 ↓ 4.4 2,864 1

Merge Full Join (cost=9,613.56..9,626.47 rows=645 width=240) (actual time=1,243.314..1,246.879 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))
20. 2.980 869.864 ↓ 4.4 2,864 1

Sort (cost=4,845.43..4,847.04 rows=645 width=112) (actual time=869.564..869.864 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
21. 0.793 866.884 ↓ 4.4 2,864 1

Subquery Scan on data_stats_session (cost=4,769.16..4,815.33 rows=645 width=112) (actual time=782.178..866.884 rows=2,864 loops=1)

22. 63.991 866.091 ↓ 4.4 2,864 1

GroupAggregate (cost=4,769.16..4,808.88 rows=645 width=208) (actual time=782.176..866.091 rows=2,864 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: ()
23. 125.825 802.100 ↓ 19.0 42,092 1

Sort (cost=4,769.16..4,774.70 rows=2,218 width=128) (actual time=782.139..802.100 rows=42,092 loops=1)

  • Sort Key: agg_stats_session.day, agg_stats_session.group1, agg_stats_session.group2
  • Sort Method: external merge Disk: 3200kB
24. 8.681 676.275 ↓ 19.0 42,092 1

Subquery Scan on agg_stats_session (cost=4,346.46..4,645.89 rows=2,218 width=128) (actual time=562.946..676.275 rows=42,092 loops=1)

25. 22.133 667.594 ↓ 19.0 42,092 1

Unique (cost=4,346.46..4,623.71 rows=2,218 width=358) (actual time=562.944..667.594 rows=42,092 loops=1)

26. 285.566 645.461 ↓ 4.4 97,000 1

Sort (cost=4,346.46..4,401.91 rows=22,180 width=358) (actual time=562.943..645.461 rows=97,000 loops=1)

  • Sort Key: data."stats_session.id", data."stats_session.created_at", data."stats_session.user_agent_family", data."stats_event.event_id
  • Sort Method: external merge Disk: 9688kB
27. 359.895 359.895 ↓ 4.4 97,000 1

CTE Scan on data (cost=0.00..665.40 rows=22,180 width=358) (actual time=97.830..359.895 rows=97,000 loops=1)

28. 7.016 373.983 ↓ 4.4 2,864 1

Sort (cost=4,768.13..4,769.74 rows=645 width=128) (actual time=373.739..373.983 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
29. 0.443 366.967 ↓ 4.4 2,864 1

Subquery Scan on data_stats_page_view (cost=4,346.46..4,738.03 rows=645 width=128) (actual time=365.150..366.967 rows=2,864 loops=1)

30. 89.831 366.524 ↓ 4.4 2,864 1

MixedAggregate (cost=4,346.46..4,731.58 rows=645 width=224) (actual time=365.148..366.524 rows=2,864 loops=1)

  • Hash 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))
  • Hash Key: (COALESCE((data_1."stats_page_view.created_at")::text, '-'::text)), (COALESCE((data_1."stats_session.user_agent_family")::text, '-'::text))
  • Hash Key: (COALESCE((data_1."stats_page_view.created_at")::text, '-'::text))
  • Group Key: ()
31. 26.714 276.693 ↓ 40.1 88,893 1

Unique (cost=4,346.46..4,623.71 rows=2,218 width=354) (actual time=224.297..276.693 rows=88,893 loops=1)

32. 187.052 249.979 ↓ 4.4 97,000 1

Sort (cost=4,346.46..4,401.91 rows=22,180 width=354) (actual time=224.296..249.979 rows=97,000 loops=1)

  • Sort Key: data_1."stats_page_view.id", data_1."stats_session.created_at", data_1."stats_session.user_agent_family", data_1."stats_event.event_id
  • Sort Method: external merge Disk: 9328kB
33. 62.927 62.927 ↓ 4.4 97,000 1

CTE Scan on data data_1 (cost=0.00..665.40 rows=22,180 width=354) (actual time=0.065..62.927 rows=97,000 loops=1)

34. 8.036 158.891 ↑ 1.6 2,864 1

Sort (cost=2,272.00..2,283.59 rows=4,637 width=132) (actual time=158.614..158.891 rows=2,864 loops=1)

  • Sort Key: data_stats_event.day, data_stats_event.group1, data_stats_event.group2
  • Sort Method: quicksort Memory: 345kB
35. 0.485 150.855 ↑ 1.6 2,864 1

Subquery Scan on data_stats_event (cost=0.00..1,989.63 rows=4,637 width=132) (actual time=149.411..150.855 rows=2,864 loops=1)

36. 81.672 150.370 ↑ 1.6 2,864 1

MixedAggregate (cost=0.00..1,943.26 rows=4,637 width=228) (actual time=149.409..150.370 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: ()
37. 68.698 68.698 ↓ 4.4 97,000 1

CTE Scan on data data_2 (cost=0.00..443.60 rows=22,180 width=132) (actual time=0.026..68.698 rows=97,000 loops=1)