explain.depesz.com

PostgreSQL's explain analyze made readable

Result: vsA3 : Diferimento x Valor Bruto x Pagamento x Desconto - Detalhamento

Settings
# exclusive inclusive rows x rows loops node
1. 8.542 4,572.750 ↑ 455.9 134 1

Subquery Scan on financeira (cost=63,847.48..68,124.2 rows=61,096 width=282) (actual time=4,440.564..4,572.75 rows=134 loops=1)

  • Buffers: shared hit=5405 dirtied=12, temp read=1572 written=1568
2.          

CTE desconto

3. 68.577 324.112 ↑ 1.1 35,514 1

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

  • Group Key: ped_1.codigo, ped_1.instituicao
  • Buffers: shared hit=1245 dirtied=12
4. 73.724 255.535 ↓ 1.1 40,296 1

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

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

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

  • Buffers: shared hit=1245 dirtied=12
6. 40.645 40.645 ↑ 1.0 46,448 1

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

  • Buffers: shared hit=355 dirtied=12
7. 34.418 100.675 ↑ 1.0 59,420 1

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

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

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

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

CTE diferimento

10. 104.701 1,508.587 ↑ 2.3 24,841 1

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

  • Group Key: ped_2.codigo, ped_2.instituicao
  • Buffers: shared hit=2087, temp read=340 written=341
11. 125.975 1,403.886 ↑ 2.3 24,898 1

Gather Merge (cost=16,861.5..24,995.3 rows=57,878 width=40) (actual time=1,033.863..1,403.886 rows=24,898 loops=1)

  • Workers Planned: 1
  • Workers Launched: 1
  • Buffers: shared hit=4060, temp read=638 written=640
12. 194.030 1,277.911 ↑ 4.6 12,449 2 / 2

GroupAggregate (cost=15,861.49..17,484.02 rows=57,878 width=40) (actual time=999.874..1,277.911 rows=12,449 loops=2)

  • Group Key: ped_2.codigo, ped_2.instituicao
  • Buffers: shared hit=4060, temp read=638 written=640
13. 455.775 1,083.881 ↓ 1.0 93,643 2 / 2

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

  • Sort Key: ped_2.codigo, ped_2.instituicao
  • Sort Method: external merge Disk: 2720kB
  • Buffers: shared hit=4060, temp read=638 written=640
14. 301.941 628.106 ↓ 1.0 93,643 2 / 2

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

  • Buffers: shared hit=4032
15. 126.857 126.857 ↑ 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.008..126.857 rows=93,650 loops=2)

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

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

  • Buffers: shared hit=1783
17. 125.294 125.294 ↑ 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..125.294 rows=59,420 loops=2)

  • Filter: ((ped_2.status)::text = ANY ('{wc-completed,wc-cancelled}'::text[]))
  • Buffers: shared hit=1783
18. 85.380 4,564.208 ↑ 455.9 134 1

GroupAggregate (cost=30,056.13..33,263.67 rows=61,096 width=282) (actual time=4,440.537..4,564.208 rows=134 loops=1)

  • Group Key: (to_char(ped.data_pedido, 'yyyy-mm'::text)), inst.nome, cur.duracao_meses_titulo
  • Buffers: shared hit=5405 dirtied=12, temp read=1572 written=1568
19. 664.570 4,478.828 ↑ 1.0 59,458 1

Sort (cost=30,056.13..30,208.87 rows=61,096 width=226) (actual time=4,438.893..4,478.828 rows=59,458 loops=1)

  • Sort Key: (to_char(ped.data_pedido, 'yyyy-mm'::text)), inst.nome, cur.duracao_meses_titulo
  • Sort Method: external merge Disk: 2576kB
  • Buffers: shared hit=5405 dirtied=12, temp read=1572 written=1568
20. 177.176 3,814.258 ↑ 1.0 59,458 1

Hash Join (cost=12,239.81..18,514.37 rows=61,096 width=226) (actual time=2,260.898..3,814.258 rows=59,458 loops=1)

  • Buffers: shared hit=5405 dirtied=12, temp read=1250 written=1245
21. 292.329 3,636.636 ↑ 1.0 59,458 1

Hash Join (cost=12,202.86..18,002.94 rows=61,096 width=206) (actual time=2,260.427..3,636.636 rows=59,458 loops=1)

  • Buffers: shared hit=5382 dirtied=12, temp read=1250 written=1245
22. 196.103 3,343.955 ↑ 1.0 59,458 1

