explain.depesz.com

PostgreSQL's explain analyze made readable

Result: CKMI : contasareceber_saldo atual

Settings

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 2,992.490 2,992.490 ↓ 1.0 205,443 1

CTE Scan on contas_final (cost=180,224.50..189,964.08 rows=204,611 width=305) (actual time=615.185..2,992.490 rows=205,443 loops=1)

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

CTE contas

3. 98.115 1,503.166 ↑ 1.0 205,639 1

Hash Left Join (cost=56,810.62..88,689.44 rows=205,639 width=297) (actual time=615.165..1,503.166 rows=205,639 loops=1)

  • Hash Cond: (cr.codigo = cr_fat.contasareceber_codigo)
4. 52.965 1,345.434 ↑ 1.0 205,639 1

Hash Left Join (cost=51,078.12..79,617.70 rows=205,639 width=293) (actual time=554.461..1,345.434 rows=205,639 loops=1)

  • Hash Cond: (cr_sit.situacoesconta_codigo = sc.codigo)
5. 85.876 1,292.457 ↑ 1.0 205,639 1

Hash Left Join (cost=51,076.99..76,789.02 rows=205,639 width=296) (actual time=554.437..1,292.457 rows=205,639 loops=1)

  • Hash Cond: (cr.codigo = cr_sit.contasareceber_codigo)
6. 104.335 1,150.982 ↑ 1.0 205,639 1

Hash Right Join (cost=45,539.11..68,423.61 rows=205,639 width=292) (actual time=497.722..1,150.982 rows=205,639 loops=1)

  • Hash Cond: (crpg.contasareceber_faturamentos_cr_codigo = cr.codigo)
7. 30.357 988.141 ↑ 1.0 204,168 1

Merge Left Join (cost=38,436.23..58,513.34 rows=204,174 width=235) (actual time=438.118..988.141 rows=204,168 loops=1)

  • Merge Cond: (crpg.contasareceber_faturamentos_cr_codigo = vcr_ultpagto_juros.contasareceber_faturamentos_cr_codigo)
8. 505.637 651.322 ↑ 1.0 204,168 1

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

  • Group Key: crpg.contasareceber_faturamentos_cr_codigo
9. 94.865 145.685 ↑ 1.0 204,170 1

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

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

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

  • Hash Cond: (crpg.codigo = crpg_e.contasareceber_pagtos_codigo)
11. 13.110 13.110 ↑ 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.003..13.110 rows=204,337 loops=1)

12. 0.022 0.036 ↓ 1.0 167 1

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

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

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

14. 0.018 306.462 ↑ 22,686.0 1 1

Materialize (cost=13,278.31..14,526.04 rows=22,686 width=43) (actual time=306.458..306.462 rows=1 loops=1)

15. 24.523 306.444 ↑ 22,686.0 1 1

Subquery Scan on vcr_ultpagto_juros (cost=13,278.31..14,469.33 rows=22,686 width=43) (actual time=306.441..306.444 rows=1 loops=1)

  • Filter: (vcr_ultpagto_juros.valorjurosdevido > '0'::numeric)
  • Rows Removed by Filter: 204167
16. 20.938 281.921 ↓ 3.0 204,168 1

Unique (cost=13,278.31..13,618.60 rows=68,058 width=21) (actual time=254.023..281.921 rows=204,168 loops=1)

17. 118.166 260.983 ↓ 3.0 204,170 1

Sort (cost=13,278.31..13,448.46 rows=68,058 width=21) (actual time=254.020..260.983 rows=204,170 loops=1)

  • Sort Key: crpg_1.contasareceber_faturamentos_cr_codigo, crpg_1.dhpagamento DESC
  • Sort Method: quicksort Memory: 22095kB
18. 78.928 142.817 ↓ 3.0 204,170 1

Hash Anti Join (cost=5.67..7,815.13 rows=68,058 width=21) (actual time=0.077..142.817 rows=204,170 loops=1)

  • Hash Cond: (crpg_1.codigo = crpg_e_1.contasareceber_pagtos_codigo)
19. 63.844 63.844 ↓ 3.0 204,337 1

Seq Scan on contasareceber_pagtos crpg_1 (cost=0.00..6,609.74 rows=68,112 width=25) (actual time=0.022..63.844 rows=204,337 loops=1)

  • Filter: ((dhpagamento)::date <= (now())::date)
20. 0.026 0.045 ↓ 1.0 167 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 14kB
21. 0.019 0.019 ↓ 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.004..0.019 rows=167 loops=1)

22. 43.217 58.506 ↑ 1.0 205,639 1

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

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

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

24. 34.569 55.599 ↑ 1.0 205,639 1

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

  • Buckets: 262144 Batches: 1 Memory Usage: 10081kB
25. 21.030 21.030 ↑ 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.010..21.030 rows=205,639 loops=1)

26. 0.009 0.012 ↑ 1.0 6 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
27. 0.003 0.003 ↑ 1.0 6 1

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

28. 36.327 59.617 ↓ 1.0 205,443 1

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

  • Buckets: 262144 Batches: 1 Memory Usage: 9271kB
29. 23.290 23.290 ↓ 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.005..23.290 rows=205,443 loops=1)

30.          

CTE datas_uteis

31. 55.617 98.829 ↓ 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=98.556..98.829 rows=7,423 loops=1)

32.          

Initplan (forFunction Scan)

33. 13.543 43.212 ↑ 1.0 1 1

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

34. 29.669 29.669 ↑ 1.0 205,639 1

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

35.          

CTE contas_saldos_ultimopagtoatrasado

36. 1,701.332 2,461.154 ↑ 1.0 205,639 1

CTE Scan on contas contas_1 (cost=0.00..76,101.87 rows=205,639 width=423) (actual time=615.170..2,461.154 rows=205,639 loops=1)

37.          

SubPlan (forCTE Scan)

38. 0.000 759.822 ↑ 1.0 1 1,106

Limit (cost=0.27..0.35 rows=1 width=4) (actual time=0.687..0.687 rows=1 loops=1,106)

39.          

Initplan (forLimit)

40. 0.132 0.132 ↑ 1.0 1 1

Result (cost=0.00..0.27 rows=1 width=0) (actual time=0.131..0.132 rows=1 loops=1)

41. 759.822 759.822 ↑ 333.0 1 1,106

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

  • Filter: (diautil >= (contas_1.datavencimento + $3))
  • Rows Removed by Filter: 6287
42.          

CTE contas_final

43. 2,730.081 2,730.081 ↑ 1.0 205,639 1

CTE Scan on contas_saldos_ultimopagtoatrasado (cost=0.00..10,796.05 rows=205,639 width=424) (actual time=615.179..2,730.081 rows=205,639 loops=1)