explain.depesz.com

PostgreSQL's explain analyze made readable

Result: I18M : Diferimento x Valor Bruto x Pagamento x Desconto - Gráfico

Settings
# exclusive inclusive rows x rows loops node
1. 0.153 4,064.760 ↑ 2,258.2 26 1

Subquery Scan on financeira (cost=57,571.79..61,423.93 rows=58,714 width=160) (actual time=3,898.893..4,064.76 rows=26 loops=1)

  • Buffers: shared hit=5156, temp read=790 written=790
2.          

CTE desconto

3. 140.280 641.591 ↑ 1.1 35,514 1

GroupAggregate (cost=6,641.35..7,493.79 rows=37,886 width=40) (actual time=491.681..641.591 rows=35,514 loops=1)

  • Group Key: ped_1.codigo, ped_1.instituicao
  • Buffers: shared hit=1245
4. 85.038 501.311 ↓ 1.1 40,296 1

Sort (cost=6,641.35..6,736.07 rows=37,886 width=13) (actual time=491.668..501.311 rows=40,296 loops=1)

  • Sort Key: ped_1.codigo, ped_1.instituicao
  • Sort Method: quicksort Memory: 3255kB
  • Buffers: shared hit=1245
5. 98.491 416.273 ↓ 1.1 40,296 1

Hash Join (cost=2,696.35..3,760.24 rows=37,886 width=13) (actual time=298.272..416.273 rows=40,296 loops=1)

  • Buffers: shared hit=1245
6. 19.592 19.592 ↑ 1.0 46,448 1

Seq Scan on desconto des_1 (cost=0..819.84 rows=46,484 width=13) (actual time=0.012..19.592 rows=46,448 loops=1)

  • Buffers: shared hit=355
7. 57.143 298.190 ↑ 1.0 59,420 1

Hash (cost=1,803.2..1,803.2 rows=59,543 width=8) (actual time=298.19..298.19 rows=59,420 loops=1)

  • Buffers: shared hit=890
8. 241.047 241.047 ↑ 1.0 59,420 1

Seq Scan on pedido ped_1 (cost=0..1,803.2 rows=59,543 width=8) (actual time=0.01..241.047 rows=59,420 loops=1)

  • Filter: ((ped_1.status)::text = ANY ('{wc-completed,wc-cancelled}'::text[]))
  • Buffers: shared hit=890
9.          

CTE diferimento

10. 86.789 1,408.711 ↑ 2.3 24,841 1

GroupAggregate (cost=16,861.5..26,297.56 rows=57,878 width=40) (actual time=975.312..1,408.711 rows=24,841 loops=1)

  • Group Key: ped_2.codigo, ped_2.instituicao
  • Buffers: shared hit=1867, temp read=280 written=281
11. 71.026 1,321.922 ↑ 2.3 24,887 1

Gather Merge (cost=16,861.5..24,995.3 rows=57,878 width=40) (actual time=975.293..1,321.922 rows=24,887 loops=1)

  • Workers Planned: 1
  • Workers Launched: 1
  • Buffers: shared hit=4060, temp read=639 written=641
12. 228.156 1,250.896 ↑ 4.7 12,444 2 / 2

GroupAggregate (cost=15,861.49..17,484.02 rows=57,878 width=40) (actual time=954.224..1,250.896 rows=12,444 loops=2)

  • Group Key: ped_2.codigo, ped_2.instituicao
  • Buffers: shared hit=4060, temp read=639 written=641
13. 388.085 1,022.740 ↓ 1.0 93,643 2 / 2

Sort (cost=15,861.49..16,086.25 rows=89,905 width=17) (actual time=954.209..1,022.74 rows=93,643 loops=2)

  • Sort Key: ped_2.codigo, ped_2.instituicao
  • Sort Method: external merge Disk: 2240kB
  • Buffers: shared hit=4060, temp read=639 written=641
14. 291.291 634.655 ↓ 1.0 93,643 2 / 2

Hash Join (cost=2,696.35..6,619.56 rows=89,905 width=17) (actual time=259.734..634.655 rows=93,643 loops=2)

  • Buffers: shared hit=4032
15. 83.881 83.881 ↑ 1.2 93,650 2 / 2

Seq Scan on diferimento dif_1 (cost=0..3,344.08 rows=110,308 width=17) (actual time=0.005..83.881 rows=93,650 loops=2)

  • Buffers: shared hit=2241
16. 135.006 259.483 ↑ 1.0 59,420 2 / 2

Hash (cost=1,803.2..1,803.2 rows=59,543 width=8) (actual time=259.483..259.483 rows=59,420 loops=2)

  • Buffers: shared hit=1783
17. 124.477 124.477 ↑ 1.0 59,420 2 / 2

Seq Scan on pedido ped_2 (cost=0..1,803.2 rows=59,543 width=8) (actual time=0.017..124.477 rows=59,420 loops=2)

  • Filter: ((ped_2.status)::text = ANY ('{wc-completed,wc-cancelled}'::text[]))
  • Buffers: shared hit=1783
18. 129.798 4,064.607 ↑ 2,258.2 26 1

