explain.depesz.com

PostgreSQL's explain analyze made readable

Result: wmbY : Diferimento - Base - Query

Settings

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 0.112 2,132.107 ↑ 220.8 24 1

Subquery Scan on dif (cost=18,438.18..19,077.77 rows=5,298 width=140) (actual time=2,047.736..2,132.107 rows=24 loops=1)

  • Buffers: shared hit=160,182, temp read=731 written=733
2. 0.013 2,131.995 ↑ 220.8 24 1

GroupAggregate (cost=18,438.18..18,971.81 rows=5,298 width=76) (actual time=2,047.722..2,131.995 rows=24 loops=1)

  • Group Key: d.data_diferimento, met.valor_mes, p.instituicao
  • Buffers: shared hit=160,182, temp read=731 written=733
3. 286.926 2,131.982 ↑ 64.9 48 1

Gather Merge (cost=18,438.18..18,866.63 rows=3,116 width=76) (actual time=2,044.116..2,131.982 rows=48 loops=1)

  • Workers Planned: 1
  • Workers Launched: 1
  • Buffers: shared hit=252,960, temp read=1,126 written=1,129
4. 76.265 1,845.056 ↑ 129.8 24 2 / 2

GroupAggregate (cost=17,438.17..17,516.07 rows=3,116 width=76) (actual time=1,755.379..1,845.056 rows=24 loops=2)

  • Group Key: d.data_diferimento, met.valor_mes, p.instituicao
  • Buffers: shared hit=252,960, temp read=1,126 written=1,129
5. 226.420 1,768.791 ↓ 13.4 41,724 2 / 2

Sort (cost=17,438.17..17,445.96 rows=3,116 width=53) (actual time=1,754.981..1,768.791 rows=41,724 loops=2)

  • Sort Key: d.data_diferimento, met.valor_mes, p.instituicao
  • Sort Method: external sort Disk: 2,160kB
  • Buffers: shared hit=252,960, temp read=1,126 written=1,129
6. 154.203 1,542.371 ↓ 13.4 41,724 2 / 2

Nested Loop (cost=15,304.42..17,257.36 rows=3,116 width=53) (actual time=515.589..1,542.371 rows=41,724 loops=2)

  • Buffers: shared hit=252,942, temp read=856 written=859
7. 235.687 970.923 ↓ 13.4 41,724 2 / 2

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

  • Buffers: shared hit=2,250, temp read=856 written=859
8. 527.305 688.557 ↑ 1.2 91,248 2 / 2

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

  • Sort Key: d.instituicao, d.data_diferimento
  • Sort Method: external merge Disk: 3,688kB
  • Buffers: shared hit=2,241, temp read=856 written=859
9. 161.252 161.252 ↑ 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..161.252 rows=93,650 loops=2)

  • Buffers: shared hit=2,241
10. 46.653 46.679 ↓ 35.7 40,324 2 / 2

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

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

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

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

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

  • Index Cond: ((p.codigo = d.codigo_pedido) AND (p.instituicao = d.instituicao))
  • Heap Fetches: 52,901
  • Buffers: shared hit=250,692
Planning time : 0.636 ms
Execution time : 2,133.221 ms