explain.depesz.com

PostgreSQL's explain analyze made readable

Result: hz8y : query7

Settings
# exclusive inclusive rows x rows loops node
1. 0.840 9,145.284 ↑ 9.6 397 1

Hash Join (cost=3,759,595.85..3,944,472.26 rows=3,792 width=273) (actual time=9,109.811..9,145.284 rows=397 loops=1)

  • Hash Cond: ((st.run_id = lrid.run_id) AND (st.date = lrid.run_date))
2.          

CTE latest_run_id_dates

3. 0.819 3,095.759 ↓ 2.0 397 1

HashAggregate (cost=1,661,184.69..1,661,186.69 rows=200 width=8) (actual time=3,095.658..3,095.759 rows=397 loops=1)

  • Group Key: r_1.date
4. 15.610 3,094.940 ↑ 381.6 1,987 1

HashAggregate (cost=1,642,226.89..1,649,810.01 rows=758,312 width=86) (actual time=3,091.849..3,094.940 rows=1,987 loops=1)

  • Group Key: master_2.level0top, rim_1.blueprint_id, rim_1.run_id, rim_1.backfill, rim_1.birthdate, r_1.date, rim_1.cancelled, m_1.valid_range, m_1.version, m_1.active_date_range, m_1.active
5. 39.011 3,079.330 ↑ 190.8 9,935 1

GroupAggregate (cost=1,490,564.57..1,571,135.17 rows=1,895,779 width=118) (actual time=3,030.208..3,079.330 rows=9,935 loops=1)

  • Group Key: master_2.level0top, rim_1.blueprint_id, rim_1.run_id, rim_1.backfill, rim_1.birthdate, r_1.date, rim_1.cancelled, s_1.stage, m_1.version, m_1.valid_range, m_1.active_date_range, m_1.active
6.          

CTE stages

7. 0.013 0.013 ↑ 20.0 5 1

Function Scan on unnest (cost=0.00..1.00 rows=100 width=32) (actual time=0.008..0.013 rows=5 loops=1)

8.          

CTE run_dates

9. 67.654 67.654 ↑ 563.1 51,849 1

Seq Scan on run_id_mapping (cost=0.00..146,872.92 rows=29,195,000 width=12) (actual time=0.040..67.654 rows=51,849 loops=1)

10. 276.544 3,040.319 ↑ 38.9 48,777 1

Sort (cost=1,343,690.64..1,348,430.09 rows=1,895,779 width=118) (actual time=3,030.174..3,040.319 rows=48,777 loops=1)

  • Sort Key: master_2.level0top, rim_1.blueprint_id, rim_1.run_id, rim_1.backfill, rim_1.birthdate, r_1.date, rim_1.cancelled, s_1.stage, m_1.version, m_1.valid_range, m_1.active_date_range, m_1.active
  • Sort Method: quicksort Memory: 8396kB
11. 1,002.009 2,763.775 ↑ 38.9 48,777 1

Merge Left Join (cost=955,485.11..1,146,014.36 rows=1,895,779 width=118) (actual time=2,279.553..2,763.775 rows=48,777 loops=1)

  • Merge Cond: ((r_1.date = log_1.date) AND (rim_1.run_id = log_1.run_id) AND (s_1.stage = log_1.state))
12. 12.906 147.152 ↑ 190.8 9,935 1

Sort (cost=955,484.55..960,224.00 rows=1,895,779 width=118) (actual time=143.493..147.152 rows=9,935 loops=1)

  • Sort Key: r_1.date, rim_1.run_id, s_1.stage
  • Sort Method: quicksort Memory: 1782kB
13. 3.798 134.246 ↑ 190.8 9,935 1

Nested Loop (cost=3,449.37..757,808.27 rows=1,895,779 width=118) (actual time=20.892..134.246 rows=9,935 loops=1)

14. 0.018 0.018 ↑ 20.0 5 1

CTE Scan on stages s_1 (cost=0.00..2.00 rows=100 width=32) (actual time=0.009..0.018 rows=5 loops=1)

