explain.depesz.com

PostgreSQL's explain analyze made readable

Result: iWKt : Optimization for: Optimization for: Optimization for: Optimization for: Optimization for: Optimization for: Optimization for: Optimization for: Optimization for: Optimization for: Optimization for: plan #Uty0; plan #oKUj; plan #1Rpu; plan #sxgX; plan #GP1; plan #8BPr; plan #w1WL; plan #hu9E; plan #jHPe; plan #AswY; plan #r1N8

Settings

Optimization path:

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 0.036 21,475.610 ↑ 1.0 100 1

Limit (cost=3,039,464.34..3,069,742.40 rows=100 width=843) (actual time=21,401.548..21,475.610 rows=100 loops=1)

2. 0.123 21,475.574 ↑ 12,411.6 100 1

Subquery Scan on final (cost=3,039,464.34..378,840,232.78 rows=1,241,165 width=843) (actual time=21,401.546..21,475.574 rows=100 loops=1)

3. 2.672 21,475.451 ↑ 12,411.6 100 1

Nested Loop Left Join (cost=3,039,464.34..378,802,997.83 rows=1,241,165 width=891) (actual time=21,401.543..21,475.451 rows=100 loops=1)

4. 0.101 21,448.379 ↑ 12,411.6 100 1

Nested Loop Left Join (cost=3,039,455.88..367,955,215.73 rows=1,241,165 width=747) (actual time=21,399.981..21,448.379 rows=100 loops=1)

5. 0.143 21,448.178 ↑ 12,411.6 100 1

Nested Loop (cost=3,039,455.60..367,579,782.48 rows=1,241,165 width=747) (actual time=21,399.975..21,448.178 rows=100 loops=1)

6. 0.129 21,447.635 ↑ 12,411.6 100 1

Nested Loop (cost=3,039,455.46..367,376,099.96 rows=1,241,165 width=727) (actual time=21,399.680..21,447.635 rows=100 loops=1)

7. 0.111 21,446.406 ↑ 51,715.2 100 1

Hash Join (cost=3,039,455.32..366,559,275.01 rows=5,171,519 width=727) (actual time=21,398.962..21,446.406 rows=100 loops=1)

  • Hash Cond: (COALESCE(sistema_de_gestao.hospital_id, valor_esperado.hospital_id) = hospital.id)
8. 0.158 21,446.003 ↑ 10,343,037.0 100 1

Hash Full Join (cost=3,039,454.29..363,786,693.62 rows=1,034,303,702 width=607) (actual time=21,398.663..21,446.003 rows=100 loops=1)

  • Hash Cond: ((CASE WHEN (remessa.rem_numero ~ '^[0-9]+$'::text) THEN (concat_immutable(VARIADIC ARRAY[(remessa.sistema_de_gestao_id)::text, remessa.rem_numero]))::numeric ELSE (concat_immutable(VARIADIC ARRAY[(remessa.sistema_de_gestao_id)::text, (abs(((('x'::text || substr(md5(remessa.rem_numero), 1, 16)))::bit(16))::integer))::text]))::numeric END) = valor_esperado.entidade_id)
9. 46.639 21,301.088 ↑ 54,947.5 100 1

GroupAggregate (cost=3,015,681.58..327,491,546.48 rows=5,494,747 width=499) (actual time=21,253.875..21,301.088 rows=100 loops=1)

  • Group Key: remessa.id, justificativa.id, sistema_de_gestao.hospital_id, convenio_1.id, operadora_1.id, competencia_1.id, rpa.id
10. 2,131.268 21,250.717 ↑ 1,096.5 5,011 1

Sort (cost=3,015,681.58..3,029,418.45 rows=5,494,747 width=455) (actual time=21,249.701..21,250.717 rows=5,011 loops=1)

  • Sort Key: remessa.id, justificativa.id, sistema_de_gestao.hospital_id, convenio_1.id, operadora_1.id, competencia_1.id, rpa.id
  • Sort Method: external merge Disk: 593,136kB
11. 816.031 19,119.449 ↑ 1.8 2,987,733 1

Hash Left Join (cost=544,580.80..1,273,702.05 rows=5,494,747 width=455) (actual time=9,128.305..19,119.449 rows=2,987,733 loops=1)

  • Hash Cond: ((convenio_1.convnome = rpa.nome_convenio) AND (remessa.rem_numero = rpa.numero_remessa))
12. 567.622 18,303.416 ↑ 1.8 2,987,733 1

