explain.depesz.com

PostgreSQL's explain analyze made readable

Result: SjbM

Settings
# exclusive inclusive rows x rows loops node
1. 5,112.520 20,842.250 ↓ 5.2 207,827 1

Hash Join (cost=1,227,434.10..1,307,192.44 rows=40,207 width=658) (actual time=12,102.579..20,842.250 rows=207,827 loops=1)

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

CTE ultimamedicao

3. 825.405 15,227.064 ↓ 5.2 207,827 1

Unique (cost=1,179,668.01..1,225,909.17 rows=40,000 width=51) (actual time=12,099.975..15,227.064 rows=207,827 loops=1)

4. 9,013.036 14,401.659 ↓ 1.0 6,175,075 1

Sort (cost=1,179,668.01..1,195,081.73 rows=6,165,487 width=51) (actual time=12,099.972..14,401.659 rows=6,175,075 loops=1)

  • Sort Key: medicaoindicador_1.indicadorid, medicaoindicador_1.unidadenegocioid, medicaoindicador_1.data DESC
  • Sort Method: external merge Disk: 321048kB
5. 683.487 5,388.623 ↓ 1.0 6,175,075 1

Append (cost=0.42..273,596.07 rows=6,165,487 width=51) (actual time=0.814..5,388.623 rows=6,175,075 loops=1)

  • Subplans Removed: 10
6. 3,399.502 3,399.502 ↓ 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.813..3,399.502 rows=4,080,275 loops=1)

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

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

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

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

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

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

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

CTE medicoeseerros

11. 22.835 15,307.661 ↓ 5.2 207,827 1

Append (cost=0.00..1,424.99 rows=40,207 width=80) (actual time=12,099.980..15,307.661 rows=207,827 loops=1)

12. 15,284.815 15,284.815 ↓ 5.2 207,827 1

CTE Scan on ultimamedicao (cost=0.00..800.00 rows=40,000 width=66) (actual time=12,099.978..15,284.815 rows=207,827 loops=1)

13. 0.002 0.011 ↓ 0.0 0 1

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

14. 0.009 0.009 ↓ 0.0 0 1

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

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

Hash Join (cost=53.39..1,402.72 rows=40,207 width=238) (actual time=12,100.503..15,727.818 rows=207,827 loops=1)

  • Hash Cond: (indicador.classificacaoinvestimentoid = classificacaoinvestimento.id)
16. 64.910 15,662.340 ↓ 5.2 207,827 1

Hash Join (cost=48.90..1,288.65 rows=40,207 width=215) (actual time=12,100.441..15,662.340 rows=207,827 loops=1)

  • Hash Cond: (indicador.classificacaoramoatividadeid = classificacaoramoatividade.id)
17. 65.370 15,597.388 ↓ 5.2 207,827 1

Hash Join (cost=44.40..1,174.58 rows=40,207 width=192) (actual time=12,100.389..15,597.388 rows=207,827 loops=1)

  • Hash Cond: (indicador.classificacaogestaoid = classificacaogestao.id)
18. 68.007 15,531.979 ↓ 5.2 207,827 1

Hash Join (cost=39.90..1,060.51 rows=40,207 width=169) (actual time=12,100.340..15,531.979 rows=207,827 loops=1)

  • Hash Cond: (indicador.classificacaodepartamentoid = classificacaodepartamento.id)
19. 98.281 15,463.933 ↓ 5.2 207,827 1

Hash Join (cost=35.40..946.44 rows=40,207 width=146) (actual time=12,100.291..15,463.933 rows=207,827 loops=1)

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

CTE Scan on medicoeseerros medicaoindicador (cost=0.00..804.14 rows=40,207 width=64) (actual time=12,099.983..15,365.367 rows=207,827 loops=1)

21. 0.106 0.285 ↑ 1.0 329 1

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

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

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

23. 0.020 0.039 ↑ 1.0 111 1

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

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

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

25. 0.020 0.039 ↑ 1.0 111 1

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

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

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

27. 0.020 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.022 0.022 ↑ 1.0 111 1

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

29. 0.021 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.024 0.024 ↑ 1.0 111 1

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

31. 0.233 1.912 ↑ 1.0 1,358 1

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

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

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

Planning time : 9.511 ms
Execution time : 20,904.089 ms