explain.depesz.com

PostgreSQL's explain analyze made readable

Result: gvhr

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

CTE Scan on contas_total (cost=76,784.77..85,002.49 rows=205,443 width=573) (actual time=1,111.910..2,180.364 rows=205,446 loops=1)

2.          

CTE contas

3. 129.617 861.342 ↓ 1.0 205,446 1

Hash Left Join (cost=25,165.21..47,161.00 rows=205,443 width=94) (actual time=240.775..861.342 rows=205,446 loops=1)

  • Hash Cond: (cr.tipodocumento_codigo = t.codigo)
4. 36.591 731.717 ↓ 1.0 205,446 1

Hash Left Join (cost=25,163.72..43,307.45 rows=205,443 width=80) (actual time=240.756..731.717 rows=205,446 loops=1)

  • Hash Cond: (cr.gruposcontas_codigo = grct.codigo)
5. 56.325 695.116 ↓ 1.0 205,446 1

Hash Left Join (cost=25,162.63..42,535.94 rows=205,443 width=75) (actual time=240.742..695.116 rows=205,446 loops=1)

  • Hash Cond: (cr_sit.situacoesconta_codigo = sc.codigo)
6. 96.508 638.778 ↓ 1.0 205,446 1

Hash Left Join (cost=25,161.49..39,709.96 rows=205,443 width=68) (actual time=240.719..638.778 rows=205,446 loops=1)

  • Hash Cond: (cr.codigo = cr_sit.contasareceber_codigo)
7. 79.994 493.059 ↓ 1.0 205,446 1

Hash Left Join (cost=19,623.62..31,347.25 rows=205,443 width=64) (actual time=190.204..493.059 rows=205,446 loops=1)

  • Hash Cond: (cr.codigo = crav.contasareceber_codigo)
8. 62.863 364.669 ↓ 1.0 205,446 1

Hash Left Join (cost=14,672.14..23,853.06 rows=205,443 width=60) (actual time=140.718..364.669 rows=205,446 loops=1)

  • Hash Cond: (cr.codigo = pvpg_cr.contasareceber_codigo)
9. 32.346 228.188 ↓ 1.0 205,446 1

Hash Left Join (cost=6,428.25..14,558.39 rows=205,443 width=52) (actual time=66.927..228.188 rows=205,446 loops=1)

  • Hash Cond: (cr.codigo = snfpg_cr.contasareceber_codigo)
10. 109.799 192.295 ↓ 1.0 205,446 1

Hash Join (cost=5,733.47..13,091.43 rows=205,443 width=44) (actual time=63.371..192.295 rows=205,446 loops=1)

  • Hash Cond: (cr.codigo = cr_fat.contasareceber_codigo)
11. 20.357 20.357 ↑ 1.0 205,639 1

Seq Scan on contasareceber cr (cost=0.00..4,532.39 rows=205,639 width=44) (actual time=0.005..20.357 rows=205,639 loops=1)

12. 36.911 62.139 ↓ 1.0 205,446 1

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

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

  • Filter: (contasareceber_codigo IS NOT NULL)
14. 0.038 3.547 ↑ 1.0 176 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 16kB
15. 0.134 3.509 ↑ 1.0 176 1

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

16. 2.052 3.199 ↑ 1.0 176 1

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

  • Hash Cond: (snfpg.codigo = snfpg_cr.saidasnf_pagtos_codigo)
17. 1.097 1.097 ↑ 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.097 rows=22,122 loops=1)

18. 0.032 0.050 ↑ 1.0 176 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 15kB
19. 0.018 0.018 ↑ 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.018 rows=176 loops=1)

20. 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)
21. 5.674 73.618 ↑ 1.0 28,064 1

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

  • Buckets: 32768 Batches: 1 Memory Usage: 1462kB
22. 21.693 67.944 ↑ 1.0 28,064 1

Hash Right Join (cost=3,859.05..7,893.09 rows=28,064 width=12) (actual time=40.354..67.944 rows=28,064 loops=1)

  • Hash Cond: (pv.codigo = pvpg.codprevenda)
23. 6.077 6.077 ↑ 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..6.077 rows=91,560 loops=1)

24. 4.317 40.174 ↑ 1.0 28,064 1

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

  • Buckets: 32768 Batches: 1 Memory Usage: 1353kB
