explain.depesz.com

PostgreSQL's explain analyze made readable

Result: X4wT : Optimization for: Optimization for: plan #EfpZ; plan #1BwB

Settings

Optimization path:

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 0.083 29,707.053 ↑ 6,206.9 100 1

Subquery Scan on final (cost=329,389,355.95..481,071,667.58 rows=620,692 width=843) (actual time=29,376.494..29,707.053 rows=100 loops=1)

2. 1.662 29,706.970 ↑ 6,206.9 100 1

Nested Loop Left Join (cost=329,389,355.95..481,053,046.82 rows=620,692 width=891) (actual time=29,376.491..29,706.970 rows=100 loops=1)

3. 0.056 29,700.208 ↑ 6,206.9 100 1

Hash Left Join (cost=329,389,347.50..475,628,198.74 rows=620,692 width=747) (actual time=29,376.412..29,700.208 rows=100 loops=1)

  • Hash Cond: (COALESCE(remessa_importada.competencia_id, valor_esperado.competencia_id) = competencia.id)
4. 0.052 29,699.616 ↑ 6,206.9 100 1

Hash Join (cost=329,389,254.57..475,626,473.68 rows=620,692 width=747) (actual time=29,375.868..29,699.616 rows=100 loops=1)

  • Hash Cond: (COALESCE(remessa_importada.convenio_id, valor_esperado.convenio_id) = convenio.id)
5. 0.047 29,699.432 ↑ 6,206.9 100 1

Hash Join (cost=329,389,236.05..475,624,791.48 rows=620,692 width=727) (actual time=29,375.732..29,699.432 rows=100 loops=1)

  • Hash Cond: (COALESCE(remessa_importada.operadora_id, valor_esperado.operadora_id) = operadora.operadora_id)
6. 0.066 29,699.365 ↑ 25,862.2 100 1

Hash Join (cost=329,389,231.97..475,617,854.73 rows=2,586,217 width=727) (actual time=29,375.709..29,699.365 rows=100 loops=1)

  • Hash Cond: (COALESCE(remessa_importada.hospital_id, valor_esperado.hospital_id) = hospital.id)
7. 309.457 29,699.296 ↑ 5,172,434.3 100 1

Merge Full Join (cost=329,389,230.95..474,231,318.04 rows=517,243,427 width=607) (actual time=29,375.701..29,699.296 rows=100 loops=1)

  • Merge Cond: (valor_esperado.entidade_id = remessa_importada.entidade_id)
  • Filter: (COALESCE(valor_esperado.entidade_id, remessa_importada.entidade_id) = ANY ('{1074090111113686,107409011901539,107409011490170,107409011872188,107409011901678,107409011489327,107409011488820,107409011488804,107409011487946,107409011487536,107409011487524,107409011484165,107409011481353,107409011480680,107409011480676,107409011479550,107409011478814,107409011478071,107409011476987,107409011476983,107409011475563,107409011475559,107409011475149,107409011473645,107409011472342,107409011472338,107409011462938,107409011462894,107409011399488,107409011517987,107409011514770,107409011514732,107409011513898,107409011513062,107409011509552,107409011509548,107409011504868,107409011504860,107409011504675,107409011504595,107409011502275,107409011502237,107409011499797,107409011499498,107409011498668,107409011498664,107409011498457,107409011498371,107409011497998,107409011497994,107409011497990,107409011520613,107409011520094,107409011509724,107409011530914,107409011520851,107409011553276,107409011530717,107409011531138,107409011545521,107409011543946,107409011545453,107409011540386,107409011530329,107409011552170,107409011549751,107409011544039,107409011544512,107409011527613,107409011546105,107409011531225,107409011534480,107409011526483,107409011543905,107409011543929,107409011526668,107409011531678,107409011534484,107409011528846,107409011526487,107409011531674,107409011526744,107409011544792,107409011544788,107409011545427,107409011531591,107409011547866,107409011544094,107409011527609,1074090112833225,1074090112833253,1074090112833149,1074090112828923,1074090112829019,1074090112827457,1074090112829015,1074090112827014,1074090112833001,1074090112827010,1074090112826261}'::numeric[]))
  • Rows Removed by Filter: 58,870
8. 19.312 138.018 ↑ 1.0 37,647 1

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

  • Sort Key: valor_esperado.entidade_id
  • Sort Method: quicksort Memory: 11,536kB
9. 5.915 118.706 ↑ 1.0 37,647 1

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

10. 84.936 112.791 ↑ 1.0 37,647 1

Hash Left Join (cost=119.63..22,925.66 rows=37,647 width=140) (actual time=0.832..112.791 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))
11. 8.306 27.180 ↑ 1.0 37,647 1

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

  • Hash Cond: (operadora_1.opconvenio = convenio_1.id)
12. 12.730 18.768 ↑ 1.0 37,647 1

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

  • Hash Cond: (valor_faturado.id_operadora = operadora_1.id)
13. 6.026 6.026 ↑ 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.003..6.026 rows=37,647 loops=1)

  • Filter: (numero_remessa IS NOT NULL)
14. 0.006 0.012 ↑ 1.0 48 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 11kB
15. 0.006 0.006 ↑ 1.0 48 1

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

16. 0.028 0.106 ↑ 1.0 201 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 20kB
17. 0.078 0.078 ↑ 1.0 201 1

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

18. 0.407 0.675 ↑ 1.0 2,441 1

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

  • Buckets: 4,096 Batches: 1 Memory Usage: 175kB
19. 0.268 0.268 ↑ 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.268 rows=2,441 loops=1)

20. 10.140 29,251.821 ↑ 94.3 58,270 1

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

