explain.depesz.com

PostgreSQL's explain analyze made readable

Result: o9xc

Settings
# exclusive inclusive rows x rows loops node
1. 2.956 1,830.350 ↑ 500.4 2,864 1

Merge Full Join (cost=10,674,209.11..10,690,656.89 rows=1,433,129 width=184) (actual time=1,826.927..1,830.350 rows=2,864 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))))
2.          

CTE data

3. 92.758 274.283 ↑ 143.6 97,000 1

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

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

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

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

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

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

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

Append (cost=0.00..764.14 rows=980 width=52) (actual time=0.030..1.172 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. 1.073 1.073 ↑ 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.026..1.073 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.898 10.622 ↑ 1.0 9,700 1

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

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

Append (cost=0.00..7,675.38 rows=10,126 width=44) (actual time=0.028..6.724 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.749 5.749 ↑ 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.749 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. 103.401 161.221 ↑ 1.0 97,000 1

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

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

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

16. 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))
17. 47.926 47.926 ↑ 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.027..47.926 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. 1.520 667.807 ↑ 500.4 2,864 1

Sort (cost=3,112,934.12..3,116,516.94 rows=1,433,129 width=136) (actual time=667.607..667.807 rows=2,864 loops=1)

  • Sort Key: data_stats_event.day, data_stats_event.group1, data_stats_event.group2
  • Sort Method: quicksort Memory: 346kB
19. 0.494 666.287 ↑ 500.4 2,864 1

Subquery Scan on data_stats_event (cost=2,529,931.90..2,910,409.40 rows=1,433,129 width=136) (actual time=610.410..666.287 rows=2,864 loops=1)

20. 34.395 665.793 ↑ 500.4 2,864 1

GroupAggregate (cost=2,529,931.90..2,896,078.11 rows=1,433,129 width=232) (actual time=610.408..665.793 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. 246.867 631.398 ↑ 143.6 97,000 1

Sort (cost=2,529,931.90..2,564,755.11 rows=13,929,284 width=148) (actual time=610.390..631.398 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: quicksort Memory: 16642kB
22. 384.531 384.531 ↑ 143.6 97,000 1

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

23. 2.339 1,159.587 ↑ 62.7 2,864 1

Sort (cost=6,635,320.77..6,635,769.51 rows=179,494 width=240) (actual time=1,159.313..1,159.587 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
24. 2.999 1,157.248 ↑ 62.7 2,864 1

Merge Full Join (cost=6,616,026.56..6,619,656.71 rows=179,494 width=240) (actual time=1,153.883..1,157.248 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))
25. 1.966 672.459 ↑ 62.7 2,864 1

Sort (cost=3,719,682.52..3,720,131.25 rows=179,494 width=112) (actual time=672.291..672.459 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
26. 0.673 670.493 ↑ 62.7 2,864 1

Subquery Scan on data_stats_session (cost=3,376,572.74..3,704,018.46 rows=179,494 width=112) (actual time=565.191..670.493 rows=2,864 loops=1)

27. 51.541 669.820 ↑ 62.7 2,864 1

GroupAggregate (cost=3,376,572.74..3,702,223.52 rows=179,494 width=208) (actual time=565.189..669.820 rows=2,864 loops=1)

  • Group Key: (COALESCE((data_1."stats_session.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_session.created_at")::text, '-'::text)), (COALESCE((data_1."stats_session.user_agent_family")::text, '-'::text))
  • Group Key: (COALESCE((data_1."stats_session.created_at")::text, '-'::text))
  • Group Key: ()
28. 40.164 618.279 ↑ 33.1 42,092 1

Group (cost=3,376,572.74..3,669,087.70 rows=1,392,928 width=354) (actual time=565.153..618.279 rows=42,092 loops=1)

  • Group Key: (COALESCE((data_1."stats_session.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_session.user_agent_family", data_1."stats_session.id", data_1."stats_session.user_id", data_1."stats_session.created_at
29. 517.198 578.115 ↑ 143.6 97,000 1

Sort (cost=3,376,572.74..3,411,395.95 rows=13,929,284 width=354) (actual time=565.148..578.115 rows=97,000 loops=1)

  • Sort Key: (COALESCE((data_1."stats_session.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_session.user_agent_family", data_1."stats_session.id", data_1."stats_session.user_id", data_1."stats_session.created_at
  • Sort Method: quicksort Memory: 16713kB
30. 60.917 60.917 ↑ 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=0.007..60.917 rows=97,000 loops=1)

31. 1.687 481.790 ↑ 62.7 2,864 1

Sort (cost=2,896,344.04..2,896,792.78 rows=179,494 width=128) (actual time=481.583..481.790 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
32. 0.563 480.103 ↑ 62.7 2,864 1

Subquery Scan on data_stats_page_view (cost=2,587,608.74..2,880,679.98 rows=179,494 width=128) (actual time=387.884..480.103 rows=2,864 loops=1)

33. 39.037 479.540 ↑ 62.7 2,864 1

GroupAggregate (cost=2,587,608.74..2,878,885.04 rows=179,494 width=224) (actual time=387.882..479.540 rows=2,864 loops=1)

  • Group 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))
  • Group Key: (COALESCE((data_2."stats_page_view.created_at")::text, '-'::text)), (COALESCE((data_2."stats_session.user_agent_family")::text, '-'::text))
  • Group Key: (COALESCE((data_2."stats_page_view.created_at")::text, '-'::text))
  • Group Key: ()
34. 39.248 440.503 ↑ 15.7 88,893 1

Group (cost=2,587,608.74..2,845,300.49 rows=1,392,928 width=124) (actual time=387.856..440.503 rows=88,893 loops=1)

  • Group 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)), data_2."stats_page_view.runtime_active", data_2."stats_page_view.id", data_2."stats_page_view.created_at
35. 348.008 401.255 ↑ 143.6 97,000 1

Sort (cost=2,587,608.74..2,622,431.95 rows=13,929,284 width=124) (actual time=387.851..401.255 rows=97,000 loops=1)

  • Sort 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)), data_2."stats_page_view.runtime_active", data_2."stats_page_view.id", data_2."stats_page_view.created_at
  • Sort Method: quicksort Memory: 16713kB
36. 53.247 53.247 ↑ 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.016..53.247 rows=97,000 loops=1)