explain.depesz.com

PostgreSQL's explain analyze made readable

Result: ujTt

Settings
# exclusive inclusive rows x rows loops node
1. 0.032 3,900.675 ↑ 34.2 4 1

GroupAggregate (cost=1,149,405.74..1,153,596.80 rows=137 width=146) (actual time=3,900.611..3,900.675 rows=4 loops=1)

  • Group Key: ms.mapping_scheme_name, lm.mapping_scheme_id, cs.progserv_id, w.workbasket_id, (CASE WHEN ((SubPlan 3) IS NOT NULL) THEN (SubPlan 4) ELSE (SubPlan 5) END)
2. 0.072 3,900.507 ↑ 6.0 23 1

Sort (cost=1,149,405.74..1,149,406.08 rows=137 width=125) (actual time=3,900.505..3,900.507 rows=23 loops=1)

  • Sort Key: ms.mapping_scheme_name, lm.mapping_scheme_id, cs.progserv_id, w.workbasket_id, (CASE WHEN ((SubPlan 3) IS NOT NULL) THEN (SubPlan 4) ELSE (SubPlan 5) END)
  • Sort Method: quicksort Memory: 28kB
3. 0.070 3,900.435 ↑ 6.0 23 1

Nested Loop Semi Join (cost=90.73..1,149,400.87 rows=137 width=125) (actual time=3,780.857..3,900.435 rows=23 loops=1)

4. 0.042 3,898.984 ↑ 11.9 23 1

Nested Loop Left Join (cost=76.64..1,143,204.66 rows=274 width=104) (actual time=3,780.640..3,898.984 rows=23 loops=1)

  • Join Filter: (((workload_t.w_start_date <= lm.start_dt) AND (lm.start_dt <= workload_t.w_end_date)) OR (lm.start_dt IS NULL))
  • Rows Removed by Join Filter: 24
5. 0.023 3,898.896 ↑ 11.9 23 1

Nested Loop (cost=76.35..1,143,106.69 rows=274 width=103) (actual time=3,780.618..3,898.896 rows=23 loops=1)

6. 118.121 3,898.804 ↑ 11.9 23 1

Nested Loop (cost=76.06..1,143,021.74 rows=274 width=90) (actual time=3,780.599..3,898.804 rows=23 loops=1)

7. 238.118 1,752.162 ↓ 3.4 184,411 1

Nested Loop (cost=75.77..401,814.48 rows=54,861 width=84) (actual time=1.771..1,752.162 rows=184,411 loops=1)

8. 123.617 736.000 ↓ 1.9 259,348 1

Hash Join (cost=75.33..336,380.62 rows=135,237 width=83) (actual time=1.750..736.000 rows=259,348 loops=1)

  • Hash Cond: (lm.mapping_scheme_id = ms.mapping_scheme_id)
9. 159.689 610.798 ↓ 1.6 264,422 1

Nested Loop (cost=0.43..335,869.85 rows=165,708 width=58) (actual time=0.146..610.798 rows=264,422 loops=1)

10. 0.022 0.022 ↑ 1.0 1 1

Seq Scan on workbasket w (cost=0.00..1.61 rows=1 width=19) (actual time=0.015..0.022 rows=1 loops=1)

  • Filter: ((workbasket_tag)::text = 'LINEAR'::text)
  • Rows Removed by Filter: 48
11. 451.087 451.087 ↑ 1.3 264,422 1

Index Scan using legacy_mappable_mapping_scheme_id_workbasket_id_legacy_mapp_idx on legacy_mappable lm (cost=0.43..332,337.94 rows=353,030 width=39) (actual time=0.127..451.087 rows=264,422 loops=1)

  • Index Cond: (workbasket_id = w.workbasket_id)
12. 0.707 1.585 ↑ 1.0 1,719 1

Hash (cost=53.38..53.38 rows=1,722 width=30) (actual time=1.585..1.585 rows=1,719 loops=1)

  • Buckets: 2,048 Batches: 1 Memory Usage: 120kB
13. 0.878 0.878 ↑ 1.0 1,719 1

Seq Scan on mapping_scheme ms (cost=0.00..53.38 rows=1,722 width=30) (actual time=0.016..0.878 rows=1,719 loops=1)

  • Filter: ((mapping_scheme_name)::text !~~ 'zzz_%'::text)
  • Rows Removed by Filter: 391
14. 778.044 778.044 ↑ 1.0 1 259,348

