explain.depesz.com

PostgreSQL's explain analyze made readable

Result: fKQN

Settings
# exclusive inclusive rows x rows loops node
1. 3.740 136.118 ↓ 875.0 875 1

GroupAggregate (cost=2,767.64..2,767.71 rows=1 width=204) (actual time=130.891..136.118 rows=875 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 media_uf

3. 3.593 40.766 ↓ 100.6 1,308 1

GroupAggregate (cost=2,745.00..2,745.42 rows=13 width=54) (actual time=35.949..40.766 rows=1,308 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
4. 5.724 37.173 ↓ 100.8 1,311 1

Sort (cost=2,745.00..2,745.03 rows=13 width=54) (actual time=35.933..37.173 rows=1,311 loops=1)

  • Sort Key: fat_1.fk_tempo, fat_1.fk_unidade, fat_1.fk_rg, fat_1.fk_uf
  • Sort Method: quicksort Memory: 233kB
5. 3.640 31.449 ↓ 100.8 1,311 1

Bitmap Heap Scan on fat_siagro_sem fat_1 (cost=1,740.07..2,744.76 rows=13 width=54) (actual time=28.497..31.449 rows=1,311 loops=1)

  • Recheck Cond: (((fk_tempo)::text >= '198201'::text) AND ((fk_tempo)::text <= '200000'::text) AND (fk_classificacao = 4,744))
  • Filter: ((fk_nivel = 5) AND (fk_produto = 29) AND ((fk_rg)::text = ANY ('{CO,N,NE,S,SE}'::text[])))
  • Rows Removed by Filter: 1,300
  • Heap Blocks: exact=546
6. 0.295 27.809 ↓ 0.0 0 1

BitmapAnd (cost=1,740.07..1,740.07 rows=898 width=0) (actual time=27.809..27.809 rows=0 loops=1)

7. 8.875 8.875 ↓ 1.1 23,390 1

Bitmap Index Scan on fat_siagro_sem_idx_035 (cost=0.00..341.23 rows=21,980 width=0) (actual time=8.865..8.875 rows=23,390 loops=1)

  • Index Cond: (((fk_tempo)::text >= '198201'::text) AND ((fk_tempo)::text <= '200000'::text))
8. 18.639 18.639 ↑ 1.0 129,870 1

Bitmap Index Scan on fat_siagro_sem_idx_010 (cost=0.00..1,398.58 rows=132,740 width=0) (actual time=18.638..18.639 rows=129,870 loops=1)

  • Index Cond: (fk_classificacao = 4,744)
9. 7.246 132.378 ↓ 2,179.0 2,179 1

Sort (cost=22.22..22.22 rows=1 width=136) (actual time=130.858..132.378 rows=2,179 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
  • Sort Method: quicksort Memory: 675kB
10. 5.882 125.132 ↓ 2,179.0 2,179 1

Nested Loop (cost=0.43..22.21 rows=1 width=136) (actual time=36.036..125.132 rows=2,179 loops=1)

11. 44.694 44.694 ↓ 1,308.0 1,308 1

CTE Scan on media_uf m (cost=0.00..0.33 rows=1 width=58) (actual time=35.968..44.694 rows=1,308 loops=1)

  • Filter: ((classificacao = 4,744) AND (nivel = 5))
12. 74.556 74.556 ↓ 2.0 2 1,308

Index Scan using fat_siagro_sem_idx_035 on fat_siagro_sem fat (cost=0.43..21.87 rows=1 width=138) (actual time=0.050..0.057 rows=2 loops=1,308)

  • Index Cond: (((fk_tempo)::text = (m.tempo)::text) AND ((fk_tempo)::text >= '198201'::text) AND ((fk_tempo)::text <= '200000'::text))
  • Filter: ((fk_classificacao = 4,744) AND (fk_nivel = 5) AND (fk_produto = 29) AND ((m.rg)::text = (fk_rg)::text) AND (m.unidade = fk_unidade) AND ((fk_rg)::text = ANY ('{CO,N,NE,S,SE}'::text[])))
  • Rows Removed by Filter: 50
Planning time : 2.300 ms
Execution time : 137.035 ms