explain.depesz.com

PostgreSQL's explain analyze made readable

Result: ccde

Settings
# exclusive inclusive rows x rows loops node
1. 2.316 7,081.988 ↓ 2.1 356 1

Sort (cost=3,964,514.06..3,964,514.49 rows=173 width=380) (actual time=7,081.965..7,081.988 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.763 7,079.672 ↓ 2.1 356 1

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

3. 2.185 7,074.485 ↓ 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=28.903..7,074.485 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,072.300 ↓ 0.0 0 359

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

6. 1.077 7,071.941 ↓ 0.0 0 359

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

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

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

8. 0.251 7,052.196 ↓ 0.0 0 359

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

9. 0.359 7,051.837 ↓ 0.0 0 359

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

10. 44.516 7,051.478 ↓ 0.0 0 359

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

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

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

12. 0.359 6,587.650 ↑ 2.0 1 359

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

13. 0.359 6,587.291 ↑ 2.0 1 359

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

14. 0.359 6,582.624 ↑ 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.336..18.336 rows=1 loops=359)

15. 1.795 6,582.265 ↑ 1.0 1 359

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

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

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

17. 18.668 6,577.598 ↑ 1.0 1 359

Nested Loop (cost=0.43..4,873.96 rows=1 width=21) (actual time=0.615..18.322 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.465 6,447.999 ↑ 1.0 1 359

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

  • Join Filter: (ntf_2.actividad_id = pac_2.actividad_id)
  • Rows Removed by Join Filter: 635
19. 6,373.327 6,373.327 ↑ 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.193..17.753 rows=1 loops=359)

  • Index Cond: ((not_estado)::text = 'OPEN'::text)
  • Filter: (caso_id = '895541'::numeric)
  • Rows Removed by Filter: 29508
20. 26.207 26.207 ↑ 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.073 rows=636 loops=359)

21. 110.931 110.931 ↓ 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.309 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. 50.260 384.130 ↓ 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.257..1.070 rows=1,096 loops=359)

32. 263.147 333.870 ↓ 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.250..0.930 rows=1,096 loops=359)

  • Recheck Cond: ((usr_codigo)::text = a.ep_usuario)
  • Heap Blocks: exact=133664
33. 70.723 70.723 ↓ 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.197..0.197 rows=1,096 loops=359)

  • Index Cond: ((usr_codigo)::text = a.ep_usuario)
34. 0.108 0.108 ↑ 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.036..0.036 rows=1 loops=3)

  • Index Cond: ((usr_codigo = a.ep_usuario) AND (rol_nombre = ("ANY_subquery".recipient_role)::text))
  • Heap Fetches: 3
35. 0.718 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.000 16.873 ↓ 0.0 0 359

Append (cost=22.63..39.27 rows=2 width=0) (actual time=0.047..0.047 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.513 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.770 ↓ 0.0 0 359

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

41. 0.359 10.411 ↓ 0.0 0 359

Nested Loop (cost=0.99..15.17 rows=1 width=12) (actual time=0.029..0.029 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.424 1.424 ↓ 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.004..0.004 rows=0 loops=356)

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