explain.depesz.com

PostgreSQL's explain analyze made readable

Result: rtnG

Settings
# exclusive inclusive rows x rows loops node
1. 159.119 8,163.174 ↑ 2.5 205,443 1

Sort (cost=477,059.84..478,345.09 rows=514,100 width=381) (actual time=8,144.826..8,163.174 rows=205,443 loops=1)

  • Sort Key: contas_total.datavencimento DESC, contas_total.situacao
  • Sort Method: quicksort Memory: 35035kB
2.          

CTE contas

3. 84.349 4,852.624 ↓ 2.0 205,443 1

Subquery Scan on vcontasareceber_ultimopagamento (cost=290,702.23..293,529.78 rows=102,820 width=317) (actual time=4,661.330..4,852.624 rows=205,443 loops=1)

4. 378.127 4,768.275 ↓ 2.0 205,443 1

HashAggregate (cost=290,702.23..291,987.48 rows=102,820 width=531) (actual time=4,661.321..4,768.275 rows=205,443 loops=1)

  • Group Key: vcr.codigo, vcr.dhemissao, vcr.datavencimento, vcr.documento, vcr.prevendas_codigo, vcr.contasareceber_avulsas_codigo, vcr.saidasnf_codigo, vcr.conta_vinculada, vcr.clientes_codigo, vcr.valor, vcr.diascarencia, vcr.multapercent, vcr.jurosdiapercent, vcr.situacoescontas_codigo, vcr.situacao, vcr.faturada, vcr.observacao, vcr.gruposcontas_codigo, vcr.prevendas_pagtos_codigo, vcr.gruposcontas_descricao, vcr.entidade_codigo, vcr.entidade, vcr.editaconta, vcr.estornaconta, vcr.estornapagamento, vcr.aceitapagamento, vcr.parcela, (vcr.estornapagamento AND (NOT vcr.aceitapagamento)), ((max(vcontasareceber_pagtos.dhpagamento) OVER (?)))::date, last_value(vcontasareceber_pagtos.valorjurosdevido) OVER (?)
5. 248.304 4,390.148 ↓ 2.0 205,445 1

WindowAgg (cost=280,677.28..282,990.73 rows=102,820 width=531) (actual time=4,125.893..4,390.148 rows=205,445 loops=1)

6. 130.145 4,141.844 ↓ 2.0 205,445 1

Sort (cost=280,677.28..280,934.33 rows=102,820 width=531) (actual time=4,125.876..4,141.844 rows=205,445 loops=1)

  • Sort Key: vcontasareceber_pagtos.contasareceber_faturamentos_cr_codigo, vcontasareceber_pagtos.dhpagamento
  • Sort Method: quicksort Memory: 60607kB
7. 186.664 4,011.699 ↓ 2.0 205,445 1

WindowAgg (cost=270,318.28..272,117.63 rows=102,820 width=531) (actual time=3,814.209..4,011.699 rows=205,445 loops=1)

8. 140.683 3,825.035 ↓ 2.0 205,445 1

Sort (cost=270,318.28..270,575.33 rows=102,820 width=531) (actual time=3,814.198..3,825.035 rows=205,445 loops=1)

  • Sort Key: vcontasareceber_pagtos.contasareceber_faturamentos_cr_codigo
  • Sort Method: quicksort Memory: 60607kB
9. 86.972 3,684.352 ↓ 2.0 205,445 1

Hash Left Join (cost=250,062.92..261,758.64 rows=102,820 width=531) (actual time=3,322.292..3,684.352 rows=205,445 loops=1)

  • Hash Cond: (vcr.codigo = vcontasareceber_pagtos.contasareceber_faturamentos_cr_codigo)
10. 35.063 1,728.373 ↓ 2.0 205,443 1

Subquery Scan on vcr (cost=177,809.08..188,091.03 rows=102,820 width=516) (actual time=1,452.186..1,728.373 rows=205,443 loops=1)

  • Filter: vcr.faturada
  • Rows Removed by Filter: 196
11. 226.203 1,693.310 ↑ 1.0 205,639 1

WindowAgg (cost=177,809.08..186,034.64 rows=205,639 width=484) (actual time=1,452.184..1,693.310 rows=205,639 loops=1)

12.          

CTE contas

13. 73.046 772.173 ↑ 1.0 205,639 1

