explain.depesz.com

PostgreSQL's explain analyze made readable

Result: idOi

Settings
# exclusive inclusive rows x rows loops node
1. 2.359 7,196.541 ↓ 2.1 356 1

Sort (cost=3,964,514.06..3,964,514.49 rows=173 width=380) (actual time=7,196.518..7,196.541 rows=356 loops=1)

  • Sort Key: (nvl((a.ep_alias)::character varying, (a.ep_nombres)::character varying)), a.ep_ape_pat
  • Sort Method: quicksort Memory: 208kB
2. 3.617 7,194.182 ↓ 2.1 356 1

Nested Loop Anti Join (cost=0.55..3,964,507.63 rows=173 width=380) (actual time=23.668..7,194.182 rows=356 loops=1)

3. 2.323 7,188.785 ↓ 2.0 356 1

Index Scan using ep_fun_todos_ep_usuario_idx on ep_fun_todos a (cost=0.28..3,964,329.89 rows=174 width=316) (actual time=23.631..7,188.785 rows=356 loops=1)

  • Index Cond: (ep_usuario IS NOT NULL)
  • Filter: (((ep_vigente)::text = 'S'::text) AND (NOT (SubPlan 2)))
  • Rows Removed by Filter: 204
4.          

SubPlan (forIndex Scan)

5. 0.359 7,186.462 ↓ 0.0 0 359

Unique (cost=7,078.95..7,078.96 rows=2 width=4) (actual time=20.018..20.018 rows=0 loops=359)

6. 1.077 7,186.103 ↓ 0.0 0 359

Sort (cost=7,078.95..7,078.95 rows=2 width=4) (actual time=20.017..20.017 rows=0 loops=359)

  • Sort Key: (1)
  • Sort Method: quicksort Memory: 25kB
7. 1.077 7,185.026 ↓ 0.0 0 359

Append (cost=4,881.85..7,078.94 rows=2 width=4) (actual time=20.013..20.014 rows=0 loops=359)

8. 0.296 7,166.358 ↓ 0.0 0 359

Nested Loop (cost=4,881.85..7,046.96 rows=1 width=4) (actual time=19.962..19.962 rows=0 loops=359)

9. 0.359 7,165.999 ↓ 0.0 0 359

Subquery Scan on ANY_subquery (cost=4,881.42..7,032.06 rows=2 width=218) (actual time=19.961..19.961 rows=0 loops=359)

10. 44.516 7,165.640 ↓ 0.0 0 359

HashSetOp Intersect (cost=4,881.42..7,032.04 rows=2 width=222) (actual time=19.960..19.960 rows=0 loops=359)

11. 35.182 7,121.124 ↓ 1.0 1,097 359

Append (cost=4,881.42..7,029.43 rows=1,045 width=222) (actual time=18.703..19.836 rows=1,097 loops=359)

12. 0.718 6,719.044 ↑ 2.0 1 359

Subquery Scan on *SELECT* 1 (cost=4,881.42..4,897.06 rows=2 width=13) (actual time=18.702..18.716 rows=1 loops=359)

13. 0.359 6,718.326 ↑ 2.0 1 359

Append (cost=4,881.42..4,897.04 rows=2 width=9) (actual time=18.701..18.714 rows=1 loops=359)

14. 0.359 6,713.659 ↑ 1.0 1 359

Subquery Scan on vw_casos_open_1 (cost=4,881.42..4,881.44 rows=1 width=9) (actual time=18.701..18.701 rows=1 loops=359)

15. 1.795 6,713.300 ↑ 1.0 1 359

Sort (cost=4,881.42..4,881.43 rows=1 width=3,847) (actual time=18.700..18.700 rows=1 loops=359)

  • Sort Key: ntf_2.not_id
  • Sort Method: quicksort Memory: 25kB
16. 1.436 6,711.505 ↑ 1.0 1 359

Nested Loop (cost=0.85..4,881.41 rows=1 width=3,847) (actual time=0.618..18.695 rows=1 loops=359)

17. 18.309 6,708.274 ↑ 1.0 1 359

Nested Loop (cost=0.43..4,873.96 rows=1 width=21) (actual time=0.610..18.686 rows=1 loops=359)

  • Join Filter: (((pac_2.item_type)::text = (act_2.item_type)::text) AND ((pac_2.act_nombre)::text = (act_2.act_nombre)::text))
  • Rows Removed by Join Filter: 514
18. 48.106 6,580.111 ↑ 1.0 1 359

Nested Loop (cost=0.43..4,834.41 rows=1 width=44) (actual time=0.397..18.329 rows=1 loops=359)

  • Join Filter: (ntf_2.actividad_id = pac_2.actividad_id)
  • Rows Removed by Join Filter: 635
19. 6,506.875 6,506.875 ↑ 1.0 1 359

Index Scan using wfa_notificacion_n1 on cwfa_notificacion ntf_2 (cost=0.43..4,788.74 rows=1 width=26) (actual time=0.194..18.125 rows=1 loops=359)

  • Index Cond: ((not_estado)::text = 'OPEN'::text)
  • Filter: (caso_id = '895541'::numeric)
  • Rows Removed by Filter: 29508
