explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 3XPO

Settings

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 0.000 0.000 ↓ 0.0

Sort (cost=9,616,845.32..9,616,848.00 rows=1,072 width=176) (actual rows= loops=)

  • Sort Key: agrupado.tipo, (CASE WHEN (total.mes = 1) THEN ((total.ano)::text || '- Janeiro'::text) WHEN (total.mes = 2) THEN ((total.ano)::text || '- Fevereiro'::text) WHEN (total.mes = 3) THEN ((total.ano)::text || '- Marco'::text) WHEN (total.mes = 4) THEN ((total.ano)::text || '- Abril'::text) WHEN (total.mes = 5) THEN ((total.ano)::text || '- Maio'::text) WHEN (total.mes = 6) THEN ((total.ano)::text || '- Junho'::text) WHEN (total.mes = 7) THEN ((total.ano)::text || '- Julho'::text) WHEN (total.mes = 8) THEN ((total.ano)::text || '- Agosto'::text) WHEN (total.mes = 9) THEN ((total.ano)::text || '- Setembro'::text) WHEN (total.mes = 10) THEN ((total.ano)::text || '- Outubro'::text) WHEN (total.mes = 11) THEN ((total.ano)::text || '- Novembro'::text) WHEN (total.mes = 12) THEN ((total.ano)::text || '- Dezembro'::text) ELSE NULL::text END), ((((agrupado.freadabrusca + agrupado.excessovelocidade) + agrupado.excessorpm) + agrupado.paradoligado)) DESC
2.          

CTE total

3. 0.000 0.000 ↓ 0.0

Finalize GroupAggregate (cost=4,378,292.15..4,378,351.82 rows=200 width=40) (actual rows= loops=)

  • Group Key: ((date_part('year'::text, timezone('America/Sao_Paulo'::text, ((alerta_5.datahora)::date)::timestamp with time zone)))::integer), ((date_part('month'::text, timezone('America/Sao_Paulo'::text, ((alerta_5.datahora)::date)::timestamp with time zone)))::integer)
4. 0.000 0.000 ↓ 0.0

Gather Merge (cost=4,378,292.15..4,378,338.82 rows=400 width=40) (actual rows= loops=)

  • Workers Planned: 2
5. 0.000 0.000 ↓ 0.0

Sort (cost=4,377,292.13..4,377,292.63 rows=200 width=40) (actual rows= loops=)

  • Sort Key: ((date_part('year'::text, timezone('America/Sao_Paulo'::text, ((alerta_5.datahora)::date)::timestamp with time zone)))::integer), ((date_part('month'::text, timezone('America/Sao_Paulo'::text, ((alerta_5.datahora)::date)::timestamp with time zone)))::integer)
6. 0.000 0.000 ↓ 0.0

Partial HashAggregate (cost=4,377,277.49..4,377,284.49 rows=200 width=40) (actual rows= loops=)

  • Group Key: (date_part('year'::text, timezone('America/Sao_Paulo'::text, ((alerta_5.datahora)::date)::timestamp with time zone)))::integer, (date_part('month'::text, timezone('America/Sao_Paulo'::text, ((alerta_5.datahora)::date)::timestamp with time zone)))::integer
7. 0.000 0.000 ↓ 0.0

Result (cost=0.00..4,267,970.07 rows=3,123,069 width=36) (actual rows= loops=)

8. 0.000 0.000 ↓ 0.0

Parallel Append (cost=0.00..4,158,662.66 rows=3,123,069 width=36) (actual rows= loops=)

9. 0.000 0.000 ↓ 0.0

Parallel Bitmap Heap Scan on alerta alerta_5 (cost=39,416.10..413,249.67 rows=320,899 width=37) (actual rows= loops=)

  • Recheck Cond: ((uo_id = ANY ('{596,597,2307,2308,2318,3438,3439,3440,3441,3442,3443,3444,3445,3446,3447,3448,3451,3628}'::bigint[])) AND (datahora >= '2019-01-01 02:00:00-02'::timestamp with time zone))
10. 0.000 0.000 ↓ 0.0

Bitmap Index Scan on idx_2019_05_alerta_uo_id (cost=0.00..39,223.56 rows=770,157 width=0) (actual rows= loops=)

  • Index Cond: ((uo_id = ANY ('{596,597,2307,2308,2318,3438,3439,3440,3441,3442,3443,3444,3445,3446,3447,3448,3451,3628}'::bigint[])) AND (datahora >= '2019-01-01 02:00:00-02'::timestamp with time zone))
11. 0.000 0.000 ↓ 0.0

