explain.depesz.com

PostgreSQL's explain analyze made readable

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

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 0.176 14.364 ↑ 1.7 169 1

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

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

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

4. 0.116 13.843 ↑ 1.7 277 1

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

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

Hash Full Join (cost=4,071.07..6,408.85 rows=470 width=182) (actual time=10.909..13.727 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.112 2.976 ↑ 1.7 275 1

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

7. 0.054 1.214 ↓ 275.0 275 1

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

8. 0.044 0.885 ↓ 275.0 275 1

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

9. 0.000 0.566 ↓ 275.0 275 1

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

10. 0.028 0.028 ↑ 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.002..0.028 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.241 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.309 ↑ 1.0 1,447 1

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

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

19. 0.110 10.572 ↓ 275.0 275 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 31kB
20. 5.399 10.462 ↓ 275.0 275 1

Nested Loop (cost=0.71..2,327.46 rows=1 width=43) (actual time=0.018..10.462 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.033 0.938 ↓ 275.0 275 1

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

22. 0.056 0.630 ↓ 275.0 275 1

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

23. 0.024 0.024 ↑ 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.024 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.734 ms
Execution time : 14.453 ms