explain.depesz.com

PostgreSQL's explain analyze made readable

Result: KTv5

Settings
# exclusive inclusive rows x rows loops node
1. 6.082 2,107.225 ↑ 62.7 2,864 1

Sort (cost=15,576,805.87..15,577,254.60 rows=179,494 width=184) (actual time=2,107.075..2,107.225 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. 100.152 314.337 ↑ 143.6 97,000 1

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

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

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

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

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

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

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

Append (cost=0.00..764.14 rows=980 width=52) (actual time=0.032..1.055 rows=970 loops=1)

8. 0.006 0.006 ↓ 0.0 0 1

Seq Scan on stats_session (cost=0.00..0.00 rows=1 width=262) (actual time=0.005..0.006 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.931 0.931 ↑ 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..0.931 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.647 10.070 ↑ 1.0 9,700 1

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

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

Append (cost=0.00..7,675.38 rows=10,126 width=44) (actual time=0.027..6.423 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.004..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.474 5.474 ↑ 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.474 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.646 194.353 ↑ 1.0 97,000 1

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

15. 105.106 180.707 ↑ 1.0 97,000 1

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

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

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

17. 0.011 0.011 ↓ 0.0 0 1

Seq Scan on stats_event (cost=0.00..0.00 rows=1 width=76) (actual time=0.011..0.011 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. 54.783 54.783 ↑ 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.023..54.783 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. 3.007 2,101.143 ↑ 62.7 2,864 1

Merge Full Join (cost=14,617,912.04..14,621,990.93 rows=179,494 width=184) (actual time=2,097.348..2,101.143 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))
20. 2.658 1,486.086 ↑ 62.7 2,864 1

Sort (cost=10,305,521.51..10,305,970.24 rows=179,494 width=240) (actual time=1,485.857..1,486.086 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
21. 2.844 1,483.428 ↑ 62.7 2,864 1

Merge Full Join (cost=10,274,208.56..10,278,287.45 rows=179,494 width=240) (actual time=1,479.870..1,483.428 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))
22. 1.952 960.303 ↑ 62.7 2,864 1

Sort (cost=6,337,386.52..6,337,835.25 rows=179,494 width=112) (actual time=960.139..960.303 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
23. 0.696 958.351 ↑ 62.7 2,864 1

Subquery Scan on data_stats_session (cost=5,988,316.74..6,315,762.46 rows=179,494 width=112) (actual time=766.518..958.351 rows=2,864 loops=1)

24. 48.368 957.655 ↑ 62.7 2,864 1

GroupAggregate (cost=5,988,316.74..6,313,967.52 rows=179,494 width=208) (actual time=766.516..957.655 rows=2,864 loops=1)

  • Group Key: (COALESCE((data."stats_session.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_session.created_at")::text, '-'::text)), (COALESCE((data."stats_session.user_agent_family")::text, '-'::text))
  • Group Key: (COALESCE((data."stats_session.created_at")::text, '-'::text))
  • Group Key: ()
25. 33.298 909.287 ↑ 33.1 42,092 1

Group (cost=5,988,316.74..6,280,831.70 rows=1,392,928 width=354) (actual time=766.477..909.287 rows=42,092 loops=1)

  • Group Key: (COALESCE((data."stats_session.created_at")::text, '-'::text)), (COALESCE((data."stats_session.user_agent_family")::text, '-'::text)), (COALESCE((data."stats_event.event_id")::text, '-'::text)), data."stats_session.user_agent_family", data."stats_session.id", data."stats_session.user_id", data."stats_session.created_at
26. 444.831 875.989 ↑ 143.6 97,000 1

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

  • Sort Key: (COALESCE((data."stats_session.created_at")::text, '-'::text)), (COALESCE((data."stats_session.user_agent_family")::text, '-'::text)), (COALESCE((data."stats_event.event_id")::text, '-'::text)), data."stats_session.user_agent_family", data."stats_session.id", data."stats_session.user_id", data."stats_session.created_at
  • Sort Method: external merge Disk: 9320kB
27. 431.158 431.158 ↑ 143.6 97,000 1

CTE Scan on data (cost=0.00..417,878.52 rows=13,929,284 width=354) (actual time=171.279..431.158 rows=97,000 loops=1)

28. 0.370 520.281 ↑ 62.7 2,864 1

Materialize (cost=3,936,822.04..3,937,719.51 rows=179,494 width=128) (actual time=519.723..520.281 rows=2,864 loops=1)

29. 1.385 519.911 ↑ 62.7 2,864 1

Sort (cost=3,936,822.04..3,937,270.78 rows=179,494 width=128) (actual time=519.719..519.911 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
30. 0.481 518.526 ↑ 62.7 2,864 1

Subquery Scan on data_stats_page_view (cost=3,621,424.74..3,914,495.98 rows=179,494 width=128) (actual time=363.706..518.526 rows=2,864 loops=1)

31. 40.610 518.045 ↑ 62.7 2,864 1

GroupAggregate (cost=3,621,424.74..3,912,701.04 rows=179,494 width=224) (actual time=363.704..518.045 rows=2,864 loops=1)

  • Group 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))
  • Group Key: (COALESCE((data_1."stats_page_view.created_at")::text, '-'::text)), (COALESCE((data_1."stats_session.user_agent_family")::text, '-'::text))
  • Group Key: (COALESCE((data_1."stats_page_view.created_at")::text, '-'::text))
  • Group Key: ()
32. 34.610 477.435 ↑ 15.7 88,893 1

Group (cost=3,621,424.74..3,879,116.49 rows=1,392,928 width=124) (actual time=363.654..477.435 rows=88,893 loops=1)

  • Group 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)), data_1."stats_page_view.runtime_active", data_1."stats_page_view.id", data_1."stats_page_view.created_at
33. 382.160 442.825 ↑ 143.6 97,000 1

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

  • Sort 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)), data_1."stats_page_view.runtime_active", data_1."stats_page_view.id", data_1."stats_page_view.created_at
  • Sort Method: external merge Disk: 7272kB
