explain.depesz.com

PostgreSQL's explain analyze made readable

Result: rXW

Settings

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 3,032.510 3,032.510 ↓ 1.0 205,443 1

CTE Scan on contas_final (cost=118,656.69..128,396.27 rows=204,611 width=305) (actual time=617.023..3,032.510 rows=205,443 loops=1)

  • Filter: (contasareceber_faturamentos_codigo IS NOT NULL)
  • Rows Removed by Filter: 196
2.          

CTE contas

3. 36.300 1,416.188 ↑ 1.0 205,639 1

Hash Left Join (cost=50,659.83..80,073.39 rows=205,639 width=294) (actual time=550.587..1,416.188 rows=205,639 loops=1)

  • Hash Cond: (cr.codigo = vcr_ultpagto_juros.contasareceber_faturamentos_cr_codigo)
4. 85.813 1,346.898 ↑ 1.0 205,639 1

Hash Left Join (cost=43,532.31..72,174.71 rows=205,639 width=258) (actual time=517.580..1,346.898 rows=205,639 loops=1)

  • Hash Cond: (cr.codigo = cr_fat.contasareceber_codigo)
5. 50.751 1,184.418 ↑ 1.0 205,639 1

Hash Left Join (cost=37,799.81..63,617.07 rows=205,639 width=254) (actual time=439.778..1,184.418 rows=205,639 loops=1)

  • Hash Cond: (cr_sit.situacoesconta_codigo = sc.codigo)
6. 83.031 1,133.594 ↑ 1.0 205,639 1

Hash Left Join (cost=37,798.68..60,788.39 rows=205,639 width=257) (actual time=439.695..1,133.594 rows=205,639 loops=1)

  • Hash Cond: (cr.codigo = cr_sit.contasareceber_codigo)
7. 99.912 960.918 ↑ 1.0 205,639 1

Hash Right Join (cost=32,260.80..52,422.98 rows=205,639 width=253) (actual time=348.832..960.918 rows=205,639 loops=1)

  • Hash Cond: (crpg.contasareceber_faturamentos_cr_codigo = cr.codigo)
8. 499.061 699.671 ↑ 1.0 204,168 1

GroupAggregate (cost=25,157.92..40,470.97 rows=204,174 width=24) (actual time=186.350..699.671 rows=204,168 loops=1)

  • Group Key: crpg.contasareceber_faturamentos_cr_codigo
9. 101.677 200.610 ↑ 1.0 204,170 1

Sort (cost=25,157.92..25,668.36 rows=204,174 width=24) (actual time=186.308..200.610 rows=204,170 loops=1)

  • Sort Key: crpg.contasareceber_faturamentos_cr_codigo
  • Sort Method: quicksort Memory: 22095kB
10. 40.989 98.933 ↑ 1.0 204,170 1

Hash Anti Join (cost=5.67..7,150.35 rows=204,174 width=24) (actual time=0.111..98.933 rows=204,170 loops=1)

  • Hash Cond: (crpg.codigo = crpg_e.contasareceber_pagtos_codigo)
11. 57.877 57.877 ↑ 1.0 204,337 1

Seq Scan on contasareceber_pagtos crpg (cost=0.00..4,566.37 rows=204,337 width=28) (actual time=0.032..57.877 rows=204,337 loops=1)

12. 0.028 0.067 ↓ 1.0 167 1

