explain.depesz.com

PostgreSQL's explain analyze made readable

Result: PRKq

Settings
# exclusive inclusive rows x rows loops node
1. 46.499 2,332.016 ↑ 1.0 205,639 1

Nested Loop (cost=55,248.23..78,398.08 rows=205,639 width=427) (actual time=1,604.440..2,332.016 rows=205,639 loops=1)

2.          

CTE contas

3. 128.587 1,372.304 ↑ 1.0 205,639 1

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

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

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

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

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

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

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

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

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

8. 37.007 56.633 ↑ 1.0 205,639 1

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

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

10. 0.010 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
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. 34.515 53.658 ↓ 1.0 205,446 1

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

  • Buckets: 262144 Batches: 1 Memory Usage: 9271kB
13. 19.143 19.143 ↓ 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.007..19.143 rows=205,446 loops=1)

14. 59.525 906.810 ↓ 3.0 204,170 1

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

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

16. 23.747 822.949 ↓ 3.0 204,170 1

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

17. 63.557 799.202 ↓ 3.0 204,172 1

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

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

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

19. 95.559 189.723 ↓ 3.0 204,172 1

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

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

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

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

  • Filter: ((dhpagamento)::date <= (now())::date)
22. 0.027 0.044 ↑ 1.0 167 1

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

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

24.          

CTE datas_uteis

25. 55.508 583.890 ↓ 7.4 7,424 1

Function Scan on obter_datas_uteis diautil (cost=4,627.14..4,637.14 rows=1,000 width=4) (actual time=583.604..583.890 rows=7,424 loops=1)

26.          

Initplan (forFunction Scan)

27. 18.151 528.382 ↑ 1.0 1 1

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

28. 510.231 510.231 ↑ 1.0 205,639 1

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

29.          

CTE config_carencia_juros

30. 0.120 0.120 ↑ 1.0 1 1

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

31. 0.122 0.122 ↑ 1.0 1 1

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

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

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

33.          

SubPlan (forNested Loop)

34. 1.109 1,248.734 ↑ 1.0 1 1,109

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

35. 1,247.625 1,247.625 ↑ 333.0 1 1,109

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

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