explain.depesz.com

PostgreSQL's explain analyze made readable

Result: yUkE : Optimization for: contasareceber_saldo atual; plan #CKMI

Settings

Optimization path:

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

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

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

CTE contas

3. 38.493 417.645 ↑ 1.0 205,639 1

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

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

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

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

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

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

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

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

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

8. 39.990 61.826 ↑ 1.0 205,639 1

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

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

10. 0.004 0.007 ↑ 1.0 6 1

Hash (cost=1.06..1.06 rows=6 width=5) (actual time=0.007..0.007 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. 35.829 55.485 ↓ 1.0 205,443 1

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

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

14. 0.003 31.612 ↑ 1.0 1 1

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

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

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

16. 0.001 31.607 ↑ 1.0 1 1

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

17. 0.011 31.606 ↓ 3.0 3 1

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

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

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

19. 0.006 31.552 ↓ 3.0 3 1

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

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

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

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

Seq Scan on contasareceber_pagtos crpg (cost=0.00..7,120.58 rows=1 width=39) (actual time=31.460..31.462 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. 57.986 468.813 ↓ 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=468.511..468.813 rows=7,423 loops=1)

26.          

Initplan (forFunction Scan)

27. 22.127 410.827 ↑ 1.0 1 1

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

28. 388.700 388.700 ↑ 1.0 205,639 1

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

29.          

CTE config_carencia_juros

30. 0.098 0.098 ↑ 1.0 1 1

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

31.          

CTE contas_saldos_ultimopagtoatrasado

32. 44.286 1,361.536 ↑ 1.0 205,639 1

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

33. 0.099 0.099 ↑ 1.0 1 1

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

34. 177.971 177.971 ↑ 1.0 205,639 1

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

35.          

SubPlan (forNested Loop)

36. 1.106 1,139.180 ↑ 1.0 1 1,106

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

37. 1,138.074 1,138.074 ↑ 333.0 1 1,106

CTE Scan on datas_uteis (cost=0.00..27.50 rows=333 width=4) (actual time=1.029..1.029 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,561.229 1,561.229 ↑ 1.0 205,639 1

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