Nested Loop (cost=544,565.30..1,244,839.13 rows=5,494,747 width=259) (actual time=9,128.298..18,303.416 rows=2,987,733 loops=1)

  • Join Filter: (remessa.sistema_de_gestao_id = sistema_de_gestao.id)
13. 0.265 0.265 ↑ 1.0 1 1

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

14. 716.864 17,735.529 ↑ 1.8 2,987,733 1

Hash Left Join (cost=544,565.30..1,176,153.78 rows=5,494,747 width=251) (actual time=9,128.031..17,735.529 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))
15. 652.417 17,018.014 ↑ 1.8 2,987,733 1

Hash Left Join (cost=544,563.98..1,147,304.95 rows=5,494,747 width=179) (actual time=9,127.372..17,018.014 rows=2,987,733 loops=1)

  • Hash Cond: (guia_convenio.recebimento_id = recebimento.id)
16. 838.984 16,355.786 ↑ 1.8 2,987,733 1

Hash Left Join (cost=544,279.45..1,132,592.18 rows=5,494,747 width=183) (actual time=9,117.551..16,355.786 rows=2,987,733 loops=1)

  • Hash Cond: (guia_convenio.guiprotocolo = protocolo_convenio.id)
17. 1,203.892 14,999.757 ↑ 1.8 2,987,733 1

Hash Join (cost=493,026.73..1,066,915.71 rows=5,494,747 width=183) (actual time=8,599.853..14,999.757 rows=2,987,733 loops=1)

  • Hash Cond: (guia_prestador.guiremessa = remessa.id)
18. 2,986.967 13,725.876 ↑ 1.0 5,491,696 1

Hash Left Join (cost=489,927.22..1,012,152.76 rows=5,494,747 width=46) (actual time=8,529.839..13,725.876 rows=5,491,696 loops=1)

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

Seq Scan on fiboguia guia_prestador (cost=0.00..379,057.11 rows=5,494,747 width=30) (actual time=0.352..2,210.077 rows=5,491,696 loops=1)

  • Filter: (discriminador <> 'RECURSO'::text)
  • Rows Removed by Filter: 243,168
20. 1,253.941 8,528.832 ↑ 1.3 4,517,836 1

Hash (cost=364,722.49..364,722.49 rows=5,733,849 width=32) (actual time=8,528.832..8,528.832 rows=4,517,836 loops=1)

  • Buckets: 2,097,152 Batches: 4 Memory Usage: 82,521kB
21. 7,274.891 7,274.891 ↓ 1.0 5,734,864 1

Seq Scan on fiboguia guia_convenio (cost=0.00..364,722.49 rows=5,733,849 width=32) (actual time=0.014..7,274.891 rows=5,734,864 loops=1)

22. 17.484 69.989 ↑ 1.0 58,270 1

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

  • Buckets: 65,536 Batches: 1 Memory Usage: 11,877kB
23. 10.943 52.505 ↑ 1.0 58,270 1

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

  • Hash Cond: (remessa.operadora_id = operadora_1.id)
24. 12.840 41.549 ↑ 1.0 58,270 1

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

  • Hash Cond: (competencia_1.comp_convenio = convenio_1.convnome)
25. 11.664 28.585 ↑ 1.0 58,270 1

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

  • Hash Cond: (remessa.remcompetencia = competencia_1.id)
26. 16.384 16.384 ↑ 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..16.384 rows=58,270 loops=1)

  • Filter: ((rem_numero IS NOT NULL) AND (sistema_de_gestao_id IS NOT NULL))
  • Rows Removed by Filter: 1
27. 0.274 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
28. 0.263 0.263 ↑ 1.0 2,441 1

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

29. 0.033 0.124 ↑ 1.0 201 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 20kB
30. 0.091 0.091 ↑ 1.0 201 1

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

31. 0.005 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
32. 0.008 0.008 ↑ 1.0 48 1

Seq Scan on fibooperadora operadora_1 (cost=0.00..1.48 rows=48 width=8) (actual time=0.005..0.008 rows=48 loops=1)

33. 238.272 517.045 ↑ 1.0 1,276,343 1

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

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

35. 0.580 9.811 ↑ 1.0 5,668 1

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

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

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

37. 0.006 0.651 ↑ 1.0 13 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 10kB
38. 0.645 0.645 ↑ 1.0 13 1

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

39. 0.000 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
40. 0.002 0.002 ↓ 0.0 0 1

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

41.          

SubPlan (for GroupAggregate)

42. 0.100 0.800 ↑ 1.0 1 100

Aggregate (cost=8.30..8.31 rows=1 width=32) (actual time=0.008..0.008 rows=1 loops=100)

