explain.depesz.com

PostgreSQL's explain analyze made readable

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

Settings

Optimization path:

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 0.177 14.445 ↑ 1.7 169 1

GroupAggregate (cost=6,456.45..6,473.22 rows=287 width=208) (actual time=14.265..14.445 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.310 14.268 ↑ 1.7 277 1

Sort (cost=6,456.45..6,457.62 rows=470 width=182) (actual time=14.258..14.268 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.034 13.958 ↑ 1.7 277 1

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

4. 0.113 13.924 ↑ 1.7 277 1

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

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

Hash Full Join (cost=4,071.07..6,408.85 rows=470 width=182) (actual time=10.960..13.811 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.126 3.021 ↑ 1.7 275 1

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

7. 0.057 1.245 ↓ 275.0 275 1

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

8. 0.048 0.913 ↓ 275.0 275 1

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

9. 0.011 0.590 ↓ 275.0 275 1

Nested Loop (cost=0.43..2,310.28 rows=1 width=30) (actual time=0.008..0.590 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.240 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.310 ↑ 1.0 1,447 1

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

  • Buckets: 2048 Batches: 1 Memory Usage: 106kB
18. 0.140 0.140 ↑ 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.140 rows=1,447 loops=1)

19. 0.105 10.613 ↓ 275.0 275 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 31kB
20. 5.418 10.508 ↓ 275.0 275 1

Nested Loop (cost=0.71..2,327.46 rows=1 width=43) (actual time=0.019..10.508 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.035 0.965 ↓ 275.0 275 1

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

22. 0.077 0.655 ↓ 275.0 275 1

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

23. 0.028 0.028 ↑ 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.028 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.968 ms
Execution time : 14.544 ms