GroupAggregate (cost=23,780.45..26,605.09 rows=58,714 width=160) (actual time=3,898.878..4,064.607 rows=26 loops=1)

  • Group Key: (to_char(ped.data_pedido, 'yyyy-mm'::text))
  • Buffers: shared hit=5156, temp read=790 written=790
19. 601.317 3,934.809 ↑ 1.0 59,458 1

Sort (cost=23,780.45..23,933.19 rows=61,096 width=104) (actual time=3,893.356..3,934.809 rows=59,458 loops=1)

  • Sort Key: (to_char(ped.data_pedido, 'yyyy-mm'::text))
  • Sort Method: external merge Disk: 1960kB
  • Buffers: shared hit=5156, temp read=790 written=790
20. 202.404 3,333.492 ↑ 1.0 59,458 1

Hash Join (cost=12,817.06..15,581.19 rows=61,096 width=104) (actual time=2,901.536..3,333.492 rows=59,458 loops=1)

  • Buffers: shared hit=5156, temp read=545 written=544
21. 722.982 722.982 ↑ 1.1 35,514 1

CTE Scan on desconto des (cost=0..757.72 rows=37,886 width=40) (actual time=491.687..722.982 rows=35,514 loops=1)

  • Buffers: shared hit=1245
22. 95.142 2,408.106 ↑ 1.0 59,458 1

Hash (cost=11,303.62..11,303.62 rows=61,096 width=56) (actual time=2,408.105..2,408.106 rows=59,458 loops=1)

  • Buffers: shared hit=3911, temp read=280 written=465
23. 134.833 2,312.964 ↑ 1.0 59,458 1

Hash Join (cost=9,130.67..11,303.62 rows=61,096 width=56) (actual time=1,699.416..2,312.964 rows=59,458 loops=1)

  • Buffers: shared hit=3911, temp read=280 written=281
24. 1,454.119 1,454.119 ↑ 2.3 24,841 1

CTE Scan on diferimento dif (cost=0..1,157.56 rows=57,878 width=40) (actual time=975.316..1,454.119 rows=24,841 loops=1)

  • Buffers: shared hit=1867, temp read=280 written=281
25. 69.300 724.012 ↑ 1.0 59,458 1

Hash (cost=8,214.23..8,214.23 rows=61,096 width=24) (actual time=724.011..724.012 rows=59,458 loops=1)

  • Buffers: shared hit=2044
26. 136.911 654.712 ↑ 1.0 59,458 1

Hash Join (cost=5,027.74..8,214.23 rows=61,096 width=24) (actual time=282.869..654.712 rows=59,458 loops=1)

  • Buffers: shared hit=2044
27. 83.395 235.168 ↑ 1.0 72,836 1

Hash Join (cost=21.93..1,895.42 rows=74,961 width=16) (actual time=0.023..235.168 rows=72,836 loops=1)

  • Buffers: shared hit=736
28. 151.766 151.766 ↑ 1.0 72,836 1

Seq Scan on pagamento pag (cost=0..1,675.15 rows=74,961 width=12) (actual time=0.01..151.766 rows=72,836 loops=1)

  • Filter: (pag.tipo_pagamento = 1)
  • Buffers: shared hit=735
29. 0.003 0.007 ↑ 265.0 2 1

Hash (cost=15.3..15.3 rows=530 width=4) (actual time=0.007..0.007 rows=2 loops=1)

  • Buffers: shared hit=1
30. 0.004 0.004 ↑ 265.0 2 1

Seq Scan on instituicao inst (cost=0..15.3 rows=530 width=4) (actual time=0.003..0.004 rows=2 loops=1)

  • Buffers: shared hit=1
31. 49.431 282.633 ↑ 1.0 59,458 1

Hash (cost=4,112.67..4,112.67 rows=59,543 width=20) (actual time=282.632..282.633 rows=59,458 loops=1)

  • Buffers: shared hit=1308
32. 75.118 233.202 ↑ 1.0 59,458 1

Hash Join (cost=2,696.35..4,112.67 rows=59,543 width=20) (actual time=122.804..233.202 rows=59,458 loops=1)

  • Buffers: shared hit=1308
33. 35.500 35.500 ↓ 1.0 65,542 1

Seq Scan on pedido_oferta pofer (cost=0..1,072.63 rows=65,463 width=16) (actual time=0.007..35.5 rows=65,542 loops=1)

  • Buffers: shared hit=418
34. 56.380 122.584 ↑ 1.0 59,420 1

Hash (cost=1,803.2..1,803.2 rows=59,543 width=20) (actual time=122.584..122.584 rows=59,420 loops=1)

  • Buffers: shared hit=890
35. 66.204 66.204 ↑ 1.0 59,420 1

Seq Scan on pedido ped (cost=0..1,803.2 rows=59,543 width=20) (actual time=0.008..66.204 rows=59,420 loops=1)

  • Filter: ((ped.status)::text = ANY ('{wc-completed,wc-cancelled}'::text[]))
  • Buffers: shared hit=890
Planning time : 2.355 ms
Execution time : 4,066.607 ms