15. 2.407 130.430 ↑ 9.5 1,987 5

Materialize (cost=3,449.37..734,156.16 rows=18,958 width=86) (actual time=4.177..26.086 rows=1,987 loops=5)

16. 8.294 128.023 ↑ 9.5 1,987 1

Hash Join (cost=3,449.37..734,061.37 rows=18,958 width=86) (actual time=20.880..128.023 rows=1,987 loops=1)

  • Hash Cond: (r_1.run_id = rim_1.run_id)
17. 98.949 98.949 ↑ 5.1 28,483 1

CTE Scan on run_dates r_1 (cost=0.00..729,875.00 rows=145,975 width=8) (actual time=0.049..98.949 rows=28,483 loops=1)

  • Filter: ((date >= '2018-05-01'::date) AND (date <= '2019-06-01'::date))
  • Rows Removed by Filter: 23366
18. 1.372 20.780 ↑ 1.1 3,582 1

Hash (cost=3,401.97..3,401.97 rows=3,792 width=82) (actual time=20.780..20.780 rows=3,582 loops=1)

  • Buckets: 4096 Batches: 1 Memory Usage: 408kB
19. 1.245 19.408 ↑ 1.1 3,582 1

Hash Join (cost=1,374.19..3,401.97 rows=3,792 width=82) (actual time=17.990..19.408 rows=3,582 loops=1)

  • Hash Cond: (master_2.version = rim_1.blueprint_id)
20. 0.178 1.048 ↑ 1.6 491 1

Nested Loop (cost=257.31..277.10 rows=790 width=65) (actual time=0.778..1.048 rows=491 loops=1)

21. 0.013 0.013 ↑ 1.0 1 1

Index Scan using master_idx on master m_1 (cost=0.14..2.16 rows=1 width=56) (actual time=0.012..0.013 rows=1 loops=1)

  • Index Cond: (level0top = 'MASTER_PG_NA_3'::text)
22. 0.461 0.857 ↑ 1.6 491 1

Sort (cost=257.17..259.14 rows=790 width=106) (actual time=0.763..0.857 rows=491 loops=1)

  • Sort Key: master_2.level0top, master_2.version DESC
  • Sort Method: quicksort Memory: 94kB
23. 0.138 0.396 ↑ 1.6 491 1

Append (cost=0.14..219.14 rows=790 width=106) (actual time=0.005..0.396 rows=491 loops=1)

24. 0.006 0.006 ↑ 1.0 1 1

Index Scan using master_idx on master master_2 (cost=0.14..2.16 rows=1 width=86) (actual time=0.005..0.006 rows=1 loops=1)

  • Index Cond: (level0top = 'MASTER_PG_NA_3'::text)
25. 0.252 0.252 ↑ 1.6 490 1

Index Scan using master_version_history_idx on master master_3 (cost=0.29..209.09 rows=789 width=105) (actual time=0.017..0.252 rows=490 loops=1)

  • Index Cond: (level0top = 'MASTER_PG_NA_3'::text)
26. 8.329 17.115 ↑ 1.0 29,195 1

Hash (cost=751.95..751.95 rows=29,195 width=25) (actual time=17.115..17.115 rows=29,195 loops=1)

  • Buckets: 32768 Batches: 1 Memory Usage: 1776kB
27. 8.786 8.786 ↑ 1.0 29,195 1

Seq Scan on run_id_mapping rim_1 (cost=0.00..751.95 rows=29,195 width=25) (actual time=0.012..8.786 rows=29,195 loops=1)

28. 1,614.614 1,614.614 ↑ 1.0 5,791,750 1

Index Only Scan using run_log_pkey on run_log log_1 (cost=0.56..127,009.33 rows=5,940,918 width=14) (actual time=0.024..1,614.614 rows=5,791,750 loops=1)

  • Heap Fetches: 32057
29. 32.729 6,048.399 ↑ 381.6 1,987 1

