explain.depesz.com

PostgreSQL's explain analyze made readable

Result: gCLU

Settings
# exclusive inclusive rows x rows loops node
1. 45.190 3,188.887 ↓ 842.4 58,970 1

Subquery Scan on final (cost=3,124.68..27,619.60 rows=70 width=761) (actual time=66.881..3,188.887 rows=58,970 loops=1)

2. 1,070.089 3,143.697 ↓ 842.4 58,970 1

Nested Loop Left Join (cost=3,124.68..27,617.50 rows=70 width=809) (actual time=66.878..3,143.697 rows=58,970 loops=1)

3. 39.264 599.358 ↓ 842.4 58,970 1

Nested Loop Left Join (cost=3,116.23..27,005.70 rows=70 width=618) (actual time=64.589..599.358 rows=58,970 loops=1)

4. 81.002 501.124 ↓ 842.4 58,970 1

Nested Loop (cost=3,115.95..26,984.52 rows=70 width=618) (actual time=64.289..501.124 rows=58,970 loops=1)

5. 26.571 361.152 ↓ 842.4 58,970 1

Hash Join (cost=3,115.80..26,973.03 rows=70 width=598) (actual time=63.863..361.152 rows=58,970 loops=1)

  • Hash Cond: (COALESCE(totais_remessa_rolling_table.operadora_id, valor_faturado.id_operadora) = operadora.operadora_id)
6. 28.782 333.674 ↓ 202.6 58,970 1

Hash Join (cost=3,111.72..26,968.18 rows=291 width=598) (actual time=62.952..333.674 rows=58,970 loops=1)

  • Hash Cond: (COALESCE(totais_remessa_rolling_table.hospital_id, ((valor_faturado.id_hospital)::bigint)) = hospital.id)
7. 53.723 304.608 ↓ 1.0 58,970 1

Hash Full Join (cost=3,110.70..26,810.96 rows=58,270 width=478) (actual time=62.663..304.608 rows=58,970 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. 146.685 192.449 ↑ 1.0 37,647 1

Hash Left Join (cost=119.63..22,925.66 rows=37,647 width=140) (actual time=4.195..192.449 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. 11.153 43.318 ↑ 1.0 37,647 1

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

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

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

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

  • Filter: (numero_remessa IS NOT NULL)
12. 0.006 0.254 ↑ 1.0 48 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 11kB
13. 0.248 0.248 ↑ 1.0 48 1

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

14. 0.029 0.890 ↑ 1.0 201 1

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

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

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

16. 0.417 2.446 ↑ 1.0 2,441 1

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

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

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

18. 23.582 58.436 ↑ 1.0 58,270 1

Hash (cost=2,262.70..2,262.70 rows=58,270 width=346) (actual time=58.436..58.436 rows=58,270 loops=1)

  • Buckets: 65,536 Batches: 1 Memory Usage: 12,947kB
19. 34.854 34.854 ↑ 1.0 58,270 1

Seq Scan on totais_remessa_rolling_table (cost=0.00..2,262.70 rows=58,270 width=346) (actual time=1.033..34.854 rows=58,270 loops=1)

20. 0.001 0.284 ↑ 1.0 1 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
21. 0.283 0.283 ↑ 1.0 1 1

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

22. 0.007 0.907 ↑ 1.0 48 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 11kB
23. 0.900 0.900 ↑ 1.0 48 1

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

24. 58.970 58.970 ↑ 1.0 1 58,970

Index Scan using fiboconvenio_pkey on fiboconvenio convenio (cost=0.14..0.16 rows=1 width=36) (actual time=0.001..0.001 rows=1 loops=58,970)

  • Index Cond: (id = COALESCE(totais_remessa_rolling_table.convenio_id, convenio_1.id))
25. 58.970 58.970 ↑ 1.0 1 58,970

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=58,970)

  • Index Cond: (COALESCE(totais_remessa_rolling_table.competencia_id, competencia_1.id) = id)
26. 0.000 1,474.250 ↑ 1.0 1 58,970

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

27.          

Initplan (for Result)

28. 1,474.250 1,474.250 ↑ 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.025..0.025 rows=1 loops=58,970)

  • Index Cond: (COALESCE($0, $1) = numero_remessa)
  • Filter: ((cnpj_hospital = $2) AND (lower(metadado_nome_convenio) = lower($3)))
  • Rows Removed by Filter: 0
Planning time : 11.343 ms
Execution time : 3,195.150 ms