explain.depesz.com

PostgreSQL's explain analyze made readable

Result: mEoI

Settings
# exclusive inclusive rows x rows loops node
1. 0.045 2,273.968 ↓ 4.0 4 1

GroupAggregate (cost=165,733.54..165,764.13 rows=1 width=146) (actual time=2,273.870..2,273.968 rows=4 loops=1)

  • Group Key: ms.mapping_scheme_name, lm.mapping_scheme_id, tms.progserv_id, tms.call_sign_text, w.workbasket_id, (CASE WHEN ((SubPlan 3) IS NOT NULL) THEN (SubPlan 4) ELSE (SubPlan 5) END)
2. 0.063 2,273.775 ↓ 23.0 23 1

Sort (cost=165,733.54..165,733.54 rows=1 width=125) (actual time=2,273.773..2,273.775 rows=23 loops=1)

  • Sort Key: ms.mapping_scheme_name, lm.mapping_scheme_id, tms.progserv_id, tms.call_sign_text, w.workbasket_id, (CASE WHEN ((SubPlan 3) IS NOT NULL) THEN (SubPlan 4) ELSE (SubPlan 5) END)
  • Sort Method: quicksort Memory: 28kB
3. 0.060 2,273.712 ↓ 23.0 23 1

Nested Loop Semi Join (cost=15.53..165,733.53 rows=1 width=125) (actual time=2,264.653..2,273.712 rows=23 loops=1)

4. 0.047 2,271.857 ↓ 11.5 23 1

Nested Loop Left Join (cost=1.45..165,688.30 rows=2 width=104) (actual time=2,264.403..2,271.857 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. 7.295 2,271.741 ↓ 11.5 23 1

Nested Loop (cost=1.16..165,687.58 rows=2 width=103) (actual time=2,264.383..2,271.741 rows=23 loops=1)

  • Join Filter: (lm.mapping_scheme_id = ms.mapping_scheme_id)
  • Rows Removed by Join Filter: 39,514
6. 0.523 0.523 ↑ 1.0 1,719 1

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

  • Filter: ((mapping_scheme_name)::text !~~ 'zzz_%'::text)
  • Rows Removed by Filter: 391
7. 1.938 2,263.923 ↓ 11.5 23 1,719

Materialize (cost=1.16..165,582.55 rows=2 width=78) (actual time=0.002..1.317 rows=23 loops=1,719)

8. 0.017 2,261.985 ↓ 11.5 23 1

Nested Loop (cost=1.16..165,582.54 rows=2 width=78) (actual time=2.521..2,261.985 rows=23 loops=1)

  • Join Filter: (lm.workbasket_id = w.workbasket_id)
9. 0.016 0.016 ↑ 1.0 1 1

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

  • Filter: ((workbasket_tag)::text = 'LINEAR'::text)
  • Rows Removed by Filter: 48
10. 0.000 2,261.952 ↑ 4.0 23 1

Nested Loop (cost=1.16..165,579.77 rows=93 width=59) (actual time=2.511..2,261.952 rows=23 loops=1)

11. 4.932 2,183.501 ↓ 50.9 26,871 1

Nested Loop (cost=0.73..165,283.46 rows=528 width=32) (actual time=0.139..2,183.501 rows=26,871 loops=1)

12. 72.413 2,163.206 ↓ 189.7 569 1

Nested Loop (cost=0.29..164,354.89 rows=3 width=19) (actual time=0.121..2,163.206 rows=569 loops=1)

13. 117.088 649.653 ↓ 180.3 96,076 1

Seq Scan on tms_call_sign tms (cost=0.00..156,888.54 rows=533 width=13) (actual time=0.039..649.653 rows=96,076 loops=1)

  • Filter: (call_sign_eff_dt = (SubPlan 7))
  • Rows Removed by Filter: 10,437
14.          

SubPlan (for Seq Scan)

15. 0.000 532.565 ↑ 1.0 1 106,513

Result (cost=1.44..1.45 rows=1 width=8) (actual time=0.005..0.005 rows=1 loops=106,513)

16.          

Initplan (for Result)

17. 106.513 532.565 ↑ 1.0 1 106,513

Limit (cost=0.42..1.44 rows=1 width=8) (actual time=0.004..0.005 rows=1 loops=106,513)

18. 426.052 426.052 ↑ 1.0 1 106,513

Index Only Scan Backward using "IX_DBO_TMS_CALL_SIGN_3" on tms_call_sign tms0 (cost=0.42..1.44 rows=1 width=8) (actual time=0.004..0.004 rows=1 loops=106,513)

  • Index Cond: ((progserv_id = tms.progserv_id) AND (call_sign_eff_dt IS NOT NULL) AND (call_sign_eff_dt <= now()))
  • Heap Fetches: 0
19. 370.345 1,441.140 ↓ 0.0 0 96,076

Index Only Scan using "IX_DBO_PROGSERV_2" on progserv p (cost=0.29..14.01 rows=1 width=6) (actual time=0.015..0.015 rows=0 loops=96,076)

  • Index Cond: (progserv_id = tms.progserv_id)
  • Filter: (CASE WHEN ((SubPlan 8) IS NOT NULL) THEN (SubPlan 9) ELSE (SubPlan 10) END = '368'::numeric)
  • Rows Removed by Filter: 1
  • Heap Fetches: 0
20.          

SubPlan (for Index Only Scan)

21. 72.710 480.380 ↓ 0.0 0 96,076

Nested Loop (cost=0.29..3.53 rows=1 width=5) (actual time=0.005..0.005 rows=0 loops=96,076)

  • Join Filter: (ea_3.editor_assignment_type_id = eat_3.editor_assignment_type_id)
  • Rows Removed by Join Filter: 1
22. 288.228 288.228 ↑ 1.0 1 96,076

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.003..0.003 rows=1 loops=96,076)

  • Index Cond: (progserv_id = p.progserv_id)
