explain.depesz.com

PostgreSQL's explain analyze made readable

Result: LgPj

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 0.010 32,001.397 ↓ 55.0 55 1

Limit (cost=329,389,356.23..358,591,741.77 rows=1 width=843) (actual time=31,593.362..32,001.397 rows=55 loops=1)

2. 6.457 32,001.387 ↓ 55.0 55 1

Nested Loop (cost=329,389,356.23..358,591,741.77 rows=1 width=843) (actual time=31,593.361..32,001.387 rows=55 loops=1)

  • Join Filter: (competencia.id = fibocompetencia.id)
  • Rows Removed by Join Filter: 58,915
3. 0.975 0.975 ↑ 1.0 1 1

Index Scan using fibocompetencia_comp_convenio_comp_desc_unique on fibocompetencia (cost=0.28..8.30 rows=1 width=8) (actual time=0.973..0.975 rows=1 loops=1)

  • Index Cond: ((comp_convenio = 'Amil'::text) AND (comp_desc = '06-2019'::text))
4. 1,127.193 31,993.955 ↑ 21.1 58,970 1

Nested Loop Left Join (cost=329,389,355.95..358,563,802.31 rows=1,241,384 width=891) (actual time=29,130.322..31,993.955 rows=58,970 loops=1)

5. 27.517 29,333.542 ↑ 21.1 58,970 1

Hash Left Join (cost=329,389,347.50..347,714,106.15 rows=1,241,384 width=747) (actual time=29,129.268..29,333.542 rows=58,970 loops=1)

  • Hash Cond: (COALESCE(remessa_importada.competencia_id, valor_esperado.competencia_id) = competencia.id)
6. 27.062 29,305.495 ↑ 21.1 58,970 1

Hash Join (cost=329,389,254.57..347,710,748.93 rows=1,241,384 width=747) (actual time=29,128.731..29,305.495 rows=58,970 loops=1)

  • Hash Cond: (COALESCE(remessa_importada.convenio_id, valor_esperado.convenio_id) = convenio.id)
7. 25.837 29,278.289 ↑ 21.1 58,970 1

Hash Join (cost=329,389,236.05..347,707,403.07 rows=1,241,384 width=727) (actual time=29,128.582..29,278.289 rows=58,970 loops=1)

  • Hash Cond: (COALESCE(remessa_importada.operadora_id, valor_esperado.operadora_id) = operadora.operadora_id)
8. 28.513 29,252.175 ↑ 87.7 58,970 1

Hash Join (cost=329,389,231.97..347,693,533.64 rows=5,172,434 width=727) (actual time=29,128.300..29,252.175 rows=58,970 loops=1)

  • Hash Cond: (COALESCE(remessa_importada.hospital_id, valor_esperado.hospital_id) = hospital.id)
9. 69.216 29,223.658 ↑ 17,542.6 58,970 1

Merge Full Join (cost=329,389,230.95..344,920,461.29 rows=1,034,486,854 width=607) (actual time=29,128.291..29,223.658 rows=58,970 loops=1)

  • Merge Cond: (valor_esperado.entidade_id = remessa_importada.entidade_id)
10. 21.072 141.796 ↑ 1.0 37,647 1

Sort (cost=26,163.34..26,257.46 rows=37,647 width=132) (actual time=138.416..141.796 rows=37,647 loops=1)

  • Sort Key: valor_esperado.entidade_id
  • Sort Method: quicksort Memory: 11,536kB
11. 6.267 120.724 ↑ 1.0 37,647 1

Subquery Scan on valor_esperado (cost=119.63..23,302.13 rows=37,647 width=132) (actual time=0.847..120.724 rows=37,647 loops=1)

12. 85.195 114.457 ↑ 1.0 37,647 1

