explain.depesz.com

PostgreSQL's explain analyze made readable

Result: gMkw

Settings
# exclusive inclusive rows x rows loops node
1. 30.728 2,337.462 ↑ 1,837,356.7 1,483 1

GroupAggregate (cost=2,264,528,026.93..2,529,486,371.93 rows=2,724,800,000 width=151) (actual time=2,258.213..2,337.462 rows=1,483 loops=1)

  • Group Key: (to_char(timezone('America/Sao_Paulo'::text, reg.data_hora), 'yyyy-MM-dd'::text)), s.nome_simulacao, potgerada.potencia, qtdturbina.qtd, (CASE WHEN (isallaborted.qtdaborted IS NULL) THEN false ELSE (count(reg_all_aborted.id) = isallaborted.qtdaborted) END)
  • Functions: 107
  • Options: Inlining true, Optimization true, Expressions true, Deforming true
  • Timing: Generation 16.095 ms, Inlining 94.751 ms, Optimization 941.088 ms, Emission 617.082 ms, Total 1669.016 ms
2. 184.434 2,306.734 ↑ 76,287.7 67,491 1

Sort (cost=2,264,528,026.93..2,277,399,861.43 rows=5,148,733,800 width=100) (actual time=2,258.113..2,306.734 rows=67,491 loops=1)

  • Sort Key: (to_char(timezone('America/Sao_Paulo'::text, reg.data_hora), 'yyyy-MM-dd'::text)), s.nome_simulacao, potgerada.potencia, qtdturbina.qtd, (CASE WHEN (isallaborted.qtdaborted IS NULL) THEN false ELSE (count(reg_all_aborted.id) = isallaborted.qtdaborted) END)
  • Sort Method: external merge Disk: 4808kB
3. 85.954 2,122.300 ↑ 76,287.7 67,491 1

Hash Join (cost=15,765.69..26,139,928.63 rows=5,148,733,800 width=100) (actual time=1,978.014..2,122.300 rows=67,491 loops=1)

  • Hash Cond: (potgerada.potdata = qtdturbina.qtddata)
4. 24.396 309.908 ↑ 1,791.8 67,491 1

Hash Join (cost=12,452.29..68,769.51 rows=120,933,266 width=132) (actual time=251.438..309.908 rows=67,491 loops=1)

  • Hash Cond: ((to_char(timezone('America/Sao_Paulo'::text, reg_all_aborted.data_hora), 'yyyy-MM-dd'::text)) = potgerada.potdata)
5. 29.300 223.399 ↑ 42.1 67,491 1

Hash Left Join (cost=10,350.40..59,053.37 rows=2,840,476 width=68) (actual time=189.307..223.399 rows=67,491 loops=1)

  • Hash Cond: (reg.id = turbvalida.registro_id)
6. 2.104 94.121 ↑ 42.6 8,516 1

Merge Join (cost=2,789.33..10,423.03 rows=362,526 width=49) (actual time=89.074..94.121 rows=8,516 loops=1)

  • Merge Cond: ((to_char(timezone('America/Sao_Paulo'::text, reg_all_aborted.data_hora), 'yyyy-MM-dd'::text)) = (to_char(timezone('America/Sao_Paulo'::text, reg.data_hora), 'yyyy-MM-dd'::text)))
7. 1.256 57.018 ↑ 23.3 365 1

GroupAggregate (cost=1,563.34..1,797.50 rows=8,515 width=41) (actual time=55.065..57.018 rows=365 loops=1)

  • Group Key: (to_char(timezone('America/Sao_Paulo'::text, reg_all_aborted.data_hora), 'yyyy-MM-dd'::text)), isallaborted.qtdaborted
8. 11.106 55.762 ↓ 1.0 8,516 1

Sort (cost=1,563.34..1,584.63 rows=8,515 width=48) (actual time=55.033..55.762 rows=8,516 loops=1)

  • Sort Key: (to_char(timezone('America/Sao_Paulo'::text, reg_all_aborted.data_hora), 'yyyy-MM-dd'::text)), isallaborted.qtdaborted
  • Sort Method: quicksort Memory: 1050kB
9. 20.367 44.656 ↓ 1.0 8,516 1

Hash Left Join (cost=269.71..1,007.49 rows=8,515 width=48) (actual time=1.036..44.656 rows=8,516 loops=1)

  • Hash Cond: (to_char(timezone('America/Sao_Paulo'::text, reg_all_aborted.data_hora), 'yyyy-MM-dd'::text) = isallaborted.allaborteddata)
