explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Q6XD : stage_status4

Settings
# exclusive inclusive rows x rows loops node
1. 169.122 60,794.732 ↑ 579,726.1 10 1

Hash Join (cost=459,041,854.72..583,740,945.50 rows=5,797,261 width=8) (actual time=60,728.570..60,794.732 rows=10 loops=1)

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

CTE latest_run_id_dates

3. 0.015 514.749 ↑ 100.0 2 1

HashAggregate (cost=1,872,704.38..1,872,706.38 rows=200 width=8) (actual time=514.749..514.749 rows=2 loops=1)

  • Group Key: r_1.date
4. 0.090 514.734 ↑ 3,360.0 4 1

HashAggregate (cost=1,872,368.38..1,872,502.78 rows=13,440 width=86) (actual time=514.704..514.734 rows=4 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. 0.730 514.644 ↑ 1,680.0 20 1

HashAggregate (cost=1,870,772.42..1,871,108.41 rows=33,599 width=118) (actual time=514.524..514.644 rows=20 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.016 0.016 ↑ 20.0 5 1

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

8.          

CTE run_dates

9. 275.431 275.431 ↑ 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.057..275.431 rows=51,849 loops=1)

10. 0.632 513.914 ↑ 148.7 226 1

Nested Loop Left Join (cost=1,324.01..1,562,270.11 rows=33,599 width=118) (actual time=123.602..513.914 rows=226 loops=1)

  • Join Filter: (log_1.state = s_1.stage)
  • Rows Removed by Join Filter: 904
11. 0.030 488.662 ↑ 1,680.0 20 1

Nested Loop (cost=1,323.58..1,533,472.70 rows=33,599 width=118) (actual time=107.053..488.662 rows=20 loops=1)

12. 0.022 0.022 ↑ 20.0 5 1

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

13. 0.021 488.610 ↑ 84.0 4 5

Materialize (cost=1,323.58..1,533,051.54 rows=336 width=86) (actual time=21.408..97.722 rows=4 loops=5)

14. 0.195 488.589 ↑ 84.0 4 1

Hash Join (cost=1,323.58..1,533,049.86 rows=336 width=86) (actual time=107.032..488.589 rows=4 loops=1)

  • Hash Cond: (r_1.run_id = rim_1.run_id)
15. 392.206 392.206 ↑ 1,523.0 201 1

CTE Scan on run_dates r_1 (cost=0.00..1,530,575.00 rows=306,115 width=8) (actual time=1.773..392.206 rows=201 loops=1)

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

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

  • Buckets: 1024 Batches: 1 Memory Usage: 24kB
17. 0.120 95.991 ↓ 2.0 135 1

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

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

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

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

  • Hash Cond: (rim_1.blueprint_id = cm.version)
20. 86.649 86.649 ↑ 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.020..86.649 rows=29,195 loops=1)

21. 0.008 0.100 ↑ 3.5 4 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
22. 0.005 0.092 ↑ 3.5 4 1

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

23. 0.041 0.087 ↑ 3.5 4 1

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

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

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

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

  • Index Cond: (level0top = 'VID_NESTLE_MASTER_1'::text)
26. 0.033 0.033 ↑ 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.029..0.033 rows=3 loops=1)

  • Index Cond: (level0top = 'VID_NESTLE_MASTER_1'::text)
27. 24.620 24.620 ↓ 56.0 56 20

Index Only Scan using run_log_run_id_date_state on run_log log_1 (cost=0.43..0.84 rows=1 width=14) (actual time=1.211..1.231 rows=56 loops=20)

  • Index Cond: ((date = r_1.date) AND (run_id = rim_1.run_id))
  • Heap Fetches: 0
28. 2,708.818 60,110.849 ↑ 7,791.8 148,805 1

GroupAggregate (cost=457,169,141.35..506,445,862.48 rows=1,159,452,262 width=118) (actual time=55,930.968..60,110.849 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
29.          

CTE stages

30. 0.030 0.030 ↑ 20.0 5 1

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

31.          

CTE run_dates

32. 428.073 428.073 ↑ 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=0.111..428.073 rows=51,849 loops=1)

33. 24,277.464 57,402.031 ↑ 1,214.4 954,786 1

Sort (cost=456,861,647.00..459,760,277.66 rows=1,159,452,262 width=118) (actual time=55,930.919..57,402.031 rows=954,786 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: 170777kB
34. 7,216.825 33,124.567 ↑ 1,214.4 954,786 1

Merge Left Join (cost=229,784,933.96..241,539,498.74 rows=1,159,452,262 width=118) (actual time=22,502.593..33,124.567 rows=954,786 loops=1)

  • Merge Cond: ((r.date = log.date) AND (rim.run_id = log.run_id) AND (s.stage = log.state))
35. 988.434 2,803.414 ↑ 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=2,524.135..2,803.414 rows=148,875 loops=1)

  • Sort Key: r.date, rim.run_id, s.stage
  • Sort Method: quicksort Memory: 27198kB
36. 493.308 1,814.980 ↑ 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=692.683..1,814.980 rows=148,875 loops=1)

  • Hash Cond: (r.run_id = rim.run_id)
37. 634.563 634.563 ↑ 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=0.117..634.563 rows=51,849 loops=1)

38. 231.094 687.109 ↑ 12.8 90,690 1

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

  • Buckets: 2097152 Batches: 1 Memory Usage: 27066kB
39. 144.830 456.015 ↑ 12.8 90,690 1

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

40. 0.045 0.045 ↑ 20.0 5 1

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

41. 95.876 311.140 ↓ 1.6 18,138 5

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

42. 38.104 215.264 ↓ 1.6 18,138 1

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

  • Merge Cond: (master.version = rim.blueprint_id)
43. 7.759 126.425 ↓ 2.4 5,696 1

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

  • Sort Key: master.version
  • Sort Method: quicksort Memory: 994kB
44. 41.091 118.666 ↓ 2.4 5,696 1

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

  • Merge Cond: (master.level0top = m.level0top)
45. 67.486 75.589 ↑ 1.6 6,399 1

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

  • Sort Key: master.level0top, master.version DESC
  • Sort Method: quicksort Memory: 1464kB
46. 3.193 8.103 ↑ 1.6 6,399 1

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

47. 0.049 0.049 ↑ 1.0 47 1

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

48. 4.861 4.861 ↑ 1.6 6,352 1

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

49. 1.938 1.986 ↓ 121.2 5,696 1

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

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

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

51. 32.503 50.735 ↑ 2.5 24,224 1

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

  • Sort Key: rim.blueprint_id
  • Sort Method: quicksort Memory: 3049kB
52. 18.232 18.232 ↑ 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.042..18.232 rows=29,195 loops=1)

53. 23,104.328 23,104.328 ↓ 1.0 5,952,141 1

Index Only Scan using run_log_run_id_date_state on run_log log (cost=0.43..113,796.69 rows=5,940,484 width=14) (actual time=10.547..23,104.328 rows=5,952,141 loops=1)

  • Heap Fetches: 87808
54. 0.004 514.761 ↑ 100.0 2 1

Hash (cost=4.00..4.00 rows=200 width=8) (actual time=514.761..514.761 rows=2 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
55. 514.757 514.757 ↑ 100.0 2 1

CTE Scan on latest_run_id_dates lrid (cost=0.00..4.00 rows=200 width=8) (actual time=514.753..514.757 rows=2 loops=1)

Planning time : 7.552 ms
Execution time : 60,826.241 ms