25. 9.479 35.857 ↑ 1.0 28,064 1

Hash Left Join (cost=2,716.73..3,508.25 rows=28,064 width=8) (actual time=25.641..35.857 rows=28,064 loops=1)

  • Hash Cond: (pvpg_cr.prevendas_pagtos_codigo = pvpg.codigo)
26. 1.361 1.361 ↑ 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.003..1.361 rows=28,064 loops=1)

27. 15.803 25.017 ↑ 1.0 93,499 1

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

  • Buckets: 131072 Batches: 1 Memory Usage: 4677kB
28. 9.214 9.214 ↑ 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.214 rows=93,499 loops=1)

29. 30.931 48.396 ↑ 1.0 177,399 1

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

  • Buckets: 262144 Batches: 1 Memory Usage: 8978kB
30. 17.465 17.465 ↑ 1.0 177,399 1

Seq Scan on contasareceber_avulsas crav (cost=0.00..2,733.99 rows=177,399 width=8) (actual time=0.004..17.465 rows=177,399 loops=1)

31. 37.829 49.211 ↑ 1.0 205,639 1

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

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

33. 0.010 0.013 ↑ 1.0 6 1

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

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

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

35. 0.008 0.010 ↑ 1.0 4 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
36. 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.001..0.002 rows=4 loops=1)

37. 0.005 0.008 ↑ 1.0 22 1

