explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 9wWL : query1

Settings
# exclusive inclusive rows x rows loops node
1. 0.159 41,692.269 ↑ 118.5 32 1

Hash Join (cost=3,700,460.43..3,869,222.72 rows=3,792 width=196) (actual time=41,690.847..41,692.269 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.126 10,557.538 ↑ 6.2 32 1

HashAggregate (cost=1,647,982.65..1,647,984.65 rows=200 width=8) (actual time=10,557.523..10,557.538 rows=32 loops=1)

  • Group Key: r_1.date
4. 4.773 10,557.412 ↑ 8,520.4 89 1

HashAggregate (cost=1,629,024.85..1,636,607.97 rows=758,312 width=86) (actual time=10,553.827..10,557.412 rows=89 loops=1)

  • Group Key: master_2.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. 5.308 10,552.639 ↑ 4,260.2 445 1

GroupAggregate (cost=1,477,362.53..1,557,933.13 rows=1,895,779 width=118) (actual time=10,529.643..10,552.639 rows=445 loops=1)

  • Group Key: master_2.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. 147.564 147.564 ↑ 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.063..147.564 rows=51,849 loops=1)

10. 78.482 10,547.331 ↑ 493.8 3,839 1

Sort (cost=1,330,488.60..1,335,228.05 rows=1,895,779 width=118) (actual time=10,529.614..10,547.331 rows=3,839 loops=1)

  • Sort Key: master_2.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
  • Sort Method: quicksort Memory: 636kB
11. 3,911.990 10,468.849 ↑ 493.8 3,839 1

Merge Left Join (cost=955,484.99..1,132,812.32 rows=1,895,779 width=118) (actual time=10,153.270..10,468.849 rows=3,839 loops=1)

  • Merge Cond: ((r_1.date = log_1.date) AND (rim_1.run_id = log_1.run_id) AND (s_1.stage = log_1.state))
12. 1.086 344.800 ↑ 4,260.2 445 1

Sort (cost=955,484.55..960,224.00 rows=1,895,779 width=118) (actual time=344.468..344.800 rows=445 loops=1)

  • Sort Key: r_1.date, rim_1.run_id, s_1.stage
  • Sort Method: quicksort Memory: 87kB
13. 0.341 343.714 ↑ 4,260.2 445 1

Nested Loop (cost=3,449.37..757,808.27 rows=1,895,779 width=118) (actual time=124.495..343.714 rows=445 loops=1)

14. 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)

15. 0.248 343.350 ↑ 213.0 89 5

Materialize (cost=3,449.37..734,156.16 rows=18,958 width=86) (actual time=24.896..68.670 rows=89 loops=5)

16. 1.461 343.102 ↑ 213.0 89 1

Hash Join (cost=3,449.37..734,061.37 rows=18,958 width=86) (actual time=124.472..343.102 rows=89 loops=1)

  • Hash Cond: (r_1.run_id = rim_1.run_id)
17. 218.317 218.317 ↑ 77.5 1,884 1

CTE Scan on run_dates r_1 (cost=0.00..729,875.00 rows=145,975 width=8) (actual time=0.356..218.317 rows=1,884 loops=1)

  • Filter: ((date >= '2019-05-01'::date) AND (date <= '2019-06-01'::date))
  • Rows Removed by Filter: 49965
18. 2.345 123.324 ↑ 1.1 3,582 1

Hash (cost=3,401.97..3,401.97 rows=3,792 width=82) (actual time=123.324..123.324 rows=3,582 loops=1)

  • Buckets: 4096 Batches: 1 Memory Usage: 408kB
19. 2.364 120.979 ↑ 1.1 3,582 1

Hash Join (cost=1,374.19..3,401.97 rows=3,792 width=82) (actual time=118.165..120.979 rows=3,582 loops=1)

  • Hash Cond: (master_2.version = rim_1.blueprint_id)
20. 0.366 19.751 ↑ 1.6 491 1

