explain.depesz.com

PostgreSQL's explain analyze made readable

Result: dB5o : run_status

Settings
# exclusive inclusive rows x rows loops node
1. 4.584 43,207.067 ↑ 5,797,261.3 32 1

Hash Join (cost=924,882,570.06..961,753,152.01 rows=185,512,362 width=244) (actual time=43,205.278..43,207.067 rows=32 loops=1)

  • Hash Cond: (js.run_id = lrid.run_id)
2.          

CTE latest_run_id_dates

3. 0.029 90.580 ↑ 6.2 32 1

HashAggregate (cost=1,882,595.83..1,882,597.83 rows=200 width=8) (actual time=90.572..90.580 rows=32 loops=1)

  • Group Key: r_1.date
4. 0.439 90.551 ↑ 210.0 64 1

HashAggregate (cost=1,882,259.83..1,882,394.23 rows=13,440 width=86) (actual time=90.502..90.551 rows=64 loops=1)

  • Group Key: cm.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. 2.197 90.112 ↑ 105.0 320 1

HashAggregate (cost=1,880,663.87..1,880,999.86 rows=33,599 width=118) (actual time=89.918..90.112 rows=320 loops=1)

  • Group Key: cm.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.010 0.010 ↑ 20.0 5 1

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

8.          

CTE run_dates

9. 48.282 48.282 ↑ 1,180.8 51,849 1

Seq Scan on run_id_mapping (cost=0.00..307,493.35 rows=61,223,000 width=12) (actual time=0.042..48.282 rows=51,849 loops=1)

10. 1.985 87.915 ↑ 21.1 1,595 1

Nested Loop Left Join (cost=1,324.13..1,572,161.56 rows=33,599 width=118) (actual time=17.943..87.915 rows=1,595 loops=1)

  • Join Filter: (log_1.state = s_1.stage)
  • Rows Removed by Join Filter: 6380
11. 0.118 82.730 ↑ 105.0 320 1

Nested Loop (cost=1,323.58..1,533,472.70 rows=33,599 width=118) (actual time=17.870..82.730 rows=320 loops=1)

12. 0.017 0.017 ↑ 20.0 5 1

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

13. 0.085 82.595 ↑ 5.2 64 5

Materialize (cost=1,323.58..1,533,051.54 rows=336 width=86) (actual time=3.572..16.519 rows=64 loops=5)

14. 0.662 82.510 ↑ 5.2 64 1

Hash Join (cost=1,323.58..1,533,049.86 rows=336 width=86) (actual time=17.855..82.510 rows=64 loops=1)

  • Hash Cond: (r_1.run_id = rim_1.run_id)
15. 69.522 69.522 ↑ 99.1 3,090 1

CTE Scan on run_dates r_1 (cost=0.00..1,530,575.00 rows=306,115 width=8) (actual time=0.650..69.522 rows=3,090 loops=1)

  • Filter: ((date >= '2019-01-01'::date) AND (date <= '2019-02-01'::date))
  • Rows Removed by Filter: 48759
16. 0.071 12.326 ↓ 2.0 135 1

Hash (cost=1,322.74..1,322.74 rows=67 width=82) (actual time=12.326..12.326 rows=135 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 24kB
17. 0.045 12.255 ↓ 2.0 135 1

Nested Loop (cost=17.57..1,322.74 rows=67 width=82) (actual time=0.853..12.255 rows=135 loops=1)

18. 0.012 0.012 ↑ 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.011..0.012 rows=1 loops=1)

  • Index Cond: (level0top = 'VID_NESTLE_MASTER_1'::text)
19. 4.524 12.198 ↓ 2.0 135 1

Hash Join (cost=17.43..1,319.91 rows=67 width=47) (actual time=0.839..12.198 rows=135 loops=1)

  • Hash Cond: (rim_1.blueprint_id = cm.version)
20. 7.624 7.624 ↑ 2.1 29,195 1

Seq Scan on run_id_mapping rim_1 (cost=0.00..1,072.23 rows=61,223 width=25) (actual time=0.010..7.624 rows=29,195 loops=1)

21. 0.008 0.050 ↑ 3.5 4 1

Hash (cost=17.25..17.25 rows=14 width=30) (actual time=0.050..0.050 rows=4 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
22. 0.003 0.042 ↑ 3.5 4 1

Subquery Scan on cm (cost=17.08..17.25 rows=14 width=30) (actual time=0.039..0.042 rows=4 loops=1)

23. 0.018 0.039 ↑ 3.5 4 1

Sort (cost=17.08..17.11 rows=14 width=105) (actual time=0.037..0.039 rows=4 loops=1)

  • Sort Key: master_2.level0top, master_2.version DESC
  • Sort Method: quicksort Memory: 25kB
24. 0.001 0.021 ↑ 3.5 4 1

Append (cost=0.14..16.81 rows=14 width=105) (actual time=0.007..0.021 rows=4 loops=1)

25. 0.007 0.007 ↑ 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.006..0.007 rows=1 loops=1)

  • Index Cond: (level0top = 'VID_NESTLE_MASTER_1'::text)
