explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Js1L : get_consulta_autorizaciones_profesional

Settings
# exclusive inclusive rows x rows loops node
1. 0.283 9,037.843 ↓ 42.5 85 1

GroupAggregate (cost=1,741,014.99..1,741,015.15 rows=2 width=211) (actual time=9,037.585..9,037.843 rows=85 loops=1)

  • Group Key: sede.id, band.id, t_4.id, prescr.id, proc.id, pe.id, bene.id, dele.id, tipo.id, torig.id, a.codigo
2.          

Initplan (forGroupAggregate)

3. 0.000 0.000 ↓ 0.0 0

Seq Scan on tipo_prescriptor (cost=0.00..1.02 rows=1 width=4) (never executed)

  • Filter: ((codigo)::text = 'PROF'::text)
4. 0.000 9,037.560 ↓ 69.5 139 1

Sort (cost=1,741,013.97..1,741,013.97 rows=2 width=170) (actual time=9,037.543..9,037.560 rows=139 loops=1)

  • Sort Key: sede.id, band.id, t_4.id, prescr.id, proc.id, pe.id, bene.id, dele.id, tipo.id, torig.id, a.codigo
  • Sort Method: quicksort Memory: 61kB
5. 176.297 9,142.929 ↓ 69.5 139 1

Gather (cost=1,075,352.00..1,741,013.96 rows=2 width=170) (actual time=7,565.168..9,142.929 rows=139 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
6. 0.355 8,966.632 ↓ 46.0 46 3

Nested Loop Left Join (cost=1,074,352.00..1,740,013.76 rows=1 width=170) (actual time=7,527.438..8,966.632 rows=46 loops=3)

7. 0.119 8,966.271 ↓ 46.0 46 3

Nested Loop Left Join (cost=1,074,351.58..1,740,010.85 rows=1 width=170) (actual time=7,527.412..8,966.271 rows=46 loops=3)

8. 0.371 8,966.151 ↓ 46.0 46 3

Nested Loop Left Join (cost=1,074,351.44..1,740,009.63 rows=1 width=170) (actual time=7,527.392..8,966.151 rows=46 loops=3)

9. 0.428 8,965.775 ↓ 46.0 46 3

Nested Loop Left Join (cost=1,074,351.16..1,740,006.89 rows=1 width=148) (actual time=7,527.353..8,965.775 rows=46 loops=3)

10. 45.570 8,965.340 ↓ 46.0 46 3

Parallel Hash Semi Join (cost=1,074,350.88..1,740,004.22 rows=1 width=140) (actual time=7,527.311..8,965.340 rows=46 loops=3)

  • Hash Cond: (proc.id = subet.proceso_transaccion_id)
11. 795.149 8,866.911 ↓ 2.9 132,261 3

Nested Loop Left Join (cost=1,042,362.93..1,707,897.76 rows=45,142 width=144) (actual time=7,322.873..8,866.911 rows=132,261 loops=3)

12. 66.573 8,071.757 ↓ 2.9 132,261 3

Hash Join (cost=1,042,362.93..1,072,331.87 rows=45,142 width=140) (actual time=7,322.730..8,071.757 rows=132,261 loops=3)

  • Hash Cond: (etiep.estado_solicitud_item_id = esi.id)
13. 69.690 8,005.099 ↓ 2.9 132,261 3

Hash Join (cost=1,042,361.61..1,072,176.19 rows=45,142 width=139) (actual time=7,322.572..8,005.099 rows=132,261 loops=3)

  • Hash Cond: (band.tipo_evento_transaccion_id = tipo.id)
14. 173.494 7,935.360 ↓ 2.9 132,261 3

Parallel Hash Left Join (cost=1,042,360.18..1,072,029.83 rows=45,142 width=132) (actual time=7,322.499..7,935.360 rows=132,261 loops=3)

  • Hash Cond: ((band.proceso_transaccion_id = etorig.proceso_transaccion_id) AND (band.tipo_evento_transaccion_id = etorig.tipo_evento_transaccion_id))
  • Join Filter: (etorig.id <> band.id)
  • Rows Removed by Join Filter: 130099
15. 60.863 6,714.694 ↓ 2.9 132,261 3

Hash Join (cost=936,322.16..965,594.45 rows=45,142 width=128) (actual time=6,267.884..6,714.694 rows=132,261 loops=3)

  • Hash Cond: (bene.tipo_documento_id = td.id)
16. 327.686 6,653.749 ↓ 2.9 132,261 3

Parallel Hash Join (cost=936,320.96..965,418.95 rows=45,142 width=132) (actual time=6,267.737..6,653.749 rows=132,261 loops=3)

  • Hash Cond: (etiep.evento_transaccion_item_id = eti.id)
17. 216.818 216.818 ↑ 1.2 612,583 3

Parallel Seq Scan on evento_transaccion_item_estado_parcial etiep (cost=0.00..26,043.75 rows=764,307 width=10) (actual time=0.018..216.818 rows=612,583 loops=3)

  • Filter: ((deleted IS FALSE) AND (es_anulado IS FALSE))
  • Rows Removed by Filter: 29537
18. 150.935 6,109.245 ↓ 2.8 131,242 3

Parallel Hash (cost=935,737.62..935,737.62 rows=46,667 width=130) (actual time=6,109.245..6,109.245 rows=131,242 loops=3)

  • Buckets: 524288 (originally 131072) Batches: 1 (originally 1) Memory Usage: 73312kB
19. 290.450 5,958.310 ↓ 2.8 131,242 3

Parallel Hash Join (cost=888,647.03..935,737.62 rows=46,667 width=130) (actual time=5,468.857..5,958.310 rows=131,242 loops=3)

  • Hash Cond: (eti.evento_transaccion_id = band.id)
20. 376.340 376.340 ↑ 1.3 608,321 3

Parallel Seq Scan on evento_transaccion_item eti (cost=0.00..44,041.38 rows=761,271 width=8) (actual time=0.018..376.340 rows=608,321 loops=3)

  • Filter: ((deleted IS FALSE) AND (es_anulado IS FALSE))
  • Rows Removed by Filter: 23657
21. 142.520 5,291.520 ↑ 1.3 103,369 3

Parallel Hash (cost=887,014.25..887,014.25 rows=130,622 width=134) (actual time=5,291.519..5,291.520 rows=103,369 loops=3)

  • Buckets: 524288 Batches: 1 Memory Usage: 57568kB
22. 114.759 5,149.000 ↑ 1.3 103,369 3

Parallel Hash Join (cost=167,915.76..887,014.25 rows=130,622 width=134) (actual time=3,005.441..5,149.000 rows=103,369 loops=3)

  • Hash Cond: (proc.beneficiario_id = bene.id)
23. 51.352 4,964.483 ↑ 1.3 103,369 3

Hash Join (cost=157,472.50..876,228.10 rows=130,622 width=97) (actual time=2,934.933..4,964.483 rows=103,369 loops=3)

  • Hash Cond: (band.ambito_id = a.id)
24. 50.801 4,913.084 ↑ 1.3 103,369 3

Hash Join (cost=157,471.46..875,157.59 rows=130,622 width=99) (actual time=2,934.855..4,913.084 rows=103,369 loops=3)

  • Hash Cond: (band.tipo_evento_transaccion_id = tet.id)
25. 61.245 4,862.257 ↑ 1.3 103,369 3

Hash Left Join (cost=157,470.03..874,736.79 rows=130,622 width=95) (actual time=2,934.808..4,862.257 rows=103,369 loops=3)

  • Hash Cond: (band.ips_sede_id = sede.id)
26. 726.904 4,800.458 ↑ 1.3 103,369 3

Parallel Hash Join (cost=157,443.36..874,365.60 rows=130,622 width=71) (actual time=2,934.202..4,800.458 rows=103,369 loops=3)

  • Hash Cond: (t_4.evento_transaccion_id = band.id)
27. 0.000 1,721.089 ↑ 1.3 1,655,023 3

Parallel Append (cost=0.00..708,617.94 rows=2,069,343 width=12) (actual time=0.150..1,721.089 rows=1,655,023 loops=3)

28. 780.017 780.017 ↑ 1.3 858,069 3

Parallel Seq Scan on ticket_2018 t_4 (cost=0.00..373,076.92 rows=1,073,792 width=12) (actual time=0.156..780.017 rows=858,069 loops=3)

29. 589.644 589.644 ↓ 1.2 577,772 2

Parallel Seq Scan on ticket_2019 t_5 (cost=0.00..170,995.89 rows=481,289 width=12) (actual time=0.141..589.644 rows=577,772 loops=2)

30. 978.716 978.716 ↓ 2.4 1,184,781 1

Parallel Seq Scan on ticket_2017 t_3 (cost=0.00..149,189.13 rows=493,013 width=12) (actual time=0.092..978.716 rows=1,184,781 loops=1)

31. 33.149 33.149 ↓ 2.4 50,533 1

Parallel Seq Scan on ticket_2016 t_2 (cost=0.00..4,995.55 rows=21,055 width=12) (actual time=0.006..33.149 rows=50,533 loops=1)

32. 0.001 0.001 ↓ 0.0 0 1

Parallel Seq Scan on ticket_2015 t_1 (cost=0.00..12.71 rows=271 width=12) (actual time=0.001..0.001 rows=0 loops=1)

33. 0.018 0.018 ↓ 2.0 4 1

Parallel Seq Scan on ticket_otros t_6 (cost=0.00..1.02 rows=2 width=12) (actual time=0.015..0.018 rows=4 loops=1)

34. 0.003 0.003 ↓ 0.0 0 1

Parallel Seq Scan on ticket t (cost=0.00..0.00 rows=1 width=12) (actual time=0.002..0.003 rows=0 loops=1)

35. 84.741 2,352.465 ↑ 1.3 103,369 3

Parallel Hash (cost=155,762.07..155,762.07 rows=134,503 width=59) (actual time=2,352.465..2,352.465 rows=103,369 loops=3)

  • Buckets: 524288 Batches: 1 Memory Usage: 33952kB
36. 196.830 2,267.724 ↑ 1.3 103,369 3

Parallel Hash Join (cost=51,816.30..155,762.07 rows=134,503 width=59) (actual time=1,175.382..2,267.724 rows=103,369 loops=3)

  • Hash Cond: (band.id = aso.id)
37. 684.148 1,757.386 ↑ 1.2 389,499 3

Parallel Hash Join (cost=27,543.38..130,309.23 rows=449,492 width=55) (actual time=821.493..1,757.386 rows=389,499 loops=3)

  • Hash Cond: (band.proceso_transaccion_id = proc.id)
38. 727.574 727.574 ↑ 1.2 1,683,637 3

Parallel Seq Scan on evento_transaccion band (cost=0.00..97,243.16 rows=2,103,877 width=39) (actual time=0.018..727.574 rows=1,683,637 loops=3)

  • Filter: ((deleted IS FALSE) AND (es_anulado IS FALSE))
  • Rows Removed by Filter: 20992
39. 130.840 345.664 ↑ 1.3 280,476 3

Parallel Hash (cost=23,047.89..23,047.89 rows=359,639 width=16) (actual time=345.663..345.664 rows=280,476 loops=3)

  • Buckets: 1048576 Batches: 1 Memory Usage: 47712kB
40. 214.824 214.824 ↑ 1.3 280,476 3

Parallel Index Scan using ix_proceso_transaccion_fecha_proceso_transaccion on proceso_transaccion proc (cost=0.43..23,047.89 rows=359,639 width=16) (actual time=0.126..214.824 rows=280,476 loops=3)

  • Index Cond: ((fecha_proceso_transaccion >= '2019-01-01 00:00:00'::timestamp without time zone) AND (fecha_proceso_transaccion <= CURRENT_DATE))
41. 219.798 313.508 ↑ 1.2 510,095 3

Parallel Hash (cost=16,302.80..16,302.80 rows=637,610 width=4) (actual time=313.508..313.508 rows=510,095 loops=3)

  • Buckets: 2097152 Batches: 1 Memory Usage: 76288kB
42. 93.710 93.710 ↑ 1.2 510,095 3

Parallel Index Only Scan using pk_ambulatorio_solicitud on ambulatorio_solicitud aso (cost=0.43..16,302.80 rows=637,610 width=4) (actual time=0.069..93.710 rows=510,095 loops=3)

  • Heap Fetches: 233
43. 0.248 0.554 ↑ 1.0 652 3

Hash (cost=18.52..18.52 rows=652 width=28) (actual time=0.553..0.554 rows=652 loops=3)

  • Buckets: 1024 Batches: 1 Memory Usage: 48kB
44. 0.306 0.306 ↑ 1.0 652 3

Seq Scan on ips_sede sede (cost=0.00..18.52 rows=652 width=28) (actual time=0.042..0.306 rows=652 loops=3)

45. 0.016 0.026 ↑ 1.0 19 3

Hash (cost=1.19..1.19 rows=19 width=4) (actual time=0.026..0.026 rows=19 loops=3)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
46. 0.010 0.010 ↑ 1.0 19 3

Seq Scan on tipo_evento_transaccion tet (cost=0.00..1.19 rows=19 width=4) (actual time=0.005..0.010 rows=19 loops=3)

47. 0.010 0.047 ↑ 1.0 2 3

Hash (cost=1.02..1.02 rows=2 width=6) (actual time=0.047..0.047 rows=2 loops=3)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
48. 0.037 0.037 ↑ 1.0 2 3

Seq Scan on ambito a (cost=0.00..1.02 rows=2 width=6) (actual time=0.036..0.037 rows=2 loops=3)

49. 31.704 69.758 ↑ 1.2 50,143 3

Parallel Hash (cost=9,659.78..9,659.78 rows=62,678 width=41) (actual time=69.758..69.758 rows=50,143 loops=3)

  • Buckets: 262144 Batches: 1 Memory Usage: 13536kB
50. 38.054 38.054 ↑ 1.2 50,143 3

Parallel Seq Scan on beneficiario bene (cost=0.00..9,659.78 rows=62,678 width=41) (actual time=0.048..38.054 rows=50,143 loops=3)

51. 0.013 0.082 ↑ 1.0 9 3

Hash (cost=1.09..1.09 rows=9 width=4) (actual time=0.082..0.082 rows=9 loops=3)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
52. 0.069 0.069 ↑ 1.0 9 3

Seq Scan on tipo_documento td (cost=0.00..1.09 rows=9 width=4) (actual time=0.065..0.069 rows=9 loops=3)

53. 344.265 1,047.172 ↑ 1.2 508,942 3

Parallel Hash (cost=96,629.98..96,629.98 rows=627,203 width=12) (actual time=1,047.172..1,047.172 rows=508,942 loops=3)

  • Buckets: 2097152 Batches: 1 Memory Usage: 88064kB
54. 554.836 702.907 ↑ 1.2 508,942 3

Parallel Bitmap Heap Scan on evento_transaccion etorig (cost=12,851.80..96,629.98 rows=627,203 width=12) (actual time=176.576..702.907 rows=508,942 loops=3)

  • Recheck Cond: (tipo_evento_transaccion_id = ANY ('{17,12}'::integer[]))
  • Filter: (evento_transaccion_original_id IS NULL)
  • Rows Removed by Filter: 1153
  • Heap Blocks: exact=28675
55. 148.071 148.071 ↓ 1.0 1,530,301 1

Bitmap Index Scan on ix_evento_transaccion_tipo_evento_transaccion (cost=0.00..12,475.48 rows=1,505,890 width=0) (actual time=148.071..148.071 rows=1,530,301 loops=1)

  • Index Cond: (tipo_evento_transaccion_id = ANY ('{17,12}'::integer[]))
56. 0.015 0.049 ↑ 1.0 19 3

Hash (cost=1.19..1.19 rows=19 width=15) (actual time=0.049..0.049 rows=19 loops=3)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
57. 0.034 0.034 ↑ 1.0 19 3

Seq Scan on tipo_evento_transaccion tipo (cost=0.00..1.19 rows=19 width=15) (actual time=0.027..0.034 rows=19 loops=3)

58. 0.017 0.085 ↑ 1.0 14 3

Hash (cost=1.14..1.14 rows=14 width=9) (actual time=0.085..0.085 rows=14 loops=3)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
59. 0.068 0.068 ↑ 1.0 14 3

Seq Scan on estado_solicitud_item esi (cost=0.00..1.14 rows=14 width=9) (actual time=0.061..0.068 rows=14 loops=3)

60. 0.003 0.005 ↓ 0.0 0 396,783

Append (cost=0.00..14.01 rows=7 width=12) (actual time=0.005..0.005 rows=0 loops=396,783)

61. 0.000 0.000 ↓ 0.0 0 396,783

Seq Scan on ticket torig (cost=0.00..0.00 rows=1 width=12) (actual time=0.000..0.000 rows=0 loops=396,783)

  • Filter: (evento_transaccion_id = etorig.id)
62. 0.000 0.000 ↓ 0.0 0 396,783

Index Scan using ix_ticket_2015_evento_transaccion on ticket_2015 torig_1 (cost=0.15..1.36 rows=1 width=12) (actual time=0.000..0.000 rows=0 loops=396,783)

  • Index Cond: (evento_transaccion_id = etorig.id)
63. 0.000 0.000 ↓ 0.0 0 396,783

Index Scan using ix_ticket_2016_evento_transaccion on ticket_2016 torig_2 (cost=0.29..2.76 rows=1 width=12) (actual time=0.000..0.000 rows=0 loops=396,783)

  • Index Cond: (evento_transaccion_id = etorig.id)
64. 0.000 0.000 ↓ 0.0 0 396,783

Index Scan using ix_ticket_2017_evento_transaccion on ticket_2017 torig_3 (cost=0.43..2.92 rows=1 width=12) (actual time=0.000..0.000 rows=0 loops=396,783)

  • Index Cond: (evento_transaccion_id = etorig.id)
65. 0.000 0.000 ↓ 0.0 0 396,783

Index Scan using ix_ticket_2018_evento_transaccion on ticket_2018 torig_4 (cost=0.43..2.96 rows=1 width=12) (actual time=0.000..0.000 rows=0 loops=396,783)

  • Index Cond: (evento_transaccion_id = etorig.id)
66. 0.000 0.000 ↓ 0.0 0 396,783

Index Scan using ix_ticket_2019_evento_transaccion on ticket_2019 torig_5 (cost=0.43..2.93 rows=1 width=12) (actual time=0.000..0.000 rows=0 loops=396,783)

  • Index Cond: (evento_transaccion_id = etorig.id)
67. 0.002 0.002 ↓ 0.0 0 396,783

Seq Scan on ticket_otros torig_6 (cost=0.00..1.05 rows=1 width=12) (actual time=0.002..0.002 rows=0 loops=396,783)

  • Filter: (evento_transaccion_id = etorig.id)
  • Rows Removed by Filter: 4
68. 0.515 52.859 ↓ 14.2 355 3

Parallel Hash (cost=31,987.64..31,987.64 rows=25 width=4) (actual time=52.859..52.859 rows=355 loops=3)

  • Buckets: 1024 Batches: 1 Memory Usage: 104kB
69. 1.808 52.344 ↓ 14.2 355 3

Hash Join (cost=385.27..31,987.64 rows=25 width=4) (actual time=9.758..52.344 rows=355 loops=3)

  • Hash Cond: (subet.profesional_especialidad_medica_id = subpem.id)
  • Join Filter: ((subpem.profesional_id = 1) OR (subet.usuario_modelo_id = 49))
  • Rows Removed by Join Filter: 240
70. 36.694 46.394 ↑ 1.6 6,157 3

Parallel Bitmap Heap Scan on evento_transaccion subet (cost=231.63..31,807.36 rows=10,148 width=12) (actual time=5.549..46.394 rows=6,157 loops=3)

  • Recheck Cond: (ips_sede_id = 5)
  • Filter: ((NOT es_anulado) AND (NOT deleted) AND (tipo_evento_transaccion_id <> 10))
  • Rows Removed by Filter: 2604
  • Heap Blocks: exact=8783
71. 9.700 9.700 ↓ 1.1 26,302 1

Bitmap Index Scan on ix_evento_transaccion_ips_sede (cost=0.00..225.54 rows=24,888 width=0) (actual time=9.700..9.700 rows=26,302 loops=1)

  • Index Cond: (ips_sede_id = 5)
72. 1.740 4.142 ↑ 1.0 4,073 3

Hash (cost=102.73..102.73 rows=4,073 width=8) (actual time=4.142..4.142 rows=4,073 loops=3)

  • Buckets: 4096 Batches: 1 Memory Usage: 192kB
73. 2.402 2.402 ↑ 1.0 4,073 3

Seq Scan on profesional_especialidad_medica subpem (cost=0.00..102.73 rows=4,073 width=8) (actual time=0.068..2.402 rows=4,073 loops=3)

74. 0.007 0.007 ↑ 1.0 1 139

Index Scan using pk_profesional_especialidad_medica on profesional_especialidad_medica pe (cost=0.28..2.66 rows=1 width=12) (actual time=0.007..0.007 rows=1 loops=139)

  • Index Cond: (band.profesional_especialidad_medica_id = id)
75. 0.005 0.005 ↑ 1.0 1 139

Index Scan using pk_profesional on profesional prescr (cost=0.28..2.74 rows=1 width=26) (actual time=0.005..0.005 rows=1 loops=139)

  • Index Cond: (pe.profesional_id = id)
76. 0.001 0.001 ↓ 0.0 0 139

Index Only Scan using delegacion_pkey on delegacion dele (cost=0.14..1.15 rows=1 width=4) (actual time=0.001..0.001 rows=0 loops=139)

  • Index Cond: (id = band.delegacion_id)
  • Heap Fetches: 0
77. 0.006 0.006 ↓ 0.0 0 139

Index Scan using ix_evento_transaccion_delegacion_evento_transaccion_id on evento_transaccion_delegacion etd (cost=0.42..2.90 rows=1 width=12) (actual time=0.006..0.006 rows=0 loops=139)

  • Index Cond: (band.id = evento_transaccion_id)