Nested Loop (cost=257.31..277.10 rows=790 width=65) (actual time=19.202..19.751 rows=491 loops=1)

21. 0.020 0.020 ↑ 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.017..0.020 rows=1 loops=1)

  • Index Cond: (level0top = 'MASTER_PG_NA_3'::text)
22. 0.855 19.365 ↑ 1.6 491 1

Sort (cost=257.17..259.14 rows=790 width=106) (actual time=19.181..19.365 rows=491 loops=1)

  • Sort Key: master_2.level0top, master_2.version DESC
  • Sort Method: quicksort Memory: 94kB
23. 0.243 18.510 ↑ 1.6 491 1

Append (cost=0.14..219.14 rows=790 width=106) (actual time=0.010..18.510 rows=491 loops=1)

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

  • Index Cond: (level0top = 'MASTER_PG_NA_3'::text)
25. 18.256 18.256 ↑ 1.6 490 1

Index Scan using master_version_history_idx on master master_3 (cost=0.29..209.09 rows=789 width=105) (actual time=0.025..18.256 rows=490 loops=1)

  • Index Cond: (level0top = 'MASTER_PG_NA_3'::text)
26. 34.025 98.864 ↑ 1.0 29,195 1

Hash (cost=751.95..751.95 rows=29,195 width=25) (actual time=98.864..98.864 rows=29,195 loops=1)

  • Buckets: 32768 Batches: 1 Memory Usage: 1776kB
27. 64.839 64.839 ↑ 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.041..64.839 rows=29,195 loops=1)

28. 6,212.059 6,212.059 ↑ 1.0 5,791,750 1

Index Only Scan using run_log_run_id_date_state on run_log log_1 (cost=0.43..113,808.59 rows=5,940,744 width=14) (actual time=0.047..6,212.059 rows=5,791,750 loops=1)

  • Heap Fetches: 32057
29. 2.775 31,134.499 ↑ 8,520.4 89 1

