explain.depesz.com

PostgreSQL's explain analyze made readable

Result: OjKDX

Settings
# exclusive inclusive rows x rows loops node
1. 0.001 2.148 ↓ 0.0 0 1

Limit (cost=244,895,387.20..244,895,392.20 rows=10 width=213) (actual time=2.148..2.148 rows=0 loops=1)

2. 0.022 2.147 ↓ 0.0 0 1

Sort (cost=244,895,387.20..251,072,401.20 rows=12,354,028 width=213) (actual time=2.147..2.147 rows=0 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: 25kB
3. 0.001 2.125 ↓ 0.0 0 1

Merge Join (cost=5,178.66..191,502,166.50 rows=12,354,028 width=213) (actual time=2.125..2.125 rows=0 loops=1)

  • Merge Cond: (convcomp.id = guiaconvsolta.guicompetencia)
4. 0.003 0.084 ↑ 5,938.0 1 1

Nested Loop (cost=29.00..6,518.00 rows=5,938 width=25) (actual time=0.083..0.084 rows=1 loops=1)

5. 0.065 0.065 ↑ 1.0 1 1

Index Scan using fibocompetencia_pkey on fibocompetencia convcomp (cost=29.00..394.00 rows=1 width=25) (actual time=0.065..0.065 rows=1 loops=1)

  • Filter: ((comp_convenio = 'Unimed Florianopolis'::text) AND (comp_desc = '09-2019'::text))
  • Rows Removed by Filter: 162
6. 0.016 0.016 ↑ 5,938.0 1 1

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

7. 0.026 2.040 ↓ 0.0 0 1

Sort (cost=5,149.66..5,151.16 rows=3 width=170) (actual time=2.040..2.040 rows=0 loops=1)

  • Sort Key: guiaconvsolta.guicompetencia
  • Sort Method: quicksort Memory: 25kB
8. 0.001 2.014 ↓ 0.0 0 1

Nested Loop (cost=2,476.54..5,144.90 rows=3 width=170) (actual time=2.014..2.014 rows=0 loops=1)

9. 0.000 2.013 ↓ 0.0 0 1

Nested Loop (cost=2,392.04..4,877.90 rows=3 width=154) (actual time=2.013..2.013 rows=0 loops=1)

10. 0.029 2.013 ↓ 0.0 0 1

Hash Join (cost=2,307.54..4,607.90 rows=3 width=138) (actual time=2.013..2.013 rows=0 loops=1)

  • Hash Cond: ((guiaconvsolta.guidtatend)::date = (guiahospsolta.guidtatend)::date)
  • Join Filter: (abs((guiahospsolta.guivalortotal - guiaconvsolta.guivlrapresentado)) <= '0'::numeric)
11. 0.015 0.974 ↑ 1.8 60 1

Nested Loop (cost=90.50..2,154.86 rows=110 width=74) (actual time=0.483..0.974 rows=60 loops=1)

12. 0.084 0.269 ↑ 18.3 3 1

Hash Join (cost=6.00..255.50 rows=55 width=0) (actual time=0.208..0.269 rows=3 loops=1)

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

Seq Scan on fiboprestador prest (cost=0.00..76.00 rows=146 width=7) (actual time=0.013..0.033 rows=147 loops=1)

14. 0.142 0.152 ↑ 1.0 3 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
15. 0.010 0.010 ↑ 1.0 3 1

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

16. 0.017 0.690 ↓ 10.0 20 3

Materialize (cost=84.50..1,790.36 rows=2 width=74) (actual time=0.091..0.230 rows=20 loops=3)

17. 0.673 0.673 ↓ 10.0 20 1

Index Scan using fiboguia_pkey on fiboguia guiaconvsolta (cost=84.50..1,788.36 rows=2 width=74) (actual time=0.270..0.673 rows=20 loops=1)

  • Index Cond: (id = ANY ('{119555533,119555454,119555414,119555521,119555362,119555478,119555354,119555450,119555444,119555498,119555493,119555409,119555446,119555371,119555391,119555487,119555538,119555485,119555427,119555355}'::bigint[]))
  • Filter: ((guiguiaassoc IS NULL) AND (guivlrpago > '0'::numeric))
18. 0.325 1.010 ↓ 50.7 760 1

Hash (cost=2,202.04..2,202.04 rows=15 width=64) (actual time=1.010..1.010 rows=760 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 95kB
19. 0.246 0.685 ↓ 50.7 760 1

Hash Join (cost=150.75..2,202.04 rows=15 width=64) (actual time=0.175..0.685 rows=760 loops=1)

  • Hash Cond: (guiahospsolta.guicompetencia = hospcomp.id)
20. 0.125 0.403 ↓ 3.3 760 1

Nested Loop (cost=84.50..2,015.36 rows=228 width=55) (actual time=0.124..0.403 rows=760 loops=1)

21. 0.158 0.158 ↓ 3.3 20 1

Index Scan using fiboguia_pkey on fiboguia guiahospsolta (cost=84.50..1,778.36 rows=6 width=55) (actual time=0.109..0.158 rows=20 loops=1)

  • Index Cond: (id = ANY ('{142577812,142577811,142577810,142577809,142577808,142577807,142577806,142577805,142577804,142577803,142577802,142577801,142577800,142577799,142577798,142577797,142577796,142577795,142577794,142577793}'::bigint[]))
  • Filter: (guiguiaassoc IS NULL)
22. 0.120 0.120 ↑ 1.0 38 20

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

23. 0.007 0.036 ↑ 1.0 12 1

Hash (cost=54.25..54.25 rows=12 width=25) (actual time=0.036..0.036 rows=12 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
24. 0.029 0.029 ↑ 1.0 12 1

Index Scan using idx_fibocompetencia_comp_convenio_idx on fibocompetencia hospcomp (cost=29.00..54.25 rows=12 width=25) (actual time=0.025..0.029 rows=12 loops=1)

  • Index Cond: (comp_convenio = 'Unimed Florianopolis'::text)
25. 0.000 0.000 ↓ 0.0 0

Index Scan using fibobeneficiario_pkey on fibobeneficiario hospben (cost=84.50..90.00 rows=1 width=32) (never executed)

  • Index Cond: (id = guiahospsolta.guibenefic)
  • Filter: (split_part(regexp_replace(bnf_nome, '(RN )(DE )'::text, ''::text), ' '::text, 1) IS NOT NULL)
26. 0.000 0.000 ↓ 0.0 0

Index Scan using fibobeneficiario_pkey on fibobeneficiario convben (cost=84.50..89.00 rows=1 width=32) (never executed)

  • Index Cond: (id = guiaconvsolta.guibenefic)
Planning time : 39.219 ms
Execution time : 2.414 ms