Hash (cost=3.63..3.63 rows=163 width=4) (actual time=0.067..0.067 rows=167 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 14kB
13. 0.039 0.039 ↓ 1.0 167 1

Seq Scan on contasareceber_pagtos_estornos crpg_e (cost=0.00..3.63 rows=163 width=4) (actual time=0.025..0.039 rows=167 loops=1)

14. 72.699 161.335 ↑ 1.0 205,639 1

Hash (cost=4,532.39..4,532.39 rows=205,639 width=57) (actual time=161.335..161.335 rows=205,639 loops=1)

  • Buckets: 262144 Batches: 1 Memory Usage: 22040kB
15. 88.636 88.636 ↑ 1.0 205,639 1

Seq Scan on contasareceber cr (cost=0.00..4,532.39 rows=205,639 width=57) (actual time=0.084..88.636 rows=205,639 loops=1)

16. 48.060 89.645 ↑ 1.0 205,639 1

Hash (cost=2,967.39..2,967.39 rows=205,639 width=8) (actual time=89.645..89.645 rows=205,639 loops=1)

  • Buckets: 262144 Batches: 1 Memory Usage: 10081kB
17. 41.585 41.585 ↑ 1.0 205,639 1

Seq Scan on contasareceber_situacao cr_sit (cost=0.00..2,967.39 rows=205,639 width=8) (actual time=0.154..41.585 rows=205,639 loops=1)

18. 0.011 0.073 ↑ 1.0 6 1

Hash (cost=1.06..1.06 rows=6 width=5) (actual time=0.073..0.073 rows=6 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
19. 0.062 0.062 ↑ 1.0 6 1

Seq Scan on situacoescontas sc (cost=0.00..1.06 rows=6 width=5) (actual time=0.061..0.062 rows=6 loops=1)

20. 39.036 76.667 ↓ 1.0 205,443 1

Hash (cost=3,165.00..3,165.00 rows=205,400 width=4) (actual time=76.667..76.667 rows=205,443 loops=1)

  • Buckets: 262144 Batches: 1 Memory Usage: 9271kB
21. 37.631 37.631 ↓ 1.0 205,443 1

Seq Scan on contasareceber_faturamentos cr_fat (cost=0.00..3,165.00 rows=205,400 width=4) (actual time=0.036..37.631 rows=205,443 loops=1)

22. 0.019 32.990 ↑ 1.0 1 1

Hash (cost=7,127.51..7,127.51 rows=1 width=40) (actual time=32.990..32.990 rows=1 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
23. 0.001 32.971 ↑ 1.0 1 1

Subquery Scan on vcr_ultpagto_juros (cost=7,127.49..7,127.51 rows=1 width=40) (actual time=32.970..32.971 rows=1 loops=1)

24. 0.001 32.970 ↑ 1.0 1 1

Unique (cost=7,127.49..7,127.50 rows=1 width=21) (actual time=32.969..32.970 rows=1 loops=1)

25. 0.042 32.969 ↓ 3.0 3 1

Sort (cost=7,127.49..7,127.49 rows=1 width=21) (actual time=32.969..32.969 rows=3 loops=1)

  • Sort Key: crpg_1.contasareceber_faturamentos_cr_codigo, crpg_1.dhpagamento DESC
  • Sort Method: quicksort Memory: 25kB
26. 0.039 32.927 ↓ 3.0 3 1

Nested Loop Anti Join (cost=0.00..7,127.48 rows=1 width=21) (actual time=32.896..32.927 rows=3 loops=1)

  • Join Filter: (crpg_e_1.contasareceber_pagtos_codigo = crpg_1.codigo)
  • Rows Removed by Join Filter: 501
27. 32.837 32.837 ↓ 3.0 3 1

Seq Scan on contasareceber_pagtos crpg_1 (cost=0.00..7,120.58 rows=1 width=25) (actual time=32.836..32.837 rows=3 loops=1)

  • Filter: ((valorjurosdevido > '0'::numeric) AND ((dhpagamento)::date <= (now())::date))
  • Rows Removed by Filter: 204334
28. 0.031 0.051 ↓ 1.0 167 3

Materialize (cost=0.00..4.45 rows=163 width=4) (actual time=0.003..0.017 rows=167 loops=3)

29. 0.020 0.020 ↓ 1.0 167 1

Seq Scan on contasareceber_pagtos_estornos crpg_e_1 (cost=0.00..3.63 rows=163 width=4) (actual time=0.005..0.020 rows=167 loops=1)

30.          

CTE datas_uteis

31. 78.328 122.081 ↓ 7.4 7,423 1

Function Scan on obter_datas_uteis diautil (cost=4,627.14..4,637.14 rows=1,000 width=4) (actual time=121.817..122.081 rows=7,423 loops=1)

32.          

Initplan (forFunction Scan)

33. 13.639 43.753 ↑ 1.0 1 1

Aggregate (cost=4,626.88..4,626.89 rows=1 width=4) (actual time=43.753..43.753 rows=1 loops=1)

34. 30.114 30.114 ↑ 1.0 205,639 1

CTE Scan on contas (cost=0.00..4,112.78 rows=205,639 width=4) (actual time=0.000..30.114 rows=205,639 loops=1)

35.          

CTE config_carencia_juros

36. 66.417 66.417 ↑ 1.0 1 1

Function Scan on carenciajuros (cost=0.26..0.27 rows=1 width=4) (actual time=66.417..66.417 rows=1 loops=1)

37.          

CTE contas_saldos_ultimopagtoatrasado

38. 59.018 2,495.239 ↑ 1.0 205,639 1

Nested Loop (cost=0.00..23,149.85 rows=205,639 width=427) (actual time=617.011..2,495.239 rows=205,639 loops=1)

39. 66.419 66.419 ↑ 1.0 1 1

CTE Scan on config_carencia_juros (cost=0.00..0.02 rows=1 width=4) (actual time=66.418..66.419 rows=1 loops=1)

40. 1,565.740 1,565.740 ↑ 1.0 205,639 1

CTE Scan on contas contas_1 (cost=0.00..4,112.78 rows=205,639 width=423) (actual time=550.590..1,565.740 rows=205,639 loops=1)

41.          

SubPlan (forNested Loop)

42. 1.106 804.062 ↑ 1.0 1 1,106

Limit (cost=0.00..0.08 rows=1 width=4) (actual time=0.727..0.727 rows=1 loops=1,106)

43. 802.956 802.956 ↑ 333.0 1 1,106

CTE Scan on datas_uteis (cost=0.00..27.50 rows=333 width=4) (actual time=0.726..0.726 rows=1 loops=1,106)

  • Filter: (diautil >= (contas_1.datavencimento + config_carencia_juros.carenciajuros))
  • Rows Removed by Filter: 6287
44.          

CTE contas_final

45. 2,770.819 2,770.819 ↑ 1.0 205,639 1

CTE Scan on contas_saldos_ultimopagtoatrasado (cost=0.00..10,796.05 rows=205,639 width=428) (actual time=617.018..2,770.819 rows=205,639 loops=1)