Hash Left Join (cost=119.63..22,925.66 rows=37,647 width=140) (actual time=0.846..114.457 rows=37,647 loops=1)

  • Hash Cond: ((COALESCE(convenio_1.convnome, valor_faturado.convenio_integracao) = competencia_1.comp_convenio) AND (valor_faturado.competencia = competencia_1.comp_desc))
13. 8.499 28.580 ↑ 1.0 37,647 1

Hash Join (cost=20.60..1,452.52 rows=37,647 width=131) (actual time=0.132..28.580 rows=37,647 loops=1)

  • Hash Cond: (operadora_1.opconvenio = convenio_1.id)
14. 13.425 19.972 ↑ 1.0 37,647 1

Hash Join (cost=2.08..1,333.10 rows=37,647 width=111) (actual time=0.020..19.972 rows=37,647 loops=1)

  • Hash Cond: (valor_faturado.id_operadora = operadora_1.id)
15. 6.535 6.535 ↑ 1.0 37,647 1

Seq Scan on fibovaloresfaturadoserp valor_faturado (cost=0.00..1,223.47 rows=37,647 width=103) (actual time=0.004..6.535 rows=37,647 loops=1)

  • Filter: (numero_remessa IS NOT NULL)
16. 0.005 0.012 ↑ 1.0 48 1

