explain.depesz.com

PostgreSQL's explain analyze made readable

Result: uUXu : jobs_by_l0t(new query)

Settings
# exclusive inclusive rows x rows loops node
1. 0.056 212.756 ↑ 2.1 32 1

Hash Join (cost=1,883,513.63..1,886,504.39 rows=67 width=196) (actual time=212.265..212.756 rows=32 loops=1)

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

CTE latest_run_id_dates

3. 0.049 93.935 ↑ 6.2 32 1

HashAggregate (cost=926,028.64..926,030.64 rows=200 width=8) (actual time=93.918..93.935 rows=32 loops=1)

  • Group Key: r_1.date
4. 0.407 93.886 ↑ 210.0 64 1

HashAggregate (cost=925,692.66..925,827.05 rows=13,439 width=86) (actual time=93.843..93.886 rows=64 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. 2.289 93.479 ↑ 105.0 320 1

HashAggregate (cost=924,096.85..924,432.81 rows=33,596 width=118) (actual time=93.272..93.479 rows=320 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.008 0.008 ↑ 20.0 5 1

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

8.          

CTE run_dates

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

10. 2.131 91.190 ↑ 21.1 1,595 1

Nested Loop Left Join (cost=883.75..776,215.04 rows=33,596 width=118) (actual time=18.881..91.190 rows=1,595 loops=1)

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

Nested Loop (cost=883.19..731,731.79 rows=33,596 width=118) (actual time=18.795..85.539 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.009..0.015 rows=5 loops=1)

13. 0.100 85.385 ↑ 5.2 64 5

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

14. 0.714 85.285 ↑ 5.2 64 1

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

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

CTE Scan on run_dates r_1 (cost=0.00..729,875.00 rows=145,975 width=8) (actual time=0.658..71.515 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 13.056 ↓ 2.0 135 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 24kB
17. 0.058 12.987 ↓ 2.0 135 1

Nested Loop (cost=17.57..882.35 rows=67 width=82) (actual time=0.888..12.987 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.963 12.917 ↓ 2.0 135 1

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

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

21. 0.004 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.038 ↑ 3.5 4 1

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

23. 0.014 0.035 ↑ 3.5 4 1

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

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

Append (cost=0.14..16.81 rows=14 width=105) (actual time=0.006..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.014 0.014 ↑ 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.010..0.014 rows=3 loops=1)

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

Index Only Scan using run_log_pkey on run_log log_1 (cost=0.56..1.31 rows=1 width=14) (actual time=0.005..0.011 rows=25 loops=320)

  • Index Cond: ((date = r_1.date) AND (run_id = rim_1.run_id))
  • Heap Fetches: 0
28. 0.929 118.744 ↑ 210.0 64 1

GroupAggregate (cost=957,475.99..959,592.55 rows=13,439 width=160) (actual time=117.798..118.744 rows=64 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. 0.631 117.815 ↑ 105.0 320 1

Sort (cost=957,475.99..957,559.98 rows=33,596 width=160) (actual time=117.755..117.815 rows=320 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: 110kB
30. 0.131 117.184 ↑ 105.0 320 1

Subquery Scan on st (cost=952,514.53..954,950.24 rows=33,596 width=160) (actual time=100.241..117.184 rows=320 loops=1)

31. 16.669 117.053 ↑ 105.0 320 1

GroupAggregate (cost=952,514.53..954,614.28 rows=33,596 width=168) (actual time=100.239..117.053 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
32.          

CTE stages

33. 0.011 0.011 ↑ 20.0 5 1

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

34.          

CTE run_dates

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

36. 8.559 100.384 ↑ 21.1 1,595 1

Sort (cost=805,640.61..805,724.60 rows=33,596 width=168) (actual time=99.985..100.384 rows=1,595 loops=1)

  • Sort 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
  • Sort Method: quicksort Memory: 474kB
37. 2.563 91.825 ↑ 21.1 1,595 1

Nested Loop Left Join (cost=883.75..803,114.86 rows=33,596 width=168) (actual time=18.767..91.825 rows=1,595 loops=1)

  • Join Filter: (log.state = s.stage)
  • Rows Removed by Join Filter: 6380
38. 0.132 84.782 ↑ 105.0 320 1

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

39. 0.020 0.020 ↑ 20.0 5 1

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

40. 0.101 84.630 ↑ 5.2 64 5

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

41. 0.688 84.529 ↑ 5.2 64 1

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

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

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

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

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

  • Buckets: 1024 Batches: 1 Memory Usage: 24kB
44. 0.054 12.912 ↓ 2.0 135 1

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

45. 0.012 0.012 ↑ 1.0 1 1

Index Scan using master_idx on master m (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)
46. 4.896 12.846 ↓ 2.0 135 1

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

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

48. 0.004 0.044 ↑ 3.5 4 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
49. 0.002 0.040 ↑ 3.5 4 1

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

50. 0.015 0.038 ↑ 3.5 4 1

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

  • Sort Key: master.level0top, master.version DESC
  • Sort Method: quicksort Memory: 25kB
51. 0.003 0.023 ↑ 3.5 4 1

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

52. 0.005 0.005 ↑ 1.0 1 1

Index Scan using master_idx on master (cost=0.14..2.16 rows=1 width=86) (actual time=0.005..0.005 rows=1 loops=1)

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

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

Index Scan using run_log_pkey on run_log log (cost=0.56..2.11 rows=1 width=64) (actual time=0.005..0.014 rows=25 loops=320)

  • Index Cond: ((r.date = date) AND (run_id = rim.run_id))
55. 0.010 93.956 ↑ 6.2 32 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
56. 93.946 93.946 ↑ 6.2 32 1

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

Planning time : 4.855 ms
Execution time : 214.232 ms