34. 60.665 60.665 ↑ 143.6 97,000 1

CTE Scan on data data_1 (cost=0.00..417,878.52 rows=13,929,284 width=124) (actual time=0.054..60.665 rows=97,000 loops=1)

35. 0.365 612.050 ↑ 62.7 2,864 1

Materialize (cost=4,312,390.53..4,313,288.00 rows=179,494 width=136) (actual time=611.482..612.050 rows=2,864 loops=1)

36. 1.570 611.685 ↑ 62.7 2,864 1

Sort (cost=4,312,390.53..4,312,839.27 rows=179,494 width=136) (actual time=611.479..611.685 rows=2,864 loops=1)

  • Sort Key: data_stats_event.day, data_stats_event.group1, data_stats_event.group2
  • Sort Method: quicksort Memory: 346kB
37. 0.534 610.115 ↑ 62.7 2,864 1

Subquery Scan on data_stats_event (cost=3,947,890.74..4,289,714.47 rows=179,494 width=136) (actual time=440.367..610.115 rows=2,864 loops=1)

38. 26.423 609.581 ↑ 62.7 2,864 1

GroupAggregate (cost=3,947,890.74..4,287,919.53 rows=179,494 width=232) (actual time=440.365..609.581 rows=2,864 loops=1)

  • Group 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))
  • Group Key: (COALESCE((data_2."stats_event.created_at")::text, '-'::text)), (COALESCE((data_2."stats_session.user_agent_family")::text, '-'::text))
  • Group Key: (COALESCE((data_2."stats_event.created_at")::text, '-'::text))
  • Group Key: ()
39. 36.118 583.158 ↑ 14.4 97,000 1

Group (cost=3,947,890.74..4,240,405.70 rows=1,392,928 width=156) (actual time=440.342..583.158 rows=97,000 loops=1)

  • Group 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)), data_2."stats_event.event_id", data_2."stats_event.values->>'scrollTriggerValue'", data_2."stats_event.id", data_2."stats_event.created_at
40. 488.379 547.040 ↑ 143.6 97,000 1

Sort (cost=3,947,890.74..3,982,713.95 rows=13,929,284 width=156) (actual time=440.339..547.040 rows=97,000 loops=1)

  • Sort 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)), data_2."stats_event.event_id", data_2."stats_event.values->>'scrollTriggerValue'", data_2."stats_event.id", data_2."stats_event.created_at
  • Sort Method: external merge Disk: 7248kB
41. 58.661 58.661 ↑ 143.6 97,000 1

CTE Scan on data data_2 (cost=0.00..417,878.52 rows=13,929,284 width=156) (actual time=0.025..58.661 rows=97,000 loops=1)