explain.depesz.com

A tool for finding a real cause for slow queries.

Result: vS1

options
# exclusive inclusive rows x rows loops node
1. 3.229 1295.443 ↓ 7.9 3977 1
→

Merge Left Join (cost=112687.26..112693.75 rows=504 width=104) (actual time=1290.664..1295.443 rows=3977 loops=1)

  • Merge Cond: ((guiaconvenio.id = guiaprestador.guiguiaassoc) AND ((COALESCE(agrupadorconvenio.id, (-1)::bigint)) = (COALESCE(agrupadorprestador.id, (-1)::bigint))))
2. 3.363 263.655 ↓ 7.9 3977 1
→

Sort (cost=43242.55..43243.81 rows=504 width=48) (actual time=262.774..263.655 rows=3977 loops=1)

  • Sort Key: guiaconvenio.id, (COALESCE(agrupadorconvenio.id, (-1)::bigint))
  • Sort Method: quicksort Memory: 344kB
3. 36.793 260.292 ↓ 7.9 3977 1
→

HashAggregate (cost=43209.85..43214.89 rows=504 width=22) (actual time=258.943..260.292 rows=3977 loops=1)

4. 26.367 223.499 ↓ 104.7 52746 1
→

Hash Left Join (cost=6965.22..43206.07 rows=504 width=22) (actual time=65.278..223.499 rows=52746 loops=1)

  • Hash Cond: (produtoconvenio.prodcc = agrupadorconvenio.id)
5. 48.093 197.110 ↓ 104.7 52746 1
→

Hash Join (cost=6963.57..43197.53 rows=504 width=22) (actual time=65.238..197.110 rows=52746 loops=1)

  • Hash Cond: ((produtoconvenio.prodguia = guiaconvenio.id) AND (competenciaconvenio.id = guiaconvenio.guicompetencia))
6. 55.324 93.674 ↓ 4.6 106711 1
→

Nested Loop (cost=538.45..36245.98 rows=23311 width=38) (actual time=9.854..93.674 rows=106711 loops=1)

7. 0.057 0.057 ↑ 1.0 1 1
→

Index Scan using fibocompetencia_pkey on fibocompetencia competenciaconvenio (cost=0.00..75.83 rows=1 width=8) (actual time=0.046..0.057 rows=1 loops=1)

  • Filter: (((comp_convenio)::text = 'Ipasgo'::text) AND ((comp_desc)::text = '01-2011'::text))
8. 28.883 38.293 ↓ 3.7 106711 1
→

Bitmap Heap Scan on fiboproduto produtoconvenio (cost=538.45..35812.00 rows=28652 width=30) (actual time=9.797..38.293 rows=106711 loops=1)

  • Recheck Cond: (prodcomp = competenciaconvenio.id)
9. 9.410 9.410 ↓ 3.7 106711 1
→

Bitmap Index Scan on idx_fiboproduto_prodcomp_idx (cost=0.00..531.29 rows=28652 width=0) (actual time=9.410..9.410 rows=106711 loops=1)

  • Index Cond: (prodcomp = competenciaconvenio.id)
10. 19.074 55.343 ↓ 1.0 55276 1
→

Hash (cost=5603.84..5603.84 rows=54752 width=16) (actual time=55.343..55.343 rows=55276 loops=1)

  • Buckets: 8192 Batches: 1 Memory Usage: 1944kB
11. 36.269 36.269 ↓ 1.0 55287 1
→

Seq Scan on fiboguia guiaconvenio (cost=0.00..5603.84 rows=54752 width=16) (actual time=0.033..36.269 rows=55287 loops=1)

  • Filter: ((guiorigem)::text = 'CONVENIO'::text)
12. 0.006 0.022 ↓ 1.2 35 1
→