Hash Left Join (cost=25,163.72..43,317.74 rows=205,639 width=111) (actual time=246.881..772.173 rows=205,639 loops=1)

  • Hash Cond: (cr.gruposcontas_codigo = grct.codigo)
14. 64.858 699.116 ↑ 1.0 205,639 1

Hash Left Join (cost=25,162.63..42,545.50 rows=205,639 width=102) (actual time=246.864..699.116 rows=205,639 loops=1)

  • Hash Cond: (cr_sit.situacoesconta_codigo = sc.codigo)
15. 91.853 634.245 ↑ 1.0 205,639 1

Hash Left Join (cost=25,161.49..39,716.82 rows=205,639 width=89) (actual time=246.841..634.245 rows=205,639 loops=1)

  • Hash Cond: (cr.codigo = cr_sit.contasareceber_codigo)
16. 75.340 492.066 ↑ 1.0 205,639 1

Hash Left Join (cost=19,623.62..31,351.41 rows=205,639 width=85) (actual time=195.219..492.066 rows=205,639 loops=1)

  • Hash Cond: (cr.codigo = crav.contasareceber_codigo)
17. 82.992 360.576 ↑ 1.0 205,639 1

Hash Left Join (cost=14,672.14..23,854.80 rows=205,639 width=77) (actual time=137.943..360.576 rows=205,639 loops=1)

  • Hash Cond: (cr.codigo = cr_fat.contasareceber_codigo)
18. 60.879 221.998 ↑ 1.0 205,639 1

Hash Left Join (cost=8,938.67..15,295.75 rows=205,639 width=73) (actual time=81.189..221.998 rows=205,639 loops=1)

  • Hash Cond: (cr.codigo = pvpg_cr.contasareceber_codigo)
19. 61.754 83.457 ↑ 1.0 205,639 1

Hash Left Join (cost=694.78..6,000.08 rows=205,639 width=61) (actual time=3.361..83.457 rows=205,639 loops=1)

  • Hash Cond: (cr.codigo = snfpg_cr.contasareceber_codigo)
20. 18.350 18.350 ↑ 1.0 205,639 1

Seq Scan on contasareceber cr (cost=0.00..4,532.39 rows=205,639 width=53) (actual time=0.004..18.350 rows=205,639 loops=1)

21. 0.035 3.353 ↑ 1.0 176 1

