explain.depesz.com

PostgreSQL's explain analyze made readable

Result: tY4s : Optimization for: plan #GPxY

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 3.950 6,099.739 ↑ 1.1 930 1

Sort (cost=287,322.63..287,325.22 rows=1,037 width=1,184) (actual time=6,099.236..6,099.739 rows=930 loops=1)

  • Sort Key: (avg(CASE WHEN (medicaoindicador.valor IS NULL) THEN NULL::numeric WHEN (indicador.desempenhomelhor = 'A'::bpchar) THEN round(LEAST(GREATEST((((medicaoindicador.valor - indicador.desempenhominimo) / (indicador.desempenhomaximo - indicador.desempenhominimo)) * '100'::numeric), '0'::numeric), '100'::numeric), 2) ELSE round(LEAST(GREATEST((((indicador.desempenhomaximo - medicaoindicador.valor) / (indicador.desempenhomaximo - indicador.desempenhominimo)) * '100'::numeric), '0'::numeric), '100'::numeric), 2) END))
  • Sort Method: quicksort Memory: 1688kB
2.          

CTE medicoeseerros

3. 52.054 90.427 ↓ 1.3 135,002 1

Append (cost=0.00..3,383.97 rows=106,373 width=80) (actual time=0.009..90.427 rows=135,002 loops=1)

4. 37.731 37.731 ↓ 1.3 134,582 1

Seq Scan on ultimamedicao (cost=0.00..2,291.52 rows=105,952 width=66) (actual time=0.008..37.731 rows=134,582 loops=1)

5. 0.206 0.642 ↑ 1.0 420 1

Subquery Scan on *SELECT* 2 (cost=0.00..32.94 rows=421 width=80) (actual time=0.037..0.642 rows=420 loops=1)

6. 0.436 0.436 ↑ 1.0 420 1

Seq Scan on erromedicaoindicador (cost=0.00..27.67 rows=421 width=76) (actual time=0.034..0.436 rows=420 loops=1)

  • Filter: (datahora >= (('now'::cstring)::date - '1 mon'::interval))
  • Rows Removed by Filter: 11
7. 5,127.123 6,095.789 ↑ 1.1 930 1

GroupAggregate (cost=70,752.93..283,886.71 rows=1,037 width=1,184) (actual time=836.044..6,095.789 rows=930 loops=1)

  • Group Key: (((lpad(((unidadenegocio.grupoclientealpha7)::character varying)::text, 3, '0'::text) || '-'::text) || lpad(((unidadenegocio.codigoclientealpha7)::character varying)::text, 4, '0'::text))), unidadenegocio.id, NULL::text, NULL::text, NULL::text, NULL::text, NULL::text, NULL::text, NULL::text, NULL::text, NULL::text, NULL::text, NULL::text, NULL::text, NULL::text, NULL::text
8. 544.237 968.666 ↓ 1.3 135,002 1

Sort (cost=70,752.93..71,018.86 rows=106,373 width=576) (actual time=828.514..968.666 rows=135,002 loops=1)

  • Sort Key: (((lpad(((unidadenegocio.grupoclientealpha7)::character varying)::text, 3, '0'::text) || '-'::text) || lpad(((unidadenegocio.codigoclientealpha7)::character varying)::text, 4, '0'::text))), unidadenegocio.id
  • Sort Method: external merge Disk: 13056kB
9. 161.774 424.429 ↓ 1.3 135,002 1

Hash Join (cost=154.26..7,334.43 rows=106,373 width=576) (actual time=2.714..424.429 rows=135,002 loops=1)

  • Hash Cond: (medicaoindicador.unidadenegocioid = unidadenegocio.id)
10. 88.312 261.352 ↓ 1.3 135,002 1

Hash Join (cost=97.88..3,687.97 rows=106,373 width=96) (actual time=1.393..261.352 rows=135,002 loops=1)

  • Hash Cond: (medicaoindicador.indicadorid = indicador.id)
11. 171.663 171.663 ↓ 1.3 135,002 1

CTE Scan on medicoeseerros medicaoindicador (cost=0.00..2,127.46 rows=106,373 width=64) (actual time=0.011..171.663 rows=135,002 loops=1)