Index Scan using ix_dbo_legacy_mappable_x_progserv_2 on legacy_mappable_x_progserv lmxp (cost=0.44..0.47 rows=1 width=13) (actual time=0.003..0.003 rows=1 loops=259,348)

  • Index Cond: (legacy_mappable_id = lm.legacy_mappable_id)
15. 363.082 2,028.521 ↓ 0.0 0 184,411

Index Only Scan using "IX_DBO_PROGSERV_2" on progserv p (cost=0.29..13.51 rows=1 width=6) (actual time=0.011..0.011 rows=0 loops=184,411)

  • Index Cond: (progserv_id = lmxp.progserv_id)
  • Filter: (CASE WHEN ((SubPlan 6) IS NOT NULL) THEN (SubPlan 7) ELSE (SubPlan 8) END = '368'::numeric)
  • Rows Removed by Filter: 1
  • Heap Fetches: 0
16.          

SubPlan (for Index Only Scan)

17. 110.366 737.644 ↓ 0.0 0 184,411

Nested Loop (cost=0.29..3.53 rows=1 width=5) (actual time=0.004..0.004 rows=0 loops=184,411)

  • Join Filter: (ea_3.editor_assignment_type_id = eat_3.editor_assignment_type_id)
  • Rows Removed by Join Filter: 1
18. 368.822 368.822 ↑ 1.0 1 184,411

Index Scan using "IX_DBO_EDITOR_ASSIGNMENT_4" on editor_assignment ea_3 (cost=0.29..2.31 rows=1 width=10) (actual time=0.002..0.002 rows=1 loops=184,411)

  • Index Cond: (progserv_id = p.progserv_id)
19. 258.456 258.456 ↑ 1.0 1 129,228

Seq Scan on editor_assignment_type eat_3 (cost=0.00..1.21 rows=1 width=5) (actual time=0.001..0.002 rows=1 loops=129,228)

  • Filter: ((editor_assignment_type_tag)::text = 'APOLLO COVERAGE EDITOR'::text)
  • Rows Removed by Filter: 16
20. 1.330 12.915 ↑ 1.0 1 1,435

Nested Loop (cost=0.57..4.83 rows=1 width=5) (actual time=0.007..0.009 rows=1 loops=1,435)

  • Join Filter: (ea_4.editor_assignment_type_id = eat_4.editor_assignment_type_id)
  • Rows Removed by Join Filter: 1
21. 2.765 8.610 ↓ 2.0 2 1,435

Nested Loop (cost=0.57..3.61 rows=1 width=10) (actual time=0.004..0.006 rows=2 loops=1,435)

22. 2.870 2.870 ↓ 2.0 2 1,435

Index Scan using "IX_DBO_EDITOR_ASSIGNMENT_4" on editor_assignment ea_4 (cost=0.29..2.31 rows=1 width=10) (actual time=0.002..0.002 rows=2 loops=1,435)

  • Index Cond: (progserv_id = p.progserv_id)
23. 2.975 2.975 ↑ 1.0 1 2,975

Index Only Scan using "IX_DBO_EMPLOYEE_2" on employee e_3 (cost=0.28..1.30 rows=1 width=5) (actual time=0.001..0.001 rows=1 loops=2,975)

  • Index Cond: (employee_id = ea_4.employee_id)
  • Heap Fetches: 0
24. 2.975 2.975 ↑ 1.0 1 2,975

Seq Scan on editor_assignment_type eat_4 (cost=0.00..1.21 rows=1 width=5) (actual time=0.001..0.001 rows=1 loops=2,975)

  • Filter: ((editor_assignment_type_tag)::text = 'APOLLO COVERAGE EDITOR'::text)
  • Rows Removed by Filter: 11
25. 239.699 914.880 ↑ 1.0 1 182,976

Nested Loop (cost=0.57..4.83 rows=1 width=5) (actual time=0.004..0.005 rows=1 loops=182,976)

  • Join Filter: (ea_5.editor_assignment_type_id = eat_5.editor_assignment_type_id)
  • Rows Removed by Join Filter: 0
26. 56.723 548.928 ↑ 1.0 1 182,976

Nested Loop (cost=0.57..3.61 rows=1 width=10) (actual time=0.003..0.003 rows=1 loops=182,976)

27. 365.952 365.952 ↑ 1.0 1 182,976

Index Scan using "IX_DBO_EDITOR_ASSIGNMENT_4" on editor_assignment ea_5 (cost=0.29..2.31 rows=1 width=10) (actual time=0.002..0.002 rows=1 loops=182,976)

  • Index Cond: (progserv_id = p.progserv_id)