20. 25.130 25.130 ↑ 2.0 636 359

Seq Scan on cwfa_actividades_proceso pac_2 (cost=0.00..29.74 rows=1,274 width=28) (actual time=0.004..0.070 rows=636 loops=359)

21. 109.854 109.854 ↓ 73.6 515 359

Seq Scan on cwfa_actividad act_2 (cost=0.00..39.45 rows=7 width=22) (actual time=0.004..0.306 rows=515 loops=359)

  • Filter: (act_version = act_ult_version)
  • Rows Removed by Filter: 744
22. 1.795 1.795 ↑ 1.0 1 359

Index Scan using caso_pk on casos ca_2 (cost=0.42..7.44 rows=1 width=6) (actual time=0.005..0.005 rows=1 loops=359)

  • Index Cond: (caso_id = '895541'::numeric)
  • Filter: (caso_fecha_termino IS NULL)
23. 0.359 4.308 ↓ 0.0 0 359

Subquery Scan on *SELECT* 2_2 (cost=1.41..15.60 rows=1 width=9) (actual time=0.012..0.012 rows=0 loops=359)

24. 0.000 3.949 ↓ 0.0 0 359

Nested Loop (cost=1.41..15.59 rows=1 width=3,861) (actual time=0.011..0.011 rows=0 loops=359)

25. 0.000 3.949 ↓ 0.0 0 359

Nested Loop (cost=1.13..15.19 rows=1 width=32) (actual time=0.011..0.011 rows=0 loops=359)

26. 0.359 3.949 ↓ 0.0 0 359

Nested Loop (cost=0.85..14.89 rows=1 width=14) (actual time=0.011..0.011 rows=0 loops=359)

27. 3.590 3.590 ↓ 0.0 0 359

Index Scan using caso_pk on casos ca_3 (cost=0.42..7.44 rows=1 width=6) (actual time=0.010..0.010 rows=0 loops=359)

  • Index Cond: (caso_id = '895541'::numeric)
  • Filter: (caso_fecha_termino IS NOT NULL)
  • Rows Removed by Filter: 1
28. 0.000 0.000 ↓ 0.0 0

Index Scan using wfa_notificaciones_pk on cwfa_notificacion ntf_3 (cost=0.43..7.45 rows=1 width=20) (never executed)

  • Index Cond: (not_id = ca_3.caso_last_nid)
29. 0.000 0.000 ↓ 0.0 0

Index Scan using wfa_actividades_wf_pk on cwfa_actividades_proceso pac_3 (cost=0.28..0.30 rows=1 width=28) (never executed)

  • Index Cond: (actividad_id = ntf_3.actividad_id)
30. 0.000 0.000 ↓ 0.0 0

Index Scan using wfa_actividades_v2_pk on cwfa_actividad act_3 (cost=0.28..0.40 rows=1 width=22) (never executed)

  • Index Cond: (((act_nombre)::text = (pac_3.act_nombre)::text) AND ((item_type)::text = (pac_3.item_type)::text))
  • Filter: (act_version = act_ult_version)
31. 49.542 366.898 ↓ 1.1 1,096 359

Subquery Scan on *SELECT* 2_1 (cost=36.51..2,132.36 rows=1,043 width=14) (actual time=0.229..1.022 rows=1,096 loops=359)

32. 257.403 317.356 ↓ 1.1 1,096 359

Bitmap Heap Scan on cwfa_roles_usuario wf_local_user_roles2 (cost=36.51..2,121.93 rows=1,043 width=10) (actual time=0.222..0.884 rows=1,096 loops=359)

  • Recheck Cond: ((usr_codigo)::text = a.ep_usuario)
  • Heap Blocks: exact=133664
33. 59.953 59.953 ↓ 1.1 1,096 359

Bitmap Index Scan on wfa_roles_usuarios_pk (cost=0.00..36.24 rows=1,043 width=0) (actual time=0.167..0.167 rows=1,096 loops=359)

  • Index Cond: ((usr_codigo)::text = a.ep_usuario)
34. 0.063 0.063 ↑ 1.0 1 3

Index Only Scan using wfa_roles_usuarios_pk on cwfa_roles_usuario wf_local_user_roles1 (cost=0.42..7.44 rows=1 width=10) (actual time=0.021..0.021 rows=1 loops=3)

  • Index Cond: ((usr_codigo = a.ep_usuario) AND (rol_nombre = ("ANY_subquery".recipient_role)::text))
  • Heap Fetches: 3
35. 0.359 17.591 ↓ 0.0 0 359

Result (cost=30.95..31.96 rows=1 width=4) (actual time=0.049..0.049 rows=0 loops=359)

  • One-Time Filter: $8
36.          

Initplan (forResult)

37. 0.359 17.232 ↓ 0.0 0 359

Append (cost=22.63..39.27 rows=2 width=0) (actual time=0.048..0.048 rows=0 loops=359)

38. 0.718 14.001 ↓ 0.0 0 359

Subquery Scan on vw_casos_open (cost=22.63..22.64 rows=1 width=0) (actual time=0.039..0.039 rows=0 loops=359)