26. 0.013 0.013 ↑ 4.3 3 1

Index Scan using master_version_history_idx on master master_3 (cost=0.29..14.51 rows=13 width=105) (actual time=0.009..0.013 rows=3 loops=1)

  • Index Cond: (level0top = 'VID_NESTLE_MASTER_1'::text)
27. 3.200 3.200 ↓ 25.0 25 320

Index Only Scan using run_log_pkey on run_log log_1 (cost=0.56..1.14 rows=1 width=14) (actual time=0.004..0.010 rows=25 loops=320)

  • Index Cond: ((date = r_1.date) AND (run_id = rim_1.run_id))
  • Heap Fetches: 0
28. 87.630 43,111.880 ↑ 10,235.7 18,124 1

GroupAggregate (cost=922,999,965.74..951,058,710.49 rows=185,512,362 width=136) (actual time=43,017.454..43,111.880 rows=18,124 loops=1)

  • Group Key: js.master_level0top, js.blueprint_id, js.run_id, js.backfill, js.birthdate, js.latest_valid_range, js.latest_blueprint_id, js.latest_active_date_range, js.is_master_level0top_active
29. 37.789 43,024.250 ↑ 15,583.5 29,761 1

Sort (cost=922,999,965.74..924,159,418.00 rows=463,780,905 width=136) (actual time=43,017.358..43,024.250 rows=29,761 loops=1)

  • Sort Key: js.master_level0top, js.blueprint_id, js.run_id, js.backfill, js.birthdate, js.latest_valid_range, js.latest_blueprint_id, js.latest_active_date_range, js.is_master_level0top_active
  • Sort Method: quicksort Memory: 11238kB
30. 10.179 42,986.461 ↑ 15,583.5 29,761 1

Subquery Scan on js (cost=778,413,095.55..838,124,887.04 rows=463,780,905 width=136) (actual time=42,694.256..42,986.461 rows=29,761 loops=1)

31. 244.347 42,976.282 ↑ 15,583.5 29,761 1

