explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 9M8b

Settings

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 8.099 1,888.518 ↓ 24.1 6,915 1

GroupAggregate (cost=7,150.26..7,167.03 rows=287 width=208) (actual time=1,879.557..1,888.518 rows=6,915 loops=1)

  • Group Key: core.data_envio_site, core.convenio, core.competencia, core.protocolo_zg, core.data_envio_zg, core.protocolo_site
2. 31.381 1,880.419 ↓ 38.2 17,955 1

Sort (cost=7,150.26..7,151.43 rows=470 width=182) (actual time=1,879.547..1,880.419 rows=17,955 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: 3181kB
3. 2.071 1,849.038 ↓ 38.2 17,955 1

Subquery Scan on core (cost=7,123.52..7,129.40 rows=470 width=182) (actual time=1,846.296..1,849.038 rows=17,955 loops=1)

4. 6.566 1,846.967 ↓ 38.2 17,955 1

Sort (cost=7,123.52..7,124.70 rows=470 width=182) (actual time=1,846.295..1,846.967 rows=17,955 loops=1)

  • Sort Key: protocolo_no_site.data_envio
  • Sort Method: quicksort Memory: 3181kB
5. 8.265 1,840.401 ↓ 38.2 17,955 1

Hash Full Join (cost=4,071.33..7,102.66 rows=470 width=182) (actual time=11.114..1,840.401 rows=17,955 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. 12.056 1,821.502 ↓ 38.2 17,953 1

Nested Loop Left Join (cost=1,743.85..4,766.95 rows=470 width=115) (actual time=0.475..1,821.502 rows=17,953 loops=1)

7. 325.453 1,719.976 ↓ 17,894.0 17,894 1

Nested Loop Left Join (cost=1.25..3,007.90 rows=1 width=45) (actual time=0.148..1,719.976 rows=17,894 loops=1)

  • Join Filter: (guia_recurso.id = dados_guia_no_site.guia_pendencia_recurso_id)
  • Rows Removed by Join Filter: 4920575
8. 4.345 1,108.219 ↓ 17,894.0 17,894 1

Nested Loop (cost=1.25..2,998.71 rows=1 width=45) (actual time=0.110..1,108.219 rows=17,894 loops=1)

9. 4.380 1,085.980 ↓ 17,894.0 17,894 1

Merge Join (cost=0.97..2,997.71 rows=1 width=36) (actual time=0.106..1,085.980 rows=17,894 loops=1)

  • Merge Cond: (guia_recurso.id_lote_recurso = lote_recurso.id)
10. 1,080.220 1,080.220 ↓ 101.1 17,894 1

Index Scan using idx_fiboguia_id_lote_recurso on fiboguia guia_recurso (cost=0.43..477,461.67 rows=177 width=30) (actual time=0.086..1,080.220 rows=17,894 loops=1)

  • Filter: ((discriminador = 'RECURSO'::text) AND (status_envio_recurso = 'ENVIADO'::text))
  • Rows Removed by Filter: 2651855
11. 1.380 1.380 ↑ 1.0 6,901 1

Index Scan using id_lote_recurso_pkey on lote_recurso (cost=0.28..282.50 rows=6,901 width=22) (actual time=0.004..1.380 rows=6,901 loops=1)

12. 17.894 17.894 ↑ 1.0 1 17,894

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=17,894)

  • Index Cond: (id = guia_recurso.guicompetencia)
13. 286.304 286.304 ↑ 1.0 275 17,894

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.016 rows=275 loops=17,894)

14. 35.788 89.470 ↓ 0.0 0 17,894

HashAggregate (cost=1,742.60..1,749.65 rows=470 width=48) (actual time=0.005..0.005 rows=0 loops=17,894)

  • Group Key: registro_envio.protocolo, (registro_envio.data_envio)::date
15. 17.585 53.682 ↓ 0.0 0 17,894

Hash Join (cost=50.00..1,739.08 rows=470 width=22) (actual time=0.003..0.003 rows=0 loops=17,894)

  • Hash Cond: (produto.registro_envio_recurso_id = registro_envio.id)
16. 35.788 35.788 ↑ 94.0 5 17,894

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=5 loops=17,894)

  • 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.104 10.634 ↓ 275.0 275 1

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

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

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

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

22. 0.076 0.651 ↓ 275.0 275 1

Nested Loop (cost=0.43..2,310.28 rows=1 width=24) (actual time=0.012..0.651 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 : 1.356 ms
Execution time : 1,888.966 ms