explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Onwr

Settings
# exclusive inclusive rows x rows loops node
1. 25.653 5,302.808 ↑ 2,835.6 2,864 1

Sort (cost=1,530,979,421,174.81..1,530,979,441,477.83 rows=8,121,210 width=184) (actual time=5,301.536..5,302.808 rows=2,864 loops=1)

  • Sort Key: (COALESCE(data_stats_session.day, data_stats_page_view.day, data_stats_event.day)) DESC, (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: 499kB
2.          

CTE data

3. 214.972 3,067.700 ↑ 7,915,642.8 97,000 1

Nested Loop (cost=0.00..9,577,907,194.48 rows=767,817,347,231 width=136) (actual time=0.715..3,067.700 rows=97,000 loops=1)

  • Join Filter: (((stats_session.created_at >= '2019-03-11 09:21:00+00'::timestamp with time zone) AND (stats_session.created_at <= '2019-05-10 09:21:59+00'::timestamp with time zone) AND (stats_page_view.created_at >= '2019-03-11 09:21:00+00'::timestamp with time zone) AND (stats_page_view.created_at <= '2019-05-10 09:21:59+00'::timestamp with time zone) AND (stats_event.created_at >= '2019-03-11 09:21:00+00'::timestamp with time zone) AND (stats_event.created_at <= '2019-05-10 09:21:59+00'::timestamp with time zone) AND ((stats_session.property_id)::numeric = '1'::numeric) AND ((stats_page_view.property_id)::numeric = '1'::numeric) AND ((stats_event.property_id)::numeric = '1'::numeric) AND ((stats_session.campaign_id)::numeric >= '1'::numeric)) OR (((stats_event."values" ->> 'scrollTriggerValue'::text))::numeric <= '100'::numeric))
4. 51.360 2,464.728 ↑ 35,181.6 97,000 1

Nested Loop (cost=0.00..991,941.39 rows=3,412,616,334 width=112) (actual time=0.694..2,464.728 rows=97,000 loops=1)

5. 10.050 2,025.368 ↑ 15.6 97,000 1

Append (cost=0.00..262,736.34 rows=1,516,715 width=80) (actual time=0.675..2,025.368 rows=97,000 loops=1)

6. 0.005 0.005 ↓ 0.0 0 1

Seq Scan on stats_event (cost=0.00..0.00 rows=1 width=80) (actual time=0.005..0.005 rows=0 loops=1)

  • Filter: (((created_at >= '2019-03-11 09:21:00+00'::timestamp with time zone) AND (created_at <= '2019-05-10 09:21:59+00'::timestamp with time zone) AND ((property_id)::numeric = '1'::numeric)) OR ((("values" ->> 'scrollTriggerValue'::text))::numeric <= '100'::numeric))
7. 2,015.313 2,015.313 ↑ 15.6 97,000 1

Seq Scan on _hyper_5_4_chunk (cost=0.00..255,152.76 rows=1,516,714 width=80) (actual time=0.669..2,015.313 rows=97,000 loops=1)

  • Filter: (((created_at >= '2019-03-11 09:21:00+00'::timestamp with time zone) AND (created_at <= '2019-05-10 09:21:59+00'::timestamp with time zone) AND ((property_id)::numeric = '1'::numeric)) OR ((("values" ->> 'scrollTriggerValue'::text))::numeric <= '100'::numeric))
  • Rows Removed by Filter: 4402999
8. 194.000 388.000 ↑ 2.0 1 97,000

Append (cost=0.00..0.46 rows=2 width=48) (actual time=0.004..0.004 rows=1 loops=97,000)

9. 0.000 0.000 ↓ 0.0 0 97,000

Seq Scan on stats_page_view (cost=0.00..0.00 rows=1 width=48) (actual time=0.000..0.000 rows=0 loops=97,000)

  • Filter: (stats_event.page_view_id = id)
10. 194.000 194.000 ↑ 1.0 1 97,000

Index Scan using "3_3_stats_page_view_id" on _hyper_3_3_chunk (cost=0.42..0.45 rows=1 width=48) (actual time=0.002..0.002 rows=1 loops=97,000)

  • Index Cond: (id = stats_event.page_view_id)
11. 194.000 388.000 ↑ 2.0 1 97,000

Append (cost=0.00..0.45 rows=2 width=56) (actual time=0.004..0.004 rows=1 loops=97,000)

12. 0.000 0.000 ↓ 0.0 0 97,000

Seq Scan on stats_session (cost=0.00..0.00 rows=1 width=266) (actual time=0.000..0.000 rows=0 loops=97,000)

  • Filter: (stats_page_view.session_id = id)
13. 194.000 194.000 ↑ 1.0 1 97,000

Index Scan using "2_2_stats_session_id" on _hyper_2_2_chunk (cost=0.41..0.44 rows=1 width=56) (actual time=0.002..0.002 rows=1 loops=97,000)

  • Index Cond: (id = stats_page_view.session_id)
14. 13.907 5,277.155 ↑ 2,835.6 2,864 1

Merge Full Join (cost=1,521,399,081,693.25..1,521,399,344,720.97 rows=8,121,210 width=184) (actual time=5,260.021..5,277.155 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))))
15. 20.054 3,783.356 ↑ 2,807.3 2,864 1

Sort (cost=435,057,325,102.65..435,057,345,203.15 rows=8,040,201 width=136) (actual time=3,783.020..3,783.356 rows=2,864 loops=1)

  • Sort Key: data_stats_event.day, data_stats_event.group1, data_stats_event.group2
  • Sort Method: quicksort Memory: 346kB
