explain.depesz.com

PostgreSQL's explain analyze made readable

Result: NsGI

Settings
# exclusive inclusive rows x rows loops node
1. 5,008.703 22,892.241 ↓ 5.2 207,827 1

Hash Join (cost=1,438,169.10..1,517,927.44 rows=40,207 width=658) (actual time=14,524.753..22,892.241 rows=207,827 loops=1)

  • Hash Cond: (medicaoindicador.unidadenegocioid = unidadenegocio.id)
2.          

CTE ultimamedicao

3. 815.425 17,336.253 ↓ 5.2 207,827 1

Unique (cost=1,390,403.01..1,436,644.17 rows=40,000 width=51) (actual time=14,523.518..17,336.253 rows=207,827 loops=1)

4. 11,197.510 16,520.828 ↓ 1.0 6,174,779 1

Sort (cost=1,390,403.01..1,405,816.73 rows=6,165,487 width=51) (actual time=14,523.513..16,520.828 rows=6,174,779 loops=1)

  • Sort Key: medicaoindicador_1.indicadorid, medicaoindicador_1.unidadenegocioid, medicaoindicador_1.data DESC
  • Sort Method: external merge Disk: 321072kB
5. 651.543 5,323.318 ↓ 1.0 6,174,779 1

Append (cost=0.42..273,596.07 rows=6,165,487 width=51) (actual time=0.613..5,323.318 rows=6,174,779 loops=1)

  • Subplans Removed: 10
6. 3,313.675 3,313.675 ↓ 1.0 4,080,275 1

Seq Scan on medicaoindicador_201908 medicaoindicador_1 (cost=0.00..178,778.28 rows=4,075,047 width=51) (actual time=0.612..3,313.675 rows=4,080,275 loops=1)

  • Filter: (data >= (CURRENT_DATE - '1 mon'::interval))
  • Rows Removed by Filter: 1909785
7. 1,358.007 1,358.007 ↓ 1.0 2,094,297 1

Seq Scan on medicaoindicador_201909 medicaoindicador_1_1 (cost=0.00..63,910.94 rows=2,090,225 width=51) (actual time=0.431..1,358.007 rows=2,094,297 loops=1)

  • Filter: (data >= (CURRENT_DATE - '1 mon'::interval))
8. 0.009 0.009 ↓ 3.0 3 1

Seq Scan on medicaoindicador_202006 medicaoindicador_1_2 (cost=0.00..1.05 rows=1 width=51) (actual time=0.008..0.009 rows=3 loops=1)

  • Filter: (data >= (CURRENT_DATE - '1 mon'::interval))
9. 0.084 0.084 ↑ 1.0 204 1

Seq Scan on medicaoindicador_202007 medicaoindicador_1_3 (cost=0.00..6.57 rows=204 width=51) (actual time=0.006..0.084 rows=204 loops=1)

  • Filter: (data >= (CURRENT_DATE - '1 mon'::interval))
10.          

CTE medicoeseerros

11. 22.366 17,460.377 ↓ 5.2 207,827 1

Append (cost=0.00..1,424.99 rows=40,207 width=80) (actual time=14,523.526..17,460.377 rows=207,827 loops=1)

12. 17,438.004 17,438.004 ↓ 5.2 207,827 1

CTE Scan on ultimamedicao (cost=0.00..800.00 rows=40,000 width=66) (actual time=14,523.525..17,438.004 rows=207,827 loops=1)

13. 0.001 0.007 ↓ 0.0 0 1

Subquery Scan on *SELECT* 2 (cost=0.00..23.95 rows=207 width=80) (actual time=0.007..0.007 rows=0 loops=1)

14. 0.006 0.006 ↓ 0.0 0 1

Seq Scan on erromedicaoindicador (cost=0.00..21.37 rows=207 width=76) (actual time=0.006..0.006 rows=0 loops=1)

  • Filter: (datahora >= (CURRENT_DATE - '7 days'::interval))
15. 63.234 17,883.047 ↓ 5.2 207,827 1

Hash Join (cost=53.39..1,402.72 rows=40,207 width=238) (actual time=14,524.065..17,883.047 rows=207,827 loops=1)

  • Hash Cond: (indicador.classificacaoinvestimentoid = classificacaoinvestimento.id)
