explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Tisl : stage_status5

Settings
# exclusive inclusive rows x rows loops node
1. 16.230 1,418.241 ↑ 16.8 10 1

Hash Join (cost=1,850,127.58..1,852,648.96 rows=168 width=8) (actual time=1,417.711..1,418.241 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.016 641.870 ↑ 100.0 2 1

HashAggregate (cost=926,025.18..926,027.18 rows=200 width=8) (actual time=641.869..641.870 rows=2 loops=1)

  • Group Key: r_1.date
4. 0.129 641.854 ↑ 3,359.8 4 1

HashAggregate (cost=925,689.21..925,823.60 rows=13,439 width=86) (actual time=641.823..641.854 rows=4 loops=1)

  • Group Key: cm_1.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. 3.333 641.725 ↑ 1,679.8 20 1

HashAggregate (cost=924,093.40..924,429.36 rows=33,596 width=118) (actual time=641.605..641.725 rows=20 loops=1)

  • Group Key: cm_1.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.018 0.018 ↑ 20.0 5 1

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

8.          

CTE run_dates

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

10. 0.580 638.392 ↑ 148.7 226 1

Nested Loop Left Join (cost=883.75..776,211.59 rows=33,596 width=118) (actual time=117.435..638.392 rows=226 loops=1)

  • Join Filter: (log_1.state = s_1.stage)
  • Rows Removed by Join Filter: 904
11. 0.029 636.872 ↑ 1,679.8 20 1

Nested Loop (cost=883.19..731,731.79 rows=33,596 width=118) (actual time=117.229..636.872 rows=20 loops=1)

12. 0.023 0.023 ↑ 20.0 5 1

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

13. 0.015 636.820 ↑ 84.0 4 5

Materialize (cost=883.19..731,310.63 rows=336 width=86) (actual time=23.442..127.364 rows=4 loops=5)

14. 0.191 636.805 ↑ 84.0 4 1

Hash Join (cost=883.19..731,308.95 rows=336 width=86) (actual time=117.208..636.805 rows=4 loops=1)

  • Hash Cond: (r_1.run_id = rim_1.run_id)
15. 540.981 540.981 ↑ 726.2 201 1

CTE Scan on run_dates r_1 (cost=0.00..729,875.00 rows=145,975 width=8) (actual time=2.350..540.981 rows=201 loops=1)

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

Hash (cost=882.35..882.35 rows=67 width=82) (actual time=95.633..95.633 rows=135 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 24kB
17. 0.119 95.442 ↓ 2.0 135 1

Nested Loop (cost=17.57..882.35 rows=67 width=82) (actual time=1.580..95.442 rows=135 loops=1)

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

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

Hash Join (cost=17.43..879.53 rows=67 width=47) (actual time=1.562..95.288 rows=135 loops=1)

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

21. 0.007 0.063 ↑ 3.5 4 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
22. 0.002 0.056 ↑ 3.5 4 1

Subquery Scan on cm_1 (cost=17.08..17.25 rows=14 width=30) (actual time=0.053..0.056 rows=4 loops=1)

23. 0.016 0.054 ↑ 3.5 4 1

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

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

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

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

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

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

Index Only Scan using run_log_pkey on run_log log_1 (cost=0.56..1.31 rows=1 width=14) (actual time=0.016..0.047 rows=56 loops=20)

  • Index Cond: ((date = r_1.date) AND (run_id = rim_1.run_id))
  • Heap Fetches: 0
28. 28.592 760.129 ↑ 105.0 320 1

HashAggregate (cost=924,093.40..924,429.36 rows=33,596 width=118) (actual time=759.739..760.129 rows=320 loops=1)

  • Group Key: cm.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.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.018..0.026 rows=5 loops=1)

31.          

CTE run_dates

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

33. 3.960 731.537 ↑ 21.1 1,595 1

Nested Loop Left Join (cost=883.75..776,211.59 rows=33,596 width=118) (actual time=122.094..731.537 rows=1,595 loops=1)

  • Join Filter: (log.state = s.stage)
  • Rows Removed by Join Filter: 6380
34. 0.280 508.057 ↑ 105.0 320 1

Nested Loop (cost=883.19..731,731.79 rows=33,596 width=118) (actual time=121.948..508.057 rows=320 loops=1)

35. 0.037 0.037 ↑ 20.0 5 1

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

36. 0.216 507.740 ↑ 5.2 64 5

Materialize (cost=883.19..731,310.63 rows=336 width=86) (actual time=24.385..101.548 rows=64 loops=5)

37. 1.664 507.524 ↑ 5.2 64 1

Hash Join (cost=883.19..731,308.95 rows=336 width=86) (actual time=121.922..507.524 rows=64 loops=1)

  • Hash Cond: (r.run_id = rim.run_id)
38. 403.838 403.838 ↑ 47.2 3,090 1

CTE Scan on run_dates r (cost=0.00..729,875.00 rows=145,975 width=8) (actual time=1.108..403.838 rows=3,090 loops=1)

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

Hash (cost=882.35..882.35 rows=67 width=82) (actual time=102.022..102.022 rows=135 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 24kB
40. 0.113 101.868 ↓ 2.0 135 1

Nested Loop (cost=17.57..882.35 rows=67 width=82) (actual time=1.695..101.868 rows=135 loops=1)

41. 0.024 0.024 ↑ 1.0 1 1

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

  • Index Cond: (level0top = 'VID_NESTLE_MASTER_1'::text)
42. 28.360 101.731 ↓ 2.0 135 1

Hash Join (cost=17.43..879.53 rows=67 width=47) (actual time=1.667..101.731 rows=135 loops=1)

  • Hash Cond: (rim.blueprint_id = cm.version)
43. 73.292 73.292 ↑ 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.017..73.292 rows=29,195 loops=1)

44. 0.005 0.079 ↑ 3.5 4 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
45. 0.003 0.074 ↑ 3.5 4 1

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

46. 0.026 0.071 ↑ 3.5 4 1

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

  • Sort Key: master.level0top, master.version DESC
  • Sort Method: quicksort Memory: 25kB
47. 0.002 0.045 ↑ 3.5 4 1

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

48. 0.014 0.014 ↑ 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.014 rows=1 loops=1)

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

Index Scan using master_version_history_idx on master master_1 (cost=0.29..14.51 rows=13 width=105) (actual time=0.023..0.029 rows=3 loops=1)

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

Index Only Scan using run_log_pkey on run_log log (cost=0.56..1.31 rows=1 width=14) (actual time=0.672..0.686 rows=25 loops=320)

  • Index Cond: ((date = r.date) AND (run_id = rim.run_id))
  • Heap Fetches: 0
51. 0.005 641.882 ↑ 100.0 2 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
52. 641.877 641.877 ↑ 100.0 2 1

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

Planning time : 17.906 ms
Execution time : 1,421.379 ms