explain.depesz.com

PostgreSQL's explain analyze made readable

Result: hVv : Inadimplência - Parcelas Pagos x Parcelas Não Pagos - Total

Settings

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 14,108.533 14,108.533 ↑ 1.0 1 1

CTE Scan on inadimplente p (cost=83,760.54..83,760.61 rows=1 width=192) (actual time=14,108.531..14,108.533 rows=1 loops=1)

  • Buffers: shared hit=1352159 read=33 dirtied=7, temp read=3775 written=3769
2.          

CTE inadimplente

3. 0.522 14,108.500 ↑ 1.0 1 1

Aggregate (cost=83,760.53..83,760.54 rows=1 width=160) (actual time=14,108.5..14,108.5 rows=1 loops=1)

  • Buffers: shared hit=1352159 read=33 dirtied=7, temp read=3775 written=3769
4. 36.296 14,107.978 ↑ 4.7 1,169 1

GroupAggregate (cost=81,723.76..83,636.65 rows=5,505 width=196) (actual time=14,068.09..14,107.978 rows=1,169 loops=1)

  • Group Key: inad.dias_vencidos, inad.data_pedido
  • Buffers: shared hit=1352159 read=33 dirtied=7, temp read=3775 written=3769
5. 25.487 14,071.682 ↑ 2.9 18,905 1

Sort (cost=81,723.76..81,861.38 rows=55,047 width=100) (actual time=14,068.031..14,071.682 rows=18,905 loops=1)

  • Sort Key: inad.dias_vencidos, inad.data_pedido
  • Sort Method: quicksort Memory: 2245kB
  • Buffers: shared hit=1352159 read=33 dirtied=7, temp read=3775 written=3769
6. 29.931 14,046.195 ↑ 2.9 18,905 1

Subquery Scan on inad (cost=73,687.67..74,375.76 rows=55,047 width=100) (actual time=13,992.612..14,046.195 rows=18,905 loops=1)

  • Buffers: shared hit=1352159 read=33 dirtied=7, temp read=3775 written=3769
7. 62.015 14,016.264 ↑ 2.9 18,905 1

Sort (cost=73,687.67..73,825.29 rows=55,047 width=112) (actual time=13,992.609..14,016.264 rows=18,905 loops=1)

  • Sort Key: ((to_date(to_char(now(), 'YYYYMMDD'::text), 'YYYYMMDD'::text) - to_date(to_char(min(parc.data_vencimento), 'YYYYMMDD'::text), 'YYYYMMDD'::text)))
  • Sort Method: quicksort Memory: 2245kB
  • Buffers: shared hit=1352159 read=33 dirtied=7, temp read=3775 written=3769
8. 628.362 13,954.249 ↑ 2.9 18,905 1

GroupAggregate (cost=56,698.69..66,154.17 rows=55,047 width=112) (actual time=12,975.061..13,954.249 rows=18,905 loops=1)

  • Group Key: (to_char(ped.data_pedido, 'YYYY-MM'::text)), ped.codigo
  • Filter: ((to_date(to_char(now(), 'YYYYMMDD'::text), 'YYYYMMDD'::text) - to_date(to_char(min(parc.data_vencimento), 'YYYYMMDD'::text), 'YYYYMMDD'::text)) >= 30)
  • Buffers: shared hit=1352159 read=33 dirtied=7, temp read=3775 written=3769
9. 1,956.058 13,325.887 ↑ 1.2 251,050 1

Sort (cost=56,698.69..57,465.33 rows=306,658 width=70) (actual time=12,975.013..13,325.887 rows=251,050 loops=1)

  • Sort Key: (to_char(ped.data_pedido, 'YYYY-MM'::text)), ped.codigo
  • Sort Method: external merge Disk: 13976kB
  • Buffers: shared hit=1352159 read=33 dirtied=7, temp read=3775 written=3769
10. 3,144.752 11,369.829 ↑ 1.2 251,050 1

Nested Loop (cost=7,055.77..16,173.53 rows=306,658 width=70) (actual time=2,165.348..11,369.829 rows=251,050 loops=1)

  • Buffers: shared hit=1352159 read=33 dirtied=7, temp read=2028 written=2016
11. 1,007.872 5,101.215 ↓ 572.1 446,266 1

Hash Join (cost=7,055.48..15,090.29 rows=780 width=62) (actual time=2,165.185..5,101.215 rows=446,266 loops=1)

  • Buffers: shared hit=9407, temp read=2028 written=2016
12. 930.271 4,080.709 ↓ 572.1 446,266 1