GroupAggregate (cost=2,098,402.17..2,233,950.38 rows=758,312 width=200) (actual time=6,013.734..6,048.399 rows=1,987 loops=1)

  • Group Key: st.master_level0top, st.blueprint_id, st.run_id, st.backfill, st.birthdate, st.date, st.cancelled, st.latest_valid_range, st.latest_blueprint_id, st.latest_active_date_range, st.is_master_level0top_active
30. 17.911 6,015.670 ↑ 190.8 9,935 1

Sort (cost=2,098,402.17..2,103,141.62 rows=1,895,779 width=200) (actual time=6,013.665..6,015.670 rows=9,935 loops=1)

  • Sort Key: st.master_level0top, st.blueprint_id, st.run_id, st.backfill, st.birthdate, st.date, st.cancelled, st.latest_valid_range, st.latest_blueprint_id, st.latest_active_date_range, st.is_master_level0top_active
  • Sort Method: quicksort Memory: 2984kB
31. 3.615 5,997.759 ↑ 190.8 9,935 1

Subquery Scan on st (cost=1,645,387.57..1,797,049.89 rows=1,895,779 width=200) (actual time=5,867.558..5,997.759 rows=9,935 loops=1)

32. 113.593 5,994.144 ↑ 190.8 9,935 1

GroupAggregate (cost=1,645,387.57..1,778,092.10 rows=1,895,779 width=168) (actual time=5,867.553..5,994.144 rows=9,935 loops=1)

  • Group Key: master.level0top, rim.blueprint_id, rim.run_id, rim.backfill, rim.birthdate, r.date, rim.cancelled, s.stage, m.version, m.valid_range, m.active_date_range, m.active
33.          

CTE stages

34. 0.026 0.026 ↑ 20.0 5 1

Function Scan on unnest unnest_1 (cost=0.00..1.00 rows=100 width=32) (actual time=0.024..0.026 rows=5 loops=1)

35.          

CTE run_dates

36. 52.068 52.068 ↑ 563.1 51,849 1

Seq Scan on run_id_mapping run_id_mapping_1 (cost=0.00..146,872.92 rows=29,195,000 width=12) (actual time=0.062..52.068 rows=51,849 loops=1)

37. 303.689 5,880.551 ↑ 38.9 48,777 1

Sort (cost=1,498,513.64..1,503,253.09 rows=1,895,779 width=168) (actual time=5,867.418..5,880.551 rows=48,777 loops=1)

  • Sort Key: master.level0top, rim.blueprint_id, rim.run_id, rim.backfill, rim.birthdate, r.date, rim.cancelled, s.stage, m.version, m.valid_range, m.active_date_range, m.active
  • Sort Method: quicksort Memory: 14786kB
38. 1,445.998 5,576.862 ↑ 38.9 48,777 1

Merge Right Join (cost=955,485.11..1,211,971.36 rows=1,895,779 width=168) (actual time=4,648.928..5,576.862 rows=48,777 loops=1)

  • Merge Cond: ((log.date = r.date) AND (log.run_id = rim.run_id) AND (log.state = s.stage))
39. 3,994.394 3,994.394 ↑ 1.0 5,791,750 1

Index Scan using run_log_pkey on run_log log (cost=0.56..192,966.33 rows=5,940,918 width=64) (actual time=0.023..3,994.394 rows=5,791,750 loops=1)

40. 20.134 136.470 ↑ 38.9 48,764 1

Sort (cost=955,484.55..960,224.00 rows=1,895,779 width=118) (actual time=125.584..136.470 rows=48,764 loops=1)

  • Sort Key: r.date, rim.run_id, s.stage
  • Sort Method: quicksort Memory: 1782kB
41. 3.823 116.336 ↑ 190.8 9,935 1

Nested Loop (cost=3,449.37..757,808.27 rows=1,895,779 width=118) (actual time=22.838..116.336 rows=9,935 loops=1)

42. 0.033 0.033 ↑ 20.0 5 1

CTE Scan on stages s (cost=0.00..2.00 rows=100 width=32) (actual time=0.027..0.033 rows=5 loops=1)