Hash (cost=692.58..692.58 rows=176 width=12) (actual time=3.353..3.353 rows=176 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 16kB
22. 0.117 3.318 ↑ 1.0 176 1

Nested Loop Left Join (cost=5.25..692.58 rows=176 width=12) (actual time=2.269..3.318 rows=176 loops=1)

23. 1.837 3.025 ↑ 1.0 176 1

Hash Right Join (cost=4.96..605.16 rows=176 width=8) (actual time=2.258..3.025 rows=176 loops=1)

  • Hash Cond: (snfpg.codigo = snfpg_cr.saidasnf_pagtos_codigo)
24. 1.145 1.145 ↑ 1.0 22,122 1

Seq Scan on saidasnf_pagto snfpg (cost=0.00..377.22 rows=22,122 width=8) (actual time=0.002..1.145 rows=22,122 loops=1)

25. 0.023 0.043 ↑ 1.0 176 1

Hash (cost=2.76..2.76 rows=176 width=8) (actual time=0.043..0.043 rows=176 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 15kB
26. 0.020 0.020 ↑ 1.0 176 1

Seq Scan on saidasnf_pagtos_contasareceber snfpg_cr (cost=0.00..2.76 rows=176 width=8) (actual time=0.003..0.020 rows=176 loops=1)

27. 0.176 0.176 ↑ 1.0 1 176

Index Scan using saidasnf_codigo_index on saidasnf snf (cost=0.29..0.49 rows=1 width=8) (actual time=0.001..0.001 rows=1 loops=176)

  • Index Cond: (snfpg.codsaidasnf = codigo)
28. 5.959 77.662 ↑ 1.0 28,064 1

Hash (cost=7,893.09..7,893.09 rows=28,064 width=16) (actual time=77.662..77.662 rows=28,064 loops=1)

  • Buckets: 32768 Batches: 1 Memory Usage: 1572kB
29. 22.664 71.703 ↑ 1.0 28,064 1

Hash Right Join (cost=3,859.05..7,893.09 rows=28,064 width=16) (actual time=43.833..71.703 rows=28,064 loops=1)

  • Hash Cond: (pv.codigo = pvpg.codprevenda)
30. 5.349 5.349 ↑ 1.0 91,560 1

Seq Scan on prevendas pv (cost=0.00..3,295.60 rows=91,560 width=8) (actual time=0.001..5.349 rows=91,560 loops=1)

31. 4.877 43.690 ↑ 1.0 28,064 1

Hash (cost=3,508.25..3,508.25 rows=28,064 width=12) (actual time=43.690..43.690 rows=28,064 loops=1)

  • Buckets: 32768 Batches: 1 Memory Usage: 1462kB
32. 10.408 38.813 ↑ 1.0 28,064 1

Hash Left Join (cost=2,716.73..3,508.25 rows=28,064 width=12) (actual time=27.475..38.813 rows=28,064 loops=1)

  • Hash Cond: (pvpg_cr.prevendas_pagtos_codigo = pvpg.codigo)
33. 1.505 1.505 ↑ 1.0 28,064 1

Seq Scan on prevendas_pagtos_contasareceber pvpg_cr (cost=0.00..405.64 rows=28,064 width=8) (actual time=0.002..1.505 rows=28,064 loops=1)

34. 17.235 26.900 ↑ 1.0 93,499 1

Hash (cost=1,547.99..1,547.99 rows=93,499 width=8) (actual time=26.900..26.900 rows=93,499 loops=1)

  • Buckets: 131072 Batches: 1 Memory Usage: 4677kB
35. 9.665 9.665 ↑ 1.0 93,499 1

Seq Scan on prevenda_pagto pvpg (cost=0.00..1,547.99 rows=93,499 width=8) (actual time=0.003..9.665 rows=93,499 loops=1)

36. 36.727 55.586 ↑ 1.0 205,443 1

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

  • Buckets: 262144 Batches: 1 Memory Usage: 9271kB
37. 18.859 18.859 ↑ 1.0 205,443 1

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

38. 37.680 56.150 ↑ 1.0 177,399 1

Hash (cost=2,733.99..2,733.99 rows=177,399 width=12) (actual time=56.150..56.150 rows=177,399 loops=1)

  • Buckets: 262144 Batches: 1 Memory Usage: 9671kB
39. 18.470 18.470 ↑ 1.0 177,399 1

Seq Scan on contasareceber_avulsas crav (cost=0.00..2,733.99 rows=177,399 width=12) (actual time=0.006..18.470 rows=177,399 loops=1)

40. 38.837 50.326 ↑ 1.0 205,639 1

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

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

42. 0.010 0.013 ↑ 1.0 6 1

Hash (cost=1.06..1.06 rows=6 width=17) (actual time=0.013..0.013 rows=6 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
43. 0.003 0.003 ↑ 1.0 6 1

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

44. 0.009 0.011 ↑ 1.0 4 1

Hash (cost=1.04..1.04 rows=4 width=13) (actual time=0.011..0.011 rows=4 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
45. 0.002 0.002 ↑ 1.0 4 1

Seq Scan on gruposcontas grct (cost=0.00..1.04 rows=4 width=13) (actual time=0.002..0.002 rows=4 loops=1)

46. 148.459 1,467.107 ↑ 1.0 205,639 1

Sort (cost=134,491.34..135,005.44 rows=205,639 width=484) (actual time=1,452.170..1,467.107 rows=205,639 loops=1)

  • Sort Key: contas.entidade_codigo, contas.entidade, contas.datavencimento, contas.codigo
  • Sort Method: quicksort Memory: 56673kB
47. 188.194 1,318.648 ↑ 1.0 205,639 1

WindowAgg (cost=67,245.67..71,358.45 rows=205,639 width=484) (actual time=1,121.412..1,318.648 rows=205,639 loops=1)

48. 157.754 1,130.454 ↑ 1.0 205,639 1

Sort (cost=67,245.67..67,759.77 rows=205,639 width=484) (actual time=1,121.393..1,130.454 rows=205,639 loops=1)

  • Sort Key: contas.entidade_codigo, contas.entidade
  • Sort Method: quicksort Memory: 50245kB
49. 972.700 972.700 ↑ 1.0 205,639 1

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

50. 56.278 1,869.007 ↑ 1.0 204,170 1

Hash (cost=69,701.71..69,701.71 rows=204,170 width=15) (actual time=1,869.007..1,869.007 rows=204,170 loops=1)

  • Buckets: 262144 Batches: 1 Memory Usage: 11619kB
51. 42.047 1,812.729 ↑ 1.0 204,170 1

Subquery Scan on vcontasareceber_pagtos (cost=399.69..69,701.71 rows=204,170 width=15) (actual time=2.929..1,812.729 rows=204,170 loops=1)

52. 1,696.470 1,770.682 ↑ 1.0 204,170 1

Hash Left Join (cost=399.69..67,660.01 rows=204,170 width=19) (actual time=2.928..1,770.682 rows=204,170 loops=1)

  • Hash Cond: (crpg.codigo = lotpg.contasareceber_pagtos_codigo)
53. 55.231 71.490 ↑ 1.0 204,170 1

Hash Anti Join (cost=5.76..7,150.40 rows=204,170 width=23) (actual time=0.053..71.490 rows=204,170 loops=1)

  • Hash Cond: (crpg.codigo = crpg_e.contasareceber_pagtos_codigo)
54. 16.218 16.218 ↑ 1.0 204,337 1

Seq Scan on contasareceber_pagtos crpg (cost=0.00..4,566.37 rows=204,337 width=23) (actual time=0.006..16.218 rows=204,337 loops=1)

55. 0.022 0.041 ↑ 1.0 167 1

Hash (cost=3.67..3.67 rows=167 width=8) (actual time=0.041..0.041 rows=167 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 15kB
56. 0.019 0.019 ↑ 1.0 167 1

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

57. 1.428 2.722 ↑ 1.0 14,086 1

Hash (cost=217.86..217.86 rows=14,086 width=4) (actual time=2.722..2.722 rows=14,086 loops=1)

  • Buckets: 16384 Batches: 1 Memory Usage: 624kB
58. 1.294 1.294 ↑ 1.0 14,086 1

Seq Scan on contasareceber_pagtos_lote_pagtos lotpg (cost=0.00..217.86 rows=14,086 width=4) (actual time=0.003..1.294 rows=14,086 loops=1)

59.          

CTE contas_saldo

60. 2,503.069 7,472.906 ↓ 205.4 205,443 1

Function Scan on contasareceber_saldo (cost=2,313.72..2,323.72 rows=1,000 width=160) (actual time=7,464.547..7,472.906 rows=205,443 loops=1)

61.          

Initplan (forFunction Scan)

62. 21.984 4,969.837 ↑ 1.0 1 1

Aggregate (cost=2,313.45..2,313.46 rows=1 width=4) (actual time=4,969.836..4,969.837 rows=1 loops=1)

63. 4,947.853 4,947.853 ↓ 2.0 205,443 1

CTE Scan on contas contas_1 (cost=0.00..2,056.40 rows=102,820 width=4) (actual time=4,661.332..4,947.853 rows=205,443 loops=1)

64.          

CTE contas_total

65. 110.857 7,805.458 ↑ 2.5 205,443 1

Merge Right Join (cost=10,685.87..22,258.12 rows=514,100 width=381) (actual time=7,655.844..7,805.458 rows=205,443 loops=1)

  • Merge Cond: (cr_saldo.contasareceber_codigo = vcr_1.codigo)
66. 58.192 7,586.286 ↓ 205.4 205,443 1

Sort (cost=69.83..72.33 rows=1,000 width=72) (actual time=7,575.836..7,586.286 rows=205,443 loops=1)

  • Sort Key: cr_saldo.contasareceber_codigo
  • Sort Method: quicksort Memory: 22195kB
67. 7,528.094 7,528.094 ↓ 205.4 205,443 1

CTE Scan on contas_saldo cr_saldo (cost=0.00..20.00 rows=1,000 width=72) (actual time=7,464.551..7,528.094 rows=205,443 loops=1)

68. 93.677 108.315 ↓ 2.0 205,443 1

Sort (cost=10,616.04..10,873.09 rows=102,820 width=313) (actual time=79.994..108.315 rows=205,443 loops=1)

  • Sort Key: vcr_1.codigo
  • Sort Method: quicksort Memory: 35035kB
69. 14.638 14.638 ↓ 2.0 205,443 1

CTE Scan on contas vcr_1 (cost=0.00..2,056.40 rows=102,820 width=313) (actual time=0.001..14.638 rows=205,443 loops=1)

70. 8,004.055 8,004.055 ↑ 2.5 205,443 1

CTE Scan on contas_total (cost=0.00..20,564.00 rows=514,100 width=381) (actual time=7,655.853..8,004.055 rows=205,443 loops=1)