Parallel Seq Scan on alerta alerta_3 (cost=0.00..972,166.94 rows=720,154 width=36) (actual rows= loops=)

  • Filter: ((datahora >= '2019-01-01 02:00:00-02'::timestamp with time zone) AND (uo_id = ANY ('{596,597,2307,2308,2318,3438,3439,3440,3441,3442,3443,3444,3445,3446,3447,3448,3451,3628}'::bigint[])))
12. 0.000 0.000 ↓ 0.0

Parallel Seq Scan on alerta alerta_1 (cost=0.00..928,446.44 rows=737,758 width=36) (actual rows= loops=)

  • Filter: ((datahora >= '2019-01-01 02:00:00-02'::timestamp with time zone) AND (uo_id = ANY ('{596,597,2307,2308,2318,3438,3439,3440,3441,3442,3443,3444,3445,3446,3447,3448,3451,3628}'::bigint[])))
13. 0.000 0.000 ↓ 0.0

Parallel Seq Scan on alerta alerta_4 (cost=0.00..923,818.23 rows=706,244 width=37) (actual rows= loops=)

  • Filter: ((datahora >= '2019-01-01 02:00:00-02'::timestamp with time zone) AND (uo_id = ANY ('{596,597,2307,2308,2318,3438,3439,3440,3441,3442,3443,3444,3445,3446,3447,3448,3451,3628}'::bigint[])))
14. 0.000 0.000 ↓ 0.0

Parallel Seq Scan on alerta alerta_2 (cost=0.00..905,352.53 rows=638,010 width=36) (actual rows= loops=)

  • Filter: ((datahora >= '2019-01-01 02:00:00-02'::timestamp with time zone) AND (uo_id = ANY ('{596,597,2307,2308,2318,3438,3439,3440,3441,3442,3443,3444,3445,3446,3447,3448,3451,3628}'::bigint[])))
15. 0.000 0.000 ↓ 0.0

Parallel Seq Scan on alerta alerta_6 (cost=0.00..13.50 rows=4 width=52) (actual rows= loops=)

  • Filter: ((datahora >= '2019-01-01 02:00:00-02'::timestamp with time zone) AND (uo_id = ANY ('{596,597,2307,2308,2318,3438,3439,3440,3441,3442,3443,3444,3445,3446,3447,3448,3451,3628}'::bigint[])))
16. 0.000 0.000 ↓ 0.0

Parallel Seq Scan on alerta (cost=0.00..0.00 rows=1 width=52) (actual rows= loops=)

  • Filter: ((datahora >= '2019-01-01 02:00:00-02'::timestamp with time zone) AND (uo_id = ANY ('{596,597,2307,2308,2318,3438,3439,3440,3441,3442,3443,3444,3445,3446,3447,3448,3451,3628}'::bigint[])))
17.          

CTE agrupado

18. 0.000 0.000 ↓ 0.0

Finalize GroupAggregate (cost=5,010,490.33..5,221,844.12 rows=214,478 width=126) (actual rows= loops=)

  • Group Key: alerta_9.uo_nome, ((date_part('year'::text, timezone('America/Sao_Paulo'::text, ((alerta_9.datahora)::date)::timestamp with time zone)))::integer), ((date_part('month'::text, timezone('America/Sao_Paulo'::text, ((alerta_9.datahora)::date)::timestamp with time zone)))::integer), alerta_9.tipo
19. 0.000 0.000 ↓ 0.0

Gather Merge (cost=5,010,490.33..5,199,323.93 rows=428,956 width=126) (actual rows= loops=)

  • Workers Planned: 2
20. 0.000 0.000 ↓ 0.0

Partial GroupAggregate (cost=5,009,490.30..5,148,811.74 rows=214,478 width=126) (actual rows= loops=)

  • Group Key: alerta_9.uo_nome, ((date_part('year'::text, timezone('America/Sao_Paulo'::text, ((alerta_9.datahora)::date)::timestamp with time zone)))::integer), ((date_part('month'::text, timezone('America/Sao_Paulo'::text, ((alerta_9.datahora)::date)::timestamp with time zone)))::integer), alerta_9.tipo
21. 0.000 0.000 ↓ 0.0

Sort (cost=5,009,490.30..5,011,724.45 rows=893,659 width=66) (actual rows= loops=)

  • Sort Key: alerta_9.uo_nome, ((date_part('year'::text, timezone('America/Sao_Paulo'::text, ((alerta_9.datahora)::date)::timestamp with time zone)))::integer), ((date_part('month'::text, timezone('America/Sao_Paulo'::text, ((alerta_9.datahora)::date)::timestamp with time zone)))::integer), alerta_9.tipo
22. 0.000 0.000 ↓ 0.0

Result (cost=39,276.14..4,884,499.45 rows=893,659 width=66) (actual rows= loops=)

23. 0.000 0.000 ↓ 0.0

