explain.depesz.com

PostgreSQL's explain analyze made readable

Result: GuTi

Settings
# exclusive inclusive rows x rows loops node
1. 14.512 226.673 ↓ 1,588.0 1,588 1

GroupAggregate (cost=2,337.75..2,337.82 rows=1 width=835) (actual time=204.704..226.673 rows=1,588 loops=1)

  • Group Key: fat.tx_produto, fat.tx_classificacao, fat.tx_unidade, fat.tx_nivel, fat.fk_tempo, fat.tx_rg, fat.tx_moeda, fat.fk_unidade, fat.fk_classificacao
2.          

CTE siagro_mes

3. 60.531 69.453 ↓ 24.9 3,767 1

Bitmap Heap Scan on fat_siagro_mes (cost=1,014.97..2,317.91 rows=151 width=471) (actual time=10.615..69.453 rows=3,767 loops=1)

  • Recheck Cond: ((fk_classificacao = 4,744) AND (fk_produto = 29))
  • Filter: (((fk_tempo)::text >= '198201'::text) AND ((fk_tempo)::text <= '900000'::text) AND (fk_nivel = 5) AND ((fk_rg)::text = ANY ('{CO,N,NE,S,SE}'::text[])))
  • Rows Removed by Filter: 42,131
  • Heap Blocks: exact=9,994
4. 0.472 8.922 ↓ 0.0 0 1

BitmapAnd (cost=1,014.97..1,014.97 rows=1,173 width=0) (actual time=8.922..8.922 rows=0 loops=1)

5. 4.237 4.237 ↑ 1.0 45,898 1

Bitmap Index Scan on fat_siagro_mes_idx_010 (cost=0.00..507.32 rows=48,079 width=0) (actual time=4.236..4.237 rows=45,898 loops=1)

  • Index Cond: (fk_classificacao = 4,744)
6. 4.213 4.213 ↑ 1.0 45,898 1

Bitmap Index Scan on fat_siagro_mes_idx_008 (cost=0.00..507.32 rows=48,079 width=0) (actual time=4.212..4.213 rows=45,898 loops=1)

  • Index Cond: (fk_produto = 29)
7.          

CTE media_uf

8. 13.099 81.370 ↓ 24.6 3,722 1

HashAggregate (cost=5.66..7.55 rows=151 width=54) (actual time=76.106..81.370 rows=3,722 loops=1)

  • Group Key: fat_1.fk_tempo, fat_1.fk_classificacao, fat_1.fk_nivel, fat_1.fk_unidade, fat_1.fk_rg, fat_1.fk_uf
9. 68.271 68.271 ↓ 24.9 3,767 1

CTE Scan on siagro_mes fat_1 (cost=0.00..3.02 rows=151 width=54) (actual time=0.001..68.271 rows=3,767 loops=1)

10. 87.592 212.161 ↓ 10,285.0 10,285 1

Sort (cost=12.29..12.29 rows=1 width=728) (actual time=204.672..212.161 rows=10,285 loops=1)

  • Sort Key: fat.tx_produto, fat.tx_classificacao, fat.tx_unidade, fat.tx_nivel, fat.fk_tempo, fat.tx_rg, fat.tx_moeda, fat.fk_unidade, fat.fk_classificacao
  • Sort Method: quicksort Memory: 1,821kB
11. 15.388 124.569 ↓ 10,285.0 10,285 1

Hash Join (cost=6.42..12.28 rows=1 width=728) (actual time=104.218..124.569 rows=10,285 loops=1)

  • Hash Cond: (((fat.fk_rg)::text = (m.rg)::text) AND ((fat.fk_tempo)::text = (m.tempo)::text) AND (fat.fk_classificacao = m.classificacao) AND (fat.fk_nivel = m.nivel) AND (fat.fk_unidade = m.unidade))
12. 15.617 15.617 ↓ 24.9 3,767 1

CTE Scan on siagro_mes fat (cost=0.00..3.02 rows=151 width=734) (actual time=10.631..15.617 rows=3,767 loops=1)

13. 4.279 93.564 ↓ 24.6 3,722 1

Hash (cost=3.02..3.02 rows=151 width=48) (actual time=93.564..93.564 rows=3,722 loops=1)

  • Buckets: 4,096 (originally 1024) Batches: 1 (originally 1) Memory Usage: 323kB
14. 89.285 89.285 ↓ 24.6 3,722 1

CTE Scan on media_uf m (cost=0.00..3.02 rows=151 width=48) (actual time=76.111..89.285 rows=3,722 loops=1)

Planning time : 1.029 ms
Execution time : 228.524 ms