Hash (cost=1.29..1.29 rows=29 width=8) (actual time=0.022..0.022 rows=35 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 1kB
13. 0.016 0.016 ↓ 1.2 35 1
→

Seq Scan on fibocentrodecusto agrupadorconvenio (cost=0.00..1.29 rows=29 width=8) (actual time=0.004..0.016 rows=35 loops=1)

14. 3.215 1028.559 ↓ 10.8 3861 1
→

Sort (cost=69444.71..69445.60 rows=356 width=56) (actual time=1027.883..1028.559 rows=3861 loops=1)

  • Sort Key: guiaprestador.guiguiaassoc, (COALESCE(agrupadorprestador.id, (-1)::bigint))
  • Sort Method: quicksort Memory: 336kB
15. 36.697 1025.344 ↓ 10.8 3861 1
→

HashAggregate (cost=69422.50..69426.06 rows=356 width=30) (actual time=1023.704..1025.344 rows=3861 loops=1)

16. 26.397 988.647 ↓ 147.6 52532 1
→

Hash Left Join (cost=11236.19..69419.83 rows=356 width=30) (actual time=710.055..988.647 rows=52532 loops=1)

  • Hash Cond: (produtoprestador.prodcc = agrupadorprestador.id)
17. 471.907 962.227 ↓ 147.6 52532 1
→

Hash Join (cost=11234.54..69413.31 rows=356 width=30) (actual time=710.012..962.227 rows=52532 loops=1)

  • Hash Cond: ((produtoprestador.prodguia = guiaprestador.id) AND (produtoprestador.prodcomp = guiaprestador.guicompetencia))
18. 402.812 402.812 ↑ 1.0 1213051 1
→

Seq Scan on fiboproduto produtoprestador (cost=0.00..49003.12 rows=1222946 width=30) (actual time=0.004..402.812 rows=1213051 loops=1)

  • Filter: prodassociado
19. 1.435 87.508 ↓ 4.9 3638 1
→

Hash (cost=11223.42..11223.42 rows=741 width=32) (actual time=87.508..87.508 rows=3638 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 185kB
20. 10.232 86.073 ↓ 4.9 3638 1
→

Hash Join (cost=4868.13..11223.42 rows=741 width=32) (actual time=38.921..86.073 rows=3638 loops=1)

  • Hash Cond: (guiaprestador.guiguiaassoc = guiaconvenioassociada.id)
21. 20.417 70.956 ↓ 2.8 33120 1
→

Hash Join (cost=2.89..6219.40 rows=11678 width=32) (actual time=0.063..70.956 rows=33120 loops=1)

  • Hash Cond: (guiaprestador.guicompetencia = competenciaprestador.id)
22. 50.517 50.517 ↑ 1.0 56705 1
→

Seq Scan on fiboguia guiaprestador (cost=0.00..5884.40 rows=57420 width=24) (actual time=0.005..50.517 rows=56705 loops=1)

  • Filter: (((discriminador)::text <> 'RECURSO'::text) AND ((guiorigem)::text = 'HOSPITAL'::text))
23. 0.003 0.022 ↑ 1.1 11 1
→

Hash (cost=2.74..2.74 rows=12 width=8) (actual time=0.022..0.022 rows=11 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 1kB
24. 0.019 0.019 ↑ 1.1 11 1
→

Seq Scan on fibocompetencia competenciaprestador (cost=0.00..2.74 rows=12 width=8) (actual time=0.005..0.019 rows=11 loops=1)

  • Filter: ((comp_convenio)::text = 'Ipasgo'::text)
25. 1.997 4.885 ↓ 1.0 7378 1
→

Hash (cost=4773.45..4773.45 rows=7343 width=8) (actual time=4.885..4.885 rows=7378 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 202kB
26. 2.263 2.888 ↓ 1.0 7378 1
→

Bitmap Heap Scan on fiboguia guiaconvenioassociada (cost=361.20..4773.45 rows=7343 width=8) (actual time=0.661..2.888 rows=7378 loops=1)

  • Recheck Cond: (guicompetencia = 29720576)
27. 0.625 0.625 ↓ 1.0 7378 1
→

Bitmap Index Scan on idx_fiboguia_guicompetencia_idx (cost=0.00..359.37 rows=7343 width=0) (actual time=0.625..0.625 rows=7378 loops=1)

  • Index Cond: (guicompetencia = 29720576)
28. 0.011 0.023 ↓ 1.2 35 1
→

Hash (cost=1.29..1.29 rows=29 width=8) (actual time=0.023..0.023 rows=35 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 1kB
29. 0.012 0.012 ↓ 1.2 35 1
→

Seq Scan on fibocentrodecusto agrupadorprestador (cost=0.00..1.29 rows=29 width=8) (actual time=0.004..0.012 rows=35 loops=1)