Parallel Append (cost=39,276.14..4,853,221.38 rows=893,659 width=58) (actual rows= loops=)

24. 0.000 0.000 ↓ 0.0

Parallel Bitmap Heap Scan on alerta alerta_9 (cost=81,290.64..1,062,278.45 rows=187,128 width=58) (actual rows= loops=)

  • Recheck Cond: ((uo_id = ANY ('{596,597,2307,2308,2318,3438,3439,3440,3441,3442,3443,3444,3445,3446,3447,3448,3451,3628}'::bigint[])) AND (datahora >= '2019-01-01 02:00:00-02'::timestamp with time zone))
  • Filter: (((tipo = 'FREADA_BRUSCA'::text) AND (limiar >= '15'::double precision)) OR ((tipo = 'EXCESSO_VELOCIDADE'::text) AND (duracao > 7)) OR ((tipo = 'EXCESSO_RPM'::text) AND (duracao > 7)) OR ((tipo = 'PARADO_LIGADO'::text) AND (duracao > 180)))
25. 0.000 0.000 ↓ 0.0

Bitmap Index Scan on idx_2019_02_alerta_uo_id (cost=0.00..81,178.37 rows=1,531,223 width=0) (actual rows= loops=)

  • Index Cond: ((uo_id = ANY ('{596,597,2307,2308,2318,3438,3439,3440,3441,3442,3443,3444,3445,3446,3447,3448,3451,3628}'::bigint[])) AND (datahora >= '2019-01-01 02:00:00-02'::timestamp with time zone))
26. 0.000 0.000 ↓ 0.0

Parallel Bitmap Heap Scan on alerta alerta_12 (cost=39,276.14..458,195.18 rows=87,640 width=58) (actual rows= loops=)

  • Recheck Cond: ((uo_id = ANY ('{596,597,2307,2308,2318,3438,3439,3440,3441,3442,3443,3444,3445,3446,3447,3448,3451,3628}'::bigint[])) AND (datahora >= '2019-01-01 02:00:00-02'::timestamp with time zone))
  • Filter: (((tipo = 'FREADA_BRUSCA'::text) AND (limiar >= '15'::double precision)) OR ((tipo = 'EXCESSO_VELOCIDADE'::text) AND (duracao > 7)) OR ((tipo = 'EXCESSO_RPM'::text) AND (duracao > 7)) OR ((tipo = 'PARADO_LIGADO'::text) AND (duracao > 180)))
27. 0.000 0.000 ↓ 0.0

Bitmap Index Scan on idx_2019_05_alerta_uo_id (cost=0.00..39,223.56 rows=770,157 width=0) (actual rows= loops=)

  • Index Cond: ((uo_id = ANY ('{596,597,2307,2308,2318,3438,3439,3440,3441,3442,3443,3444,3445,3446,3447,3448,3451,3628}'::bigint[])) AND (datahora >= '2019-01-01 02:00:00-02'::timestamp with time zone))
28. 0.000 0.000 ↓ 0.0

Parallel Seq Scan on alerta alerta_10 (cost=0.00..1,143,833.19 rows=205,324 width=58) (actual rows= loops=)

  • Filter: ((datahora >= '2019-01-01 02:00:00-02'::timestamp with time zone) AND (((tipo = 'FREADA_BRUSCA'::text) AND (limiar >= '15'::double precision)) OR ((tipo = 'EXCESSO_VELOCIDADE'::text) AND (duracao > 7)) OR ((tipo = 'EXCESSO_RPM'::text) AND (duracao > 7)) OR ((tipo = 'PARADO_LIGADO'::text) AND (duracao > 180))) AND (uo_id = ANY ('{596,597,2307,2308,2318,3438,3439,3440,3441,3442,3443,3444,3445,3446,3447,3448,3451,3628}'::bigint[])))
29. 0.000 0.000 ↓ 0.0

Parallel Seq Scan on alerta alerta_8 (cost=0.00..1,096,648.41 rows=218,811 width=57) (actual rows= loops=)

  • Filter: ((datahora >= '2019-01-01 02:00:00-02'::timestamp with time zone) AND (((tipo = 'FREADA_BRUSCA'::text) AND (limiar >= '15'::double precision)) OR ((tipo = 'EXCESSO_VELOCIDADE'::text) AND (duracao > 7)) OR ((tipo = 'EXCESSO_RPM'::text) AND (duracao > 7)) OR ((tipo = 'PARADO_LIGADO'::text) AND (duracao > 180))) AND (uo_id = ANY ('{596,597,2307,2308,2318,3438,3439,3440,3441,3442,3443,3444,3445,3446,3447,3448,3451,3628}'::bigint[])))
30. 0.000 0.000 ↓ 0.0

