explain.depesz.com

PostgreSQL's explain analyze made readable

Result: pYFN

Settings
# exclusive inclusive rows x rows loops node
1. 4.982 5,700.444 ↑ 1.1 930 1

Sort (cost=295,248.26..295,250.85 rows=1,037 width=1,184) (actual time=5,699.944..5,700.444 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. 50.880 86.313 ↑ 1.0 135,004 1

Append (cost=0.00..3,955.64 rows=135,005 width=80) (actual time=0.005..86.313 rows=135,004 loops=1)

4. 34.698 34.698 ↑ 1.0 134,582 1

Seq Scan on ultimamedicao (cost=0.00..2,577.82 rows=134,582 width=51) (actual time=0.005..34.698 rows=134,582 loops=1)

5. 0.242 0.735 ↑ 1.0 422 1

Subquery Scan on *SELECT* 2 (cost=0.00..32.01 rows=423 width=80) (actual time=0.054..0.735 rows=422 loops=1)

6. 0.493 0.493 ↑ 1.0 422 1

Seq Scan on erromedicaoindicador (cost=0.00..26.72 rows=423 width=76) (actual time=0.050..0.493 rows=422 loops=1)

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

GroupAggregate (cost=20,771.31..291,240.67 rows=1,037 width=1,184) (actual time=717.813..5,695.462 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. 380.649 791.788 ↑ 1.0 135,004 1

Sort (cost=20,771.31..21,108.82 rows=135,005 width=576) (actual time=710.269..791.788 rows=135,004 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: quicksort Memory: 25129kB
9. 159.647 411.139 ↑ 1.0 135,004 1

Hash Join (cost=154.26..9,267.09 rows=135,005 width=576) (actual time=2.755..411.139 rows=135,004 loops=1)

  • Hash Cond: (medicaoindicador.unidadenegocioid = unidadenegocio.id)
10. 87.843 250.137 ↑ 1.0 135,004 1

Hash Join (cost=97.88..4,654.30 rows=135,005 width=96) (actual time=1.380..250.137 rows=135,004 loops=1)

  • Hash Cond: (medicaoindicador.indicadorid = indicador.id)
11. 160.934 160.934 ↑ 1.0 135,004 1

CTE Scan on medicoeseerros medicaoindicador (cost=0.00..2,700.10 rows=135,005 width=64) (actual time=0.007..160.934 rows=135,004 loops=1)

12. 0.117 1.360 ↓ 1.0 311 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 33kB
13. 0.196 1.243 ↓ 1.0 311 1

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

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

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

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

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

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

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

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

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

18. 0.019 0.067 ↓ 1.1 119 1

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

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

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

20. 0.045 0.082 ↓ 1.1 119 1

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

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

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

22. 0.042 0.082 ↓ 1.1 119 1

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

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

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

24. 0.020 0.074 ↓ 1.1 119 1

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

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

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

26. 0.361 1.355 ↑ 1.1 947 1

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

  • Buckets: 2048 Batches: 1 Memory Usage: 61kB
27. 0.581 0.994 ↑ 1.1 947 1

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

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

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

29. 0.086 0.159 ↓ 1.2 269 1

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

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

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