GroupAggregate (cost=2,052,468.79..2,171,902.87 rows=758,312 width=160) (actual time=31,131.617..31,134.499 rows=89 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
30. 1.443 31,131.724 ↑ 4,260.2 445 1

Sort (cost=2,052,468.79..2,057,208.23 rows=1,895,779 width=160) (actual time=31,131.527..31,131.724 rows=445 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: 147kB
31. 0.327 31,130.281 ↑ 4,260.2 445 1

Subquery Scan on st (cost=1,632,186.53..1,769,630.50 rows=1,895,779 width=160) (actual time=31,070.631..31,130.281 rows=445 loops=1)

32. 57.567 31,129.954 ↑ 4,260.2 445 1

GroupAggregate (cost=1,632,186.53..1,750,672.71 rows=1,895,779 width=168) (actual time=31,070.625..31,129.954 rows=445 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
33.          

CTE stages

34. 0.032 0.032 ↑ 20.0 5 1

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

35.          

CTE run_dates

36. 214.677 214.677 ↑ 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.080..214.677 rows=51,849 loops=1)

37. 45.699 31,072.387 ↑ 493.8 3,839 1

Sort (cost=1,485,312.60..1,490,052.05 rows=1,895,779 width=168) (actual time=31,070.517..31,072.387 rows=3,839 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: 1529kB
38. 5,700.636 31,026.688 ↑ 493.8 3,839 1

Merge Right Join (cost=955,484.99..1,198,770.32 rows=1,895,779 width=168) (actual time=30,267.921..31,026.688 rows=3,839 loops=1)

  • Merge Cond: ((log.date = r.date) AND (log.run_id = rim.run_id) AND (log.state = s.stage))
39. 24,810.419 24,810.419 ↑ 1.0 5,791,750 1

Index Scan using run_log_run_id_date_state on run_log log (cost=0.43..179,766.59 rows=5,940,744 width=64) (actual time=0.029..24,810.419 rows=5,791,750 loops=1)

40. 2.355 515.633 ↑ 495.5 3,826 1

Sort (cost=955,484.55..960,224.00 rows=1,895,779 width=118) (actual time=514.077..515.633 rows=3,826 loops=1)

  • Sort Key: r.date, rim.run_id, s.stage
  • Sort Method: quicksort Memory: 87kB
41. 39.451 513.278 ↑ 4,260.2 445 1

Nested Loop (cost=3,449.37..757,808.27 rows=1,895,779 width=118) (actual time=116.722..513.278 rows=445 loops=1)

42. 0.042 0.042 ↑ 20.0 5 1

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

43. 0.254 473.785 ↑ 213.0 89 5

Materialize (cost=3,449.37..734,156.16 rows=18,958 width=86) (actual time=23.337..94.757 rows=89 loops=5)

44. 1.408 473.531 ↑ 213.0 89 1

Hash Join (cost=3,449.37..734,061.37 rows=18,958 width=86) (actual time=116.681..473.531 rows=89 loops=1)

  • Hash Cond: (r.run_id = rim.run_id)
45. 356.868 356.868 ↑ 77.5 1,884 1

CTE Scan on run_dates r (cost=0.00..729,875.00 rows=145,975 width=8) (actual time=0.380..356.868 rows=1,884 loops=1)

  • Filter: ((date >= '2019-05-01'::date) AND (date <= '2019-06-01'::date))
  • Rows Removed by Filter: 49965
46. 2.868 115.255 ↑ 1.1 3,582 1

Hash (cost=3,401.97..3,401.97 rows=3,792 width=82) (actual time=115.255..115.255 rows=3,582 loops=1)

  • Buckets: 4096 Batches: 1 Memory Usage: 408kB
47. 2.685 112.387 ↑ 1.1 3,582 1

Hash Join (cost=1,374.19..3,401.97 rows=3,792 width=82) (actual time=109.292..112.387 rows=3,582 loops=1)

  • Hash Cond: (master.version = rim.blueprint_id)
48. 0.310 2.555 ↑ 1.6 491 1

Nested Loop (cost=257.31..277.10 rows=790 width=65) (actual time=2.028..2.555 rows=491 loops=1)

49. 0.033 0.033 ↑ 1.0 1 1

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

  • Index Cond: (level0top = 'MASTER_PG_NA_3'::text)
50. 0.865 2.212 ↑ 1.6 491 1

Sort (cost=257.17..259.14 rows=790 width=106) (actual time=1.993..2.212 rows=491 loops=1)

  • Sort Key: master.level0top, master.version DESC
  • Sort Method: quicksort Memory: 94kB
51. 0.275 1.347 ↑ 1.6 491 1

Append (cost=0.14..219.14 rows=790 width=106) (actual time=0.018..1.347 rows=491 loops=1)

52. 0.018 0.018 ↑ 1.0 1 1

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

  • Index Cond: (level0top = 'MASTER_PG_NA_3'::text)
53. 1.054 1.054 ↑ 1.6 490 1

Index Scan using master_version_history_idx on master master_1 (cost=0.29..209.09 rows=789 width=105) (actual time=0.036..1.054 rows=490 loops=1)

  • Index Cond: (level0top = 'MASTER_PG_NA_3'::text)
54. 38.512 107.147 ↑ 1.0 29,195 1

Hash (cost=751.95..751.95 rows=29,195 width=25) (actual time=107.147..107.147 rows=29,195 loops=1)

  • Buckets: 32768 Batches: 1 Memory Usage: 1776kB
55. 68.635 68.635 ↑ 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.019..68.635 rows=29,195 loops=1)

56. 0.033 10,557.611 ↑ 6.2 32 1

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

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

CTE Scan on latest_run_id_dates lrid (cost=0.00..4.00 rows=200 width=8) (actual time=10,557.532..10,557.578 rows=32 loops=1)

Planning time : 5.367 ms
Execution time : 41,707.964 ms