Parallel Seq Scan on alerta alerta_11 (cost=0.00..1,087,782.37 rows=194,754 width=59) (actual rows= loops=)

  • Filter: ((datahora >= '2019-01-01 02:00:00-02'::timestamp with time zone) AND (((tipo = 'FREADA_BRUSCA'::text) AND (limiar >= '15'::double precision)) OR ((tipo = 'EXCESSO_VELOCIDADE'::text) AND (duracao > 7)) OR ((tipo = 'EXCESSO_RPM'::text) AND (duracao > 7)) OR ((tipo = 'PARADO_LIGADO'::text) AND (duracao > 180))) AND (uo_id = ANY ('{596,597,2307,2308,2318,3438,3439,3440,3441,3442,3443,3444,3445,3446,3447,3448,3451,3628}'::bigint[])))
31. 0.000 0.000 ↓ 0.0

Parallel Seq Scan on alerta alerta_13 (cost=0.00..15.50 rows=1 width=84) (actual rows= loops=)

  • Filter: ((datahora >= '2019-01-01 02:00:00-02'::timestamp with time zone) AND (((tipo = 'FREADA_BRUSCA'::text) AND (limiar >= '15'::double precision)) OR ((tipo = 'EXCESSO_VELOCIDADE'::text) AND (duracao > 7)) OR ((tipo = 'EXCESSO_RPM'::text) AND (duracao > 7)) OR ((tipo = 'PARADO_LIGADO'::text) AND (duracao > 180))) AND (uo_id = ANY ('{596,597,2307,2308,2318,3438,3439,3440,3441,3442,3443,3444,3445,3446,3447,3448,3451,3628}'::bigint[])))
32. 0.000 0.000 ↓ 0.0

Parallel Seq Scan on alerta alerta_7 (cost=0.00..0.00 rows=1 width=84) (actual rows= loops=)

  • Filter: ((datahora >= '2019-01-01 02:00:00-02'::timestamp with time zone) AND (((tipo = 'FREADA_BRUSCA'::text) AND (limiar >= '15'::double precision)) OR ((tipo = 'EXCESSO_VELOCIDADE'::text) AND (duracao > 7)) OR ((tipo = 'EXCESSO_RPM'::text) AND (duracao > 7)) OR ((tipo = 'PARADO_LIGADO'::text) AND (duracao > 180))) AND (uo_id = ANY ('{596,597,2307,2308,2318,3438,3439,3440,3441,3442,3443,3444,3445,3446,3447,3448,3451,3628}'::bigint[])))
33. 0.000 0.000 ↓ 0.0

WindowAgg (cost=16,391.75..16,595.43 rows=1,072 width=176) (actual rows= loops=)

34. 0.000 0.000 ↓ 0.0

Sort (cost=16,391.75..16,394.43 rows=1,072 width=232) (actual rows= loops=)

  • Sort Key: total.ano, total.mes, agrupado.freadabrusca DESC
35. 0.000 0.000 ↓ 0.0

WindowAgg (cost=16,313.67..16,337.79 rows=1,072 width=232) (actual rows= loops=)

36. 0.000 0.000 ↓ 0.0

Sort (cost=16,313.67..16,316.35 rows=1,072 width=200) (actual rows= loops=)

  • Sort Key: total.ano, total.mes, agrupado.paradoligado DESC
37. 0.000 0.000 ↓ 0.0

WindowAgg (cost=16,235.60..16,259.72 rows=1,072 width=200) (actual rows= loops=)

38. 0.000 0.000 ↓ 0.0

Sort (cost=16,235.60..16,238.28 rows=1,072 width=168) (actual rows= loops=)

  • Sort Key: total.ano, total.mes, agrupado.excessorpm DESC
39. 0.000 0.000 ↓ 0.0

WindowAgg (cost=16,157.52..16,181.64 rows=1,072 width=168) (actual rows= loops=)

40. 0.000 0.000 ↓ 0.0

Sort (cost=16,157.52..16,160.20 rows=1,072 width=136) (actual rows= loops=)

  • Sort Key: total.ano, total.mes, agrupado.excessovelocidade DESC
41. 0.000 0.000 ↓ 0.0

Hash Right Join (cost=7.00..16,103.57 rows=1,072 width=136) (actual rows= loops=)

  • Hash Cond: ((agrupado.ano = total.ano) AND (agrupado.mes = total.mes))
42. 0.000 0.000 ↓ 0.0

CTE Scan on agrupado (cost=0.00..4,289.56 rows=214,478 width=104) (actual rows= loops=)

43. 0.000 0.000 ↓ 0.0

Hash (cost=4.00..4.00 rows=200 width=40) (actual rows= loops=)

44. 0.000 0.000 ↓ 0.0

CTE Scan on total (cost=0.00..4.00 rows=200 width=40) (actual rows= loops=)