explain.depesz.com

PostgreSQL's explain analyze made readable

Result: F2GM

Settings
# exclusive inclusive rows x rows loops node
1. 0.057 7,977.716 ↓ 2.0 10 1

Limit (cost=186,646.59..186,646.60 rows=5 width=407) (actual time=7,977.661..7,977.716 rows=10 loops=1)

2. 0.215 7,977.659 ↓ 2.0 10 1

Sort (cost=186,646.59..186,646.60 rows=5 width=407) (actual time=7,977.658..7,977.659 rows=10 loops=1)

  • Sort Key: (CASE a.estado_autorizacion_id WHEN 37 THEN 1 ELSE NULL::integer END), (CASE aa.codigo WHEN '22'::text THEN 2 WHEN '21'::text THEN 3 WHEN '11'::text THEN 4 WHEN '12'::text THEN 5 ELSE NULL::integer END), color.dias_retraso DESC, s.fecha_creacion, a.numero_autorizacion, si.numero_item
  • Sort Method: top-N heapsort Memory: 30kB
3. 0.161 7,977.444 ↓ 7.6 38 1

Nested Loop (cost=184,318.40..186,646.53 rows=5 width=407) (actual time=5,463.259..7,977.444 rows=38 loops=1)

4. 0.115 7,964.401 ↓ 38.0 38 1

Nested Loop Left Join (cost=184,318.15..186,644.21 rows=1 width=385) (actual time=5,457.750..7,964.401 rows=38 loops=1)

5. 5.038 7,964.020 ↓ 38.0 38 1

Merge Join (cost=184,318.01..186,638.45 rows=1 width=385) (actual time=5,457.716..7,964.020 rows=38 loops=1)

  • Merge Cond: (e.item_mipres_id = im.id)
6. 19.822 32.527 ↓ 13.7 72,160 1

GroupAggregate (cost=0.42..376.43 rows=5,252 width=20) (actual time=0.024..32.527 rows=72,160 loops=1)

  • Group Key: e.item_mipres_id
7. 12.705 12.705 ↓ 10.4 95,744 1

Index Only Scan using ix_entrega_item_mipres_id on entrega e (cost=0.42..300.97 rows=9,174 width=4) (actual time=0.019..12.705 rows=95,744 loops=1)

  • Index Cond: (item_mipres_id IS NOT NULL)
  • Heap Fetches: 0
8. 1.760 7,926.455 ↓ 1,104.0 1,104 1

Materialize (cost=184,317.58..186,196.37 rows=1 width=385) (actual time=5,421.290..7,926.455 rows=1,104 loops=1)

9. 2.585 7,924.695 ↓ 1,104.0 1,104 1

Nested Loop (cost=184,317.58..186,196.36 rows=1 width=385) (actual time=5,421.281..7,924.695 rows=1,104 loops=1)

10. 969.370 7,828.270 ↓ 1,104.0 1,104 1

Nested Loop Left Join (cost=184,317.33..186,196.09 rows=1 width=349) (actual time=5,420.818..7,828.270 rows=1,104 loops=1)

  • Join Filter: (caa.user_id = usr.id)
  • Rows Removed by Join Filter: 12019204
11. 1.004 5,434.740 ↓ 1,104.0 1,104 1

Nested Loop Left Join (cost=184,317.33..184,499.13 rows=1 width=344) (actual time=5,418.141..5,434.740 rows=1,104 loops=1)

12. 0.458 5,431.528 ↓ 1,104.0 1,104 1

Nested Loop Left Join (cost=184,316.91..184,495.85 rows=1 width=344) (actual time=5,418.112..5,431.528 rows=1,104 loops=1)

13. 2.349 5,427.758 ↓ 1,104.0 1,104 1

Nested Loop Left Join (cost=184,316.47..184,492.53 rows=1 width=344) (actual time=5,418.084..5,427.758 rows=1,104 loops=1)

14. 10.602 5,420.993 ↓ 1,104.0 1,104 1

Merge Left Join (cost=184,316.04..184,489.25 rows=1 width=344) (actual time=5,418.032..5,420.993 rows=1,104 loops=1)

  • Merge Cond: (si.id = caa.solicitud_item_id)