21. 76.835 29,241.681 ↑ 94.3 58,270 1

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

  • Sort Key: remessa_importada.entidade_id
  • Sort Method: quicksort Memory: 17,016kB
22. 18.182 29,164.846 ↑ 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,168.735..29,164.846 rows=58,270 loops=1)

23. 13,047.714 29,146.664 ↑ 94.3 58,270 1

GroupAggregate (cost=2,976,139.42..327,509,461.93 rows=5,495,720 width=499) (actual time=15,168.733..29,146.664 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
24. 2,710.016 15,807.155 ↑ 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,167.094..15,807.155 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
25. 837.280 13,097.139 ↑ 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,495.221..13,097.139 rows=2,987,733 loops=1)

  • Hash Cond: ((convenio_2.convnome = rpa.nome_convenio) AND (remessa.rem_numero = rpa.numero_remessa))
26. 589.008 12,259.857 ↑ 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,495.216..12,259.857 rows=2,987,733 loops=1)

  • Join Filter: (remessa.sistema_de_gestao_id = sistema_de_gestao.id)
27. 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)

28. 706.128 11,670.844 ↑ 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,495.208..11,670.844 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))
29. 656.144 10,964.707 ↑ 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,495.194..10,964.707 rows=2,987,733 loops=1)

  • Hash Cond: (guia_convenio.recebimento_id = recebimento.id)
30. 830.543 10,307.286 ↑ 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,493.907..10,307.286 rows=2,987,733 loops=1)

  • Hash Cond: (guia_convenio.guiprotocolo = protocolo_convenio.id)
31. 1,205.650 9,137.509 ↑ 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,154.036..9,137.509 rows=2,987,733 loops=1)

  • Hash Cond: (guia_prestador.guiremessa = remessa.id)
32. 2,642.052 7,865.177 ↑ 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,087.331..7,865.177 rows=5,491,696 loops=1)

  • Hash Cond: (guia_prestador.guiguiaassoc = guia_convenio.id)
33. 2,136.519 2,136.519 ↑ 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,136.519 rows=5,491,696 loops=1)

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

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

  • Buckets: 2,097,152 Batches: 4 Memory Usage: 88,271kB
35. 2,232.008 2,232.008 ↑ 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.011..2,232.008 rows=5,734,864 loops=1)

36. 18.132 66.682 ↑ 1.0 58,270 1

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

  • Buckets: 65,536 Batches: 1 Memory Usage: 11,877kB
37. 11.188 48.550 ↑ 1.0 58,270 1

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

  • Hash Cond: (remessa.operadora_id = operadora_2.id)
38. 12.796 37.349 ↑ 1.0 58,270 1

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

  • Hash Cond: (competencia_2.comp_convenio = convenio_2.convnome)
39. 11.895 24.430 ↑ 1.0 58,270 1

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

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

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

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

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

  • Buckets: 4,096 Batches: 1 Memory Usage: 156kB
42. 0.261 0.261 ↑ 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.261 rows=2,441 loops=1)

43. 0.033 0.123 ↑ 1.0 201 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 20kB
44. 0.090 0.090 ↑ 1.0 201 1

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

45. 0.006 0.013 ↑ 1.0 48 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 10kB
46. 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.004..0.007 rows=48 loops=1)

47. 211.641 339.234 ↑ 1.0 1,276,343 1

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

  • Buckets: 2,097,152 Batches: 1 Memory Usage: 74,617kB
48. 127.593 127.593 ↑ 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.012..127.593 rows=1,276,343 loops=1)

49. 0.549 1.277 ↑ 1.0 5,668 1

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

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

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

51. 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
52. 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)

53. 0.001 0.002 ↓ 0.0 0 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 8kB
54. 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)

55.          

SubPlan (for GroupAggregate)

56. 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)

57. 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)
58. 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
59. 0.085 0.085 ↑ 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.085 rows=873 loops=1)

60. 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)

61. 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)

62. 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)
63. 3.982 3.982 ↑ 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.001..0.001 rows=1 loops=3,982)

  • Index Cond: (id = lancamentos_acrescimos_na_remessa.fato_contabil_id)
  • Filter: (tipo = 'Acrescimo'::text)
  • Rows Removed by Filter: 0
64. 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)

65. 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)

66. 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)
67. 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
68. 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
69. 0.360 0.360 ↑ 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.360 rows=3,982 loops=1)

70. 0.001 0.003 ↑ 1.0 1 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
71. 0.002 0.002 ↑ 1.0 1 1

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

72. 0.006 0.020 ↑ 1.0 48 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 11kB
73. 0.014 0.014 ↑ 1.0 48 1

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

74. 0.031 0.132 ↑ 1.0 201 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 22kB
75. 0.101 0.101 ↑ 1.0 201 1

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

76. 0.279 0.536 ↑ 1.0 2,441 1

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

  • Buckets: 4,096 Batches: 1 Memory Usage: 147kB
77. 0.257 0.257 ↑ 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.257 rows=2,441 loops=1)

78. 0.100 5.100 ↑ 1.0 1 100

Result (cost=8.46..8.46 rows=1 width=1) (actual time=0.050..0.051 rows=1 loops=100)

79.          

Initplan (for Result)

80. 5.000 5.000 ↑ 1.0 1 100

Index Scan using idx_fiboarquivo_numero_remessa_idx on fiboarquivo arquivo (cost=0.43..8.46 rows=1 width=0) (actual time=0.050..0.050 rows=1 loops=100)

  • Index Cond: (COALESCE($9, $10) = numero_remessa)
  • Filter: ((cnpj_hospital = $11) AND (lower(metadado_nome_convenio) = lower($12)))
Planning time : 4.527 ms
Execution time : 39,793.074 ms