explain.depesz.com

PostgreSQL's explain analyze made readable

Result: nBOf

Settings
# exclusive inclusive rows x rows loops node
1. 2,937.302 2,937.302 ↓ 1.0 205,641 1

CTE Scan on contas_final (cost=89,194.10..100,504.25 rows=205,639 width=309) (actual time=1,669.399..2,937.302 rows=205,641 loops=1)

2.          

CTE contas_pagtos

3. 131.269 1,437.918 ↓ 1.0 205,641 1

Hash Left Join (cost=35,117.88..50,610.82 rows=205,639 width=262) (actual time=1,077.604..1,437.918 rows=205,641 loops=1)

  • Hash Cond: (cr.codigo = vcr_ultpagto_juros.contasareceber_faturamentos_cr_codigo)
4. 75.542 350.754 ↓ 1.0 205,641 1

Hash Left Join (cost=11,272.48..24,285.52 rows=205,639 width=62) (actual time=121.109..350.754 rows=205,641 loops=1)

  • Hash Cond: (cr.codigo = cr_fat.contasareceber_codigo)
5. 40.488 220.398 ↓ 1.0 205,641 1

Hash Left Join (cost=5,539.01..15,726.47 rows=205,639 width=58) (actual time=65.180..220.398 rows=205,641 loops=1)

  • Hash Cond: (cr_sit.situacoesconta_codigo = sc.codigo)
6. 100.329 179.904 ↓ 1.0 205,641 1

Hash Left Join (cost=5,537.88..12,897.80 rows=205,639 width=61) (actual time=65.164..179.904 rows=205,641 loops=1)

  • Hash Cond: (cr.codigo = cr_sit.contasareceber_codigo)
7. 15.595 15.595 ↓ 1.0 205,641 1

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

8. 42.547 63.980 ↓ 1.0 205,641 1

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

  • Buckets: 262144 Batches: 1 Memory Usage: 10081kB
9. 21.433 21.433 ↓ 1.0 205,641 1

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

10. 0.004 0.006 ↑ 1.0 6 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
11. 0.002 0.002 ↑ 1.0 6 1

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

12. 36.904 54.814 ↓ 1.0 205,448 1

Hash (cost=3,165.43..3,165.43 rows=205,443 width=4) (actual time=54.814..54.814 rows=205,448 loops=1)

  • Buckets: 262144 Batches: 1 Memory Usage: 9271kB
13. 17.910 17.910 ↓ 1.0 205,448 1

Seq Scan on contasareceber_faturamentos cr_fat (cost=0.00..3,165.43 rows=205,443 width=4) (actual time=0.015..17.910 rows=205,448 loops=1)

14. 59.765 955.895 ↓ 3.0 204,170 1

Hash (cost=22,994.70..22,994.70 rows=68,056 width=200) (actual time=955.895..955.895 rows=204,170 loops=1)

  • Buckets: 262144 (originally 131072) Batches: 1 (originally 1) Memory Usage: 14830kB
15. 24.405 896.130 ↓ 3.0 204,170 1

Subquery Scan on vcr_ultpagto_juros (cost=21,973.86..22,994.70 rows=68,056 width=200) (actual time=835.575..896.130 rows=204,170 loops=1)

16. 23.687 871.725 ↓ 3.0 204,170 1

Unique (cost=21,973.86..22,314.14 rows=68,056 width=35) (actual time=835.573..871.725 rows=204,170 loops=1)

17. 62.936 848.038 ↓ 3.0 204,172 1

Sort (cost=21,973.86..22,144.00 rows=68,056 width=35) (actual time=835.572..848.038 rows=204,172 loops=1)

  • Sort Key: crpg.contasareceber_faturamentos_cr_codigo, crpg.dhpagamento DESC
  • Sort Method: quicksort Memory: 22096kB
18. 533.560 785.102 ↓ 3.0 204,172 1

WindowAgg (cost=12,937.92..16,510.86 rows=68,056 width=35) (actual time=242.420..785.102 rows=204,172 loops=1)

19. 100.573 251.542 ↓ 3.0 204,172 1

Sort (cost=12,937.92..13,108.06 rows=68,056 width=35) (actual time=242.390..251.542 rows=204,172 loops=1)

  • Sort Key: crpg.contasareceber_faturamentos_cr_codigo
  • Sort Method: quicksort Memory: 22095kB
20. 35.561 150.969 ↓ 3.0 204,172 1

Hash Anti Join (cost=5.76..7,474.91 rows=68,056 width=35) (actual time=0.062..150.969 rows=204,172 loops=1)

  • Hash Cond: (crpg.codigo = crpg_e.contasareceber_pagtos_codigo)
21. 115.366 115.366 ↓ 3.0 204,339 1

Seq Scan on contasareceber_pagtos crpg (cost=0.00..6,609.74 rows=68,112 width=39) (actual time=0.014..115.366 rows=204,339 loops=1)

  • Filter: ((dhpagamento)::date <= ('now'::cstring)::date)
22. 0.023 0.042 ↑ 1.0 167 1

Hash (cost=3.67..3.67 rows=167 width=4) (actual time=0.042..0.042 rows=167 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 14kB
23. 0.019 0.019 ↑ 1.0 167 1

Seq Scan on contasareceber_pagtos_estornos crpg_e (cost=0.00..3.67 rows=167 width=4) (actual time=0.003..0.019 rows=167 loops=1)

24.          

CTE datas_uteis

25. 53.316 591.214 ↓ 7.4 7,428 1

Function Scan on obter_datas_uteis diautil (cost=4,627.14..4,637.14 rows=1,000 width=4) (actual time=590.882..591.214 rows=7,428 loops=1)

26.          

Initplan (forFunction Scan)

27. 18.785 537.898 ↑ 1.0 1 1

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

28. 519.113 519.113 ↓ 1.0 205,641 1

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

29.          

CTE contas_saldos_jurosatrasado

30. 54.901 2,415.809 ↓ 1.0 205,641 1

Nested Loop (cost=0.26..23,150.09 rows=205,639 width=427) (actual time=1,669.380..2,415.809 rows=205,641 loops=1)

31. 0.054 0.054 ↑ 1.0 1 1

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

32. 1,099.869 1,099.869 ↓ 1.0 205,641 1

CTE Scan on contas_pagtos contas_pagtos_1 (cost=0.00..4,112.78 rows=205,639 width=423) (actual time=1,077.609..1,099.869 rows=205,641 loops=1)

33.          

SubPlan (forNested Loop)

34. 0.000 1,260.985 ↑ 1.0 1 1,111

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

35. 1,260.985 1,260.985 ↑ 333.0 1 1,111

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

  • Filter: (diautil >= (contas_pagtos_1.datavencimento + carenciajuros.carenciajuros))
  • Rows Removed by Filter: 6292
36.          

CTE contas_final

37. 2,681.891 2,681.891 ↓ 1.0 205,641 1

CTE Scan on contas_saldos_jurosatrasado (cost=0.00..10,796.05 rows=205,639 width=428) (actual time=1,669.390..2,681.891 rows=205,641 loops=1)