explain.depesz.com

PostgreSQL's explain analyze made readable

Result: dIyD : Diferimento x Valor Bruto x Pagamento x Desconto - Detalhamento diferimento maior que pagamento por pedido

Settings
# exclusive inclusive rows x rows loops node
1. 0.007 3,117.463 ↑ 10,958.0 2 1

Subquery Scan on financeira (cost=49,237.36..50,881.06 rows=21,916 width=298) (actual time=3,080.492..3,117.463 rows=2 loops=1)

  • Buffers: shared hit=5427, temp read=588 written=587
2.          

CTE desconto

3. 153.785 603.824 ↑ 1.1 35,514 1

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

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

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

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

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

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

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

  • Buffers: shared hit=355
7. 64.250 114.210 ↑ 1.0 59,420 1

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

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

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

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

CTE diferimento

10. 133.747 1,509.177 ↑ 2.3 24,841 1

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

  • Group Key: ped_2.codigo, ped_2.instituicao
  • Buffers: shared hit=2109, temp read=347 written=348
11. 55.275 1,375.430 ↑ 2.3 24,892 1

Gather Merge (cost=16,861.5..24,995.3 rows=57,878 width=40) (actual time=1,098.344..1,375.43 rows=24,892 loops=1)

  • Workers Planned: 1
  • Workers Launched: 1
  • Buffers: shared hit=4060, temp read=638 written=640
12. 180.054 1,320.155 ↑ 4.7 12,446 2 / 2

GroupAggregate (cost=15,861.49..17,484.02 rows=57,878 width=40) (actual time=1,082.033..1,320.155 rows=12,446 loops=2)

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

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

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

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

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

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

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

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

  • Filter: ((ped_2.status)::text = ANY ('{wc-completed,wc-cancelled}'::text[]))
  • Buffers: shared hit=1783
18. 91.461 3,117.456 ↑ 10,958.0 2 1

GroupAggregate (cost=15,446.02..16,815.77 rows=21,916 width=266) (actual time=3,080.487..3,117.456 rows=2 loops=1)

  • Group Key: ped.codigo, inst.nome, cur.duracao_meses_titulo, ped.status
  • Filter: (round(sum(dif.valor), 0) >= (round(sum(pag.valor), 0) + '10'::numeric))
  • Buffers: shared hit=5427, temp read=588 written=587
19. 47.677 3,025.995 ↓ 1.1 24,605 1

Sort (cost=15,446.02..15,500.81 rows=21,916 width=210) (actual time=3,020.094..3,025.995 rows=24,605 loops=1)

  • Sort Key: ped.codigo, inst.nome, cur.duracao_meses_titulo, ped.status
  • Sort Method: quicksort Memory: 2980kB
  • Buffers: shared hit=5427, temp read=588 written=587
20. 57.323 2,978.318 ↓ 1.1 24,605 1

Hash Join (cost=8,588.22..11,615.41 rows=21,916 width=210) (actual time=2,509.579..2,978.318 rows=24,605 loops=1)

  • Buffers: shared hit=5427, temp read=588 written=587
21. 26.118 2,920.569 ↓ 1.1 24,605 1

Hash Join (cost=8,551.27..11,463.04 rows=21,916 width=214) (actual time=2,509.145..2,920.569 rows=24,605 loops=1)

  • Buffers: shared hit=5404, temp read=588 written=587
22. 111.017 2,894.086 ↓ 1.1 24,605 1

Hash Join (cost=8,530.23..11,268.96 rows=21,916 width=218) (actual time=2,508.763..2,894.086 rows=24,605 loops=1)

  • Buffers: shared hit=5398, temp read=588 written=587
23. 18.574 2,658.867 ↓ 1.1 24,569 1

Hash Join (cost=6,475.65..8,704.97 rows=21,916 width=210) (actual time=2,352.829..2,658.867 rows=24,569 loops=1)

  • Buffers: shared hit=4980, temp read=347 written=348
24. 87.535 2,640.278 ↓ 1.1 24,569 1

Hash Join (cost=6,453.73..8,625.06 rows=21,916 width=96) (actual time=2,352.802..2,640.278 rows=24,569 loops=1)

  • Buffers: shared hit=4979, temp read=347 written=348
25. 1,567.396 1,567.396 ↑ 2.3 24,841 1

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

  • Buffers: shared hit=2109, temp read=347 written=348
26. 57.913 985.347 ↓ 1.1 24,569 1

Hash (cost=6,124.99..6,124.99 rows=21,916 width=64) (actual time=985.347..985.347 rows=24,569 loops=1)

  • Buffers: shared hit=2870
27. 81.945 927.434 ↓ 1.1 24,569 1

Hash Join (cost=4,703.67..6,124.99 rows=21,916 width=64) (actual time=726.606..927.434 rows=24,569 loops=1)

  • Buffers: shared hit=2870
28. 656.506 656.506 ↑ 1.1 35,514 1

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

  • Buffers: shared hit=1245
29. 36.219 188.983 ↓ 1.1 24,569 1

Hash (cost=4,374.93..4,374.93 rows=21,916 width=32) (actual time=188.983..188.983 rows=24,569 loops=1)

  • Buffers: shared hit=1625
30. 56.682 152.764 ↓ 1.1 24,569 1

Hash Join (cost=2,306.22..4,374.93 rows=21,916 width=32) (actual time=62.928..152.764 rows=24,569 loops=1)

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

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

  • Filter: (pag.tipo_pagamento = 1)
  • Buffers: shared hit=735
32. 22.889 62.272 ↓ 1.2 24,569 1

Hash (cost=1,985.84..1,985.84 rows=21,359 width=24) (actual time=62.272..62.272 rows=24,569 loops=1)

  • Buffers: shared hit=890
33. 39.383 39.383 ↓ 1.2 24,569 1

Seq Scan on pedido ped (cost=0..1,985.84 rows=21,359 width=24) (actual time=0.014..39.383 rows=24,569 loops=1)

  • Filter: (((ped.status)::text = ANY ('{wc-completed,wc-cancelled}'::text[])) AND (ped.data_pedido >= '2020-01-01 00:00:00'::timestamp without time zone))
  • Buffers: shared hit=890
34. 0.006 0.015 ↑ 265.0 2 1

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

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

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

  • Buffers: shared hit=1
36. 77.018 124.202 ↓ 1.0 65,542 1

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

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

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

  • Buffers: shared hit=418
38. 0.185 0.365 ↑ 1.1 507 1

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

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

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

  • Buffers: shared hit=6
40. 0.193 0.426 ↑ 1.0 558 1

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

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

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

  • Buffers: shared hit=23
Planning time : 3.979 ms
Execution time : 3,118.772 ms