23. 119.442 119.442 ↑ 1.0 1 59,721

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=59,721)

  • Filter: ((editor_assignment_type_tag)::text = 'APOLLO COVERAGE EDITOR'::text)
  • Rows Removed by Filter: 16
24. 1.664 21.573 ↑ 1.0 1 1,269

Nested Loop (cost=0.57..4.83 rows=1 width=5) (actual time=0.014..0.017 rows=1 loops=1,269)

  • Join Filter: (ea_4.editor_assignment_type_id = eat_4.editor_assignment_type_id)
  • Rows Removed by Join Filter: 1
25. 2.933 13.959 ↓ 2.0 2 1,269

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

26. 5.076 5.076 ↓ 2.0 2 1,269

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.003..0.004 rows=2 loops=1,269)

  • Index Cond: (progserv_id = p.progserv_id)
27. 5.950 5.950 ↑ 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.002..0.002 rows=1 loops=2,975)

  • Index Cond: (employee_id = ea_4.employee_id)
  • Heap Fetches: 0
28. 5.950 5.950 ↑ 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.002 rows=1 loops=2,975)

  • Filter: ((editor_assignment_type_tag)::text = 'APOLLO COVERAGE EDITOR'::text)
  • Rows Removed by Filter: 11
29. 0.000 568.842 ↓ 0.0 0 94,807

Nested Loop (cost=0.57..4.83 rows=1 width=5) (actual time=0.006..0.006 rows=0 loops=94,807)

  • Join Filter: (ea_5.editor_assignment_type_id = eat_5.editor_assignment_type_id)
  • Rows Removed by Join Filter: 0
30. 76.122 474.035 ↑ 1.0 1 94,807

Nested Loop (cost=0.57..3.61 rows=1 width=10) (actual time=0.004..0.005 rows=1 loops=94,807)

31. 284.421 284.421 ↑ 1.0 1 94,807

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.003 rows=1 loops=94,807)

  • Index Cond: (progserv_id = p.progserv_id)
32. 113.492 113.492 ↑ 1.0 1 56,746

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

  • Index Cond: (employee_id = ea_5.employee_id)
  • Heap Fetches: 0
33. 113.492 113.492 ↑ 1.0 1 56,746

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

  • Filter: ((editor_assignment_type_tag)::text = 'APOLLO EDITOR'::text)
  • Rows Removed by Filter: 8
34. 15.363 15.363 ↑ 88.7 47 569

Index Scan using ix_dbo_legacy_mappable_x_progserv_3 on legacy_mappable_x_progserv lmxp (cost=0.44..267.85 rows=4,167 width=13) (actual time=0.006..0.027 rows=47 loops=569)

  • Index Cond: (progserv_id = tms.progserv_id)
35. 80.613 80.613 ↓ 0.0 0 26,871

Index Scan using legacy_mappable_mapping_scheme_id_workbasket_id_legacy_mapp_idx on legacy_mappable lm (cost=0.43..0.56 rows=1 width=39) (actual time=0.003..0.003 rows=0 loops=26,871)

  • Index Cond: (legacy_mappable_id = lmxp.legacy_mappable_id)
