explain.depesz.com

PostgreSQL's explain analyze made readable

Result: eQZv

Settings
# exclusive inclusive rows x rows loops node
1. 5.148 2,388.331 ↑ 1.0 20 1

Limit (cost=176,027.39..176,027.44 rows=20 width=441) (actual time=2,383.184..2,388.331 rows=20 loops=1)

2. 49.164 2,383.183 ↑ 305.9 20 1

Sort (cost=176,027.39..176,042.69 rows=6,119 width=441) (actual time=2,383.181..2,383.183 rows=20 loops=1)

  • Sort Key: aso.numero_solicitud DESC
  • Sort Method: top-N heapsort Memory: 35kB
3. 98.320 2,334.019 ↓ 10.2 62,193 1

WindowAgg (cost=174,681.16..175,864.57 rows=6,119 width=441) (actual time=2,294.729..2,334.019 rows=62,193 loops=1)

4. 80.618 2,235.699 ↓ 10.2 62,193 1

Finalize GroupAggregate (cost=174,681.16..175,726.89 rows=6,119 width=153) (actual time=2,054.541..2,235.699 rows=62,193 loops=1)

  • Group Key: aso.numero_solicitud, proc.fecha_proceso_transaccion, prescr.apellido_nombre, pe.matricula, (CASE WHEN (band.delegacion_id IS NOT NULL) THEN dele.nombre ELSE 'PRESTADOR'::character varying END), band.id, bene.id, tipo.codigo, torig.numero_comprobante
5. 0.000 2,155.081 ↓ 12.2 62,193 1