28. 126.253 126.253 ↑ 1.0 1 126,253

Index Only Scan using "IX_DBO_EMPLOYEE_2" on employee e_4 (cost=0.28..1.30 rows=1 width=5) (actual time=0.001..0.001 rows=1 loops=126,253)

  • Index Cond: (employee_id = ea_5.employee_id)
  • Heap Fetches: 0
29. 126.253 126.253 ↑ 1.0 1 126,253

Seq Scan on editor_assignment_type eat_5 (cost=0.00..1.21 rows=1 width=5) (actual time=0.001..0.001 rows=1 loops=126,253)

  • Filter: ((editor_assignment_type_tag)::text = 'APOLLO EDITOR'::text)
  • Rows Removed by Filter: 5
30. 0.069 0.069 ↑ 1.0 1 23

Index Scan using "IX_DBO_LEGACY_CALL_SIGN_1" on legacy_call_sign cs (cost=0.29..0.31 rows=1 width=13) (actual time=0.003..0.003 rows=1 loops=23)

  • Index Cond: (progserv_id = lmxp.progserv_id)
31. 0.046 0.046 ↑ 2.0 1 23

Index Scan using "IX_DBO_WORKLOAD_T_6" on workload_t (cost=0.29..0.33 rows=2 width=28) (actual time=0.002..0.002 rows=1 loops=23)

  • Index Cond: (w_profile_num = (lmxp.progserv_id)::integer)
32. 0.023 0.943 ↑ 1.0 1 23

Nested Loop (cost=14.08..15.00 rows=1 width=15) (actual time=0.041..0.041 rows=1 loops=23)

33. 0.023 0.874 ↑ 1.0 1 23

Nested Loop (cost=13.94..14.84 rows=1 width=28) (actual time=0.038..0.038 rows=1 loops=23)

34. 0.552 0.828 ↑ 1.0 1 23

Hash Join (cost=13.66..14.54 rows=1 width=23) (actual time=0.036..0.036 rows=1 loops=23)

  • Hash Cond: (er.role_id = wb.role_id)
35. 0.000 0.207 ↑ 1.0 12 23

Index Scan using "IX_DBO_EMPLOYEE_ROLE_2" on employee_role er (cost=13.49..14.31 rows=12 width=11) (actual time=0.004..0.009 rows=12 loops=23)

  • Index Cond: (employee_id = CASE WHEN ((SubPlan 9) IS NOT NULL) THEN (SubPlan 10) ELSE (SubPlan 11) END)
36.          

SubPlan (for Index Scan)

37. 0.046 0.138 ↑ 1.0 1 23

Nested Loop (cost=0.29..3.53 rows=1 width=5) (actual time=0.006..0.006 rows=1 loops=23)

  • Join Filter: (ea_6.editor_assignment_type_id = eat_6.editor_assignment_type_id)
  • Rows Removed by Join Filter: 1
38. 0.046 0.046 ↓ 2.0 2 23

Index Scan using "IX_DBO_EDITOR_ASSIGNMENT_4" on editor_assignment ea_6 (cost=0.29..2.31 rows=1 width=10) (actual time=0.002..0.002 rows=2 loops=23)

  • Index Cond: (progserv_id = p.progserv_id)
39. 0.046 0.046 ↑ 1.0 1 46

Seq Scan on editor_assignment_type eat_6 (cost=0.00..1.21 rows=1 width=5) (actual time=0.001..0.001 rows=1 loops=46)

  • Filter: ((editor_assignment_type_tag)::text = 'APOLLO COVERAGE EDITOR'::text)
  • Rows Removed by Filter: 11
40. 0.038 0.190 ↑ 1.0 1 19

Nested Loop (cost=0.57..4.83 rows=1 width=5) (actual time=0.009..0.010 rows=1 loops=19)

  • Join Filter: (ea_7.editor_assignment_type_id = eat_7.editor_assignment_type_id)
  • Rows Removed by Join Filter: 1
41. 0.038 0.114 ↓ 2.0 2 19

Nested Loop (cost=0.57..3.61 rows=1 width=10) (actual time=0.004..0.006 rows=2 loops=19)

42. 0.038 0.038 ↓ 2.0 2 19

Index Scan using "IX_DBO_EDITOR_ASSIGNMENT_4" on editor_assignment ea_7 (cost=0.29..2.31 rows=1 width=10) (actual time=0.002..0.002 rows=2 loops=19)

  • Index Cond: (progserv_id = p.progserv_id)
43. 0.038 0.038 ↑ 1.0 1 38

