explain.depesz.com

PostgreSQL's explain analyze made readable

Result: FqjO : Optimization for: plan #rXW

Settings

Optimization path:

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

CTE Scan on contas_final (cost=71,794.35..81,533.94 rows=204,611 width=305) (actual time=622.557..1,794.372 rows=205,443 loops=1)

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

CTE contas

3. 39.752 416.924 ↑ 1.0 205,639 1

Hash Left Join (cost=18,399.09..33,211.05 rows=205,639 width=262) (actual time=147.378..416.924 rows=205,639 loops=1)

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

Hash Left Join (cost=11,271.51..24,284.12 rows=205,639 width=62) (actual time=114.302..344.106 rows=205,639 loops=1)

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

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

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

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

  • Hash Cond: (cr.codigo = cr_sit.contasareceber_codigo)
7. 20.260 20.260 ↑ 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..20.260 rows=205,639 loops=1)

8. 34.194 55.000 ↑ 1.0 205,639 1

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

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

10. 0.014 0.017 ↑ 1.0 6 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
11. 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)

12. 36.384 56.614 ↓ 1.0 205,443 1

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

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

14. 0.021 33.066 ↑ 1.0 1 1

Hash (cost=7,127.56..7,127.56 rows=1 width=200) (actual time=33.066..33.066 rows=1 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
15. 0.002 33.045 ↑ 1.0 1 1

Subquery Scan on vcr_ultpagto_juros (cost=7,127.55..7,127.56 rows=1 width=200) (actual time=33.044..33.045 rows=1 loops=1)

16. 0.001 33.043 ↑ 1.0 1 1

Unique (cost=7,127.55..7,127.55 rows=1 width=35) (actual time=33.043..33.043 rows=1 loops=1)

17. 0.010 33.042 ↓ 3.0 3 1

Sort (cost=7,127.55..7,127.55 rows=1 width=35) (actual time=33.042..33.042 rows=3 loops=1)

  • Sort Key: crpg.contasareceber_faturamentos_cr_codigo, crpg.dhpagamento DESC
  • Sort Method: quicksort Memory: 25kB
18. 0.029 33.032 ↓ 3.0 3 1

WindowAgg (cost=7,127.48..7,127.54 rows=1 width=35) (actual time=33.028..33.032 rows=3 loops=1)

19. 0.007 33.003 ↓ 3.0 3 1

Sort (cost=7,127.48..7,127.49 rows=1 width=35) (actual time=33.003..33.003 rows=3 loops=1)

  • Sort Key: crpg.contasareceber_faturamentos_cr_codigo
  • Sort Method: quicksort Memory: 25kB
20. 0.033 32.996 ↓ 3.0 3 1

Nested Loop Anti Join (cost=0.00..7,127.47 rows=1 width=35) (actual time=32.964..32.996 rows=3 loops=1)

  • Join Filter: (crpg_e.contasareceber_pagtos_codigo = crpg.codigo)
  • Rows Removed by Join Filter: 501
21. 32.906 32.906 ↓ 3.0 3 1

Seq Scan on contasareceber_pagtos crpg (cost=0.00..7,120.58 rows=1 width=39) (actual time=32.905..32.906 rows=3 loops=1)

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

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

23. 0.018 0.018 ↓ 1.0 167 1

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

24.          

CTE datas_uteis

25. 56.777 474.564 ↓ 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=474.260..474.564 rows=7,423 loops=1)

26.          

Initplan (forFunction Scan)

27. 19.829 417.787 ↑ 1.0 1 1

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

28. 397.958 397.958 ↑ 1.0 205,639 1

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

29.          

CTE config_carencia_juros

30. 0.101 0.101 ↑ 1.0 1 1

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

31.          

CTE contas_saldos_ultimopagtoatrasado

32. 50.594 1,375.855 ↑ 1.0 205,639 1

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

33. 0.101 0.101 ↑ 1.0 1 1

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

34. 176.026 176.026 ↑ 1.0 205,639 1

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

35.          

SubPlan (forNested Loop)

36. 0.000 1,149.134 ↑ 1.0 1 1,106

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

37. 1,149.134 1,149.134 ↑ 333.0 1 1,106

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

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

CTE contas_final

39. 1,582.655 1,582.655 ↑ 1.0 205,639 1

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