explain.depesz.com

PostgreSQL's explain analyze made readable

Result: V0RK : Optimization for: plan #4QDZ

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 2,970.340 2,970.340 ↓ 1.0 205,446 1

CTE Scan on contas_final (cost=89,194.10..98,933.69 rows=204,611 width=309) (actual time=1,696.881..2,970.340 rows=205,446 loops=1)

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

CTE contas_pagtos

3. 137.547 1,460.290 ↑ 1.0 205,639 1

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

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

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

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

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

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

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

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

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

8. 39.067 58.052 ↑ 1.0 205,639 1

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

  • Buckets: 262144 Batches: 1 Memory Usage: 10081kB
9. 18.985 18.985 ↑ 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.003..18.985 rows=205,639 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.001..0.002 rows=6 loops=1)

12. 38.858 57.384 ↓ 1.0 205,446 1

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

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

Seq Scan on contasareceber_faturamentos cr_fat (cost=0.00..3,165.43 rows=205,443 width=4) (actual time=0.003..18.526 rows=205,446 loops=1)

14. 62.881 970.805 ↓ 3.0 204,170 1

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

  • Buckets: 262144 (originally 131072) Batches: 1 (originally 1) Memory Usage: 14830kB
15. 25.282 907.924 ↓ 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=846.114..907.924 rows=204,170 loops=1)

16. 24.190 882.642 ↓ 3.0 204,170 1

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

17. 63.241 858.452 ↓ 3.0 204,172 1

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

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

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

19. 102.632 260.424 ↓ 3.0 204,172 1

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

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

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

  • Hash Cond: (crpg.codigo = crpg_e.contasareceber_pagtos_codigo)
21. 121.108 121.108 ↓ 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.013..121.108 rows=204,339 loops=1)

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

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

  • Buckets: 1024 Batches: 1 Memory Usage: 14kB
23. 0.011 0.011 ↑ 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.011 rows=167 loops=1)

24.          

CTE datas_uteis

25. 53.890 607.329 ↓ 7.4 7,425 1

Function Scan on obter_datas_uteis diautil (cost=4,627.14..4,637.14 rows=1,000 width=4) (actual time=607.023..607.329 rows=7,425 loops=1)

26.          

Initplan (forFunction Scan)

27. 18.858 553.439 ↑ 1.0 1 1

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

28. 534.581 534.581 ↑ 1.0 205,639 1

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

29.          

CTE contas_saldos_jurosatrasado

30. 55.017 2,449.569 ↑ 1.0 205,639 1

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

31. 0.116 0.116 ↑ 1.0 1 1

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

32. 1,111.323 1,111.323 ↑ 1.0 205,639 1

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

33.          

SubPlan (forNested Loop)

34. 0.000 1,283.113 ↑ 1.0 1 1,109

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

35. 1,283.113 1,283.113 ↑ 333.0 1 1,109

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

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

CTE contas_final

37. 2,715.179 2,715.179 ↑ 1.0 205,639 1

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