Hash (cost=1.48..1.48 rows=48 width=16) (actual time=0.012..0.012 rows=48 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 11kB
17. 0.007 0.007 ↑ 1.0 48 1

Seq Scan on fibooperadora operadora_1 (cost=0.00..1.48 rows=48 width=16) (actual time=0.003..0.007 rows=48 loops=1)

18. 0.028 0.109 ↑ 1.0 201 1

Hash (cost=16.01..16.01 rows=201 width=28) (actual time=0.109..0.109 rows=201 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 20kB
19. 0.081 0.081 ↑ 1.0 201 1

Seq Scan on fiboconvenio convenio_1 (cost=0.00..16.01 rows=201 width=28) (actual time=0.002..0.081 rows=201 loops=1)

20. 0.411 0.682 ↑ 1.0 2,441 1

Hash (cost=62.41..62.41 rows=2,441 width=27) (actual time=0.682..0.682 rows=2,441 loops=1)

  • Buckets: 4,096 Batches: 1 Memory Usage: 175kB
21. 0.271 0.271 ↑ 1.0 2,441 1

Seq Scan on fibocompetencia competencia_1 (cost=0.00..62.41 rows=2,441 width=27) (actual time=0.004..0.271 rows=2,441 loops=1)

22. 14.995 29,012.646 ↑ 94.3 58,270 1

Materialize (cost=329,363,067.60..329,390,546.20 rows=5,495,720 width=475) (actual time=28,989.870..29,012.646 rows=58,270 loops=1)

23. 76.062 28,997.651 ↑ 94.3 58,270 1

Sort (cost=329,363,067.60..329,376,806.90 rows=5,495,720 width=475) (actual time=28,989.866..28,997.651 rows=58,270 loops=1)

  • Sort Key: remessa_importada.entidade_id
  • Sort Method: quicksort Memory: 17,016kB
24. 16.851 28,921.589 ↑ 94.3 58,270 1

Subquery Scan on remessa_importada (cost=2,976,139.42..327,564,419.13 rows=5,495,720 width=475) (actual time=15,073.095..28,921.589 rows=58,270 loops=1)

25. 12,914.300 28,904.738 ↑ 94.3 58,270 1

GroupAggregate (cost=2,976,139.42..327,509,461.93 rows=5,495,720 width=499) (actual time=15,073.094..28,904.738 rows=58,270 loops=1)

  • Group Key: remessa.id, justificativa.id, sistema_de_gestao.hospital_id, convenio_2.id, operadora_2.id, competencia_2.id, rpa.id
26. 2,733.512 15,698.655 ↑ 1.8 2,987,733 1

Sort (cost=2,976,139.42..2,989,878.72 rows=5,495,720 width=455) (actual time=15,071.470..15,698.655 rows=2,987,733 loops=1)

  • Sort Key: remessa.id, justificativa.id, sistema_de_gestao.hospital_id, convenio_2.id, operadora_2.id, competencia_2.id, rpa.id
  • Sort Method: external merge Disk: 593,144kB
27. 823.185 12,965.143 ↑ 1.8 2,987,733 1

Hash Left Join (cost=524,675.02..1,233,844.44 rows=5,495,720 width=455) (actual time=3,422.515..12,965.143 rows=2,987,733 loops=1)

  • Hash Cond: ((convenio_2.convnome = rpa.nome_convenio) AND (remessa.rem_numero = rpa.numero_remessa))
28. 591.017 12,141.957 ↑ 1.8 2,987,733 1

Nested Loop (cost=524,659.52..1,204,976.41 rows=5,495,720 width=259) (actual time=3,422.510..12,141.957 rows=2,987,733 loops=1)

  • Join Filter: (remessa.sistema_de_gestao_id = sistema_de_gestao.id)
29. 0.005 0.005 ↑ 1.0 1 1

Seq Scan on sistema_de_gestao (cost=0.00..1.01 rows=1 width=16) (actual time=0.004..0.005 rows=1 loops=1)

30. 712.522 11,550.935 ↑ 1.8 2,987,733 1

Hash Left Join (cost=524,659.52..1,136,278.90 rows=5,495,720 width=251) (actual time=3,422.503..11,550.935 rows=2,987,733 loops=1)

  • Hash Cond: ((remessa.rem_numero = justificativa.numero_remessa) AND (remessa.sistema_de_gestao_id = justificativa.sistema_de_gestao_id))
31. 653.321 10,838.404 ↑ 1.8 2,987,733 1

Hash Left Join (cost=524,658.19..1,107,424.96 rows=5,495,720 width=179) (actual time=3,422.487..10,838.404 rows=2,987,733 loops=1)

  • Hash Cond: (guia_convenio.recebimento_id = recebimento.id)
32. 817.935 10,183.763 ↑ 1.8 2,987,733 1

Hash Left Join (cost=524,373.66..1,092,709.64 rows=5,495,720 width=183) (actual time=3,421.158..10,183.763 rows=2,987,733 loops=1)

  • Hash Cond: (guia_convenio.guiprotocolo = protocolo_convenio.id)
33. 1,181.729 9,022.303 ↑ 1.8 2,987,733 1

Hash Join (cost=473,120.95..1,027,030.61 rows=5,495,720 width=183) (actual time=3,076.994..9,022.303 rows=2,987,733 loops=1)

  • Hash Cond: (guia_prestador.guiremessa = remessa.id)
34. 2,631.596 7,774.593 ↑ 1.0 5,491,696 1

Hash Left Join (cost=470,021.44..972,258.52 rows=5,495,720 width=46) (actual time=3,010.990..7,774.593 rows=5,491,696 loops=1)

  • Hash Cond: (guia_prestador.guiguiaassoc = guia_convenio.id)
35. 2,132.762 2,132.762 ↑ 1.0 5,491,696 1

Seq Scan on fiboguia guia_prestador (cost=0.00..379,069.80 rows=5,495,720 width=30) (actual time=0.008..2,132.762 rows=5,491,696 loops=1)

  • Filter: (discriminador <> 'RECURSO'::text)
  • Rows Removed by Filter: 243,168
36. 828.138 3,010.235 ↑ 1.0 5,734,864 1

Hash (cost=364,732.64..364,732.64 rows=5,734,864 width=24) (actual time=3,010.235..3,010.235 rows=5,734,864 loops=1)

  • Buckets: 2,097,152 Batches: 4 Memory Usage: 88,271kB
37. 2,182.097 2,182.097 ↑ 1.0 5,734,864 1

Seq Scan on fiboguia guia_convenio (cost=0.00..364,732.64 rows=5,734,864 width=24) (actual time=0.012..2,182.097 rows=5,734,864 loops=1)

38. 17.528 65.981 ↑ 1.0 58,270 1

Hash (cost=2,371.12..2,371.12 rows=58,271 width=145) (actual time=65.981..65.981 rows=58,270 loops=1)

  • Buckets: 65,536 Batches: 1 Memory Usage: 11,877kB
39. 11.564 48.453 ↑ 1.0 58,270 1

Hash Join (cost=113.53..2,371.12 rows=58,271 width=145) (actual time=0.692..48.453 rows=58,270 loops=1)

  • Hash Cond: (remessa.operadora_id = operadora_2.id)
40. 12.697 36.877 ↑ 1.0 58,270 1

Hash Join (cost=111.45..2,202.57 rows=58,271 width=145) (actual time=0.675..36.877 rows=58,270 loops=1)

  • Hash Cond: (competencia_2.comp_convenio = convenio_2.convnome)
41. 11.480 24.058 ↑ 1.0 58,270 1

Hash Join (cost=92.92..2,027.86 rows=58,271 width=128) (actual time=0.550..24.058 rows=58,270 loops=1)

  • Hash Cond: (remessa.remcompetencia = competencia_2.id)
42. 12.037 12.037 ↑ 1.0 58,270 1

Seq Scan on fiboremessa remessa (cost=0.00..1,781.71 rows=58,271 width=117) (actual time=0.005..12.037 rows=58,270 loops=1)

  • Filter: ((rem_numero IS NOT NULL) AND (sistema_de_gestao_id IS NOT NULL))
  • Rows Removed by Filter: 1
43. 0.276 0.541 ↑ 1.0 2,441 1

Hash (cost=62.41..62.41 rows=2,441 width=19) (actual time=0.540..0.541 rows=2,441 loops=1)

  • Buckets: 4,096 Batches: 1 Memory Usage: 156kB
44. 0.265 0.265 ↑ 1.0 2,441 1

Seq Scan on fibocompetencia competencia_2 (cost=0.00..62.41 rows=2,441 width=19) (actual time=0.004..0.265 rows=2,441 loops=1)

45. 0.034 0.122 ↑ 1.0 201 1

Hash (cost=16.01..16.01 rows=201 width=28) (actual time=0.122..0.122 rows=201 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 20kB
46. 0.088 0.088 ↑ 1.0 201 1

Seq Scan on fiboconvenio convenio_2 (cost=0.00..16.01 rows=201 width=28) (actual time=0.004..0.088 rows=201 loops=1)

47. 0.005 0.012 ↑ 1.0 48 1

Hash (cost=1.48..1.48 rows=48 width=8) (actual time=0.012..0.012 rows=48 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 10kB
48. 0.007 0.007 ↑ 1.0 48 1

Seq Scan on fibooperadora operadora_2 (cost=0.00..1.48 rows=48 width=8) (actual time=0.003..0.007 rows=48 loops=1)

49. 214.866 343.525 ↑ 1.0 1,276,343 1

Hash (cost=35,298.43..35,298.43 rows=1,276,343 width=16) (actual time=343.525..343.525 rows=1,276,343 loops=1)

  • Buckets: 2,097,152 Batches: 1 Memory Usage: 74,617kB
50. 128.659 128.659 ↑ 1.0 1,276,343 1

Seq Scan on fiboprotocolo protocolo_convenio (cost=0.00..35,298.43 rows=1,276,343 width=16) (actual time=0.013..128.659 rows=1,276,343 loops=1)

51. 0.564 1.320 ↑ 1.0 5,668 1

Hash (cost=213.68..213.68 rows=5,668 width=12) (actual time=1.320..1.320 rows=5,668 loops=1)

  • Buckets: 8,192 Batches: 1 Memory Usage: 330kB
52. 0.756 0.756 ↑ 1.0 5,668 1

Seq Scan on recebimento (cost=0.00..213.68 rows=5,668 width=12) (actual time=0.005..0.756 rows=5,668 loops=1)

53. 0.004 0.009 ↑ 1.0 13 1

Hash (cost=1.13..1.13 rows=13 width=112) (actual time=0.009..0.009 rows=13 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 10kB
54. 0.005 0.005 ↑ 1.0 13 1

Seq Scan on fibojustifdifvaloresfaturados justificativa (cost=0.00..1.13 rows=13 width=112) (actual time=0.003..0.005 rows=13 loops=1)

55. 0.000 0.001 ↓ 0.0 0 1

Hash (cost=12.20..12.20 rows=220 width=272) (actual time=0.001..0.001 rows=0 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 8kB
56. 0.001 0.001 ↓ 0.0 0 1

Seq Scan on detalhes_quitacao_rpa rpa (cost=0.00..12.20 rows=220 width=272) (actual time=0.001..0.001 rows=0 loops=1)

57.          

SubPlan (for GroupAggregate)

58. 58.270 116.540 ↑ 1.0 1 58,270

Aggregate (cost=8.30..8.31 rows=1 width=32) (actual time=0.002..0.002 rows=1 loops=58,270)

59. 58.270 58.270 ↓ 0.0 0 58,270

Index Scan using idx_recebimento_remessa_remessa_id_idx on recebimento_remessa lancamentos_manuais_na_remessa (cost=0.28..8.29 rows=1 width=7) (actual time=0.001..0.001 rows=0 loops=58,270)

  • Index Cond: (remessa.id = remessa_id)
60. 0.000 0.000 ↓ 0.0 0

Index Only Scan using idx_recebimento_remessa_remessa_id_idx on recebimento_remessa lancamentos_manuais_na_remessa_1 (cost=0.28..8.29 rows=1 width=0) (never executed)

  • Index Cond: (remessa_id = remessa.id)
  • Heap Fetches: 0
61. 0.082 0.082 ↑ 1.0 873 1

Seq Scan on recebimento_remessa lancamentos_manuais_na_remessa_2 (cost=0.00..19.73 rows=873 width=8) (actual time=0.005..0.082 rows=873 loops=1)

62. 58.270 116.540 ↑ 1.0 1 58,270

Aggregate (cost=16.62..16.63 rows=1 width=32) (actual time=0.002..0.002 rows=1 loops=58,270)

63. 0.000 58.270 ↓ 0.0 0 58,270

Nested Loop (cost=0.56..16.61 rows=1 width=7) (actual time=0.001..0.001 rows=0 loops=58,270)

64. 58.270 58.270 ↓ 0.0 0 58,270

Index Scan using lancamento_remessa_remessa_id_fato_contabil_id_unique on lancamento_remessa lancamentos_acrescimos_na_remessa (cost=0.28..8.30 rows=1 width=15) (actual time=0.001..0.001 rows=0 loops=58,270)

  • Index Cond: (remessa.id = remessa_id)
65. 7.964 7.964 ↑ 1.0 1 3,982

Index Scan using fato_contabil_pkey on fato_contabil (cost=0.28..8.30 rows=1 width=8) (actual time=0.002..0.002 rows=1 loops=3,982)

  • Index Cond: (id = lancamentos_acrescimos_na_remessa.fato_contabil_id)
  • Filter: (tipo = 'Acrescimo'::text)
  • Rows Removed by Filter: 0
66. 0.000 58.270 ↑ 1.0 1 58,270

Aggregate (cost=16.66..16.67 rows=1 width=32) (actual time=0.001..0.001 rows=1 loops=58,270)

67. 0.000 58.270 ↓ 0.0 0 58,270

Nested Loop (cost=0.56..16.65 rows=1 width=7) (actual time=0.001..0.001 rows=0 loops=58,270)

68. 58.270 58.270 ↓ 0.0 0 58,270

Index Scan using lancamento_remessa_remessa_id_fato_contabil_id_unique on lancamento_remessa lancamentos_deducoes_na_remessa (cost=0.28..8.30 rows=1 width=15) (actual time=0.001..0.001 rows=0 loops=58,270)

  • Index Cond: (remessa.id = remessa_id)
69. 3.982 3.982 ↓ 0.0 0 3,982

Index Scan using fato_contabil_pkey on fato_contabil fato_contabil_1 (cost=0.28..8.30 rows=1 width=8) (actual time=0.001..0.001 rows=0 loops=3,982)

  • Index Cond: (id = lancamentos_deducoes_na_remessa.fato_contabil_id)
  • Filter: (tipo = 'Deducao'::text)
  • Rows Removed by Filter: 1
70. 0.000 0.000 ↓ 0.0 0

Index Only Scan using idx_lancamento_remessa_remessa_id_idx on lancamento_remessa lancamentos_fatos_contabeis_na_remessa (cost=0.28..8.30 rows=1 width=0) (never executed)

  • Index Cond: (remessa_id = remessa.id)
  • Heap Fetches: 0
71. 0.351 0.351 ↑ 1.0 3,982 1

Seq Scan on lancamento_remessa lancamentos_fatos_contabeis_na_remessa_1 (cost=0.00..77.82 rows=3,982 width=8) (actual time=0.003..0.351 rows=3,982 loops=1)

72. 0.001 0.004 ↑ 1.0 1 1

Hash (cost=1.01..1.01 rows=1 width=136) (actual time=0.004..0.004 rows=1 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
73. 0.003 0.003 ↑ 1.0 1 1

Seq Scan on fibohospital hospital (cost=0.00..1.01 rows=1 width=136) (actual time=0.003..0.003 rows=1 loops=1)

74. 0.006 0.277 ↑ 1.0 48 1

Hash (cost=3.48..3.48 rows=48 width=16) (actual time=0.277..0.277 rows=48 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 11kB
75. 0.271 0.271 ↑ 1.0 48 1

Seq Scan on view_smartdata_operadoras operadora (cost=0.00..3.48 rows=48 width=16) (actual time=0.004..0.271 rows=48 loops=1)

76. 0.032 0.144 ↑ 1.0 201 1

Hash (cost=16.01..16.01 rows=201 width=36) (actual time=0.144..0.144 rows=201 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 22kB
77. 0.112 0.112 ↑ 1.0 201 1

Seq Scan on fiboconvenio convenio (cost=0.00..16.01 rows=201 width=36) (actual time=0.005..0.112 rows=201 loops=1)

78. 0.276 0.530 ↑ 1.0 2,441 1

Hash (cost=62.41..62.41 rows=2,441 width=16) (actual time=0.530..0.530 rows=2,441 loops=1)

  • Buckets: 4,096 Batches: 1 Memory Usage: 147kB
79. 0.254 0.254 ↑ 1.0 2,441 1

Seq Scan on fibocompetencia competencia (cost=0.00..62.41 rows=2,441 width=16) (actual time=0.007..0.254 rows=2,441 loops=1)

80. 0.000 1,533.220 ↑ 1.0 1 58,970

Result (cost=8.46..8.46 rows=1 width=1) (actual time=0.026..0.026 rows=1 loops=58,970)

81.          

Initplan (for Result)

82. 1,533.220 1,533.220 ↑ 1.0 1 58,970

Index Scan using idx_fiboarquivo_numero_remessa_idx on fiboarquivo arquivo (cost=0.43..8.46 rows=1 width=0) (actual time=0.026..0.026 rows=1 loops=58,970)

  • Index Cond: (COALESCE($9, $10) = numero_remessa)
  • Filter: ((cnpj_hospital = $11) AND (lower(metadado_nome_convenio) = lower($12)))
  • Rows Removed by Filter: 0
Planning time : 4.459 ms
Execution time : 32,083.790 ms