16. 61.935 17,819.768 ↓ 5.2 207,827 1

Hash Join (cost=48.90..1,288.65 rows=40,207 width=215) (actual time=14,524.001..17,819.768 rows=207,827 loops=1)

  • Hash Cond: (indicador.classificacaoramoatividadeid = classificacaoramoatividade.id)
17. 63.908 17,757.791 ↓ 5.2 207,827 1

Hash Join (cost=44.40..1,174.58 rows=40,207 width=192) (actual time=14,523.943..17,757.791 rows=207,827 loops=1)

  • Hash Cond: (indicador.classificacaogestaoid = classificacaogestao.id)
18. 65.927 17,693.842 ↓ 5.2 207,827 1

Hash Join (cost=39.90..1,060.51 rows=40,207 width=169) (actual time=14,523.886..17,693.842 rows=207,827 loops=1)

  • Hash Cond: (indicador.classificacaodepartamentoid = classificacaodepartamento.id)
19. 95.380 17,627.875 ↓ 5.2 207,827 1

Hash Join (cost=35.40..946.44 rows=40,207 width=146) (actual time=14,523.830..17,627.875 rows=207,827 loops=1)

  • Hash Cond: (medicaoindicador.indicadorid = indicador.id)
20. 17,532.230 17,532.230 ↓ 5.2 207,827 1

CTE Scan on medicoeseerros medicaoindicador (cost=0.00..804.14 rows=40,207 width=64) (actual time=14,523.528..17,532.230 rows=207,827 loops=1)

21. 0.103 0.265 ↑ 1.0 329 1

Hash (cost=31.29..31.29 rows=329 width=90) (actual time=0.265..0.265 rows=329 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 51kB
22. 0.162 0.162 ↑ 1.0 329 1

Seq Scan on indicador (cost=0.00..31.29 rows=329 width=90) (actual time=0.024..0.162 rows=329 loops=1)

23. 0.023 0.040 ↑ 1.0 111 1

Hash (cost=3.11..3.11 rows=111 width=31) (actual time=0.040..0.040 rows=111 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 15kB
24. 0.017 0.017 ↑ 1.0 111 1

Seq Scan on classificacaoindicador classificacaodepartamento (cost=0.00..3.11 rows=111 width=31) (actual time=0.002..0.017 rows=111 loops=1)

25. 0.024 0.041 ↑ 1.0 111 1

Hash (cost=3.11..3.11 rows=111 width=31) (actual time=0.040..0.041 rows=111 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 15kB
26. 0.017 0.017 ↑ 1.0 111 1

Seq Scan on classificacaoindicador classificacaogestao (cost=0.00..3.11 rows=111 width=31) (actual time=0.003..0.017 rows=111 loops=1)

27. 0.025 0.042 ↑ 1.0 111 1

Hash (cost=3.11..3.11 rows=111 width=31) (actual time=0.042..0.042 rows=111 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 15kB
28. 0.017 0.017 ↑ 1.0 111 1

Seq Scan on classificacaoindicador classificacaoramoatividade (cost=0.00..3.11 rows=111 width=31) (actual time=0.003..0.017 rows=111 loops=1)

29. 0.023 0.045 ↑ 1.0 111 1

Hash (cost=3.11..3.11 rows=111 width=31) (actual time=0.045..0.045 rows=111 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 15kB
30. 0.022 0.022 ↑ 1.0 111 1

Seq Scan on classificacaoindicador classificacaoinvestimento (cost=0.00..3.11 rows=111 width=31) (actual time=0.006..0.022 rows=111 loops=1)

31. 0.213 0.491 ↑ 1.0 1,358 1

Hash (cost=29.58..29.58 rows=1,358 width=16) (actual time=0.491..0.491 rows=1,358 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 80kB
32. 0.278 0.278 ↑ 1.0 1,358 1

Seq Scan on unidadenegocio (cost=0.00..29.58 rows=1,358 width=16) (actual time=0.020..0.278 rows=1,358 loops=1)

Planning time : 30.293 ms
Execution time : 22,959.978 ms