explain.depesz.com

PostgreSQL's explain analyze made readable

Result: qDNh : Diferimento x Valor Bruto x Pagamento x Desconto - Total

Settings

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 49.351 3,491.246 ↑ 1.0 1 1

Aggregate (cost=49,198.65..49,198.67 rows=1 width=128) (actual time=3,491.245..3,491.246 rows=1 loops=1)

  • Buffers: shared hit=5050, temp read=801 written=798
2.          

CTE desconto

3. 80.372 356.816 ↑ 1.1 35,514 1

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

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

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

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

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

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

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

  • Buffers: shared hit=355
7. 33.863 114.283 ↑ 1.0 59,420 1

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

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

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

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

CTE diferimento

10. 360.767 1,476.417 ↑ 2.3 24,841 1

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

  • Group Key: ped_2.codigo, ped_2.instituicao
  • Buffers: shared hit=1761, temp read=249 written=250
11. 0.000 1,115.650 ↑ 2.3 24,880 1

Gather Merge (cost=16,861.5..24,995.3 rows=57,878 width=40) (actual time=891.957..1,115.65 rows=24,880 loops=1)

  • Workers Planned: 1
  • Workers Launched: 1
  • Buffers: shared hit=4060, temp read=638 written=641
12. 250.864 1,204.735 ↑ 4.7 12,440 2 / 2

GroupAggregate (cost=15,861.49..17,484.02 rows=57,878 width=40) (actual time=880.264..1,204.735 rows=12,440 loops=2)

  • Group Key: ped_2.codigo, ped_2.instituicao
  • Buffers: shared hit=4060, temp read=638 written=641
13. 358.375 953.871 ↓ 1.0 93,643 2 / 2

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

  • Sort Key: ped_2.codigo, ped_2.instituicao
  • Sort Method: external merge Disk: 1992kB
  • Buffers: shared hit=4060, temp read=638 written=641
14. 278.837 595.496 ↓ 1.0 93,643 2 / 2

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

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

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

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

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

  • Filter: ((ped_2.status)::text = ANY ('{wc-completed,wc-cancelled}'::text[]))
  • Buffers: shared hit=1783
18. 146.170 3,441.895 ↓ 1.1 65,853 1

Hash Join (cost=11,204.35..14,794.3 rows=61,300 width=72) (actual time=3,024.864..3,441.895 rows=65,853 loops=1)

  • Buffers: shared hit=5050, temp read=801 written=798
19. 123.817 3,185.459 ↓ 1.0 61,891 1

Hash Join (cost=9,313.43..11,866.82 rows=61,300 width=76) (actual time=2,914.489..3,185.459 rows=61,891 loops=1)

  • Buffers: shared hit=4632, temp read=493 written=492
20. 404.811 404.811 ↑ 1.1 35,514 1

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

  • Buffers: shared hit=1245
21. 92.386 2,656.831 ↓ 1.0 61,891 1

Hash (cost=7,854.93..7,854.93 rows=61,300 width=48) (actual time=2,656.831..2,656.831 rows=61,891 loops=1)

  • Buffers: shared hit=3387, temp read=249 written=413
22. 91.760 2,564.445 ↓ 1.0 61,891 1

Hash Join (cost=5,519.77..7,854.93 rows=61,300 width=48) (actual time=1,697.665..2,564.445 rows=61,891 loops=1)

  • Buffers: shared hit=3387, temp read=249 written=250
23. 121.483 2,472.671 ↓ 1.0 61,891 1

Hash Join (cost=5,497.84..7,670.81 rows=61,300 width=52) (actual time=1,697.641..2,472.671 rows=61,891 loops=1)

  • Buffers: shared hit=3386, temp read=249 written=250
24. 1,545.739 1,545.739 ↑ 2.3 24,841 1

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

  • Buffers: shared hit=1761, temp read=249 written=250
25. 51.006 805.449 ↓ 1.0 61,891 1

Hash (cost=4,578.34..4,578.34 rows=61,300 width=20) (actual time=805.449..805.449 rows=61,891 loops=1)

  • Buffers: shared hit=1625
26. 139.724 754.443 ↓ 1.0 61,891 1

Hash Join (cost=2,696.35..4,578.34 rows=61,300 width=20) (actual time=587.904..754.443 rows=61,891 loops=1)

  • Buffers: shared hit=1625
27. 27.037 27.037 ↓ 1.0 75,308 1

Seq Scan on pagamento pag (cost=0..1,487.12 rows=75,212 width=12) (actual time=0.005..27.037 rows=75,308 loops=1)

  • Buffers: shared hit=735
28. 167.129 587.682 ↑ 1.0 59,420 1

Hash (cost=1,803.2..1,803.2 rows=59,543 width=12) (actual time=587.681..587.682 rows=59,420 loops=1)

  • Buffers: shared hit=890
29. 420.553 420.553 ↑ 1.0 59,420 1

Seq Scan on pedido ped (cost=0..1,803.2 rows=59,543 width=12) (actual time=0.009..420.553 rows=59,420 loops=1)

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

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

  • Buffers: shared hit=1
31. 0.009 0.009 ↑ 265.0 2 1

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

  • Buffers: shared hit=1
32. 52.033 110.266 ↓ 1.0 65,542 1

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

  • Buffers: shared hit=418, temp written=144
33. 58.233 58.233 ↓ 1.0 65,542 1

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

  • Buffers: shared hit=418
Planning time : 98.246 ms
Execution time : 3,492.418 ms