explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Kfss : queru2

Settings
# exclusive inclusive rows x rows loops node
1. 0.370 22,971.713 ↑ 59.2 160 1

Hash Join (cost=3,357,947.05..3,656,627.03 rows=9,479 width=264) (actual time=22,966.671..22,971.713 rows=160 loops=1)

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

CTE latest_run_id_dates

3. 0.129 5,999.741 ↑ 6.2 32 1

HashAggregate (cost=1,647,989.58..1,647,991.58 rows=200 width=8) (actual time=5,999.726..5,999.741 rows=32 loops=1)

  • Group Key: r_1.date
4. 4.749 5,999.612 ↑ 8,520.4 89 1

HashAggregate (cost=1,629,031.78..1,636,614.90 rows=758,312 width=86) (actual time=5,996.066..5,999.612 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. 8.848 5,994.863 ↑ 4,260.2 445 1

GroupAggregate (cost=1,477,369.46..1,557,940.07 rows=1,895,779 width=118) (actual time=5,984.422..5,994.863 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.020 0.020 ↑ 20.0 5 1

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

8.          

CTE run_dates

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

10. 26.746 5,986.015 ↑ 493.8 3,839 1

Sort (cost=1,330,495.54..1,335,234.98 rows=1,895,779 width=118) (actual time=5,984.393..5,986.015 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. 2,226.917 5,959.269 ↑ 493.8 3,839 1

Merge Left Join (cost=955,484.99..1,132,819.25 rows=1,895,779 width=118) (actual time=5,853.473..5,959.269 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.114 359.936 ↑ 4,260.2 445 1

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

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

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

14. 0.022 0.022 ↑ 20.0 5 1

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

15. 0.234 358.430 ↑ 213.0 89 5

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

16. 1.649 358.196 ↑ 213.0 89 1

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

  • Hash Cond: (r_1.run_id = rim_1.run_id)
17. 298.985 298.985 ↑ 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.328..298.985 rows=1,884 loops=1)

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

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

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

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

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

Nested Loop (cost=257.31..277.10 rows=790 width=65) (actual time=1.380..1.920 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.803 1.562 ↑ 1.6 491 1

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

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

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

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

  • Index Cond: (level0top = 'MASTER_PG_NA_3'::text)
25. 0.460 0.460 ↑ 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.024..0.460 rows=490 loops=1)

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

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

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

28. 3,372.416 3,372.416 ↑ 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,814.22 rows=5,940,919 width=14) (actual time=0.031..3,372.416 rows=5,791,750 loops=1)

  • Heap Fetches: 32057
29. 14.108 16,971.536 ↑ 4,260.2 445 1

GroupAggregate (cost=1,709,948.46..1,885,308.02 rows=1,895,779 width=333) (actual time=16,955.994..16,971.536 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
30.          

CTE stages

31. 0.030 0.030 ↑ 20.0 5 1

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

32.          

CTE run_dates

33. 147.543 147.543 ↑ 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.079..147.543 rows=51,849 loops=1)

34. 24.874 16,957.428 ↑ 493.8 3,839 1

Sort (cost=1,563,074.54..1,567,813.98 rows=1,895,779 width=333) (actual time=16,955.853..16,957.428 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: 1602kB
35. 3,962.625 16,932.554 ↑ 493.8 3,839 1

Merge Right Join (cost=955,484.99..1,198,776.25 rows=1,895,779 width=333) (actual time=16,779.828..16,932.554 rows=3,839 loops=1)

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

Index Scan using run_log_run_id_date_state on run_log log (cost=0.43..179,771.22 rows=5,940,919 width=229) (actual time=0.029..12,732.995 rows=5,791,750 loops=1)

37. 2.036 236.934 ↑ 495.5 3,826 1

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

  • Sort Key: r.date, rim.run_id, s.stage
  • Sort Method: quicksort Memory: 87kB
38. 0.274 234.898 ↑ 4,260.2 445 1

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

39. 0.039 0.039 ↑ 20.0 5 1

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

40. 0.213 234.585 ↑ 213.0 89 5

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

41. 1.466 234.372 ↑ 213.0 89 1

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

  • Hash Cond: (r.run_id = rim.run_id)
42. 192.619 192.619 ↑ 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.381..192.619 rows=1,884 loops=1)

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

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

  • Buckets: 4096 Batches: 1 Memory Usage: 408kB
44. 2.661 37.415 ↑ 1.1 3,582 1

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

  • Hash Cond: (master.version = rim.blueprint_id)
45. 0.334 2.003 ↑ 1.6 491 1

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

46. 0.032 0.032 ↑ 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.032 rows=1 loops=1)

  • Index Cond: (level0top = 'MASTER_PG_NA_3'::text)
47. 0.850 1.637 ↑ 1.6 491 1

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

  • Sort Key: master.level0top, master.version DESC
  • Sort Method: quicksort Memory: 94kB
48. 0.252 0.787 ↑ 1.6 491 1

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

49. 0.017 0.017 ↑ 1.0 1 1

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

  • Index Cond: (level0top = 'MASTER_PG_NA_3'::text)
50. 0.518 0.518 ↑ 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.038..0.518 rows=490 loops=1)

  • Index Cond: (level0top = 'MASTER_PG_NA_3'::text)
51. 17.257 32.751 ↑ 1.0 29,195 1

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

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

53. 0.029 5,999.807 ↑ 6.2 32 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
54. 5,999.778 5,999.778 ↑ 6.2 32 1

CTE Scan on latest_run_id_dates lrid (cost=0.00..4.00 rows=200 width=8) (actual time=5,999.734..5,999.778 rows=32 loops=1)

Planning time : 4.703 ms
Execution time : 22,978.616 ms