GroupAggregate (cost=778,413,095.55..833,487,077.99 rows=463,780,905 width=136) (actual time=42,694.252..42,976.282 rows=29,761 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
32. 308.209 42,731.935 ↑ 7,791.8 148,805 1

Sort (cost=778,413,095.55..781,311,726.20 rows=1,159,452,262 width=136) (actual time=42,694.195..42,731.935 rows=148,805 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: 65229kB
33. 57.717 42,423.726 ↑ 7,791.8 148,805 1

Subquery Scan on st (cost=471,602,917.64..558,561,837.29 rows=1,159,452,262 width=136) (actual time=38,752.942..42,423.726 rows=148,805 loops=1)

34. 3,026.732 42,366.009 ↑ 7,791.8 148,805 1

GroupAggregate (cost=471,602,917.64..546,967,314.67 rows=1,159,452,262 width=164) (actual time=38,752.941..42,366.009 rows=148,805 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
35.          

CTE stages

36. 0.019 0.019 ↑ 20.0 5 1

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

37.          

CTE run_dates

38. 58.406 58.406 ↑ 1,180.8 51,849 1

Seq Scan on run_id_mapping run_id_mapping_1 (cost=0.00..307,493.35 rows=61,223,000 width=12) (actual time=2.778..58.406 rows=51,849 loops=1)

39. 13,457.037 39,339.277 ↑ 1,214.5 954,697 1

Sort (cost=471,295,423.29..474,194,053.94 rows=1,159,452,262 width=164) (actual time=38,752.840..39,339.277 rows=954,697 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: external merge Disk: 160432kB
40. 1,717.506 25,882.240 ↑ 1,214.5 954,697 1

Merge Left Join (cost=230,714,896.66..242,385,943.03 rows=1,159,452,262 width=164) (actual time=24,064.880..25,882.240 rows=954,697 loops=1)

  • Merge Cond: ((r.date = log.date) AND (rim.run_id = log.run_id) AND (s.stage = log.state))
41. 215.736 555.888 ↑ 7,788.1 148,875 1

Sort (cost=229,784,933.53..232,683,564.18 rows=1,159,452,262 width=118) (actual time=509.911..555.888 rows=148,875 loops=1)

  • Sort Key: r.date, rim.run_id, s.stage
  • Sort Method: quicksort Memory: 27198kB
42. 96.570 340.152 ↑ 7,788.1 148,875 1

Hash Right Join (cost=36,701.52..14,462,785.27 rows=1,159,452,262 width=118) (actual time=170.791..340.152 rows=148,875 loops=1)

  • Hash Cond: (r.run_id = rim.run_id)
43. 83.081 83.081 ↑ 1,180.8 51,849 1

CTE Scan on run_dates r (cost=0.00..1,224,460.00 rows=61,223,000 width=8) (actual time=2.781..83.081 rows=51,849 loops=1)

44. 43.276 160.501 ↑ 12.8 90,690 1

Hash (cost=22,208.37..22,208.37 rows=1,159,452 width=114) (actual time=160.501..160.501 rows=90,690 loops=1)

  • Buckets: 2097152 Batches: 1 Memory Usage: 27066kB
45. 33.250 117.225 ↑ 12.8 90,690 1

Nested Loop (cost=7,250.72..22,208.37 rows=1,159,452 width=114) (actual time=49.008..117.225 rows=90,690 loops=1)

46. 0.030 0.030 ↑ 20.0 5 1

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

47. 20.105 83.945 ↓ 1.6 18,138 5

Materialize (cost=7,250.72..7,742.85 rows=11,594 width=82) (actual time=9.798..16.789 rows=18,138 loops=5)

48. 8.513 63.840 ↓ 1.6 18,138 1

Merge Join (cost=7,250.72..7,684.88 rows=11,594 width=82) (actual time=48.982..63.840 rows=18,138 loops=1)

  • Merge Cond: (master.version = rim.blueprint_id)
49. 3.744 31.828 ↓ 2.4 5,696 1

Sort (cost=1,310.71..1,316.75 rows=2,416 width=65) (actual time=30.735..31.828 rows=5,696 loops=1)

  • Sort Key: master.version
  • Sort Method: quicksort Memory: 994kB
50. 5.339 28.084 ↓ 2.4 5,696 1

Merge Join (cost=984.53..1,174.95 rows=2,416 width=65) (actual time=20.755..28.084 rows=5,696 loops=1)

  • Merge Cond: (master.level0top = m.level0top)
51. 18.060 21.724 ↑ 1.6 6,399 1

Sort (cost=979.75..1,005.45 rows=10,279 width=106) (actual time=20.666..21.724 rows=6,399 loops=1)

  • Sort Key: master.level0top, master.version DESC
  • Sort Method: quicksort Memory: 1464kB
52. 1.515 3.664 ↑ 1.6 6,399 1

Append (cost=0.00..294.79 rows=10,279 width=106) (actual time=0.010..3.664 rows=6,399 loops=1)

53. 0.027 0.027 ↑ 1.0 47 1

Seq Scan on master (cost=0.00..3.47 rows=47 width=87) (actual time=0.008..0.027 rows=47 loops=1)

54. 2.122 2.122 ↑ 1.6 6,352 1

Seq Scan on master master_1 (cost=0.00..291.32 rows=10,232 width=106) (actual time=0.005..2.122 rows=6,352 loops=1)

55. 1.005 1.021 ↓ 121.2 5,696 1

Sort (cost=4.78..4.89 rows=47 width=56) (actual time=0.085..1.021 rows=5,696 loops=1)

  • Sort Key: m.level0top
  • Sort Method: quicksort Memory: 31kB
56. 0.016 0.016 ↑ 1.0 47 1

Seq Scan on master m (cost=0.00..3.47 rows=47 width=56) (actual time=0.004..0.016 rows=47 loops=1)

57. 15.561 23.499 ↑ 2.5 24,224 1

Sort (cost=5,940.01..6,093.06 rows=61,223 width=25) (actual time=18.187..23.499 rows=24,224 loops=1)

  • Sort Key: rim.blueprint_id
  • Sort Method: quicksort Memory: 3049kB
58. 7.938 7.938 ↑ 2.1 29,195 1

Seq Scan on run_id_mapping rim (cost=0.00..1,072.23 rows=61,223 width=25) (actual time=0.030..7.938 rows=29,195 loops=1)

59. 1,707.876 23,608.846 ↑ 1.0 5,952,046 1

Materialize (cost=929,963.13..959,890.09 rows=5,985,393 width=60) (actual time=17,830.388..23,608.846 rows=5,952,046 loops=1)

60. 19,906.543 21,900.970 ↑ 1.0 5,940,402 1

Sort (cost=929,963.13..944,926.61 rows=5,985,393 width=60) (actual time=17,830.379..21,900.970 rows=5,940,402 loops=1)

  • Sort Key: log.date, log.run_id, log.state
  • Sort Method: external merge Disk: 392120kB
61. 1,994.427 1,994.427 ↑ 1.0 5,940,402 1

Seq Scan on run_log log (cost=0.00..127,622.93 rows=5,985,393 width=60) (actual time=0.007..1,994.427 rows=5,940,402 loops=1)

62. 0.008 90.603 ↑ 6.2 32 1

Hash (cost=4.00..4.00 rows=200 width=8) (actual time=90.603..90.603 rows=32 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
63. 90.595 90.595 ↑ 6.2 32 1

CTE Scan on latest_run_id_dates lrid (cost=0.00..4.00 rows=200 width=8) (actual time=90.574..90.595 rows=32 loops=1)

Planning time : 2.662 ms
Execution time : 43,309.558 ms