43. 0.700 0.700 ↓ 0.0 0 100

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.007..0.007 rows=0 loops=100)

  • Index Cond: (remessa.id = remessa_id)
44. 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
45. 0.528 0.528 ↑ 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.154..0.528 rows=873 loops=1)

46. 0.000 1.300 ↑ 1.0 1 100

Aggregate (cost=16.62..16.63 rows=1 width=32) (actual time=0.013..0.013 rows=1 loops=100)

47. 0.049 1.300 ↓ 0.0 0 100

Nested Loop (cost=0.56..16.61 rows=1 width=7) (actual time=0.013..0.013 rows=0 loops=100)

48. 0.900 0.900 ↓ 0.0 0 100

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.009..0.009 rows=0 loops=100)

  • Index Cond: (remessa.id = remessa_id)
49. 0.351 0.351 ↑ 1.0 1 1

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

  • Index Cond: (id = lancamentos_acrescimos_na_remessa.fato_contabil_id)
  • Filter: (tipo = 'Acrescimo'::text)
50. 0.000 0.100 ↑ 1.0 1 100

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

51. 0.000 0.100 ↓ 0.0 0 100

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

52. 0.100 0.100 ↓ 0.0 0 100

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=100)

  • Index Cond: (remessa.id = remessa_id)
53. 0.002 0.002 ↓ 0.0 0 1

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

  • Index Cond: (id = lancamentos_deducoes_na_remessa.fato_contabil_id)
  • Filter: (tipo = 'Deducao'::text)
  • Rows Removed by Filter: 1
54. 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
55. 1.004 1.004 ↑ 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..1.004 rows=3,982 loops=1)

56. 13.235 144.757 ↑ 1.0 37,647 1

Hash (cost=23,302.13..23,302.13 rows=37,647 width=132) (actual time=144.757..144.757 rows=37,647 loops=1)

  • Buckets: 65,536 Batches: 1 Memory Usage: 6,520kB
57. 6.652 131.522 ↑ 1.0 37,647 1

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

58. 90.068 124.870 ↑ 1.0 37,647 1

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

  • Hash Cond: ((COALESCE(convenio_2.convnome, valor_faturado.convenio_integracao) = competencia_2.comp_convenio) AND (valor_faturado.competencia = competencia_2.comp_desc))
59. 8.980 32.805 ↑ 1.0 37,647 1

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

  • Hash Cond: (operadora_2.opconvenio = convenio_2.id)
60. 12.865 23.026 ↑ 1.0 37,647 1

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

  • Hash Cond: (valor_faturado.id_operadora = operadora_2.id)
61. 9.983 9.983 ↑ 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.513..9.983 rows=37,647 loops=1)

  • Filter: (numero_remessa IS NOT NULL)
62. 0.009 0.178 ↑ 1.0 48 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 11kB
63. 0.169 0.169 ↑ 1.0 48 1

Seq Scan on fibooperadora operadora_2 (cost=0.00..1.48 rows=48 width=16) (actual time=0.164..0.169 rows=48 loops=1)

64. 0.045 0.799 ↑ 1.0 201 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 20kB
65. 0.754 0.754 ↑ 1.0 201 1

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

66. 0.639 1.997 ↑ 1.0 2,441 1

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

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

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

68. 0.003 0.292 ↑ 1.0 1 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
69. 0.289 0.289 ↑ 1.0 1 1

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

70. 1.100 1.100 ↑ 1.0 1 100

Index Scan using view_smartdata_operadoras_operadora_id_unique on view_smartdata_operadoras operadora (cost=0.14..0.16 rows=1 width=16) (actual time=0.011..0.011 rows=1 loops=100)

  • Index Cond: (operadora_id = COALESCE(operadora_1.id, valor_esperado.operadora_id))
71. 0.400 0.400 ↑ 1.0 1 100

Index Scan using fiboconvenio_pkey on fiboconvenio convenio (cost=0.14..0.16 rows=1 width=36) (actual time=0.004..0.004 rows=1 loops=100)

  • Index Cond: (id = COALESCE(convenio_1.id, valor_esperado.convenio_id))
72. 0.100 0.100 ↑ 1.0 1 100

Index Scan using fibocompetencia_pkey on fibocompetencia competencia (cost=0.28..0.30 rows=1 width=16) (actual time=0.001..0.001 rows=1 loops=100)

  • Index Cond: (COALESCE(competencia_1.id, valor_esperado.competencia_id) = id)
73. 0.100 24.400 ↑ 1.0 1 100

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

74.          

Initplan (for Result)

75. 24.300 24.300 ↑ 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.243..0.243 rows=1 loops=100)

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