Index Only Scan using "IX_DBO_EMPLOYEE_2" on employee e_5 (cost=0.28..1.30 rows=1 width=5) (actual time=0.001..0.001 rows=1 loops=38)

  • Index Cond: (employee_id = ea_7.employee_id)
  • Heap Fetches: 0
44. 0.038 0.038 ↑ 1.0 1 38

Seq Scan on editor_assignment_type eat_7 (cost=0.00..1.21 rows=1 width=5) (actual time=0.001..0.001 rows=1 loops=38)

  • Filter: ((editor_assignment_type_tag)::text = 'APOLLO COVERAGE EDITOR'::text)
  • Rows Removed by Filter: 10
45. 0.008 0.052 ↑ 1.0 1 4

Nested Loop (cost=0.57..4.83 rows=1 width=5) (actual time=0.013..0.013 rows=1 loops=4)

  • Join Filter: (ea_8.editor_assignment_type_id = eat_8.editor_assignment_type_id)
  • Rows Removed by Join Filter: 1
46. 0.008 0.036 ↓ 2.0 2 4

Nested Loop (cost=0.57..3.61 rows=1 width=10) (actual time=0.007..0.009 rows=2 loops=4)

47. 0.012 0.012 ↓ 2.0 2 4

Index Scan using "IX_DBO_EDITOR_ASSIGNMENT_4" on editor_assignment ea_8 (cost=0.29..2.31 rows=1 width=10) (actual time=0.002..0.003 rows=2 loops=4)

  • Index Cond: (progserv_id = p.progserv_id)
48. 0.016 0.016 ↑ 1.0 1 8

Index Only Scan using "IX_DBO_EMPLOYEE_2" on employee e_6 (cost=0.28..1.30 rows=1 width=5) (actual time=0.002..0.002 rows=1 loops=8)

  • Index Cond: (employee_id = ea_8.employee_id)
  • Heap Fetches: 0
49. 0.008 0.008 ↑ 1.0 1 8

Seq Scan on editor_assignment_type eat_8 (cost=0.00..1.21 rows=1 width=5) (actual time=0.001..0.001 rows=1 loops=8)

  • Filter: ((editor_assignment_type_tag)::text = 'APOLLO EDITOR'::text)
  • Rows Removed by Filter: 10
50. 0.023 0.069 ↑ 1.0 1 23

Hash (cost=0.16..0.16 rows=1 width=12) (actual time=0.003..0.003 rows=1 loops=23)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
51. 0.046 0.046 ↑ 1.0 1 23

Index Scan using "IX_DBO_WORKBASKET_1" on workbasket wb (cost=0.14..0.16 rows=1 width=12) (actual time=0.002..0.002 rows=1 loops=23)

  • Index Cond: (workbasket_id = lm.workbasket_id)
52. 0.023 0.023 ↑ 1.0 1 23

Index Only Scan using "IX_DBO_EMPLOYEE_2" on employee e (cost=0.28..0.30 rows=1 width=5) (actual time=0.001..0.001 rows=1 loops=23)

  • Index Cond: (employee_id = er.employee_id)
  • Heap Fetches: 0
53. 0.046 0.046 ↑ 1.0 1 23

Index Scan using "IX_DBO_SECURITY_ROLE_1" on security_role sr (cost=0.14..0.16 rows=1 width=6) (actual time=0.002..0.002 rows=1 loops=23)

  • Index Cond: (role_id = er.role_id)
  • Filter: ((role_tag)::text ~~ '%WORKBASKET'::text)
54.          

SubPlan (for Nested Loop Semi Join)

55. 0.046 0.138 ↑ 1.0 1 23

Nested Loop (cost=0.29..3.53 rows=1 width=5) (actual time=0.006..0.006 rows=1 loops=23)

  • Join Filter: (ea.editor_assignment_type_id = eat.editor_assignment_type_id)
  • Rows Removed by Join Filter: 1
56. 0.046 0.046 ↓ 2.0 2 23

Index Scan using "IX_DBO_EDITOR_ASSIGNMENT_4" on editor_assignment ea (cost=0.29..2.31 rows=1 width=10) (actual time=0.002..0.002 rows=2 loops=23)

  • Index Cond: (progserv_id = p.progserv_id)
57. 0.046 0.046 ↑ 1.0 1 46

Seq Scan on editor_assignment_type eat (cost=0.00..1.21 rows=1 width=5) (actual time=0.001..0.001 rows=1 loops=46)

  • Filter: ((editor_assignment_type_tag)::text = 'APOLLO COVERAGE EDITOR'::text)
  • Rows Removed by Filter: 11