Hash Join (cost=12,181.82..17,499.52 rows=61,096 width=210) (actual time=2,260.061..3,343.955 rows=59,458 loops=1)

  • Buffers: shared hit=5376 dirtied=12, temp read=1250 written=1245
23. 124.091 3,039.546 ↑ 1.0 59,420 1

Hash Join (cost=10,127.24..14,024.85 rows=61,096 width=206) (actual time=2,151.53..3,039.546 rows=59,420 loops=1)

  • Buffers: shared hit=4958 dirtied=12, temp read=879 written=876
24. 226.797 2,915.435 ↑ 1.0 59,420 1

Hash Join (cost=10,105.32..13,841.27 rows=61,096 width=92) (actual time=2,151.496..2,915.435 rows=59,420 loops=1)

  • Buffers: shared hit=4957 dirtied=12, temp read=879 written=876
25. 1,571.336 1,571.336 ↑ 2.3 24,841 1

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

  • Buffers: shared hit=2087, temp read=340 written=341
26. 84.001 1,117.302 ↑ 1.0 59,420 1

Hash (cost=8,531.88..8,531.88 rows=61,096 width=60) (actual time=1,117.302..1,117.302 rows=59,420 loops=1)

  • Buffers: shared hit=2870 dirtied=12, temp read=273 written=475
27. 102.504 1,033.301 ↑ 1.0 59,420 1

Hash Join (cost=6,099.49..8,531.88 rows=61,096 width=60) (actual time=582.457..1,033.301 rows=59,420 loops=1)

  • Buffers: shared hit=2870 dirtied=12, temp read=273 written=271
28. 577.628 577.628 ↑ 1.1 35,514 1

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

  • Buffers: shared hit=1245 dirtied=12
29. 62.621 353.169 ↑ 1.0 59,420 1

Hash (cost=4,765.05..4,765.05 rows=61,096 width=28) (actual time=353.169..353.169 rows=59,420 loops=1)

  • Buffers: shared hit=1625, temp written=192
30. 102.493 290.548 ↑ 1.0 59,420 1

Hash Join (cost=2,696.35..4,765.05 rows=61,096 width=28) (actual time=114.963..290.548 rows=59,420 loops=1)

  • Buffers: shared hit=1625
31. 73.327 73.327 ↑ 1.0 72,836 1

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

  • Filter: (pag.tipo_pagamento = 1)
  • Buffers: shared hit=735
32. 41.051 114.728 ↑ 1.0 59,420 1

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

  • Buffers: shared hit=890
33. 73.677 73.677 ↑ 1.0 59,420 1

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

  • Filter: ((ped.status)::text = ANY ('{wc-completed,wc-cancelled}'::text[]))
  • Buffers: shared hit=890
34. 0.008 0.020 ↑ 265.0 2 1

Hash (cost=15.3..15.3 rows=530 width=122) (actual time=0.02..0.02 rows=2 loops=1)

  • Buffers: shared hit=1
35. 0.012 0.012 ↑ 265.0 2 1

Seq Scan on instituicao inst (cost=0..15.3 rows=530 width=122) (actual time=0.01..0.012 rows=2 loops=1)

  • Buffers: shared hit=1
36. 53.514 108.306 ↓ 1.0 65,542 1

Hash (cost=1,072.63..1,072.63 rows=65,463 width=16) (actual time=108.306..108.306 rows=65,542 loops=1)

  • Buffers: shared hit=418, temp written=143
37. 54.792 54.792 ↓ 1.0 65,542 1

Seq Scan on pedido_oferta pofert (cost=0..1,072.63 rows=65,463 width=16) (actual time=0.013..54.792 rows=65,542 loops=1)

  • Buffers: shared hit=418
38. 0.180 0.352 ↑ 1.1 507 1

Hash (cost=11.47..11.47 rows=547 width=16) (actual time=0.352..0.352 rows=507 loops=1)

  • Buffers: shared hit=6
39. 0.172 0.172 ↑ 1.1 507 1

Seq Scan on oferta ofer (cost=0..11.47 rows=547 width=16) (actual time=0.008..0.172 rows=507 loops=1)

  • Buffers: shared hit=6
40. 0.195 0.446 ↑ 1.0 558 1

Hash (cost=28.58..28.58 rows=558 width=12) (actual time=0.446..0.446 rows=558 loops=1)

  • Buffers: shared hit=23
41. 0.251 0.251 ↑ 1.0 558 1

Seq Scan on curso cur (cost=0..28.58 rows=558 width=12) (actual time=0.004..0.251 rows=558 loops=1)

  • Buffers: shared hit=23
Planning time : 4.39 ms
Execution time : 4,574.764 ms