explain.depesz.com

PostgreSQL's explain analyze made readable

Result: cL9U

Settings
# exclusive inclusive rows x rows loops node
1. 0.000 286.310 ↓ 43.0 43 1

Sort (cost=2,532,782.05..2,532,783.05 rows=1 width=213) (actual time=286.307..286.310 rows=43 loops=1)

  • 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
  • Sort Method: quicksort Memory: 36kB
2. 34.314 307.271 ↓ 43.0 43 1

Gather (cost=2,243,789.23..2,532,780.05 rows=1 width=213) (actual time=206.782..307.271 rows=43 loops=1)

  • Workers Planned: 3
  • Workers Launched: 3
3. 0.084 272.957 ↓ 11.0 11 4 / 4

Nested Loop (cost=2,242,789.23..2,531,779.95 rows=1 width=213) (actual time=197.686..272.957 rows=11 loops=4)

  • Join Filter: (hospital.id = operadora.hospital_id)
  • Rows Removed by Join Filter: 5
4. 0.000 272.796 ↓ 16.0 16 4 / 4

Nested Loop (cost=2,242,761.23..2,531,733.95 rows=1 width=177) (actual time=197.329..272.796 rows=16 loops=4)

5. 0.110 272.477 ↓ 331.0 331 4 / 4

Nested Loop (cost=2,242,704.73..2,531,675.94 rows=1 width=177) (actual time=194.607..272.477 rows=331 loops=4)

  • Join Filter: (convcomp.comp_convenio = hospcomp.comp_convenio)
6. 0.281 271.705 ↓ 331.0 331 4 / 4

Nested Loop (cost=2,242,675.73..2,531,644.44 rows=1 width=186) (actual time=194.598..271.705 rows=331 loops=4)

7. 14.516 270.763 ↓ 331.0 331 4 / 4

Parallel Hash Join (cost=2,242,646.73..2,531,613.93 rows=1 width=177) (actual time=194.563..270.763 rows=331 loops=4)

  • Hash Cond: ((guiahospsolta.guibenefic = hospben.id) AND ((guiahospsolta.guidtatend)::date = (guiaconvsolta.guidtatend)::date) AND (guiahospsolta.guinumprestador = guiaconvsolta.guinumprestador))
  • Join Filter: (abs((guiahospsolta.guivalortotal - guiaconvsolta.guivlrapresentado)) <= '0'::numeric)
  • Rows Removed by Join Filter: 1,044
8. 62.725 62.725 ↑ 1.3 30,888 4 / 4

Parallel Seq Scan on fiboguia guiahospsolta (cost=0.00..179,300.69 rows=39,878 width=77) (actual time=0.005..62.725 rows=30,888 loops=4)

  • Filter: ((NOT guiassociada) AND (guidtatend IS NOT NULL) AND (guinumprestador <> ''::text))
  • Rows Removed by Filter: 79,211
9. 7.562 193.522 ↑ 21.2 7,108 4 / 4

Parallel Hash (cost=1,941,678.73..1,941,678.73 rows=150,484 width=130) (actual time=193.522..193.522 rows=7,108 loops=4)

  • Buckets: 524,288 Batches: 1 Memory Usage: 8,960kB
10. 1.988 185.960 ↑ 21.2 7,108 4 / 4

Nested Loop (cost=314,472.83..1,941,678.73 rows=150,484 width=130) (actual time=80.467..185.960 rows=7,108 loops=4)

11. 2.530 161.918 ↑ 19.6 760 4 / 4

Parallel Hash Join (cost=314,388.33..533,438.88 rows=14,898 width=114) (actual time=80.398..161.918 rows=760 loops=4)

  • Hash Cond: (guiaconvsolta.guibenefic = convben.id)
12. 2.662 80.136 ↑ 19.7 760 4 / 4

Hash Join (cost=310.50..211,510.19 rows=14,954 width=82) (actual time=0.464..80.136 rows=760 loops=4)

  • Hash Cond: (guiaconvsolta.guiprestoriginal = prest.id)
