explain.depesz.com

PostgreSQL's explain analyze made readable

Result: kj3G

Settings
# exclusive inclusive rows x rows loops node
1. 4.806 2,913.615 ↑ 1.0 20 1

Limit (cost=209,131.52..209,131.57 rows=20 width=441) (actual time=2,908.810..2,913.615 rows=20 loops=1)

2. 40.803 2,908.809 ↑ 320.8 20 1

Sort (cost=209,131.52..209,147.56 rows=6,416 width=441) (actual time=2,908.807..2,908.809 rows=20 loops=1)

  • Sort Key: aso.numero_solicitud DESC
  • Sort Method: top-N heapsort Memory: 35kB
3. 117.527 2,868.006 ↓ 9.7 62,184 1

WindowAgg (cost=207,720.22..208,960.79 rows=6,416 width=441) (actual time=2,826.051..2,868.006 rows=62,184 loops=1)

4. 68.956 2,750.479 ↓ 9.7 62,184 1

Finalize GroupAggregate (cost=207,720.22..208,816.43 rows=6,416 width=153) (actual time=2,607.531..2,750.479 rows=62,184 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,681.523 ↓ 11.6 62,184 1

Gather Merge (cost=207,720.22..208,514.36 rows=5,346 width=169) (actual time=2,607.512..2,681.523 rows=62,184 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
6. 102.405 7,836.861 ↓ 7.8 20,728 3

Partial GroupAggregate (cost=206,720.19..206,897.28 rows=2,673 width=169) (actual time=2,570.022..2,612.287 rows=20,728 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. 188.907 7,734.456 ↓ 9.7 25,939 3

Sort (cost=206,720.19..206,726.87 rows=2,673 width=142) (actual time=2,569.989..2,578.152 rows=25,939 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: external merge Disk: 3560kB
  • Worker 0: Sort Method: external merge Disk: 3776kB
  • Worker 1: Sort Method: external merge Disk: 3736kB
8. 33.672 7,545.549 ↓ 9.7 25,939 3

Hash Left Join (cost=188,369.64..206,568.04 rows=2,673 width=142) (actual time=2,112.519..2,515.183 rows=25,939 loops=3)

  • Hash Cond: (band.delegacion_id = dele.id)
9. 41.493 7,511.733 ↓ 9.7 25,939 3

Hash Left Join (cost=188,367.98..206,559.13 rows=2,673 width=114) (actual time=2,112.432..2,503.911 rows=25,939 loops=3)

  • Hash Cond: (pe.profesional_id = prescr.id)
10. 63.237 7,462.368 ↓ 9.7 25,939 3

Hash Left Join (cost=188,210.84..206,394.96 rows=2,673 width=96) (actual time=2,109.769..2,487.456 rows=25,939 loops=3)

  • Hash Cond: (band.profesional_especialidad_medica_id = pe.id)
11. 42.765 7,391.904 ↓ 9.7 25,939 3

Nested Loop Left Join (cost=188,052.52..206,229.62 rows=2,673 width=92) (actual time=2,107.335..2,463.968 rows=25,939 loops=3)

12. 79.551 6,960.054 ↓ 9.7 25,939 3

Nested Loop Left Join (cost=188,052.52..191,550.81 rows=2,673 width=92) (actual time=2,107.284..2,320.018 rows=25,939 loops=3)

13. 45.645 6,413.601 ↓ 9.7 25,939 3

Merge Join (cost=188,052.09..188,092.24 rows=2,673 width=96) (actual time=2,107.205..2,137.867 rows=25,939 loops=3)

  • Merge Cond: (band.tipo_evento_transaccion_id = tipo.id)
14. 153.408 6,367.704 ↓ 9.7 25,939 3

Sort (cost=188,050.46..188,057.14 rows=2,673 width=85) (actual time=2,107.108..2,122.568 rows=25,939 loops=3)

  • Sort Key: band.tipo_evento_transaccion_id
  • Sort Method: external merge Disk: 2608kB
  • Worker 0: Sort Method: external merge Disk: 2760kB
  • Worker 1: Sort Method: external merge Disk: 2744kB
15. 43.548 6,214.296 ↓ 9.7 25,939 3

Hash Join (cost=141,979.88..187,898.31 rows=2,673 width=85) (actual time=1,538.648..2,071.432 rows=25,939 loops=3)

  • Hash Cond: (etiep.estado_solicitud_item_id = esi.id)
16. 103.140 6,170.430 ↓ 9.7 25,939 3

Nested Loop (cost=141,978.63..187,887.34 rows=2,673 width=86) (actual time=1,538.444..2,056.810 rows=25,939 loops=3)

17. 34.619 5,756.022 ↓ 9.7 25,939 3

Nested Loop (cost=141,978.21..184,877.64 rows=2,673 width=53) (actual time=1,538.382..1,918.674 rows=25,939 loops=3)

18. 28.971 5,412.663 ↓ 9.3 25,728 3

Nested Loop (cost=141,977.78..181,683.39 rows=2,776 width=51) (actual time=1,538.324..1,804.221 rows=25,728 loops=3)

19. 569.901 5,072.772 ↓ 2.6 20,728 3

Parallel Hash Join (cost=141,977.35..171,516.37 rows=7,943 width=51) (actual time=1,538.236..1,690.924 rows=20,728 loops=3)

  • Hash Cond: (aso.id = band.id)
20. 227.634 227.634 ↑ 1.2 651,865 3

Parallel Seq Scan on ambulatorio_solicitud aso (cost=0.00..26,450.31 rows=814,831 width=8) (actual time=0.013..75.878 rows=651,865 loops=3)

21. 50.721 4,275.237 ↑ 1.3 20,728 3

Parallel Hash (cost=141,634.38..141,634.38 rows=27,438 width=43) (actual time=1,425.079..1,425.079 rows=20,728 loops=3)

  • Buckets: 131072 Batches: 1 Memory Usage: 6496kB
22. 723.213 4,224.516 ↑ 1.3 20,728 3

Parallel Hash Join (cost=4,061.46..141,634.38 rows=27,438 width=43) (actual time=79.922..1,408.172 rows=20,728 loops=3)

  • Hash Cond: (band.proceso_transaccion_id = proc.id)
23. 3,309.105 3,309.105 ↑ 1.3 632,143 3

Parallel Seq Scan on evento_transaccion band (cost=0.00..135,444.83 rows=810,699 width=31) (actual time=2.549..1,103.035 rows=632,143 loops=3)

  • Filter: ((deleted IS FALSE) AND (es_anulado IS FALSE) AND (tipo_evento_transaccion_id = ANY ('{12,17}'::integer[])))
  • Rows Removed by Filter: 1619922
24. 106.095 192.198 ↑ 1.3 56,486 3

Parallel Hash (cost=3,145.13..3,145.13 rows=73,307 width=16) (actual time=64.066..64.066 rows=56,486 loops=3)

  • Buckets: 262144 Batches: 1 Memory Usage: 10048kB
25. 86.103 86.103 ↑ 1.3 56,486 3

Parallel Index Only Scan using idx_proceso_transaccion_fecha_bene on proceso_transaccion proc (cost=0.43..3,145.13 rows=73,307 width=16) (actual time=0.138..28.701 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
26. 310.920 310.920 ↑ 5.0 1 62,184

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

  • Index Cond: (evento_transaccion_id = aso.id)
  • Filter: ((deleted IS FALSE) AND (es_anulado IS FALSE))
27. 308.740 308.740 ↑ 1.0 1 77,185

Index Scan using ix_evento_transaccion_item_estado_parcial_evento_transaccion_it on evento_transaccion_item_estado_parcial etiep (cost=0.43..1.14 rows=1 width=10) (actual time=0.003..0.004 rows=1 loops=77,185)

  • Index Cond: (evento_transaccion_item_id = eti.id)
  • Filter: ((deleted IS FALSE) AND (es_anulado IS FALSE))
28. 311.268 311.268 ↑ 1.0 1 77,817

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

  • Index Cond: (id = proc.beneficiario_id)
29. 0.051 0.318 ↑ 1.0 11 3

Hash (cost=1.11..1.11 rows=11 width=7) (actual time=0.105..0.106 rows=11 loops=3)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
30. 0.267 0.267 ↑ 1.0 11 3

Seq Scan on estado_solicitud_item esi (cost=0.00..1.11 rows=11 width=7) (actual time=0.080..0.089 rows=11 loops=3)

31. 0.057 0.252 ↑ 1.1 17 3

Sort (cost=1.59..1.64 rows=19 width=15) (actual time=0.082..0.084 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
32. 0.195 0.195 ↑ 1.0 19 3

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

33. 466.902 466.902 ↓ 0.0 0 77,817

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

  • 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
34. 389.085 389.085 ↓ 0.0 0 77,817

Append (cost=0.00..5.42 rows=7 width=8) (actual time=0.005..0.005 rows=0 loops=77,817)

35. 0.000 0.000 ↓ 0.0 0 77,817

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

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

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

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

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

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

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

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

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

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

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

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

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,817)

  • Index Cond: (evento_transaccion_id = etorig.id)
42. 3.186 7.227 ↑ 1.0 4,192 3

Hash (cost=105.92..105.92 rows=4,192 width=12) (actual time=2.409..2.409 rows=4,192 loops=3)

  • Buckets: 8192 Batches: 1 Memory Usage: 253kB
43. 4.041 4.041 ↑ 1.0 4,192 3

Seq Scan on profesional_especialidad_medica pe (cost=0.00..105.92 rows=4,192 width=12) (actual time=0.021..1.347 rows=4,192 loops=3)

44. 3.150 7.872 ↑ 1.0 3,784 3

Hash (cost=109.84..109.84 rows=3,784 width=26) (actual time=2.623..2.624 rows=3,784 loops=3)

  • Buckets: 4096 Batches: 1 Memory Usage: 253kB
45. 4.722 4.722 ↑ 1.0 3,784 3

Seq Scan on profesional prescr (cost=0.00..109.84 rows=3,784 width=26) (actual time=0.031..1.574 rows=3,784 loops=3)

46. 0.036 0.144 ↑ 1.0 29 3

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

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
47. 0.108 0.108 ↑ 1.0 29 3

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

Planning time : 39.916 ms
Execution time : 2,917.535 ms