12. 0.105 1.377 ↓ 1.0 311 1

Hash (cost=94.06..94.06 rows=306 width=40) (actual time=1.377..1.377 rows=311 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 33kB
13. 0.171 1.272 ↓ 1.0 311 1

Hash Join (cost=18.17..94.06 rows=306 width=40) (actual time=0.308..1.272 rows=311 loops=1)

  • Hash Cond: (indicador.classificacaoinvestimentoid = classificacaoinvestimento.id)
14. 0.203 1.035 ↓ 1.0 311 1

Hash Join (cost=13.63..85.31 rows=306 width=48) (actual time=0.237..1.035 rows=311 loops=1)

  • Hash Cond: (indicador.classificacaoramoatividadeid = classificacaoramoatividade.id)
15. 0.179 0.771 ↓ 1.0 311 1

Hash Join (cost=9.09..76.56 rows=306 width=56) (actual time=0.172..0.771 rows=311 loops=1)

  • Hash Cond: (indicador.classificacaogestaoid = classificacaogestao.id)
16. 0.288 0.514 ↓ 1.0 311 1

Hash Join (cost=4.54..67.81 rows=306 width=42) (actual time=0.078..0.514 rows=311 loops=1)

  • Hash Cond: (indicador.classificacaodepartamentoid = classificacaodepartamento.id)
17. 0.158 0.158 ↓ 1.0 311 1

Seq Scan on indicador (cost=0.00..59.06 rows=306 width=50) (actual time=0.004..0.158 rows=311 loops=1)

18. 0.037 0.068 ↓ 1.1 119 1

Hash (cost=3.13..3.13 rows=113 width=8) (actual time=0.068..0.068 rows=119 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 13kB
19. 0.031 0.031 ↓ 1.1 119 1

Seq Scan on classificacaoindicador classificacaodepartamento (cost=0.00..3.13 rows=113 width=8) (actual time=0.003..0.031 rows=119 loops=1)

20. 0.042 0.078 ↓ 1.1 119 1

Hash (cost=3.13..3.13 rows=113 width=30) (actual time=0.078..0.078 rows=119 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 16kB
21. 0.036 0.036 ↓ 1.1 119 1

Seq Scan on classificacaoindicador classificacaogestao (cost=0.00..3.13 rows=113 width=30) (actual time=0.002..0.036 rows=119 loops=1)

22. 0.026 0.061 ↓ 1.1 119 1

Hash (cost=3.13..3.13 rows=113 width=8) (actual time=0.061..0.061 rows=119 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 13kB
23. 0.035 0.035 ↓ 1.1 119 1

Seq Scan on classificacaoindicador classificacaoramoatividade (cost=0.00..3.13 rows=113 width=8) (actual time=0.003..0.035 rows=119 loops=1)

24. 0.033 0.066 ↓ 1.1 119 1

Hash (cost=3.13..3.13 rows=113 width=8) (actual time=0.066..0.066 rows=119 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 13kB
25. 0.033 0.033 ↓ 1.1 119 1

Seq Scan on classificacaoindicador classificacaoinvestimento (cost=0.00..3.13 rows=113 width=8) (actual time=0.005..0.033 rows=119 loops=1)

26. 0.311 1.303 ↑ 1.1 947 1

Hash (cost=43.41..43.41 rows=1,037 width=16) (actual time=1.303..1.303 rows=947 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 61kB
27. 0.580 0.992 ↑ 1.1 947 1

Hash Join (cost=8.13..43.41 rows=1,037 width=16) (actual time=0.172..0.992 rows=947 loops=1)

  • Hash Cond: (unidadenegocio.organizacaoid = organizacao.id)
28. 0.258 0.258 ↑ 1.1 947 1

Seq Scan on unidadenegocio (cost=0.00..22.37 rows=1,037 width=24) (actual time=0.010..0.258 rows=947 loops=1)

29. 0.079 0.154 ↓ 1.2 269 1

Hash (cost=5.28..5.28 rows=228 width=8) (actual time=0.154..0.154 rows=269 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 19kB
30. 0.075 0.075 ↓ 1.2 269 1

Seq Scan on organizacao (cost=0.00..5.28 rows=228 width=8) (actual time=0.005..0.075 rows=269 loops=1)