36. 0.069 0.069 ↑ 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.003..0.003 rows=1 loops=23)

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

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

38. 0.023 1.127 ↑ 1.0 1 23

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

39. 0.713 1.058 ↑ 1.0 1 23

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

  • Hash Cond: (er.role_id = wb.role_id)
40. 0.000 0.253 ↑ 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.011 rows=12 loops=23)

  • Index Cond: (employee_id = CASE WHEN ((SubPlan 11) IS NOT NULL) THEN (SubPlan 12) ELSE (SubPlan 13) END)
41.          

SubPlan (for Index Scan)

42. 0.046 0.207 ↑ 1.0 1 23

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

  • Join Filter: (ea_6.editor_assignment_type_id = eat_6.editor_assignment_type_id)
  • Rows Removed by Join Filter: 1
43. 0.069 0.069 ↓ 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.003..0.003 rows=2 loops=23)

  • Index Cond: (progserv_id = p.progserv_id)
44. 0.092 0.092 ↑ 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.002 rows=1 loops=46)

  • Filter: ((editor_assignment_type_tag)::text = 'APOLLO COVERAGE EDITOR'::text)
  • Rows Removed by Filter: 11
45. 0.019 0.266 ↑ 1.0 1 19

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

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

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

47. 0.057 0.057 ↓ 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.003 rows=2 loops=19)

  • Index Cond: (progserv_id = p.progserv_id)
48. 0.076 0.076 ↑ 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.002..0.002 rows=1 loops=38)

  • Index Cond: (employee_id = ea_7.employee_id)
  • Heap Fetches: 0
49. 0.076 0.076 ↑ 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.002 rows=1 loops=38)

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

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

  • Join Filter: (ea_8.editor_assignment_type_id = eat_8.editor_assignment_type_id)
  • Rows Removed by Join Filter: 1
51. 0.016 0.044 ↓ 2.0 2 4

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

52. 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.003..0.003 rows=2 loops=4)

  • Index Cond: (progserv_id = p.progserv_id)
53. 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
54. 0.016 0.016 ↑ 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.002 rows=1 loops=8)

  • Filter: ((editor_assignment_type_tag)::text = 'APOLLO EDITOR'::text)
  • Rows Removed by Filter: 10
55. 0.046 0.092 ↑ 1.0 1 23

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
56. 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)
57. 0.046 0.046 ↑ 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.002..0.002 rows=1 loops=23)

  • Index Cond: (employee_id = er.employee_id)
  • Heap Fetches: 0
58. 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)
59.          

SubPlan (for Nested Loop Semi Join)

60. 0.046 0.207 ↑ 1.0 1 23

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

  • Join Filter: (ea.editor_assignment_type_id = eat.editor_assignment_type_id)
  • Rows Removed by Join Filter: 1
61. 0.069 0.069 ↓ 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.003 rows=2 loops=23)

  • Index Cond: (progserv_id = p.progserv_id)
62. 0.092 0.092 ↑ 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.002 rows=1 loops=46)

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

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

  • Join Filter: (ea_1.editor_assignment_type_id = eat_1.editor_assignment_type_id)
  • Rows Removed by Join Filter: 1
64. 0.057 0.190 ↓ 2.0 2 19

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

65. 0.057 0.057 ↓ 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.003 rows=2 loops=19)

  • Index Cond: (progserv_id = p.progserv_id)
66. 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)
67. 0.076 0.076 ↑ 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.002 rows=1 loops=38)

  • Filter: ((editor_assignment_type_tag)::text = 'APOLLO COVERAGE EDITOR'::text)
  • Rows Removed by Filter: 10
68. 0.012 0.088 ↑ 1.0 1 4

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

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

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

70. 0.020 0.020 ↓ 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.004..0.005 rows=2 loops=4)

  • Index Cond: (progserv_id = p.progserv_id)
71. 0.032 0.032 ↑ 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.004..0.004 rows=1 loops=8)

  • Index Cond: (employee_id = ea_2.employee_id)
72. 0.016 0.016 ↑ 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.002 rows=1 loops=8)

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

SubPlan (for GroupAggregate)

74. 0.004 0.148 ↑ 1.0 1 4

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

75.          

Initplan (for Result)

76. 0.024 0.144 ↓ 0.0 0 4

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

77. 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.001..0.002 rows=7 loops=4)

78. 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 : 14.206 ms
Execution time : 2,274.587 ms