16. 0.530 3,763.302 ↑ 2,807.3 2,864 1

Subquery Scan on data_stats_event (cost=416,051,800,475.35..435,055,460,723.83 rows=8,040,201 width=136) (actual time=3,576.241..3,763.302 rows=2,864 loops=1)

17. 61.606 3,762.772 ↑ 2,807.3 2,864 1

GroupAggregate (cost=416,051,800,475.35..435,055,380,321.82 rows=8,040,201 width=232) (actual time=3,576.240..3,762.772 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: ()
18. 44.614 3,701.166 ↑ 791,564.3 97,000 1

Group (cost=416,051,800,475.35..432,175,964,767.20 rows=76,781,734,723 width=156) (actual time=3,576.220..3,701.166 rows=97,000 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)), data."stats_event.event_id", data."stats_event.values->>'scrollTriggerValue'", data."stats_event.id", data."stats_event.created_at
19. 432.449 3,656.552 ↑ 7,915,642.8 97,000 1

Sort (cost=416,051,800,475.35..417,971,343,843.42 rows=767,817,347,231 width=156) (actual time=3,576.216..3,656.552 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)), data."stats_event.event_id", data."stats_event.values->>'scrollTriggerValue'", data."stats_event.id", data."stats_event.created_at
  • Sort Method: external merge Disk: 7264kB
20. 3,224.103 3,224.103 ↑ 7,915,642.8 97,000 1

CTE Scan on data (cost=0.00..23,034,520,416.93 rows=767,817,347,231 width=156) (actual time=0.721..3,224.103 rows=97,000 loops=1)

21. 2.856 1,479.892 ↑ 2,821.4 2,864 1

Materialize (cost=1,086,341,756,590.60..1,086,341,796,993.62 rows=8,080,604 width=240) (actual time=1,476.828..1,479.892 rows=2,864 loops=1)

22. 10.403 1,477.036 ↑ 2,821.4 2,864 1

Sort (cost=1,086,341,756,590.60..1,086,341,776,792.11 rows=8,080,604 width=240) (actual time=1,476.821..1,477.036 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
23. 24.849 1,466.633 ↑ 2,821.4 2,864 1

Merge Full Join (cost=1,086,339,004,921.03..1,086,339,267,035.62 rows=8,080,604 width=240) (actual time=1,441.010..1,466.633 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))
24. 2.036 788.013 ↑ 2,807.3 2,864 1

Sort (cost=696,343,007,501.30..696,343,027,601.80 rows=8,040,201 width=112) (actual time=787.831..788.013 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
25. 0.715 785.977 ↑ 2,807.3 2,864 1

Subquery Scan on data_stats_session (cost=678,489,370,331.35..696,341,284,458.48 rows=8,040,201 width=112) (actual time=610.909..785.977 rows=2,864 loops=1)

26. 64.185 785.262 ↑ 2,807.3 2,864 1

GroupAggregate (cost=678,489,370,331.35..696,341,204,056.47 rows=8,040,201 width=208) (actual time=610.908..785.262 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: ()
27. 32.812 721.077 ↑ 1,824,140.8 42,092 1

Group (cost=678,489,370,331.35..694,613,534,623.20 rows=76,781,734,723 width=354) (actual time=610.875..721.077 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
28. 596.270 688.265 ↑ 7,915,642.8 97,000 1

Sort (cost=678,489,370,331.35..680,408,913,699.42 rows=767,817,347,231 width=354) (actual time=610.871..688.265 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: external merge Disk: 9328kB
29. 91.995 91.995 ↑ 7,915,642.8 97,000 1

CTE Scan on data data_1 (cost=0.00..23,034,520,416.93 rows=767,817,347,231 width=354) (actual time=0.078..91.995 rows=97,000 loops=1)

30. 0.392 653.771 ↑ 2,807.3 2,864 1

Materialize (cost=389,995,997,419.73..389,996,037,620.73 rows=8,040,201 width=128) (actual time=653.169..653.771 rows=2,864 loops=1)

31. 28.363 653.379 ↑ 2,807.3 2,864 1

Sort (cost=389,995,997,419.73..389,996,017,520.23 rows=8,040,201 width=128) (actual time=653.165..653.379 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.540 625.016 ↑ 2,807.3 2,864 1

Subquery Scan on data_stats_page_view (cost=374,061,789,293.35..389,994,180,152.91 rows=8,040,201 width=128) (actual time=382.144..625.016 rows=2,864 loops=1)

33. 73.417 624.476 ↑ 2,807.3 2,864 1

GroupAggregate (cost=374,061,789,293.35..389,994,099,750.90 rows=8,040,201 width=224) (actual time=382.142..624.476 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. 84.154 551.059 ↑ 863,754.6 88,893 1

Group (cost=374,061,789,293.35..388,266,410,217.12 rows=76,781,734,723 width=124) (actual time=382.117..551.059 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. 406.563 466.905 ↑ 7,915,642.8 97,000 1

Sort (cost=374,061,789,293.35..375,981,332,661.42 rows=767,817,347,231 width=124) (actual time=382.113..466.905 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: external merge Disk: 7272kB
36. 60.342 60.342 ↑ 7,915,642.8 97,000 1

CTE Scan on data data_2 (cost=0.00..23,034,520,416.93 rows=767,817,347,231 width=124) (actual time=0.025..60.342 rows=97,000 loops=1)