explain.depesz.com

PostgreSQL's explain analyze made readable

Result: KWva

Settings
# exclusive inclusive rows x rows loops node
1. 0.106 334.144 ↑ 235.0 32 1

Hash Join (cost=5,899,084.51..6,156,719.71 rows=7,520 width=196) (actual time=331.621..334.144 rows=32 loops=1)

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

CTE latest_run_id_dates

3. 0.037 88.386 ↑ 6.2 32 1

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

  • Group Key: r_1.date
4. 0.403 88.349 ↑ 210.0 64 1

HashAggregate (cost=1,882,259.83..1,882,394.23 rows=13,440 width=86) (actual time=88.314..88.349 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.199 87.946 ↑ 105.0 320 1

HashAggregate (cost=1,880,663.87..1,880,999.86 rows=33,599 width=118) (actual time=87.751..87.946 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.011 0.011 ↑ 20.0 5 1

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

8.          

CTE run_dates

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

10. 2.113 85.747 ↑ 21.1 1,595 1

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

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

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

12. 0.014 0.014 ↑ 20.0 5 1

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

13. 0.090 80.285 ↑ 5.2 64 5

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

14. 0.668 80.195 ↑ 5.2 64 1

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

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

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

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

  • Buckets: 1024 Batches: 1 Memory Usage: 24kB
17. 0.053 12.823 ↓ 2.0 135 1

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

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

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

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

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

21. 0.002 0.042 ↑ 3.5 4 1

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

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

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

23. 0.015 0.037 ↑ 3.5 4 1

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

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

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

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

  • Index Cond: (level0top = 'VID_NESTLE_MASTER_1'::text)
26. 0.012 0.012 ↑ 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.012 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. 3.571 245.620 ↑ 5,806.9 259 1

GroupAggregate (cost=4,016,482.17..4,253,362.17 rows=1,504,000 width=160) (actual time=241.816..245.620 rows=259 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
29. 2.261 242.049 ↑ 2,903.5 1,295 1

Sort (cost=4,016,482.17..4,025,882.17 rows=3,760,000 width=160) (actual time=241.778..242.049 rows=1,295 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: 392kB
30. 0.547 239.788 ↑ 2,903.5 1,295 1

Subquery Scan on st (cost=3,072,662.16..3,436,938.90 rows=3,760,000 width=160) (actual time=171.387..239.788 rows=1,295 loops=1)

31. 65.504 239.241 ↑ 2,903.5 1,295 1

GroupAggregate (cost=3,072,662.16..3,399,338.90 rows=3,760,000 width=164) (actual time=171.385..239.241 rows=1,295 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
32.          

CTE stages

33. 0.013 0.013 ↑ 20.0 5 1

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

34.          

CTE run_dates

35. 46.741 46.741 ↑ 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.038..46.741 rows=51,849 loops=1)

36. 44.813 173.737 ↑ 875.7 6,620 1

Sort (cost=2,765,167.81..2,779,660.96 rows=5,797,261 width=164) (actual time=171.310..173.737 rows=6,620 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: 1956kB
37. 35.558 128.924 ↑ 875.7 6,620 1

Hash Right Join (cost=1,658,463.90..1,842,186.24 rows=5,797,261 width=164) (actual time=91.925..128.924 rows=6,620 loops=1)

  • Hash Cond: ((log.run_id = rim.run_id) AND (log.date = r.date) AND (log.state = s.stage))
38. 3.444 3.444 ↓ 17.6 6,614 1

Index Scan using run_log_run_id_time_idx on run_log log (cost=0.43..247.69 rows=375 width=60) (actual time=0.021..3.444 rows=6,614 loops=1)

  • Index Cond: (run_id = 37769)
39. 0.789 89.922 ↑ 4,476.6 1,295 1

Hash (cost=1,455,106.40..1,455,106.40 rows=5,797,261 width=118) (actual time=89.922..89.922 rows=1,295 loops=1)

  • Buckets: 2097152 Batches: 4 Memory Usage: 16425kB
40. 0.477 89.133 ↑ 4,476.6 1,295 1

Nested Loop (cost=982.21..1,455,106.40 rows=5,797,261 width=118) (actual time=75.157..89.133 rows=1,295 loops=1)

41. 0.016 0.016 ↑ 20.0 5 1

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

42. 0.281 88.640 ↑ 223.8 259 5

Materialize (cost=982.21..1,382,783.09 rows=57,973 width=86) (actual time=15.028..17.728 rows=259 loops=5)

43. 0.092 88.359 ↑ 223.8 259 1

Nested Loop Left Join (cost=982.21..1,382,493.22 rows=57,973 width=86) (actual time=75.140..88.359 rows=259 loops=1)

  • Join Filter: (rim.run_id = r.run_id)
44. 0.005 22.437 ↑ 1.0 1 1

Nested Loop (cost=982.21..1,149.28 rows=1 width=82) (actual time=22.427..22.437 rows=1 loops=1)

45. 1.273 22.421 ↑ 1.0 1 1

Hash Join (cost=982.07..1,149.12 rows=1 width=47) (actual time=22.414..22.421 rows=1 loops=1)

  • Hash Cond: (master.version = rim.blueprint_id)
46. 17.463 21.130 ↑ 1.6 6,399 1

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

  • Sort Key: master.level0top, master.version DESC
  • Sort Method: quicksort Memory: 1464kB
47. 1.569 3.667 ↑ 1.6 6,399 1

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

48. 0.027 0.027 ↑ 1.0 47 1

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

49. 2.071 2.071 ↑ 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.071 rows=6,352 loops=1)

50. 0.003 0.018 ↑ 1.0 1 1

Hash (cost=2.31..2.31 rows=1 width=25) (actual time=0.018..0.018 rows=1 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
51. 0.015 0.015 ↑ 1.0 1 1

Index Scan using run_id_mapping_run_id_blueprint_id_idx on run_id_mapping rim (cost=0.29..2.31 rows=1 width=25) (actual time=0.015..0.015 rows=1 loops=1)

  • Index Cond: (run_id = 37769)
52. 0.011 0.011 ↑ 1.0 1 1

Index Scan using master_idx on master m (cost=0.14..0.16 rows=1 width=56) (actual time=0.009..0.011 rows=1 loops=1)

  • Index Cond: (level0top = master.level0top)
53. 65.830 65.830 ↑ 1,181.9 259 1

CTE Scan on run_dates r (cost=0.00..1,377,517.50 rows=306,115 width=8) (actual time=52.708..65.830 rows=259 loops=1)

  • Filter: (run_id = 37769)
  • Rows Removed by Filter: 51590
54. 0.012 88.418 ↓ 32.0 32 1

Hash (cost=4.50..4.50 rows=1 width=8) (actual time=88.418..88.418 rows=32 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
55. 88.406 88.406 ↓ 32.0 32 1

CTE Scan on latest_run_id_dates lrid (cost=0.00..4.50 rows=1 width=8) (actual time=88.386..88.406 rows=32 loops=1)

  • Filter: (run_id = 37769)
Planning time : 2.203 ms
Execution time : 335.645 ms