explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 2d1P : Diferimento x Valor Bruto x Pagamento x Desconto - Detalhamento diferimento menor que pagamento por pedido

Settings
# exclusive inclusive rows x rows loops node
1. 0.267 3,074.431 ↑ 54.1 405 1

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

  • Buffers: shared hit=5196, temp read=523 written=522
2.          

CTE desconto

3. 91.980 393.401 ↑ 1.1 35,514 1

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

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

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

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

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

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

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

  • Buffers: shared hit=355
7. 24.248 118.626 ↑ 1.0 59,420 1

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

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

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

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

CTE diferimento

10. 125.516 1,529.335 ↑ 2.3 24,841 1

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

  • Group Key: ped_2.codigo, ped_2.instituicao
  • Buffers: shared hit=1878, temp read=282 written=283
11. 23.372 1,403.819 ↑ 2.3 24,904 1

Gather Merge (cost=16,861.5..24,995.3 rows=57,878 width=40) (actual time=1,118.256..1,403.819 rows=24,904 loops=1)

  • Workers Planned: 1
  • Workers Launched: 1
  • Buffers: shared hit=4060, temp read=638 written=641
12. 167.992 1,380.447 ↑ 4.6 12,452 2 / 2

GroupAggregate (cost=15,861.49..17,484.02 rows=57,878 width=40) (actual time=1,083.62..1,380.447 rows=12,452 loops=2)

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

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

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

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

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

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

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

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

  • Filter: ((ped_2.status)::text = ANY ('{wc-completed,wc-cancelled}'::text[]))
  • Buffers: shared hit=1783
18. 101.188 3,074.164 ↑ 54.1 405 1

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

  • Group Key: ped.codigo, inst.nome, cur.duracao_meses_titulo, ped.status
  • Filter: ((round(sum(dif.valor), 0) + '10'::numeric) <= round(sum(pag.valor), 0))
  • Buffers: shared hit=5196, temp read=523 written=522
19. 47.100 2,972.976 ↓ 1.1 24,605 1

Sort (cost=15,446.02..15,500.81 rows=21,916 width=210) (actual time=2,967.496..2,972.976 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=5196, temp read=523 written=522
20. 55.207 2,925.876 ↓ 1.1 24,605 1

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

  • Buffers: shared hit=5196, temp read=523 written=522
21. 16.119 2,870.246 ↓ 1.1 24,605 1

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

  • Buffers: shared hit=5173, temp read=523 written=522
22. 70.039 2,843.851 ↓ 1.1 24,605 1

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

  • Buffers: shared hit=5167, temp read=523 written=522
23. 14.361 2,647.955 ↓ 1.1 24,569 1

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

  • Buffers: shared hit=4749, temp read=282 written=283
24. 52.927 2,633.581 ↓ 1.1 24,569 1

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

  • Buffers: shared hit=4748, temp read=282 written=283
25. 1,583.689 1,583.689 ↑ 2.3 24,841 1

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

  • Buffers: shared hit=1878, temp read=282 written=283
26. 219.682 996.965 ↓ 1.1 24,569 1

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

  • Buffers: shared hit=2870
27. 82.485 777.283 ↓ 1.1 24,569 1

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

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

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

  • Buffers: shared hit=1245
29. 28.504 234.633 ↓ 1.1 24,569 1

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

  • Buffers: shared hit=1625
30. 65.423 206.129 ↓ 1.1 24,569 1

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

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

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

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

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

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

Seq Scan on pedido ped (cost=0..1,985.84 rows=21,359 width=24) (actual time=0.013..44.849 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.004 0.013 ↑ 265.0 2 1

Hash (cost=15.3..15.3 rows=530 width=122) (actual time=0.013..0.013 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.007..0.009 rows=2 loops=1)

  • Buffers: shared hit=1
36. 75.871 125.857 ↓ 1.0 65,542 1

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

  • Buffers: shared hit=418, temp written=143
37. 49.986 49.986 ↓ 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..49.986 rows=65,542 loops=1)

  • Buffers: shared hit=418
38. 10.097 10.276 ↑ 1.1 507 1

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

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

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

  • Buffers: shared hit=6
40. 0.191 0.423 ↑ 1.0 558 1

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

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

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

  • Buffers: shared hit=23
Planning time : 3.968 ms
Execution time : 3,078.541 ms