explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 5VQc

Settings
# exclusive inclusive rows x rows loops node
1. 5,098.942 71,749.515 ↓ 5.2 207,827 1

Hash Join (cost=1,016,699.10..1,096,457.44 rows=40,207 width=658) (actual time=64,205.908..71,749.515 rows=207,827 loops=1)

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

CTE ultimamedicao

3. 822.141 66,000.672 ↓ 5.2 207,827 1

Unique (cost=968,933.01..1,015,174.17 rows=40,000 width=51) (actual time=64,171.091..66,000.672 rows=207,827 loops=1)

4. 60,163.500 65,178.531 ↓ 1.0 6,175,262 1

Sort (cost=968,933.01..984,346.73 rows=6,165,487 width=51) (actual time=64,170.761..65,178.531 rows=6,175,262 loops=1)

  • Sort Key: medicaoindicador_1.indicadorid, medicaoindicador_1.unidadenegocioid, medicaoindicador_1.data DESC
  • Sort Method: external merge Disk: 321032kB
5. 657.101 5,015.031 ↓ 1.0 6,175,262 1

Append (cost=0.42..273,596.07 rows=6,165,487 width=51) (actual time=0.512..5,015.031 rows=6,175,262 loops=1)

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

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

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

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

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

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

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

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

CTE medicoeseerros

11. 23.320 66,153.596 ↓ 5.2 207,827 1

Append (cost=0.00..1,424.99 rows=40,207 width=80) (actual time=64,171.787..66,153.596 rows=207,827 loops=1)

12. 66,129.544 66,129.544 ↓ 5.2 207,827 1

CTE Scan on ultimamedicao (cost=0.00..800.00 rows=40,000 width=66) (actual time=64,171.559..66,129.544 rows=207,827 loops=1)

13. 0.258 0.732 ↓ 0.0 0 1

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

14. 0.474 0.474 ↓ 0.0 0 1

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

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

Hash Join (cost=53.39..1,402.72 rows=40,207 width=238) (actual time=64,183.365..66,650.136 rows=207,827 loops=1)

  • Hash Cond: (indicador.classificacaoinvestimentoid = classificacaoinvestimento.id)
16. 62.400 66,583.841 ↓ 5.2 207,827 1

Hash Join (cost=48.90..1,288.65 rows=40,207 width=215) (actual time=64,181.736..66,583.841 rows=207,827 loops=1)

  • Hash Cond: (indicador.classificacaoramoatividadeid = classificacaoramoatividade.id)
17. 67.239 66,521.403 ↓ 5.2 207,827 1

Hash Join (cost=44.40..1,174.58 rows=40,207 width=192) (actual time=64,180.676..66,521.403 rows=207,827 loops=1)

  • Hash Cond: (indicador.classificacaogestaoid = classificacaogestao.id)
18. 69.605 66,454.126 ↓ 5.2 207,827 1

Hash Join (cost=39.90..1,060.51 rows=40,207 width=169) (actual time=64,179.386..66,454.126 rows=207,827 loops=1)

  • Hash Cond: (indicador.classificacaodepartamentoid = classificacaodepartamento.id)
19. 99.745 66,384.484 ↓ 5.2 207,827 1

Hash Join (cost=35.40..946.44 rows=40,207 width=146) (actual time=64,177.864..66,384.484 rows=207,827 loops=1)

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

CTE Scan on medicoeseerros medicaoindicador (cost=0.00..804.14 rows=40,207 width=64) (actual time=64,172.438..66,283.232 rows=207,827 loops=1)

21. 0.522 1.507 ↑ 1.0 329 1

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

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

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

23. 0.020 0.037 ↑ 1.0 111 1

Hash (cost=3.11..3.11 rows=111 width=31) (actual time=0.037..0.037 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.003..0.017 rows=111 loops=1)

25. 0.019 0.038 ↑ 1.0 111 1

Hash (cost=3.11..3.11 rows=111 width=31) (actual time=0.038..0.038 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.003..0.019 rows=111 loops=1)

27. 0.020 0.038 ↑ 1.0 111 1

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

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

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

29. 0.020 0.041 ↑ 1.0 111 1

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

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

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

31. 0.180 0.437 ↑ 1.0 1,358 1

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

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

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

Planning time : 3.988 ms
Execution time : 71,834.305 ms