15. 10.598 5,190.839 ↓ 1,104.0 1,104 1

Sort (cost=184,315.61..184,315.61 rows=1 width=340) (actual time=5,189.483..5,190.839 rows=1,104 loops=1)

  • Sort Key: si.id
  • Sort Method: quicksort Memory: 1601kB
16. 8.000 5,180.241 ↓ 3,053.0 3,053 1

Nested Loop (cost=8,444.27..184,315.60 rows=1 width=340) (actual time=106.623..5,180.241 rows=3,053 loops=1)

  • Join Filter: (af.tipo_identificacion_id = ti.id)
  • Rows Removed by Join Filter: 36636
17. 11.979 5,166.135 ↓ 3,053.0 3,053 1

Nested Loop (cost=8,444.27..184,314.30 rows=1 width=341) (actual time=106.614..5,166.135 rows=3,053 loops=1)

  • Join Filter: (si.tipo_tecnologia_id = tt.id)
  • Rows Removed by Join Filter: 9159
18. 679.416 5,151.103 ↓ 3,053.0 3,053 1

Nested Loop Left Join (cost=8,444.27..184,313.21 rows=1 width=345) (actual time=106.603..5,151.103 rows=3,053 loops=1)

  • Join Filter: (sedef.id = a.sede_ips_efectora_id)
  • Rows Removed by Join Filter: 10810629
19. 795.044 3,030.671 ↓ 3,053.0 3,053 1

Nested Loop Left Join (cost=8,444.27..184,102.54 rows=1 width=284) (actual time=105.523..3,030.671 rows=3,053 loops=1)

  • Join Filter: (sede.id = s.sede_ips_id)
  • Rows Removed by Join Filter: 10807620
20. 11.657 757.975 ↓ 3,053.0 3,053 1

Nested Loop Left Join (cost=8,444.27..183,891.87 rows=1 width=223) (actual time=105.371..757.975 rows=3,053 loops=1)

  • Join Filter: (aa.id = pr.ambito_atencion_id)
  • Rows Removed by Join Filter: 12489
21. 33.843 743.265 ↓ 3,053.0 3,053 1

Nested Loop (cost=8,444.27..183,890.76 rows=1 width=202) (actual time=105.359..743.265 rows=3,053 loops=1)

  • Join Filter: (dep.id = mun.departamento_id)
  • Rows Removed by Join Filter: 103802
22. 0.050 0.112 ↑ 1.0 35 1

Nested Loop (cost=0.00..4.50 rows=35 width=30) (actual time=0.019..0.112 rows=35 loops=1)

  • Join Filter: (dep.regional_id = reg.id)
  • Rows Removed by Join Filter: 105
23. 0.027 0.027 ↑ 1.0 35 1

Seq Scan on departamento dep (cost=0.00..1.35 rows=35 width=17) (actual time=0.008..0.027 rows=35 loops=1)

24. 0.030 0.035 ↑ 1.0 4 35

Materialize (cost=0.00..1.06 rows=4 width=21) (actual time=0.000..0.001 rows=4 loops=35)

25. 0.005 0.005 ↑ 1.0 4 1

Seq Scan on regional reg (cost=0.00..1.04 rows=4 width=21) (actual time=0.003..0.005 rows=4 loops=1)

26. 9.922 709.310 ↓ 3,053.0 3,053 35

Materialize (cost=8,444.27..183,885.73 rows=1 width=180) (actual time=2.776..20.266 rows=3,053 loops=35)

27. 303.326 699.388 ↓ 3,053.0 3,053 1

Nested Loop (cost=8,444.27..183,885.73 rows=1 width=180) (actual time=97.151..699.388 rows=3,053 loops=1)

  • Join Filter: (af.municipio_residencia_id = mun.id)
  • Rows Removed by Join Filter: 3437678
28. 3.165 164.034 ↓ 3,053.0 3,053 1

Nested Loop (cost=8,444.27..183,848.37 rows=1 width=171) (actual time=96.953..164.034 rows=3,053 loops=1)

29. 3.208 145.604 ↓ 3,053.0 3,053 1

