explain.depesz.com

PostgreSQL's explain analyze made readable

Result: A2A7 : Diferimento - Por ano - Base - Query

Settings

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 0.141 5,275.866 ↑ 115.2 46 1

Subquery Scan on dif (cost=18,445.97..19,082.44 rows=5,298 width=132) (actual time=5,181.192..5,275.866 rows=46 loops=1)

  • Buffers: shared hit=132631, temp read=706 written=708
2. 0.186 5,275.725 ↑ 115.2 46 1

GroupAggregate (cost=18,445.97..19,016.21 rows=5,298 width=108) (actual time=5,181.182..5,275.725 rows=46 loops=1)

  • Group Key: (to_char(p.data_pedido, 'YYYY'::text)), d.data_diferimento, met.valor_mes, p.instituicao
  • Buffers: shared hit=132631, temp read=706 written=708
3. 170.876 5,275.539 ↑ 33.9 92 1

Gather Merge (cost=18,445.97..18,890 rows=3,116 width=108) (actual time=5,169.788..5,275.539 rows=92 loops=1)

  • Workers Planned: 1
  • Workers Launched: 1
  • Buffers: shared hit=252968, temp read=1123 written=1126
4. 103.923 5,104.663 ↑ 67.7 46 2 / 2

GroupAggregate (cost=17,445.96..17,539.44 rows=3,116 width=108) (actual time=4,971.692..5,104.663 rows=46 loops=2)

  • Group Key: (to_char(p.data_pedido, 'YYYY'::text)), d.data_diferimento, met.valor_mes, p.instituicao
  • Buffers: shared hit=252968, temp read=1123 written=1126
5. 580.158 5,000.740 ↓ 13.4 41,724 2 / 2

Sort (cost=17,445.96..17,453.75 rows=3,116 width=85) (actual time=4,971.35..5,000.74 rows=41,724 loops=2)

  • Sort Key: (to_char(p.data_pedido, 'YYYY'::text)), d.data_diferimento, met.valor_mes, p.instituicao
  • Sort Method: external sort Disk: 2128kB
  • Buffers: shared hit=252968, temp read=1123 written=1126
6. 539.113 4,420.582 ↓ 13.4 41,724 2 / 2

Nested Loop (cost=15,304.42..17,265.15 rows=3,116 width=85) (actual time=2,582.696..4,420.582 rows=41,724 loops=2)

  • Buffers: shared hit=252942, temp read=857 written=860
7. 393.947 3,255.602 ↓ 13.4 41,724 2 / 2

Merge Join (cost=15,304.13..16,185.97 rows=3,116 width=61) (actual time=2,564.452..3,255.602 rows=41,724 loops=2)

  • Buffers: shared hit=2250, temp read=857 written=860
8. 2,226.783 2,794.220 ↑ 1.2 91,248 2 / 2

Sort (cost=15,225.53..15,501.3 rows=110,308 width=25) (actual time=2,561.621..2,794.22 rows=91,248 loops=2)

  • Sort Key: d.instituicao, d.data_diferimento
  • Sort Method: external merge Disk: 3520kB
  • Buffers: shared hit=2241, temp read=857 written=860
9. 567.437 567.437 ↑ 1.2 93,650 2 / 2

Seq Scan on diferimento d (cost=0..3,344.08 rows=110,308 width=25) (actual time=0.006..567.437 rows=93,650 loops=2)

  • Buffers: shared hit=2241
10. 67.415 67.435 ↓ 35.7 40,324 2 / 2

Sort (cost=78.6..81.43 rows=1,130 width=40) (actual time=0.056..67.435 rows=40,324 loops=2)

  • Sort Key: met.instituicao, met.data_meta
  • Sort Method: quicksort Memory: 26kB
  • Buffers: shared hit=9
11. 0.020 0.020 ↑ 47.1 24 2 / 2

Seq Scan on meta_diferimento met (cost=0..21.3 rows=1,130 width=40) (actual time=0.013..0.02 rows=24 loops=2)

  • Buffers: shared hit=2
12. 625.867 625.867 ↑ 1.0 1 83,449 / 2

Index Scan using pk_pedido on pedido p (cost=0.29..0.34 rows=1 width=16) (actual time=0.015..0.015 rows=1 loops=83,449)

  • Index Cond: ((p.codigo = d.codigo_pedido) AND (p.instituicao = d.instituicao))
  • Buffers: shared hit=250692
Planning time : 0.655 ms
Execution time : 5,285.399 ms