39. 2.872 13.283 ↓ 0.0 0 359

Sort (cost=22.63..22.63 rows=1 width=4,056) (actual time=0.037..0.037 rows=0 loops=359)

  • Sort Key: ntf.not_id
  • Sort Method: quicksort Memory: 25kB
40. 0.359 10.411 ↓ 0.0 0 359

Nested Loop (cost=1.41..22.62 rows=1 width=4,056) (actual time=0.029..0.029 rows=0 loops=359)

41. 0.000 10.052 ↓ 0.0 0 359

Nested Loop (cost=0.99..15.17 rows=1 width=12) (actual time=0.028..0.028 rows=0 loops=359)

42. 0.718 10.052 ↓ 0.0 0 359

Nested Loop (cost=0.71..14.76 rows=1 width=35) (actual time=0.028..0.028 rows=0 loops=359)

43. 9.334 9.334 ↓ 0.0 0 359

Index Scan using wfa_notificaciones_n2 on cwfa_notificacion ntf (cost=0.43..7.45 rows=1 width=17) (actual time=0.026..0.026 rows=0 loops=359)

  • Index Cond: (((not_estado)::text = 'OPEN'::text) AND ((not_rol_receptor)::text = a.ep_usuario))
  • Filter: (caso_id = '895541'::numeric)
  • Rows Removed by Filter: 15
44. 0.000 0.000 ↓ 0.0 0

Index Scan using wfa_actividades_wf_pk on cwfa_actividades_proceso pac (cost=0.28..7.29 rows=1 width=28) (never executed)

  • Index Cond: (actividad_id = ntf.actividad_id)
45. 0.000 0.000 ↓ 0.0 0

Index Scan using wfa_actividades_v2_pk on cwfa_actividad act (cost=0.28..0.40 rows=1 width=22) (never executed)

  • Index Cond: (((act_nombre)::text = (pac.act_nombre)::text) AND ((item_type)::text = (pac.item_type)::text))
  • Filter: (act_version = act_ult_version)
46. 0.000 0.000 ↓ 0.0 0

Index Scan using caso_pk on casos ca (cost=0.42..7.44 rows=1 width=6) (never executed)

  • Index Cond: (caso_id = '895541'::numeric)
  • Filter: (caso_fecha_termino IS NULL)
47. 0.359 2.872 ↓ 0.0 0 359

Subquery Scan on *SELECT* 2 (cost=1.41..16.63 rows=1 width=0) (actual time=0.008..0.008 rows=0 loops=359)

48. 0.359 2.513 ↓ 0.0 0 359

Nested Loop (cost=1.41..16.62 rows=1 width=4,070) (actual time=0.007..0.007 rows=0 loops=359)

49. 0.359 2.154 ↓ 0.0 0 359

Nested Loop (cost=1.13..16.21 rows=1 width=23) (actual time=0.006..0.006 rows=0 loops=359)

50. 0.000 1.795 ↓ 0.0 0 359

Nested Loop (cost=0.85..15.04 rows=1 width=5) (actual time=0.005..0.005 rows=0 loops=359)

51. 1.795 1.795 ↓ 0.0 0 359

Index Scan using caso_pk on casos ca_1 (cost=0.42..7.44 rows=1 width=6) (actual time=0.005..0.005 rows=0 loops=359)

  • Index Cond: (caso_id = '895541'::numeric)
  • Filter: (caso_fecha_termino IS NOT NULL)
  • Rows Removed by Filter: 1
52. 0.000 0.000 ↓ 0.0 0

Index Scan using wfa_notificaciones_pk on cwfa_notificacion ntf_1 (cost=0.43..7.45 rows=1 width=11) (never executed)

  • Index Cond: (not_id = ca_1.caso_last_nid)
  • Filter: ((not_rol_receptor)::text = a.ep_usuario)
53. 0.000 0.000 ↓ 0.0 0

Index Scan using wfa_actividades_wf_pk on cwfa_actividades_proceso pac_1 (cost=0.28..1.17 rows=1 width=28) (never executed)

  • Index Cond: (actividad_id = ntf_1.actividad_id)
54. 0.000 0.000 ↓ 0.0 0

Index Scan using wfa_actividades_v2_pk on cwfa_actividad act_1 (cost=0.28..0.40 rows=1 width=22) (never executed)

  • Index Cond: (((act_nombre)::text = (pac_1.act_nombre)::text) AND ((item_type)::text = (pac_1.item_type)::text))
  • Filter: (act_version = act_ult_version)
55. 0.000 0.000 ↓ 0.0 0

Seq Scan on dual (cost=30.95..31.96 rows=1 width=4) (never executed)

56. 1.780 1.780 ↓ 0.0 0 356

Index Only Scan using wfa_usuarios_apoyo_pk on wfa_usuarios_apoyo b (cost=0.28..1.00 rows=1 width=8) (actual time=0.005..0.005 rows=0 loops=356)

  • Index Cond: ((caso_id = '895541'::numeric) AND (wup_user = a.ep_usuario))
  • Heap Fetches: 0