10. 23.314 23.314 ↓ 1.0 8,516 1

Seq Scan on registro reg_all_aborted (cost=0.00..670.14 rows=8,515 width=16) (actual time=0.036..23.314 rows=8,516 loops=1)

  • Filter: ((usina_id = 1) AND (data_hora >= to_timestamp('20190101_00:00'::text, 'YYYYMMDD_HH24:MI'::text)) AND (data_hora <= to_timestamp('20191231_23:00'::text, 'YYYYMMDD_HH24:MI'::text)))
  • Rows Removed by Filter: 8646
11. 0.042 0.975 ↑ 2.8 67 1

Hash (cost=267.40..267.40 rows=185 width=40) (actual time=0.975..0.975 rows=67 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 12kB
12. 0.014 0.933 ↑ 2.8 67 1

Subquery Scan on isallaborted (cost=262.78..267.40 rows=185 width=40) (actual time=0.901..0.933 rows=67 loops=1)

13. 0.186 0.919 ↑ 2.8 67 1

HashAggregate (cost=262.78..265.55 rows=185 width=40) (actual time=0.899..0.919 rows=67 loops=1)

  • Group Key: to_char(timezone('America/Sao_Paulo'::text, inner_reg_all_aborted.data_hora), 'yyyy-MM-dd'::text)
14. 0.733 0.733 ↑ 1.0 185 1

Index Scan using idx_registro_abortado on registro inner_reg_all_aborted (cost=0.29..261.85 rows=185 width=33) (actual time=0.088..0.733 rows=185 loops=1)

  • Index Cond: ((usina_id = 1) AND (abortado = true))
15. 9.833 34.999 ↓ 1.0 8,516 1

Sort (cost=1,226.00..1,247.28 rows=8,515 width=16) (actual time=33.988..34.999 rows=8,516 loops=1)

  • Sort Key: (to_char(timezone('America/Sao_Paulo'::text, reg.data_hora), 'yyyy-MM-dd'::text))
  • Sort Method: quicksort Memory: 1050kB
16. 25.166 25.166 ↓ 1.0 8,516 1

Seq Scan on registro reg (cost=0.00..670.14 rows=8,515 width=16) (actual time=0.042..25.166 rows=8,516 loops=1)

  • Filter: ((usina_id = 1) AND (data_hora >= to_timestamp('20190101_00:00'::text, 'YYYYMMDD_HH24:MI'::text)) AND (data_hora <= to_timestamp('20191231_23:00'::text, 'YYYYMMDD_HH24:MI'::text)))
  • Rows Removed by Filter: 8646
17. 36.006 99.978 ↑ 1.0 134,468 1

Hash (cost=4,829.21..4,829.21 rows=134,468 width=35) (actual time=99.978..99.978 rows=134,468 loops=1)

  • Buckets: 65536 Batches: 4 Memory Usage: 2806kB
18. 35.624 63.972 ↑ 1.0 134,468 1

Hash Join (cost=1,152.51..4,829.21 rows=134,468 width=35) (actual time=11.725..63.972 rows=134,468 loops=1)

  • Hash Cond: (s.turbina_id = turbvalida.id)
19. 16.867 16.867 ↑ 1.0 134,468 1

Seq Scan on simulacao s (cost=0.00..3,323.68 rows=134,468 width=30) (actual time=0.021..16.867 rows=134,468 loops=1)

20. 6.591 11.481 ↑ 1.0 34,156 1

Hash (cost=725.56..725.56 rows=34,156 width=21) (actual time=11.481..11.481 rows=34,156 loops=1)

  • Buckets: 65536 Batches: 1 Memory Usage: 2380kB
21. 4.890 4.890 ↑ 1.0 34,156 1

Seq Scan on turbina turbvalida (cost=0.00..725.56 rows=34,156 width=21) (actual time=0.011..4.890 rows=34,156 loops=1)

22. 0.258 62.113 ↑ 23.3 365 1

Hash (cost=1,995.46..1,995.46 rows=8,515 width=64) (actual time=62.113..62.113 rows=365 loops=1)

  • Buckets: 16384 Batches: 1 Memory Usage: 148kB
23. 0.087 61.855 ↑ 23.3 365 1

Subquery Scan on potgerada (cost=1,761.29..1,995.46 rows=8,515 width=64) (actual time=61.154..61.855 rows=365 loops=1)

24. 8.236 61.768 ↑ 23.3 365 1

HashAggregate (cost=1,761.29..1,910.31 rows=8,515 width=64) (actual time=61.149..61.768 rows=365 loops=1)

  • Group Key: to_char(timezone('America/Sao_Paulo'::text, regpotgerada.data_hora), 'yyyy-MM-dd'::text)
25. 30.577 53.532 ↓ 1.0 16,992 1

Hash Join (cost=776.58..1,676.56 rows=16,947 width=37) (actual time=18.847..53.532 rows=16,992 loops=1)

  • Hash Cond: (turbpotgerada.registro_id = regpotgerada.id)
26. 4.174 4.174 ↑ 1.0 34,156 1

Seq Scan on turbina turbpotgerada (cost=0.00..725.56 rows=34,156 width=13) (actual time=0.015..4.174 rows=34,156 loops=1)

27. 1.780 18.781 ↓ 1.0 8,516 1

Hash (cost=670.14..670.14 rows=8,515 width=16) (actual time=18.781..18.781 rows=8,516 loops=1)

  • Buckets: 16384 Batches: 1 Memory Usage: 528kB
28. 17.001 17.001 ↓ 1.0 8,516 1

Seq Scan on registro regpotgerada (cost=0.00..670.14 rows=8,515 width=16) (actual time=0.043..17.001 rows=8,516 loops=1)

  • Filter: ((usina_id = 1) AND (data_hora >= to_timestamp('20190101_00:00'::text, 'YYYYMMDD_HH24:MI'::text)) AND (data_hora <= to_timestamp('20191231_23:00'::text, 'YYYYMMDD_HH24:MI'::text)))
  • Rows Removed by Filter: 8646
29. 0.145 1,726.438 ↑ 23.3 365 1

Hash (cost=3,206.96..3,206.96 rows=8,515 width=40) (actual time=1,726.438..1,726.438 rows=365 loops=1)

  • Buckets: 16384 Batches: 1 Memory Usage: 147kB
30. 0.051 1,726.293 ↑ 23.3 365 1

Subquery Scan on qtdturbina (cost=2,866.98..3,206.96 rows=8,515 width=40) (actual time=1,719.907..1,726.293 rows=365 loops=1)

31. 5.133 1,726.242 ↑ 23.3 365 1

GroupAggregate (cost=2,866.98..3,121.81 rows=8,515 width=40) (actual time=1,719.905..1,726.242 rows=365 loops=1)

  • Group Key: (to_char(timezone('America/Sao_Paulo'::text, regqtdturbina.data_hora), 'yyyy-MM-dd'::text))
32. 19.391 1,721.109 ↓ 1.0 16,992 1

Sort (cost=2,866.98..2,909.35 rows=16,947 width=42) (actual time=1,719.854..1,721.109 rows=16,992 loops=1)

  • Sort Key: (to_char(timezone('America/Sao_Paulo'::text, regqtdturbina.data_hora), 'yyyy-MM-dd'::text))
  • Sort Method: quicksort Memory: 2096kB
33. 21.932 1,701.718 ↓ 1.0 16,992 1

Hash Join (cost=776.58..1,676.56 rows=16,947 width=42) (actual time=1,675.808..1,701.718 rows=16,992 loops=1)

  • Hash Cond: (turbina.registro_id = regqtdturbina.id)
34. 4.246 4.246 ↑ 1.0 34,156 1

Seq Scan on turbina (cost=0.00..725.56 rows=34,156 width=18) (actual time=0.016..4.246 rows=34,156 loops=1)

35. 1.671 1,675.540 ↓ 1.0 8,516 1

Hash (cost=670.14..670.14 rows=8,515 width=16) (actual time=1,675.540..1,675.540 rows=8,516 loops=1)

  • Buckets: 16384 Batches: 1 Memory Usage: 528kB
36. 1,673.869 1,673.869 ↓ 1.0 8,516 1

Seq Scan on registro regqtdturbina (cost=0.00..670.14 rows=8,515 width=16) (actual time=1,655.265..1,673.869 rows=8,516 loops=1)

  • Filter: ((usina_id = 1) AND (data_hora >= to_timestamp('20190101_00:00'::text, 'YYYYMMDD_HH24:MI'::text)) AND (data_hora <= to_timestamp('20191231_23:00'::text, 'YYYYMMDD_HH24:MI'::text)))
  • Rows Removed by Filter: 8646
Planning time : 4.015 ms