58. 0.057 0.228 ↑ 1.0 1 19

Nested Loop (cost=0.57..5.84 rows=1 width=32) (actual time=0.012..0.012 rows=1 loops=19)

  • Join Filter: (ea_1.editor_assignment_type_id = eat_1.editor_assignment_type_id)
  • Rows Removed by Join Filter: 1
59. 0.019 0.133 ↓ 2.0 2 19

Nested Loop (cost=0.57..4.61 rows=1 width=18) (actual time=0.005..0.007 rows=2 loops=19)

60. 0.038 0.038 ↓ 2.0 2 19

Index Scan using "IX_DBO_EDITOR_ASSIGNMENT_4" on editor_assignment ea_1 (cost=0.29..2.31 rows=1 width=10) (actual time=0.002..0.002 rows=2 loops=19)

  • Index Cond: (progserv_id = p.progserv_id)
61. 0.076 0.076 ↑ 1.0 1 38

Index Scan using "IX_DBO_EMPLOYEE_2" on employee e_1 (cost=0.28..2.30 rows=1 width=18) (actual time=0.002..0.002 rows=1 loops=38)

  • Index Cond: (employee_id = ea_1.employee_id)
62. 0.038 0.038 ↑ 1.0 1 38

Seq Scan on editor_assignment_type eat_1 (cost=0.00..1.21 rows=1 width=5) (actual time=0.001..0.001 rows=1 loops=38)

  • Filter: ((editor_assignment_type_tag)::text = 'APOLLO COVERAGE EDITOR'::text)
  • Rows Removed by Filter: 10
63. 0.028 0.072 ↑ 1.0 1 4

Nested Loop (cost=0.57..5.84 rows=1 width=32) (actual time=0.017..0.018 rows=1 loops=4)

  • Join Filter: (ea_2.editor_assignment_type_id = eat_2.editor_assignment_type_id)
  • Rows Removed by Join Filter: 1
64. 0.008 0.036 ↓ 2.0 2 4

Nested Loop (cost=0.57..4.61 rows=1 width=18) (actual time=0.007..0.009 rows=2 loops=4)

65. 0.012 0.012 ↓ 2.0 2 4

Index Scan using "IX_DBO_EDITOR_ASSIGNMENT_4" on editor_assignment ea_2 (cost=0.29..2.31 rows=1 width=10) (actual time=0.002..0.003 rows=2 loops=4)

  • Index Cond: (progserv_id = p.progserv_id)
66. 0.016 0.016 ↑ 1.0 1 8

Index Scan using "IX_DBO_EMPLOYEE_2" on employee e_2 (cost=0.28..2.30 rows=1 width=18) (actual time=0.002..0.002 rows=1 loops=8)

  • Index Cond: (employee_id = ea_2.employee_id)
67. 0.008 0.008 ↑ 1.0 1 8

Seq Scan on editor_assignment_type eat_2 (cost=0.00..1.21 rows=1 width=5) (actual time=0.001..0.001 rows=1 loops=8)

  • Filter: ((editor_assignment_type_tag)::text = 'APOLLO EDITOR'::text)
  • Rows Removed by Filter: 10
68.          

SubPlan (for GroupAggregate)

69. 0.008 0.136 ↑ 1.0 1 4

Result (cost=15.33..15.34 rows=1 width=4) (actual time=0.034..0.034 rows=1 loops=4)

70.          

Initplan (for Result)

71. 0.008 0.128 ↓ 0.0 0 4

Nested Loop (cost=0.28..15.33 rows=1 width=0) (actual time=0.032..0.032 rows=0 loops=4)

72. 0.008 0.008 ↑ 1.0 7 4

Seq Scan on relay_application ra (cost=0.00..1.07 rows=7 width=9) (actual time=0.002..0.002 rows=7 loops=4)

73. 0.112 0.112 ↓ 0.0 0 28

Index Scan using "IX_DBO_RELAY_RUNNING_APPLICATION_1" on relay_running_application rra (cost=0.28..2.03 rows=1 width=13) (actual time=0.004..0.004 rows=0 loops=28)

  • Index Cond: ((application_id = ra.application_id) AND (mapping_scheme_id = lm.mapping_scheme_id))
  • Filter: ((last_commit_dt + (concat(ra.buffer, ' seconds'))::interval) > now())
  • Rows Removed by Filter: 0
Planning time : 17.373 ms
Execution time : 3,902.286 ms