Gather Merge (cost=174,681.16..175,438.76 rows=5,100 width=169) (actual time=2,054.524..2,155.081 rows=62,193 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
6. 121.866 6,184.758 ↓ 8.1 20,731 3

Partial GroupAggregate (cost=173,681.13..173,850.07 rows=2,550 width=169) (actual time=2,016.231..2,061.586 rows=20,731 loops=3)

  • Group Key: aso.numero_solicitud, proc.fecha_proceso_transaccion, prescr.apellido_nombre, pe.matricula, (CASE WHEN (band.delegacion_id IS NOT NULL) THEN dele.nombre ELSE 'PRESTADOR'::character varying END), band.id, bene.id, tipo.codigo, torig.numero_comprobante
7. 154.581 6,062.892 ↓ 10.2 25,949 3

Sort (cost=173,681.13..173,687.51 rows=2,550 width=144) (actual time=2,016.189..2,020.964 rows=25,949 loops=3)

  • Sort Key: aso.numero_solicitud, proc.fecha_proceso_transaccion, prescr.apellido_nombre, pe.matricula, (CASE WHEN (band.delegacion_id IS NOT NULL) THEN dele.nombre ELSE 'PRESTADOR'::character varying END), band.id, bene.id, tipo.codigo, torig.numero_comprobante
  • Sort Method: quicksort Memory: 7865kB
  • Worker 0: Sort Method: quicksort Memory: 7568kB
  • Worker 1: Sort Method: quicksort Memory: 7541kB
8. 38.880 5,908.311 ↓ 10.2 25,949 3

Hash Left Join (cost=148,487.25..173,536.85 rows=2,550 width=144) (actual time=1,581.327..1,969.437 rows=25,949 loops=3)

  • Hash Cond: (band.delegacion_id = dele.id)
9. 47.751 5,869.305 ↓ 10.2 25,949 3

Hash Left Join (cost=148,485.59..173,528.26 rows=2,550 width=116) (actual time=1,581.236..1,956.435 rows=25,949 loops=3)

  • Hash Cond: (pe.profesional_id = prescr.id)
10. 69.684 5,813.835 ↓ 10.2 25,949 3

Hash Left Join (cost=148,329.41..173,365.37 rows=2,550 width=98) (actual time=1,578.634..1,937.945 rows=25,949 loops=3)

  • Hash Cond: (band.profesional_especialidad_medica_id = pe.id)
11. 61.313 5,737.725 ↓ 10.2 25,949 3

Nested Loop Left Join (cost=148,174.14..173,203.40 rows=2,550 width=94) (actual time=1,576.428..1,912.575 rows=25,949 loops=3)

12. 92.985 5,365.020 ↓ 10.2 25,949 3

Nested Loop Left Join (cost=148,174.14..152,934.11 rows=2,550 width=94) (actual time=1,576.385..1,788.340 rows=25,949 loops=3)

13. 58.605 4,804.947 ↓ 10.2 25,949 3

Merge Join (cost=148,173.71..148,212.01 rows=2,550 width=98) (actual time=1,576.334..1,601.649 rows=25,949 loops=3)

  • Merge Cond: (band.tipo_evento_transaccion_id = tipo.id)
14. 103.161 4,746.129 ↓ 10.2 25,949 3

Sort (cost=148,172.07..148,178.45 rows=2,550 width=87) (actual time=1,576.251..1,582.043 rows=25,949 loops=3)

  • Sort Key: band.tipo_evento_transaccion_id
  • Sort Method: quicksort Memory: 4550kB
  • Worker 0: Sort Method: quicksort Memory: 4389kB
  • Worker 1: Sort Method: quicksort Memory: 4378kB
15. 53.973 4,642.968 ↓ 10.2 25,949 3

Hash Join (cost=102,708.38..148,027.79 rows=2,550 width=87) (actual time=1,038.049..1,547.656 rows=25,949 loops=3)

  • Hash Cond: (etiep.estado_solicitud_item_id = esi.id)
16. 94.400 4,588.764 ↓ 10.2 25,949 3

Nested Loop (cost=102,707.07..148,017.76 rows=2,550 width=86) (actual time=1,037.902..1,529.588 rows=25,949 loops=3)

17. 45.520 4,182.972 ↓ 10.2 25,949 3

Nested Loop (cost=102,706.65..143,633.41 rows=2,550 width=53) (actual time=1,037.868..1,394.324 rows=25,949 loops=3)

18. 46.962 3,828.588 ↓ 9.8 25,739 3

Nested Loop (cost=102,706.22..139,128.39 rows=2,627 width=51) (actual time=1,037.831..1,276.196 rows=25,739 loops=3)

19. 483.666 3,470.661 ↓ 2.8 20,731 3

Parallel Hash Join (cost=102,705.79..125,838.50 rows=7,353 width=51) (actual time=1,037.749..1,156.887 rows=20,731 loops=3)

  • Hash Cond: (aso.id = band.id)
20. 191.805 191.805 ↑ 1.2 510,317 3

Parallel Seq Scan on ambulatorio_solicitud aso (cost=0.00..20,709.96 rows=637,896 width=8) (actual time=0.012..63.935 rows=510,317 loops=3)

21. 34.320 2,795.190 ↑ 1.2 20,731 3

Parallel Hash (cost=102,398.44..102,398.44 rows=24,588 width=43) (actual time=931.729..931.730 rows=20,731 loops=3)

  • Buckets: 65536 Batches: 1 Memory Usage: 6016kB
22. 593.349 2,760.870 ↑ 1.2 20,731 3

Parallel Hash Join (cost=16,723.95..102,398.44 rows=24,588 width=43) (actual time=620.579..920.290 rows=20,731 loops=3)

  • Hash Cond: (band.proceso_transaccion_id = proc.id)
23. 1,783.897 1,974.081 ↑ 1.3 495,275 3

Parallel Bitmap Heap Scan on evento_transaccion band (cost=13,088.75..97,103.53 rows=632,270 width=31) (actual time=215.982..658.027 rows=495,275 loops=3)

  • Recheck Cond: (tipo_evento_transaccion_id = ANY ('{12,17}'::integer[]))
  • Filter: ((deleted IS FALSE) AND (es_anulado IS FALSE))
  • Rows Removed by Filter: 15042
  • Heap Blocks: exact=26286
24. 190.184 190.184 ↑ 1.0 1,530,954 1

Bitmap Index Scan on ix_evento_transaccion_tipo_evento_transaccion (cost=0.00..12,709.39 rows=1,536,150 width=0) (actual time=190.184..190.184 rows=1,530,954 loops=1)

  • Index Cond: (tipo_evento_transaccion_id = ANY ('{12,17}'::integer[]))
25. 108.360 193.440 ↑ 1.2 56,486 3

Parallel Hash (cost=2,816.05..2,816.05 rows=65,532 width=16) (actual time=64.479..64.480 rows=56,486 loops=3)

  • Buckets: 262144 Batches: 1 Memory Usage: 10080kB
26. 85.080 85.080 ↑ 1.2 56,486 3

Parallel Index Only Scan using idx_proceso_transaccion_fecha_bene on proceso_transaccion proc (cost=0.43..2,816.05 rows=65,532 width=16) (actual time=0.086..28.360 rows=56,486 loops=3)

  • Index Cond: ((fecha_proceso_transaccion >= '2019-01-01 00:00:00'::timestamp without time zone) AND (fecha_proceso_transaccion <= '2019-01-31 00:00:00'::timestamp without time zone))
  • Heap Fetches: 0
27. 310.965 310.965 ↑ 3.0 1 62,193

Index Scan using ix_evento_transaccion_item_evento_transaccion_id on evento_transaccion_item eti (cost=0.43..1.78 rows=3 width=8) (actual time=0.004..0.005 rows=1 loops=62,193)

  • Index Cond: (evento_transaccion_id = aso.id)
  • Filter: ((deleted IS FALSE) AND (es_anulado IS FALSE))
28. 308.864 308.864 ↑ 1.0 1 77,216

Index Scan using idx_evento_transaccion_item_ep_evento_estado on evento_transaccion_item_estado_parcial etiep (cost=0.43..1.70 rows=1 width=10) (actual time=0.003..0.004 rows=1 loops=77,216)

  • Index Cond: (evento_transaccion_item_id = eti.id)
  • Filter: ((deleted IS FALSE) AND (es_anulado IS FALSE))
29. 311.392 311.392 ↑ 1.0 1 77,848

Index Scan using pk_beneficiario on beneficiario bene (cost=0.42..1.72 rows=1 width=37) (actual time=0.004..0.004 rows=1 loops=77,848)

  • Index Cond: (id = proc.beneficiario_id)
30. 0.054 0.231 ↑ 1.0 14 3

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
31. 0.177 0.177 ↑ 1.0 14 3

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

32. 0.069 0.213 ↑ 1.1 17 3

Sort (cost=1.59..1.64 rows=19 width=15) (actual time=0.069..0.071 rows=17 loops=3)

  • Sort Key: tipo.id
  • Sort Method: quicksort Memory: 25kB
  • Worker 0: Sort Method: quicksort Memory: 25kB
  • Worker 1: Sort Method: quicksort Memory: 25kB
33. 0.144 0.144 ↑ 1.0 19 3

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

34. 467.088 467.088 ↓ 0.0 0 77,848

Index Scan using ix_evento_transaccion_proceso_transaccion_id on evento_transaccion etorig (cost=0.43..1.84 rows=1 width=12) (actual time=0.006..0.006 rows=0 loops=77,848)

  • Index Cond: (proceso_transaccion_id = band.proceso_transaccion_id)
  • Filter: ((evento_transaccion_original_id IS NULL) AND (tipo_evento_transaccion_id = ANY ('{17,12}'::integer[])) AND (id <> band.id) AND (tipo_evento_transaccion_id = band.tipo_evento_transaccion_id))
  • Rows Removed by Filter: 2
35. 311.392 311.392 ↓ 0.0 0 77,848

Append (cost=0.00..7.88 rows=7 width=8) (actual time=0.004..0.004 rows=0 loops=77,848)

36. 0.000 0.000 ↓ 0.0 0 77,848

Seq Scan on ticket torig (cost=0.00..0.00 rows=1 width=8) (actual time=0.000..0.000 rows=0 loops=77,848)

  • Filter: (evento_transaccion_id = etorig.id)
37. 0.000 0.000 ↓ 0.0 0 77,848

Index Scan using ix_ticket_2015_evento_transaccion on ticket_2015 torig_1 (cost=0.15..0.76 rows=1 width=8) (actual time=0.000..0.000 rows=0 loops=77,848)

  • Index Cond: (evento_transaccion_id = etorig.id)
38. 0.000 0.000 ↓ 0.0 0 77,848

Index Scan using ix_ticket_2016_evento_transaccion on ticket_2016 torig_2 (cost=0.29..1.54 rows=1 width=8) (actual time=0.000..0.000 rows=0 loops=77,848)

  • Index Cond: (evento_transaccion_id = etorig.id)
39. 0.000 0.000 ↓ 0.0 0 77,848

Index Scan using ix_ticket_2017_evento_transaccion on ticket_2017 torig_3 (cost=0.43..1.76 rows=1 width=8) (actual time=0.000..0.000 rows=0 loops=77,848)

  • Index Cond: (evento_transaccion_id = etorig.id)
40. 0.000 0.000 ↓ 0.0 0 77,848

Index Scan using ix_ticket_2018_evento_transaccion on ticket_2018 torig_4 (cost=0.43..1.88 rows=1 width=8) (actual time=0.000..0.000 rows=0 loops=77,848)

  • Index Cond: (evento_transaccion_id = etorig.id)
41. 0.000 0.000 ↓ 0.0 0 77,848

Index Scan using ix_ticket_2019_evento_transaccion on ticket_2019 torig_5 (cost=0.43..1.77 rows=1 width=8) (actual time=0.000..0.000 rows=0 loops=77,848)

  • Index Cond: (evento_transaccion_id = etorig.id)
42. 0.000 0.000 ↓ 0.0 0 77,848

Index Scan using ix_ticket_otros_evento_transaccion on ticket_otros torig_6 (cost=0.13..0.14 rows=1 width=8) (actual time=0.000..0.000 rows=0 loops=77,848)

  • Index Cond: (evento_transaccion_id = etorig.id)
43. 3.222 6.426 ↑ 1.0 4,101 3

Hash (cost=104.01..104.01 rows=4,101 width=12) (actual time=2.142..2.142 rows=4,101 loops=3)

  • Buckets: 8192 Batches: 1 Memory Usage: 249kB
44. 3.204 3.204 ↑ 1.0 4,101 3

Seq Scan on profesional_especialidad_medica pe (cost=0.00..104.01 rows=4,101 width=12) (actual time=0.021..1.068 rows=4,101 loops=3)

45. 3.435 7.719 ↑ 1.0 3,697 3

Hash (cost=109.97..109.97 rows=3,697 width=26) (actual time=2.573..2.573 rows=3,697 loops=3)

  • Buckets: 4096 Batches: 1 Memory Usage: 248kB
46. 4.284 4.284 ↑ 1.0 3,697 3

Seq Scan on profesional prescr (cost=0.00..109.97 rows=3,697 width=26) (actual time=0.022..1.428 rows=3,697 loops=3)

47. 0.042 0.126 ↑ 1.0 29 3

Hash (cost=1.29..1.29 rows=29 width=14) (actual time=0.042..0.042 rows=29 loops=3)

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
48. 0.084 0.084 ↑ 1.0 29 3

Seq Scan on delegacion dele (cost=0.00..1.29 rows=29 width=14) (actual time=0.020..0.028 rows=29 loops=3)

Planning time : 21.889 ms
Execution time : 2,390.148 ms