13. 77.097 77.097 ↑ 1.3 30,888 4 / 4

Parallel Seq Scan on fiboguia guiaconvsolta (cost=0.00..179,300.69 rows=39,878 width=82) (actual time=0.016..77.097 rows=30,888 loops=4)

  • Filter: ((NOT guiassociada) AND (guidtatend IS NOT NULL) AND (guinumprestador <> ''::text))
  • Rows Removed by Filter: 79,211
14. 0.003 0.377 ↑ 18.3 3 4 / 4

Hash (cost=255.50..255.50 rows=55 width=16) (actual time=0.376..0.377 rows=3 loops=4)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
15. 0.095 0.374 ↑ 18.3 3 4 / 4

Hash Join (cost=6.00..255.50 rows=55 width=16) (actual time=0.310..0.374 rows=3 loops=4)

  • Hash Cond: (prest.prest_cod = regexp_replace(hospital.hospcnpj, '([\.|\-|/])'::text, ''::text, 'g'::text))
16. 0.053 0.053 ↓ 1.0 147 4 / 4

Seq Scan on fiboprestador prest (cost=0.00..76.00 rows=146 width=15) (actual time=0.032..0.053 rows=147 loops=4)

17. 0.209 0.226 ↑ 1.0 3 4 / 4

Hash (cost=3.00..3.00 rows=3 width=27) (actual time=0.226..0.226 rows=3 loops=4)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
18. 0.017 0.017 ↑ 1.0 3 4 / 4

Seq Scan on fibohospital hospital (cost=0.00..3.00 rows=3 width=27) (actual time=0.016..0.017 rows=3 loops=4)

19. 46.800 79.252 ↑ 1.7 91,454 4 / 4

Parallel Hash (cost=160,863.83..160,863.83 rows=153,214 width=48) (actual time=79.252..79.252 rows=91,454 loops=4)

  • Buckets: 524,288 Batches: 1 Memory Usage: 34,848kB
20. 32.452 32.452 ↑ 1.7 91,454 4 / 4

Parallel Seq Scan on fibobeneficiario convben (cost=0.00..160,863.83 rows=153,214 width=48) (actual time=0.032..32.452 rows=91,454 loops=4)

  • Filter: (bnf_matricula <> ''::text)
  • Rows Removed by Filter: 349
21. 22.055 22.055 ↓ 2.2 9 3,042 / 4

Index Scan using idx_fibobeneficiario_bnf_matricula_idx on fibobeneficiario hospben (cost=84.50..92.53 rows=4 width=48) (actual time=0.013..0.029 rows=9 loops=3,042)

  • Index Cond: (bnf_matricula = convben.bnf_matricula)
  • Filter: (bnf_matricula <> ''::text)
22. 0.661 0.661 ↑ 1.0 1 1,323 / 4

Index Scan using fibocompetencia_pkey on fibocompetencia convcomp (cost=29.00..30.50 rows=1 width=25) (actual time=0.002..0.002 rows=1 loops=1,323)

  • Index Cond: (id = guiaconvsolta.guicompetencia)
23. 0.661 0.661 ↑ 1.0 1 1,323 / 4

Index Scan using fibocompetencia_pkey on fibocompetencia hospcomp (cost=29.00..30.50 rows=1 width=25) (actual time=0.002..0.002 rows=1 loops=1,323)

  • Index Cond: (id = guiahospsolta.guicompetencia)
24. 0.331 0.331 ↓ 0.0 0 1,323 / 4

Index Scan using fiboremessa_pkey on fiboremessa remessa (cost=56.50..58.00 rows=1 width=16) (actual time=0.001..0.001 rows=0 loops=1,323)

  • Index Cond: (id = guiahospsolta.guiremessa)
25. 0.077 0.077 ↑ 1.0 1 62 / 4

Index Scan using fibooperadora_pkey on fibooperadora operadora (cost=28.00..29.50 rows=1 width=16) (actual time=0.005..0.005 rows=1 loops=62)

  • Index Cond: (id = remessa.operadora_id)
Planning time : 46.520 ms
Execution time : 308.671 ms