Nested Loop Left Join (cost=8,443.84..183,840.55 rows=1 width=119) (actual time=96.935..145.604 rows=3,053 loops=1)

30. 2.986 136.290 ↓ 3,053.0 3,053 1

Nested Loop (cost=8,443.54..183,834.56 rows=1 width=118) (actual time=96.921..136.290 rows=3,053 loops=1)

31. 6.059 124.145 ↓ 3,053.0 3,053 1

Hash Join (cost=8,443.11..183,828.37 rows=1 width=88) (actual time=96.887..124.145 rows=3,053 loops=1)

  • Hash Cond: (si.id = im.id)
  • Join Filter: (CASE WHEN (s.tipo_solicitud_id = 5) THEN 1 WHEN ((im.estado_junta_profesionales)::text = ANY ('{NO_REQUIERE,APROBADA}'::text[])) THEN 1 ELSE 0 END = 1)
  • Rows Removed by Join Filter: 115
32. 4.382 21.641 ↑ 3.8 3,220 1

Nested Loop (cost=0.87..175,339.20 rows=12,203 width=48) (actual time=0.036..21.641 rows=3,220 loops=1)

33. 5.559 5.559 ↑ 2.4 2,925 1

Index Scan using ix_solicitud_fecha_creacion on solicitud s (cost=0.43..966.26 rows=6,971 width=24) (actual time=0.025..5.559 rows=2,925 loops=1)

  • Index Cond: ((fecha_creacion >= '2020-01-01 00:00:00'::timestamp without time zone) AND (fecha_creacion < '2020-01-14 00:00:00'::timestamp without time zone))
34. 11.700 11.700 ↑ 11.0 1 2,925

Index Scan using ix_solicitud_item_solicitud_id on solicitud_item si (cost=0.44..24.90 rows=11 width=28) (actual time=0.004..0.004 rows=1 loops=2,925)

  • Index Cond: (solicitud_id = s.id)
35. 27.176 96.445 ↓ 1.0 109,588 1

Hash (cost=7,073.77..7,073.77 rows=109,477 width=44) (actual time=96.445..96.445 rows=109,588 loops=1)

  • Buckets: 131072 Batches: 1 Memory Usage: 8361kB
36. 69.269 69.269 ↓ 1.0 109,588 1

Seq Scan on item_mipres im (cost=0.00..7,073.77 rows=109,477 width=44) (actual time=0.007..69.269 rows=109,588 loops=1)

37. 9.159 9.159 ↑ 1.0 1 3,053

Index Scan using pk_autorizacion_pkey on autorizacion a (cost=0.44..6.18 rows=1 width=34) (actual time=0.003..0.003 rows=1 loops=3,053)

  • Index Cond: (id = si.autorizacion_id)
38. 6.106 6.106 ↑ 1.0 1 3,053

Index Scan using pk_prescripcion on prescripcion pr (cost=0.29..5.98 rows=1 width=9) (actual time=0.002..0.002 rows=1 loops=3,053)

  • Index Cond: (id = s.id)
39. 15.265 15.265 ↑ 1.0 1 3,053

Index Scan using pk_afiliado_pkey on afiliado af (cost=0.43..7.81 rows=1 width=56) (actual time=0.005..0.005 rows=1 loops=3,053)

  • Index Cond: (id = s.afiliado_id)
40. 232.028 232.028 ↑ 1.0 1,127 3,053

Seq Scan on municipio mun (cost=0.00..23.27 rows=1,127 width=17) (actual time=0.001..0.076 rows=1,127 loops=3,053)

41. 3.053 3.053 ↑ 1.0 5 3,053

Seq Scan on ambito_atencion aa (cost=0.00..1.05 rows=5 width=29) (actual time=0.001..0.001 rows=5 loops=3,053)

42. 1,477.652 1,477.652 ↑ 1.0 3,541 3,053

Seq Scan on sede_ips sede (cost=0.00..166.41 rows=3,541 width=69) (actual time=0.001..0.484 rows=3,541 loops=3,053)

43. 1,441.016 1,441.016 ↑ 1.0 3,541 3,053

