explain.depesz.com

PostgreSQL's explain analyze made readable

Result: LtIO : Optimization for: plan #CR8R

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 0.000 0.000 ↓ 0.0

Limit (cost=73,186,696,383,463.39..73,186,696,383,491.11 rows=10 width=213) (actual rows= loops=)

2. 0.000 0.000 ↓ 0.0

Gather Merge (cost=73,186,696,383,463.39..103,407,709,948,794.48 rows=10,903,552,699,458 width=213) (actual rows= loops=)

  • Workers Planned: 3
3. 0.000 0.000 ↓ 0.0

Sort (cost=73,186,696,382,455.39..75,003,955,165,698.39 rows=3,634,517,566,486 width=213) (actual rows= loops=)

  • Sort Key: hospben.bnf_nome, ((((substr(convcomp.comp_desc, 1, 2))::integer + ((substr(convcomp.comp_desc, 4, 4))::integer * 12)) - ((substr(hospcomp.comp_desc, 1, 2))::integer + ((substr(hospcomp.comp_desc, 4, 4))::integer * 12)))), guiaconvsolta.guinumprestador
4. 0.000 0.000 ↓ 0.0

Parallel Hash Join (cost=1,878,858,173.39..57,478,572,800,497.89 rows=3,634,517,566,486 width=213) (actual rows= loops=)

  • Hash Cond: (guiaconvsolta.guibenefic = convben.id)
5. 0.000 0.000 ↓ 0.0

Parallel Hash Join (cost=14,137,414.47..382,344,589.97 rows=614,872,425 width=145) (actual rows= loops=)

  • Hash Cond: (guiahospsolta.guibenefic = hospben.id)
6. 0.000 0.000 ↓ 0.0

Hash Join (cost=1,980,445.22..167,896,887.22 rows=16,262,184 width=129) (actual rows= loops=)

  • Hash Cond: ((guiaconvsolta.guicompetencia = convcomp.id) AND (hospcomp.comp_convenio = convcomp.comp_convenio))
7. 0.000 0.000 ↓ 0.0

Merge Join (cost=1,955,592.22..48,770,976.22 rows=3,893,683 width=138) (actual rows= loops=)

  • Merge Cond: (((guiahospsolta.guidtatend)::date) = ((guiaconvsolta.guidtatend)::date))
  • Join Filter: (abs((guiahospsolta.guivalortotal - guiaconvsolta.guivlrapresentado)) <= '0'::numeric)
8. 0.000 0.000 ↓ 0.0

Sort (cost=836,597.77..859,345.77 rows=45,496 width=64) (actual rows= loops=)

  • Sort Key: ((guiahospsolta.guidtatend)::date)
9. 0.000 0.000 ↓ 0.0

Hash Join (cost=273.00..132,617.60 rows=45,496 width=64) (actual rows= loops=)

  • Hash Cond: (guiahospsolta.guicompetencia = hospcomp.id)
10. 0.000 0.000 ↓ 0.0

Parallel Seq Scan on fiboguia guiahospsolta (cost=0.00..108,273.60 rows=45,496 width=55) (actual rows= loops=)

  • Filter: (guiguiaassoc IS NULL)
11. 0.000 0.000 ↓ 0.0

Hash (cost=95.00..95.00 rows=178 width=25) (actual rows= loops=)

12. 0.000 0.000 ↓ 0.0

Seq Scan on fibocompetencia hospcomp (cost=0.00..95.00 rows=178 width=25) (actual rows= loops=)

13. 0.000 0.000 ↓ 0.0

Sort (cost=1,118,994.45..1,144,669.45 rows=51,350 width=74) (actual rows= loops=)

  • Sort Key: ((guiaconvsolta.guidtatend)::date)
14. 0.000 0.000 ↓ 0.0

Index Scan using idx_fiboguia_guiguiaassoc_idx on fiboguia guiaconvsolta (cost=84.50..315,465.71 rows=51,350 width=74) (actual rows= loops=)

  • Index Cond: (guiguiaassoc IS NULL)
  • Filter: (guivlrpago > '0'::numeric)
15. 0.000 0.000 ↓ 0.0

Hash (cost=10,168.00..10,168.00 rows=9,790 width=25) (actual rows= loops=)

16. 0.000 0.000 ↓ 0.0

Nested Loop (cost=6.00..10,168.00 rows=9,790 width=25) (actual rows= loops=)

17. 0.000 0.000 ↓ 0.0

Seq Scan on fibocompetencia convcomp (cost=0.00..95.00 rows=178 width=25) (actual rows= loops=)

18. 0.000 0.000 ↓ 0.0

Materialize (cost=6.00..310.50 rows=55 width=0) (actual rows= loops=)

19. 0.000 0.000 ↓ 0.0

Hash Join (cost=6.00..255.50 rows=55 width=0) (actual rows= loops=)

  • Hash Cond: (prest.prest_cod = regexp_replace(hospital.hospcnpj, '([\.|\-|/])'::text, ''::text, 'g'::text))
20. 0.000 0.000 ↓ 0.0

Seq Scan on fiboprestador prest (cost=0.00..76.00 rows=146 width=7) (actual rows= loops=)

21. 0.000 0.000 ↓ 0.0

Hash (cost=3.00..3.00 rows=3 width=19) (actual rows= loops=)

22. 0.000 0.000 ↓ 0.0

Seq Scan on fibohospital hospital (cost=0.00..3.00 rows=3 width=19) (actual rows= loops=)

23. 0.000 0.000 ↓ 0.0

Parallel Hash (cost=6,282,328.25..6,282,328.25 rows=5,815,013 width=32) (actual rows= loops=)

24. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.00..6,282,328.25 rows=5,815,013 width=32) (actual rows= loops=)

25. 0.000 0.000 ↓ 0.0

Parallel Seq Scan on fibobeneficiario hospben (cost=0.00..237,761.75 rows=153,027 width=32) (actual rows= loops=)

  • Filter: (split_part(regexp_replace(bnf_nome, '(RN )(DE )'::text, ''::text), ' '::text, 1) IS NOT NULL)
26. 0.000 0.000 ↓ 0.0

Seq Scan on fibooperadora operadora (cost=0.00..20.50 rows=38 width=0) (actual rows= loops=)

27. 0.000 0.000 ↓ 0.0

Parallel Hash (cost=942,116,825.92..942,116,825.92 rows=913,239,658 width=32) (actual rows= loops=)

28. 0.000 0.000 ↓ 0.0

Nested Loop (cost=84.50..942,116,825.92 rows=913,239,658 width=32) (actual rows= loops=)

29. 0.000 0.000 ↓ 0.0

Parallel Index Scan using fibobeneficiario_pkey on fibobeneficiario convben (cost=84.50..270,121.92 rows=153,796 width=32) (actual rows= loops=)

30. 0.000 0.000 ↓ 0.0

Seq Scan on fiboremessa remessa (cost=0.00..3,155.00 rows=5,938 width=0) (actual rows= loops=)