Hash Join (cost=7,018.53..15,049.25 rows=780 width=62) (actual time=2,152.538..4,080.709 rows=446,266 loops=1)

  • Buffers: shared hit=9384, temp read=2028 written=2016
13. 149.725 3,150.067 ↓ 572.1 446,266 1

Gather (cost=6,997.49..15,022.06 rows=780 width=62) (actual time=2,152.145..3,150.067 rows=446,266 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
  • Buffers: shared hit=9378, temp read=2028 written=2016
14. 421.007 3,000.342 ↓ 457.7 148,755 3 / 3

Hash Join (cost=5,997.49..13,944.06 rows=325 width=62) (actual time=2,349.819..3,000.342 rows=148,755 loops=3)

  • Buffers: shared hit=9378, temp read=2028 written=2016
15. 229.595 229.595 ↑ 1.2 151,148 3 / 3

Seq Scan on parcela parc (cost=0..7,240.18 rows=187,506 width=38) (actual time=0.005..229.595 rows=151,148 loops=3)

  • Filter: ((parc.status)::text <> 'renegociado'::text)
  • Buffers: shared hit=4731
16. 82.200 2,349.740 ↓ 499.2 34,941 3 / 3

Hash (cost=5,996.61..5,996.61 rows=70 width=32) (actual time=2,349.74..2,349.74 rows=34,941 loops=3)

  • Buffers: shared hit=4551, temp read=2028 written=2016
17. 245.427 2,267.540 ↓ 499.2 34,941 3 / 3

Hash Join (cost=5,077.33..5,996.61 rows=70 width=32) (actual time=1,943.787..2,267.54 rows=34,941 loops=3)

  • Buffers: shared hit=4551, temp read=2028 written=2016
18. 88.581 88.581 ↑ 1.0 40,169 3 / 3

Seq Scan on forma_pagamento forpag (cost=0..767.06 rows=40,406 width=8) (actual time=0.011..88.581 rows=40,169 loops=3)

  • Buffers: shared hit=1089
19. 268.391 1,933.532 ↓ 504.2 65,542 3 / 3

Hash (cost=5,075.71..5,075.71 rows=130 width=32) (actual time=1,933.532..1,933.532 rows=65,542 loops=3)

  • Buffers: shared hit=3462, temp read=918 written=1536
20. 647.331 1,665.141 ↓ 504.2 65,542 3 / 3

Hash Join (cost=2,076.5..5,075.71 rows=130 width=32) (actual time=336.72..1,665.141 rows=65,542 loops=3)

  • Buffers: shared hit=3462, temp read=918 written=912
21. 473.001 681.344 ↑ 1.0 72,836 3 / 3

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

  • Buffers: shared hit=2208
22. 208.332 208.332 ↑ 1.0 72,836 3 / 3

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

  • Filter: (pag.tipo_pagamento = 1)
  • Buffers: shared hit=2205
23. 0.004 0.011 ↑ 265.0 2 3 / 3

Hash (cost=15.3..15.3 rows=530 width=4) (actual time=0.011..0.011 rows=2 loops=3)

  • Buffers: shared hit=3
24. 0.007 0.007 ↑ 265.0 2 3 / 3

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

  • Buffers: shared hit=3
25. 245.982 336.466 ↓ 1.0 65,542 3 / 3

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

  • Buffers: shared hit=1254, temp written=429
26. 90.484 90.484 ↓ 1.0 65,542 3 / 3

Seq Scan on pedido_oferta pof (cost=0..1,072.63 rows=65,463 width=16) (actual time=0.01..90.484 rows=65,542 loops=3)

  • Buffers: shared hit=1254
27. 0.199 0.371 ↑ 1.1 507 1

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

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

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

  • Buffers: shared hit=6
29. 12.391 12.634 ↑ 1.0 558 1

Hash (cost=28.58..28.58 rows=558 width=8) (actual time=12.634..12.634 rows=558 loops=1)

  • Buffers: shared hit=23
30. 0.243 0.243 ↑ 1.0 558 1

Seq Scan on curso curs (cost=0..28.58 rows=558 width=8) (actual time=0.009..0.243 rows=558 loops=1)

  • Buffers: shared hit=23
31. 3,123.862 3,123.862 ↑ 1.0 1 446,266

Index Scan using pk_pedido on pedido ped (cost=0.29..0.39 rows=1 width=16) (actual time=0.007..0.007 rows=1 loops=446,266)

  • Index Cond: ((ped.codigo = pag.codigo_pedido) AND (ped.instituicao = pag.instituicao))
  • Filter: ((ped.status)::text = 'wc-completed'::text)
  • Buffers: shared hit=1342752 read=33 dirtied=7
Planning time : 16.325 ms
Execution time : 14,110.918 ms