explain.depesz.com

PostgreSQL's explain analyze made readable

Result: JlHJ

Settings
# exclusive inclusive rows x rows loops node
1. 0.004 112.529 ↑ 1.0 1 1

Subquery Scan on final (cost=136.83..23,439.70 rows=1 width=761) (actual time=112.527..112.529 rows=1 loops=1)

2. 0.049 112.525 ↑ 1.0 1 1

Nested Loop Left Join (cost=136.83..23,439.67 rows=1 width=809) (actual time=112.524..112.525 rows=1 loops=1)

3. 0.003 112.462 ↑ 1.0 1 1

Nested Loop Left Join (cost=128.37..23,430.93 rows=1 width=618) (actual time=112.461..112.462 rows=1 loops=1)

4. 0.007 112.455 ↑ 1.0 1 1

Nested Loop (cost=128.09..23,422.62 rows=1 width=618) (actual time=112.454..112.455 rows=1 loops=1)

  • Join Filter: (COALESCE(totais_remessa_rolling_table.operadora_id, valor_faturado.id_operadora) = operadora.operadora_id)
  • Rows Removed by Join Filter: 41
5. 0.003 112.443 ↑ 1.0 1 1

Nested Loop (cost=128.09..23,418.54 rows=1 width=618) (actual time=112.442..112.443 rows=1 loops=1)

6. 0.004 112.434 ↑ 1.0 1 1

Nested Loop (cost=127.95..23,410.30 rows=1 width=598) (actual time=112.433..112.434 rows=1 loops=1)

  • Join Filter: (COALESCE(totais_remessa_rolling_table.hospital_id, ((valor_faturado.id_hospital)::bigint)) = hospital.id)
7. 4.744 112.427 ↑ 1.0 1 1

Hash Right Join (cost=127.95..23,409.28 rows=1 width=478) (actual time=112.426..112.427 rows=1 loops=1)

  • Hash Cond: ((CASE WHEN (valor_faturado.numero_remessa ~ '^[0-9]+$'::text) THEN (concat_immutable(VARIADIC ARRAY[(valor_faturado.sistema_de_gestao_id)::text, valor_faturado.numero_remessa]))::numeric ELSE (concat_immutable(VARIADIC ARRAY[(valor_faturado.sistema_de_gestao_id)::text, (abs(((('x'::text || substr(md5(valor_faturado.numero_remessa), 1, 16)))::bit(16))::integer))::text]))::numeric END) = totais_remessa_rolling_table.entidade_id)
8. 80.605 107.669 ↑ 1.0 37,647 1

Hash Left Join (cost=119.63..22,925.66 rows=37,647 width=140) (actual time=0.850..107.669 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))
9. 8.552 26.381 ↑ 1.0 37,647 1

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

  • Hash Cond: (operadora_1.opconvenio = convenio_1.id)
10. 11.889 17.708 ↑ 1.0 37,647 1

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

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

  • Filter: (numero_remessa IS NOT NULL)
12. 0.005 0.011 ↑ 1.0 48 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 11kB
13. 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.002..0.006 rows=48 loops=1)

14. 0.029 0.121 ↑ 1.0 201 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 20kB
15. 0.092 0.092 ↑ 1.0 201 1

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

16. 0.410 0.683 ↑ 1.0 2,441 1

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

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

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

18. 0.002 0.014 ↑ 1.0 1 1

Hash (cost=8.31..8.31 rows=1 width=346) (actual time=0.014..0.014 rows=1 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
19. 0.012 0.012 ↑ 1.0 1 1

Index Scan using totais_remessa_rolling_table_pkey on totais_remessa_rolling_table (cost=0.29..8.31 rows=1 width=346) (actual time=0.011..0.012 rows=1 loops=1)

  • Index Cond: (remessa_id = 28,868,825)
20. 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)

21. 0.006 0.006 ↑ 1.0 1 1

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

  • Index Cond: (id = COALESCE(totais_remessa_rolling_table.convenio_id, convenio_1.id))
22. 0.005 0.005 ↑ 1.1 42 1

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

23. 0.004 0.004 ↑ 1.0 1 1

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

  • Index Cond: (COALESCE(totais_remessa_rolling_table.competencia_id, competencia_1.id) = id)
24. 0.002 0.014 ↑ 1.0 1 1

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

25.          

Initplan (for Result)

26. 0.012 0.012 ↑ 1.0 1 1

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

  • Index Cond: (COALESCE($0, $1) = numero_remessa)
  • Filter: ((cnpj_hospital = $2) AND (lower(metadado_nome_convenio) = lower($3)))
Planning time : 1.783 ms
Execution time : 112.652 ms