43. 2.267 112.480 ↑ 9.5 1,987 5

Materialize (cost=3,449.37..734,156.16 rows=18,958 width=86) (actual time=4.561..22.496 rows=1,987 loops=5)

44. 7.991 110.213 ↑ 9.5 1,987 1

Hash Join (cost=3,449.37..734,061.37 rows=18,958 width=86) (actual time=22.805..110.213 rows=1,987 loops=1)

  • Hash Cond: (r.run_id = rim.run_id)
45. 79.586 79.586 ↑ 5.1 28,483 1

CTE Scan on run_dates r (cost=0.00..729,875.00 rows=145,975 width=8) (actual time=0.084..79.586 rows=28,483 loops=1)

  • Filter: ((date >= '2018-05-01'::date) AND (date <= '2019-06-01'::date))
  • Rows Removed by Filter: 23366
46. 1.410 22.636 ↑ 1.1 3,582 1

Hash (cost=3,401.97..3,401.97 rows=3,792 width=82) (actual time=22.636..22.636 rows=3,582 loops=1)

  • Buckets: 4096 Batches: 1 Memory Usage: 408kB
47. 1.518 21.226 ↑ 1.1 3,582 1

Hash Join (cost=1,374.19..3,401.97 rows=3,792 width=82) (actual time=19.503..21.226 rows=3,582 loops=1)

  • Hash Cond: (master.version = rim.blueprint_id)
48. 0.161 1.014 ↑ 1.6 491 1

Nested Loop (cost=257.31..277.10 rows=790 width=65) (actual time=0.745..1.014 rows=491 loops=1)

49. 0.030 0.030 ↑ 1.0 1 1

Index Scan using master_idx on master m (cost=0.14..2.16 rows=1 width=56) (actual time=0.025..0.030 rows=1 loops=1)

  • Index Cond: (level0top = 'MASTER_PG_NA_3'::text)
50. 0.433 0.823 ↑ 1.6 491 1

Sort (cost=257.17..259.14 rows=790 width=106) (actual time=0.718..0.823 rows=491 loops=1)

  • Sort Key: master.level0top, master.version DESC
  • Sort Method: quicksort Memory: 94kB
51. 0.134 0.390 ↑ 1.6 491 1

Append (cost=0.14..219.14 rows=790 width=106) (actual time=0.015..0.390 rows=491 loops=1)

52. 0.015 0.015 ↑ 1.0 1 1

Index Scan using master_idx on master (cost=0.14..2.16 rows=1 width=86) (actual time=0.014..0.015 rows=1 loops=1)

  • Index Cond: (level0top = 'MASTER_PG_NA_3'::text)
53. 0.241 0.241 ↑ 1.6 490 1

Index Scan using master_version_history_idx on master master_1 (cost=0.29..209.09 rows=789 width=105) (actual time=0.026..0.241 rows=490 loops=1)

  • Index Cond: (level0top = 'MASTER_PG_NA_3'::text)
54. 8.232 18.694 ↑ 1.0 29,195 1

Hash (cost=751.95..751.95 rows=29,195 width=25) (actual time=18.694..18.694 rows=29,195 loops=1)

  • Buckets: 32768 Batches: 1 Memory Usage: 1776kB
55. 10.462 10.462 ↑ 1.0 29,195 1

Seq Scan on run_id_mapping rim (cost=0.00..751.95 rows=29,195 width=25) (actual time=0.014..10.462 rows=29,195 loops=1)

56. 0.114 3,096.045 ↓ 2.0 397 1

Hash (cost=4.00..4.00 rows=200 width=8) (actual time=3,096.045..3,096.045 rows=397 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 24kB
57. 3,095.931 3,095.931 ↓ 2.0 397 1

CTE Scan on latest_run_id_dates lrid (cost=0.00..4.00 rows=200 width=8) (actual time=3,095.667..3,095.931 rows=397 loops=1)

Planning time : 2.703 ms
Execution time : 9,151.208 ms