explain.depesz.com

PostgreSQL's explain analyze made readable

Result: GPxY

Settings

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 3.395 22,022.626 ↑ 1.1 930 1

Sort (cost=1,329,711.39..1,329,713.98 rows=1,037 width=1,184) (actual time=22,022.153..22,022.626 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 ultimamedicao

3. 1,042.096 16,081.221 ↑ 1.3 134,582 1

Unique (cost=831,485.06..858,713.54 rows=173,910 width=51) (actual time=12,677.609..16,081.221 rows=134,582 loops=1)

4. 9,473.730 15,039.125 ↓ 1.0 3,634,369 1

Sort (cost=831,485.06..840,561.22 rows=3,630,464 width=51) (actual time=12,677.606..15,039.125 rows=3,634,369 loops=1)

  • Sort Key: medicaoindicador_1.indicadorid, medicaoindicador_1.unidadenegocioid, medicaoindicador_1.data DESC
  • Sort Method: external merge Disk: 189072kB
5. 5,565.395 5,565.395 ↓ 1.0 3,634,369 1

Seq Scan on medicaoindicador medicaoindicador_1 (cost=0.00..187,736.74 rows=3,630,464 width=51) (actual time=1,015.826..5,565.395 rows=3,634,369 loops=1)

  • Filter: (data >= (('now'::cstring)::date - '1 mon'::interval))
  • Rows Removed by Filter: 2012791
6.          

CTE medicoeseerros

7. 53.099 16,219.685 ↑ 1.3 135,000 1

Append (cost=0.00..5,249.16 rows=174,329 width=80) (actual time=12,677.612..16,219.685 rows=135,000 loops=1)

8. 16,165.924 16,165.924 ↑ 1.3 134,582 1

CTE Scan on ultimamedicao (cost=0.00..3,478.20 rows=173,910 width=66) (actual time=12,677.611..16,165.924 rows=134,582 loops=1)

9. 0.195 0.662 ↑ 1.0 418 1

Subquery Scan on *SELECT* 2 (cost=0.00..31.86 rows=419 width=80) (actual time=0.063..0.662 rows=418 loops=1)

10. 0.467 0.467 ↑ 1.0 418 1

Seq Scan on erromedicaoindicador (cost=0.00..26.63 rows=419 width=76) (actual time=0.059..0.467 rows=418 loops=1)

  • Filter: (datahora >= (('now'::cstring)::date - '1 mon'::interval))
  • Rows Removed by Filter: 11
11. 4,799.790 22,019.231 ↑ 1.1 930 1

GroupAggregate (cost=116,481.07..465,696.74 rows=1,037 width=1,184) (actual time=17,091.523..22,019.231 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
12. 605.764 17,219.441 ↑ 1.3 135,000 1

Sort (cost=116,481.07..116,916.89 rows=174,329 width=576) (actual time=17,083.890..17,219.441 rows=135,000 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: 13048kB
13. 202.218 16,613.677 ↑ 1.3 135,000 1

Hash Join (cost=154.26..11,921.46 rows=174,329 width=576) (actual time=12,681.031..16,613.677 rows=135,000 loops=1)

  • Hash Cond: (medicaoindicador.unidadenegocioid = unidadenegocio.id)
14. 96.845 16,409.600 ↑ 1.3 135,000 1

Hash Join (cost=97.88..5,981.49 rows=174,329 width=96) (actual time=12,679.137..16,409.600 rows=135,000 loops=1)

  • Hash Cond: (medicaoindicador.indicadorid = indicador.id)
15. 16,311.272 16,311.272 ↑ 1.3 135,000 1

CTE Scan on medicoeseerros medicaoindicador (cost=0.00..3,486.58 rows=174,329 width=64) (actual time=12,677.614..16,311.272 rows=135,000 loops=1)

16. 0.105 1.483 ↓ 1.0 311 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 33kB
17. 0.206 1.378 ↓ 1.0 311 1

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

  • Hash Cond: (indicador.classificacaoinvestimentoid = classificacaoinvestimento.id)
18. 0.197 1.071 ↓ 1.0 311 1

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

  • Hash Cond: (indicador.classificacaoramoatividadeid = classificacaoramoatividade.id)
19. 0.151 0.794 ↓ 1.0 311 1

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

  • Hash Cond: (indicador.classificacaogestaoid = classificacaogestao.id)
20. 0.334 0.561 ↓ 1.0 311 1

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

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

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

22. 0.031 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
23. 0.037 0.037 ↓ 1.1 119 1

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

24. 0.044 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
25. 0.038 0.038 ↓ 1.1 119 1

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

26. 0.054 0.080 ↓ 1.1 119 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 13kB
27. 0.026 0.026 ↓ 1.1 119 1

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

28. 0.036 0.101 ↓ 1.1 119 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 13kB
29. 0.065 0.065 ↓ 1.1 119 1

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

30. 0.345 1.859 ↑ 1.1 947 1

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

  • Buckets: 2048 Batches: 1 Memory Usage: 61kB
31. 0.606 1.514 ↑ 1.1 947 1

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

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

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

33. 0.077 0.601 ↓ 1.2 269 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 19kB
34. 0.524 0.524 ↓ 1.2 269 1

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