Seq Scan on sede_ips sedef (cost=0.00..166.41 rows=3,541 width=69) (actual time=0.001..0.472 rows=3,541 loops=3,053)

44. 3.053 3.053 ↑ 1.0 4 3,053

Seq Scan on tipo_tecnologia tt (cost=0.00..1.04 rows=4 width=4) (actual time=0.001..0.001 rows=4 loops=3,053)

45. 6.106 6.106 ↑ 1.0 13 3,053

Seq Scan on tipo_identificacion ti (cost=0.00..1.13 rows=13 width=7) (actual time=0.001..0.002 rows=13 loops=3,053)

46. 13.399 219.552 ↓ 239.8 72,431 1

Materialize (cost=0.44..52,302.37 rows=302 width=8) (actual time=0.688..219.552 rows=72,431 loops=1)

47. 11.923 206.153 ↓ 239.8 72,431 1

Subquery Scan on caa (cost=0.44..52,301.61 rows=302 width=8) (actual time=0.679..206.153 rows=72,431 loops=1)

  • Filter: (caa.num = 1)
  • Rows Removed by Filter: 776
48. 78.024 194.230 ↓ 1.2 73,207 1

WindowAgg (cost=0.44..51,547.32 rows=60,343 width=16) (actual time=0.670..194.230 rows=73,207 loops=1)

49. 116.206 116.206 ↓ 1.2 73,207 1

Index Scan using ix_concepto_autorizacion_solicitud_item_id on concepto_autorizacion ca (cost=0.44..50,642.18 rows=60,343 width=8) (actual time=0.650..116.206 rows=73,207 loops=1)

  • Index Cond: (solicitud_item_id IS NOT NULL)
  • Filter: es_concepto_autorizado
  • Rows Removed by Filter: 16571
50. 4.416 4.416 ↓ 0.0 0 1,104

Index Only Scan using ix_solicitud_medicamento_solicitud_item on solicitud_medicamento sm (cost=0.42..3.27 rows=1 width=4) (actual time=0.004..0.004 rows=0 loops=1,104)

  • Index Cond: (solicitud_item_id = si.id)
  • Heap Fetches: 0
51. 3.312 3.312 ↓ 0.0 0 1,104

Index Only Scan using ix_solicitud_procedimiento_solicitud_item on solicitud_procedimiento sp (cost=0.44..3.31 rows=1 width=4) (actual time=0.003..0.003 rows=0 loops=1,104)

  • Index Cond: (solicitud_item_id = si.id)
  • Heap Fetches: 0
52. 2.208 2.208 ↓ 0.0 0 1,104

Index Only Scan using ix_solicitud_insumo_solicitud_item on solicitud_insumo sins (cost=0.42..3.27 rows=1 width=4) (actual time=0.002..0.002 rows=0 loops=1,104)

  • Index Cond: (solicitud_item_id = si.id)
  • Heap Fetches: 0
53. 1,424.160 1,424.160 ↑ 1.0 10,887 1,104

Seq Scan on "user" usr (cost=0.00..1,560.87 rows=10,887 width=13) (actual time=0.002..1.290 rows=10,887 loops=1,104)

54. 93.840 93.840 ↑ 1.0 1 1,104

Function Scan on fn_colorimetria_bandeja_prescripcion color (cost=0.25..0.26 rows=1 width=36) (actual time=0.085..0.085 rows=1 loops=1,104)

55. 0.266 0.266 ↑ 1.0 1 38

Index Only Scan using uq_tipo_operacion_id_estado_direccionamiento_id_solicitud_item_ on reporte_ministerio rmin (cost=0.14..5.75 rows=1 width=4) (actual time=0.007..0.007 rows=1 loops=38)

  • Index Cond: ((solicitud_item_id = si.id) AND (numero_entrega = 1))
  • Heap Fetches: 23
56. 12.882 12.882 ↑ 100.0 1 38

Function Scan on fn_obtener_tecnologia_solicitada tec (cost=0.25..1.25 rows=100 width=32) (actual time=0.339..0.339 rows=1 loops=38)

Planning time : 26.421 ms
Execution time : 7,979.232 ms