explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 6ZUgK : Optimization for: plan #9M8b

Settings

Optimization path:

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 0.178 14.503 ↑ 1.7 169 1

GroupAggregate (cost=6,456.45..6,473.22 rows=287 width=208) (actual time=14.321..14.503 rows=169 loops=1)

  • Group Key: core.data_envio_site, core.convenio, core.competencia, core.protocolo_zg, core.data_envio_zg, core.protocolo_site
2. 0.315 14.325 ↑ 1.7 277 1

Sort (cost=6,456.45..6,457.62 rows=470 width=182) (actual time=14.315..14.325 rows=277 loops=1)

  • Sort Key: core.data_envio_site, core.convenio, core.competencia, core.protocolo_zg, core.data_envio_zg, core.protocolo_site
  • Sort Method: quicksort Memory: 63kB
3. 0.035 14.010 ↑ 1.7 277 1

Subquery Scan on core (cost=6,429.71..6,435.59 rows=470 width=182) (actual time=13.964..14.010 rows=277 loops=1)

4. 0.112 13.975 ↑ 1.7 277 1

Sort (cost=6,429.71..6,430.89 rows=470 width=182) (actual time=13.963..13.975 rows=277 loops=1)

  • Sort Key: protocolo_no_site.data_envio
  • Sort Method: quicksort Memory: 63kB
5. 0.182 13.863 ↑ 1.7 277 1

Hash Full Join (cost=4,071.07..6,408.85 rows=470 width=182) (actual time=10.981..13.863 rows=277 loops=1)

  • Hash Cond: ((comp.comp_convenio = comp_1.comp_convenio) AND (comp.comp_desc = comp_1.comp_desc) AND (COALESCE(registro_envio.protocolo, lote_recurso.protocolo_envio) = protocolo_no_site.numero) AND (guia_recurso.id = guia_recurso_1.id))
6. 0.136 3.049 ↑ 1.7 275 1

Nested Loop Left Join (cost=1,743.59..4,073.13 rows=470 width=115) (actual time=0.342..3.049 rows=275 loops=1)

7. 0.060 1.263 ↓ 275.0 275 1

Nested Loop (cost=0.99..2,314.08 rows=1 width=45) (actual time=0.013..1.263 rows=275 loops=1)

8. 0.045 0.928 ↓ 275.0 275 1

Nested Loop (cost=0.71..2,311.27 rows=1 width=39) (actual time=0.009..0.928 rows=275 loops=1)

9. 0.029 0.608 ↓ 275.0 275 1

Nested Loop (cost=0.43..2,310.28 rows=1 width=30) (actual time=0.007..0.608 rows=275 loops=1)

10. 0.029 0.029 ↑ 1.0 275 1

Seq Scan on dados_guia_pendencia_recurso_no_site dados_guia_no_site (cost=0.00..5.75 rows=275 width=8) (actual time=0.003..0.029 rows=275 loops=1)

11. 0.550 0.550 ↑ 1.0 1 275

Index Scan using fiboguia_pkey on fiboguia guia_recurso (cost=0.43..8.38 rows=1 width=30) (actual time=0.002..0.002 rows=1 loops=275)

  • Index Cond: (id = dados_guia_no_site.guia_pendencia_recurso_id)
  • Filter: ((discriminador = 'RECURSO'::text) AND (status_envio_recurso = 'ENVIADO'::text))
12. 0.275 0.275 ↑ 1.0 1 275

Index Scan using fibocompetencia_pkey on fibocompetencia comp (cost=0.28..1.00 rows=1 width=25) (actual time=0.001..0.001 rows=1 loops=275)

  • Index Cond: (id = guia_recurso.guicompetencia)
13. 0.275 0.275 ↑ 1.0 1 275

Index Scan using id_lote_recurso_pkey on lote_recurso (cost=0.28..2.81 rows=1 width=22) (actual time=0.001..0.001 rows=1 loops=275)

  • Index Cond: (id = guia_recurso.id_lote_recurso)
14. 0.550 1.650 ↓ 0.0 0 275

HashAggregate (cost=1,742.60..1,749.65 rows=470 width=48) (actual time=0.006..0.006 rows=0 loops=275)

  • Group Key: registro_envio.protocolo, (registro_envio.data_envio)::date
15. 0.237 1.100 ↓ 0.0 0 275

Hash Join (cost=50.00..1,739.08 rows=470 width=22) (actual time=0.004..0.004 rows=0 loops=275)

  • Hash Cond: (produto.registro_envio_recurso_id = registro_envio.id)
16. 0.550 0.550 ↑ 117.5 4 275

Index Scan using idx_fiboproduto_prodguia_idx on fiboproduto produto (cost=0.44..1,687.11 rows=470 width=14) (actual time=0.002..0.002 rows=4 loops=275)

  • Index Cond: (prodguia = guia_recurso.id)
17. 0.170 0.313 ↑ 1.0 1,447 1

Hash (cost=31.47..31.47 rows=1,447 width=28) (actual time=0.313..0.313 rows=1,447 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 106kB
18. 0.143 0.143 ↑ 1.0 1,447 1

Seq Scan on registro_envio_recurso registro_envio (cost=0.00..31.47 rows=1,447 width=28) (actual time=0.003..0.143 rows=1,447 loops=1)

19. 0.102 10.632 ↓ 275.0 275 1

Hash (cost=2,327.46..2,327.46 rows=1 width=43) (actual time=10.632..10.632 rows=275 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 31kB
20. 5.448 10.530 ↓ 275.0 275 1

Nested Loop Left Join (cost=0.71..2,327.46 rows=1 width=43) (actual time=0.020..10.530 rows=275 loops=1)

  • Join Filter: (dados_guia_pendencia_recurso_no_site.id = protocolo_no_site.dados_guia_pendencia_recurso_no_site_id)
  • Rows Removed by Join Filter: 75350
21. 0.040 0.957 ↓ 275.0 275 1

Nested Loop (cost=0.71..2,311.27 rows=1 width=33) (actual time=0.017..0.957 rows=275 loops=1)

22. 0.067 0.642 ↓ 275.0 275 1

Nested Loop (cost=0.43..2,310.28 rows=1 width=24) (actual time=0.014..0.642 rows=275 loops=1)

23. 0.025 0.025 ↑ 1.0 275 1

Seq Scan on dados_guia_pendencia_recurso_no_site (cost=0.00..5.75 rows=275 width=16) (actual time=0.005..0.025 rows=275 loops=1)

24. 0.550 0.550 ↑ 1.0 1 275

Index Scan using fiboguia_pkey on fiboguia guia_recurso_1 (cost=0.43..8.38 rows=1 width=16) (actual time=0.002..0.002 rows=1 loops=275)

  • Index Cond: (id = dados_guia_pendencia_recurso_no_site.guia_pendencia_recurso_id)
  • Filter: ((discriminador = 'RECURSO'::text) AND (status_envio_recurso = 'ENVIADO'::text))
25. 0.275 0.275 ↑ 1.0 1 275

Index Scan using fibocompetencia_pkey on fibocompetencia comp_1 (cost=0.28..1.00 rows=1 width=25) (actual time=0.001..0.001 rows=1 loops=275)

  • Index Cond: (id = guia_recurso_1.guicompetencia)
26. 4.125 4.125 ↑ 1.0 275 275

Seq Scan on protocolo_recurso_no_site protocolo_no_site (cost=0.00..12.75 rows=275 width=26) (actual time=0.000..0.015 rows=275 loops=275)

Planning time : 3.475 ms
Execution time : 14.602 ms