explain.depesz.com

PostgreSQL's explain analyze made readable

Result: u2qZ : stage_status2

Settings
# exclusive inclusive rows x rows loops node
1. 56.390 42,835.985 ↑ 27,979.1 41,440 1

Hash Join (cost=521,267,557.11..683,590,873.79 rows=1,159,452,262 width=264) (actual time=42,535.204..42,835.985 rows=41,440 loops=1)

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

CTE latest_run_id_dates

3. 0.037 95.232 ↑ 6.2 32 1

HashAggregate (cost=1,872,703.83..1,872,705.83 rows=200 width=8) (actual time=95.225..95.232 rows=32 loops=1)

  • Group Key: r_1.date
4. 0.416 95.195 ↑ 210.0 64 1

HashAggregate (cost=1,872,367.83..1,872,502.23 rows=13,440 width=86) (actual time=95.153..95.195 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.304 94.779 ↑ 105.0 320 1

HashAggregate (cost=1,870,771.87..1,871,107.86 rows=33,599 width=118) (actual time=94.582..94.779 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. 49.536 49.536 ↑ 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.139..49.536 rows=51,849 loops=1)

10. 1.725 92.475 ↑ 21.1 1,595 1

Nested Loop Left Join (cost=1,324.01..1,562,269.56 rows=33,599 width=118) (actual time=20.995..92.475 rows=1,595 loops=1)

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

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

12. 0.015 0.015 ↑ 20.0 5 1

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

13. 0.091 86.775 ↑ 5.2 64 5

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

14. 0.710 86.684 ↑ 5.2 64 1

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

  • Hash Cond: (r_1.run_id = rim_1.run_id)
15. 71.014 71.014 ↑ 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.874..71.014 rows=3,090 loops=1)

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

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

  • Buckets: 1024 Batches: 1 Memory Usage: 24kB
17. 0.056 14.891 ↓ 2.0 135 1

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

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

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

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

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

21. 0.006 0.098 ↑ 3.5 4 1

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

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

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

23. 0.029 0.091 ↑ 3.5 4 1

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

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

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

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

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

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

Index Only Scan using run_log_searcher on run_log log_1 (cost=0.43..0.84 rows=1 width=14) (actual time=0.006..0.012 rows=25 loops=320)

  • Index Cond: ((date = r_1.date) AND (run_id = rim_1.run_id))
  • Heap Fetches: 0
28. 3,365.868 42,684.334 ↑ 7,791.8 148,805 1

GroupAggregate (cost=519,394,844.78..626,644,179.02 rows=1,159,452,262 width=329) (actual time=38,679.245..42,684.334 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.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.018..0.019 rows=5 loops=1)

31.          

CTE run_dates

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

33. 13,189.084 39,318.466 ↑ 1,214.5 954,697 1

Sort (cost=519,087,350.44..521,985,981.09 rows=1,159,452,262 width=329) (actual time=38,679.100..39,318.466 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: 179784kB
34. 1,778.944 26,129.382 ↑ 1,214.5 954,697 1

Merge Left Join (cost=230,951,741.01..242,622,212.18 rows=1,159,452,262 width=329) (actual time=24,233.530..26,129.382 rows=954,697 loops=1)

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

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

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

38. 39.740 155.326 ↑ 12.8 90,690 1

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

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

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

40. 0.028 0.028 ↑ 20.0 5 1

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

41. 19.324 83.785 ↓ 1.6 18,138 5

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

42. 8.481 64.461 ↓ 1.6 18,138 1

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

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

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

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

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

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

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

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

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

47. 0.052 0.052 ↑ 1.0 47 1

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

48. 2.989 2.989 ↑ 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.989 rows=6,352 loops=1)

49. 1.012 1.031 ↓ 121.2 5,696 1

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

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

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

51. 15.204 23.118 ↑ 2.5 24,224 1

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

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

53. 1,720.774 23,800.345 ↓ 1.0 5,952,046 1

Materialize (cost=1,166,807.48..1,196,509.49 rows=5,940,402 width=225) (actual time=17,948.414..23,800.345 rows=5,952,046 loops=1)

54. 19,861.168 22,079.571 ↑ 1.0 5,940,402 1

Sort (cost=1,166,807.48..1,181,658.49 rows=5,940,402 width=225) (actual time=17,948.402..22,079.571 rows=5,940,402 loops=1)

  • Sort Key: log.date, log.run_id, log.state
  • Sort Method: external merge Disk: 415680kB
55. 2,218.403 2,218.403 ↑ 1.0 5,940,402 1

Seq Scan on run_log log (cost=0.00..127,173.02 rows=5,940,402 width=225) (actual time=0.012..2,218.403 rows=5,940,402 loops=1)

56. 0.012 95.261 ↑ 6.2 32 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
57. 95.249 95.249 ↑ 6.2 32 1

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

Planning time : 3.776 ms
Execution time : 42,984.604 ms