Hash (cost=1.22..1.22 rows=22 width=18) (actual time=0.008..0.008 rows=22 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
38. 0.003 0.003 ↑ 1.0 22 1

Seq Scan on tipodocumento t (cost=0.00..1.22 rows=22 width=18) (actual time=0.002..0.003 rows=22 loops=1)

39.          

CTE contas_saldo

40. 0.000 871.111 ↓ 0.0 0 1

Subquery Scan on contasareceber_saldo (cost=23,193.32..23,193.37 rows=1 width=92) (actual time=871.111..871.111 rows=0 loops=1)

41. 871.111 871.111 ↓ 0.0 0 1

CTE Scan on contas_final (cost=23,193.32..23,193.36 rows=1 width=145) (actual time=871.111..871.111 rows=0 loops=1)

  • Filter: (contasareceber_faturamentos_codigo IS NOT NULL)
42.          

CTE contas_pagtos

43. 0.002 871.107 ↓ 0.0 0 1

Merge Right Join (cost=21,975.25..23,182.58 rows=1 width=262) (actual time=871.107..871.107 rows=0 loops=1)

  • Merge Cond: (crpg.contasareceber_faturamentos_cr_codigo = cr_1.codigo)
44. 0.002 871.098 ↑ 68,056.0 1 1

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

45. 59.429 871.096 ↑ 68,056.0 1 1

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

  • Sort Key: crpg.contasareceber_faturamentos_cr_codigo, crpg.dhpagamento DESC
  • Sort Method: quicksort Memory: 22096kB
46. 588.803 811.667 ↓ 3.0 204,172 1

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

47. 114.444 222.864 ↓ 3.0 204,172 1

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

  • Sort Key: crpg.contasareceber_faturamentos_cr_codigo
  • Sort Method: quicksort Memory: 22095kB
48. 42.252 108.420 ↓ 3.0 204,172 1

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

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

  • Filter: ((dhpagamento)::date <= (now())::date)
50. 0.029 0.047 ↑ 1.0 167 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 14kB
51. 0.018 0.018 ↑ 1.0 167 1

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

52. 0.004 0.007 ↓ 0.0 0 1

Materialize (cost=1.39..17.72 rows=1 width=62) (actual time=0.007..0.007 rows=0 loops=1)

53. 0.001 0.003 ↓ 0.0 0 1

Nested Loop Left Join (cost=1.39..17.71 rows=1 width=62) (actual time=0.003..0.003 rows=0 loops=1)

54. 0.000 0.002 ↓ 0.0 0 1

Nested Loop Left Join (cost=0.97..13.27 rows=1 width=58) (actual time=0.002..0.002 rows=0 loops=1)

55. 0.000 0.002 ↓ 0.0 0 1

Nested Loop Left Join (cost=0.84..13.11 rows=1 width=61) (actual time=0.002..0.002 rows=0 loops=1)

56. 0.002 0.002 ↓ 0.0 0 1

Index Scan using contasareceber_pkey1 on contasareceber cr_1 (cost=0.42..4.66 rows=1 width=57) (actual time=0.002..0.002 rows=0 loops=1)

  • Index Cond: (codigo = ANY (NULL::integer[]))
57. 0.000 0.000 ↓ 0.0 0

Index Scan using contasareceber_contasareceber_codigo_index on contasareceber_situacao cr_sit_1 (cost=0.42..8.44 rows=1 width=8) (never executed)

  • Index Cond: (contasareceber_codigo = cr_1.codigo)
58. 0.000 0.000 ↓ 0.0 0

Index Scan using situacoescontas_pkey on situacoescontas sc_1 (cost=0.13..0.15 rows=1 width=5) (never executed)

  • Index Cond: (cr_sit_1.situacoesconta_codigo = codigo)
59. 0.000 0.000 ↓ 0.0 0

Index Only Scan using contasareceber_faturamentos_pkey on contasareceber_faturamentos cr_fat_1 (cost=0.42..4.44 rows=1 width=4) (never executed)

  • Index Cond: (contasareceber_codigo = cr_1.codigo)
  • Heap Fetches: 0
60.          

CTE datas_uteis

61. 0.000 0.000 ↓ 0.0 0

Function Scan on obter_datas_uteis diautil (cost=0.29..10.29 rows=1,000 width=4) (never executed)

62.          

Initplan (forFunction Scan)

63. 0.000 0.000 ↓ 0.0 0

Aggregate (cost=0.02..0.03 rows=1 width=4) (never executed)

64. 0.000 0.000 ↓ 0.0 0

CTE Scan on contas_pagtos (cost=0.00..0.02 rows=1 width=4) (never executed)

65.          

CTE config_carencia_juros

66. 0.000 0.000 ↓ 0.0 0

Function Scan on carenciajuros (cost=0.26..0.27 rows=1 width=4) (never executed)

67.          

CTE contas_saldos_ultimopagtoatrasado

68. 0.001 871.109 ↓ 0.0 0 1

Nested Loop (cost=0.00..0.13 rows=1 width=427) (actual time=871.109..871.109 rows=0 loops=1)

69. 871.108 871.108 ↓ 0.0 0 1

CTE Scan on contas_pagtos contas_pagtos_1 (cost=0.00..0.02 rows=1 width=423) (actual time=871.108..871.108 rows=0 loops=1)

70. 0.000 0.000 ↓ 0.0 0

CTE Scan on config_carencia_juros (cost=0.00..0.02 rows=1 width=4) (never executed)

71.          

SubPlan (forNested Loop)

72. 0.000 0.000 ↓ 0.0 0

Limit (cost=0.00..0.08 rows=1 width=4) (never executed)

73. 0.000 0.000 ↓ 0.0 0

CTE Scan on datas_uteis (cost=0.00..27.50 rows=333 width=4) (never executed)

  • Filter: (diautil >= (contas_pagtos_1.datavencimento + config_carencia_juros.carenciajuros))
74.          

CTE contas_final

75. 871.110 871.110 ↓ 0.0 0 1

CTE Scan on contas_saldos_ultimopagtoatrasado (cost=0.00..0.05 rows=1 width=428) (actual time=871.110..871.110 rows=0 loops=1)

76.          

CTE contas_total

77. 84.376 1,947.069 ↓ 1.0 205,446 1

Hash Left Join (cost=0.03..6,430.40 rows=205,443 width=573) (actual time=1,111.899..1,947.069 rows=205,446 loops=1)

  • Hash Cond: (vcr.codigo = cr_saldo.contasareceber_codigo)
78. 991.580 991.580 ↓ 1.0 205,446 1

CTE Scan on contas vcr (cost=0.00..4,108.86 rows=205,443 width=501) (actual time=240.778..991.580 rows=205,446 loops=1)

79. 0.000 871.113 ↓ 0.0 0 1

Hash (cost=0.02..0.02 rows=1 width=76) (actual time=871.113..871.113 rows=0 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
80. 871.113 871.113 ↓ 0.0 0 1

CTE Scan on contas_saldo cr_saldo (cost=0.00..0.02 rows=1 width=76) (actual time=871.113..871.113 rows=0 loops=1)