explain.depesz.com

PostgreSQL's explain analyze made readable

Result: b12r

Settings
# exclusive inclusive rows x rows loops node
1. 0.751 23,636.565 ↓ 12.0 323 1

Sort (cost=45,549,807.73..45,549,807.80 rows=27 width=547) (actual time=23,636.519..23,636.565 rows=323 loops=1)

  • Sort Key: (row_number() OVER (?))
  • Sort Method: quicksort Memory: 114kB
  • Buffers: shared hit=1,174,827 read=14,799
  • JIT:
  • Functions: 312
  • Options: Inlining true, Optimization true, Expressions true, Deforming true
  • Timing: Generation 76.141 ms, Inlining 164.876 ms, Optimization 7536.122 ms, Emission 5452.149 ms, Total 13229.288 ms
2.          

CTE with_dados_titulo

3. 109.868 1,791.166 ↓ 3.3 18,842 1

Sort (cost=34,610.04..34,624.45 rows=5,764 width=170) (actual time=1,758.941..1,791.166 rows=18,842 loops=1)

  • Sort Key: ct_4.vl_resultado DESC, (avg(CASE WHEN (t_2.dt_quitacao > m_1.dt_movimento) THEN (t_2.dt_quitacao - m_1.dt_movimento) ELSE 0 END)), (sum(CASE WHEN ((t_2.dt_quitacao IS NULL) AND (t_2.dt_vencimento < CURRENT_DATE)) THEN t_2.vl_titulo ELSE '0'::numeric END))
  • Sort Method: quicksort Memory: 2,241kB
  • Buffers: shared hit=212,940 read=971
4. 143.351 1,681.298 ↓ 3.3 18,842 1

HashAggregate (cost=34,105.89..34,249.99 rows=5,764 width=170) (actual time=1,585.082..1,681.298 rows=18,842 loops=1)

  • Group Key: ct_4.cd_ctrc
  • Buffers: shared hit=212,940 read=971
5. 23.007 1,537.947 ↓ 3.3 18,842 1

Nested Loop Left Join (cost=408.37..33,947.38 rows=5,764 width=32) (actual time=289.491..1,537.947 rows=18,842 loops=1)

  • Buffers: shared hit=212,940 read=971
6. 22.117 1,006.206 ↓ 3.6 18,842 1

Nested Loop Left Join (cost=407.94..31,280.83 rows=5,304 width=22) (actual time=289.444..1,006.206 rows=18,842 loops=1)

  • Buffers: shared hit=147,880 read=768
7. 10.381 720.301 ↓ 3.6 18,842 1

Hash Join (cost=407.51..23,357.30 rows=5,304 width=18) (actual time=289.401..720.301 rows=18,842 loops=1)

  • Hash Cond: (ct_4.cd_agencia = a.cd_agencia)
  • Buffers: shared hit=83,434 read=667
8. 11.643 709.839 ↓ 3.6 18,842 1

Nested Loop Left Join (cost=403.46..23,338.69 rows=5,304 width=22) (actual time=289.281..709.839 rows=18,842 loops=1)

  • Filter: CASE WHEN (cte.cd_ctrc IS NOT NULL) THEN (cte.cd_status = 100) ELSE true END
  • Buffers: shared hit=83,432 read=667
9. 74.265 359.040 ↓ 1.8 18,842 1

Index Scan using idx_ctrc_dt_emissao on ctrc ct_4 (cost=403.04..4,501.23 rows=10,608 width=22) (actual time=286.218..359.040 rows=18,842 loops=1)

  • Index Cond: ((dt_emissao >= '2020-05-01'::date) AND (dt_emissao <= '2020-05-31'::date))
  • Filter: ((dt_cancelamento IS NULL) AND (cd_ctrc_novo IS NULL) AND (NOT (hashed SubPlan 2)))
  • Rows Removed by Filter: 1,550
  • Buffers: shared hit=9,573 read=75
10.          

SubPlan (for Index Scan)

11. 0.024 284.775 ↑ 1.0 11 1

Subquery Scan on vw (cost=402.25..402.58 rows=11 width=4) (actual time=284.757..284.775 rows=11 loops=1)

  • Buffers: shared hit=1,348
12. 0.015 284.751 ↑ 1.0 11 1

Unique (cost=402.25..402.47 rows=11 width=126) (actual time=284.736..284.751 rows=11 loops=1)

  • Buffers: shared hit=1,348
13. 0.038 284.736 ↑ 1.0 11 1

Sort (cost=402.25..402.28 rows=11 width=126) (actual time=284.735..284.736 rows=11 loops=1)

  • Sort Key: (COALESCE(p_2.nm_fantasia, p_2.nm_pessoa)), p_2.cd_pessoa, p_2.cd_cidade, (((ci_1.nm_cidade || ' / '::text) || (uf.ds_sigla)::text)), ((COALESCE(pj.nr_cnpj, pf.nr_cpf))::character varying(14)), (COALESCE(substr((pj.nr_cnpj)::text, 1, 8), (pf.nr_cpf)::text)), (COALESCE((SubPlan 1), f.cd_pessoa_matriz))
  • Sort Method: quicksort Memory: 26kB
  • Buffers: shared hit=1,348
14. 0.065 284.698 ↑ 1.0 11 1

Nested Loop (cost=1.55..402.06 rows=11 width=126) (actual time=273.514..284.698 rows=11 loops=1)

  • Buffers: shared hit=1,348
15. 0.029 273.545 ↑ 1.0 11 1

Nested Loop (cost=1.41..94.35 rows=11 width=92) (actual time=273.224..273.545 rows=11 loops=1)

  • Buffers: shared hit=148
16. 0.023 273.384 ↑ 1.0 11 1

Nested Loop (cost=1.13..91.05 rows=11 width=76) (actual time=273.193..273.384 rows=11 loops=1)

  • Buffers: shared hit=115
17. 0.014 273.295 ↑ 1.0 11 1

Nested Loop (cost=0.71..62.03 rows=11 width=35) (actual time=273.174..273.295 rows=11 loops=1)

  • Join Filter: (f.cd_pessoa_matriz = eg.cd_pessoa)
  • Buffers: shared hit=71
18. 273.078 273.078 ↑ 1.0 1 1

Seq Scan on empresa_grupo eg (cost=0.00..1.01 rows=1 width=4) (actual time=273.077..273.078 rows=1 loops=1)

  • Filter: (id_ativo = 1)
  • Buffers: shared hit=1
19. 0.023 0.203 ↑ 1.0 11 1

Nested Loop Left Join (cost=0.71..60.88 rows=11 width=35) (actual time=0.088..0.203 rows=11 loops=1)

  • Join Filter: (pg.cd_empresa = f.cd_pessoa_matriz)
  • Buffers: shared hit=70
20. 0.013 0.158 ↑ 1.0 11 1

Nested Loop Left Join (cost=0.71..57.71 rows=11 width=35) (actual time=0.067..0.158 rows=11 loops=1)

  • Buffers: shared hit=67
21. 0.035 0.101 ↑ 1.0 11 1

Nested Loop Left Join (cost=0.29..28.72 rows=11 width=23) (actual time=0.049..0.101 rows=11 loops=1)

  • Buffers: shared hit=34
22. 0.022 0.022 ↑ 1.0 11 1

Seq Scan on filial f (cost=0.00..1.14 rows=11 width=8) (actual time=0.018..0.022 rows=11 loops=1)

  • Filter: (id_ativo = 1)
  • Buffers: shared hit=1
23. 0.044 0.044 ↑ 1.0 1 11

Index Scan using pk_pessoa_juridica on pessoa_juridica pj (cost=0.29..2.51 rows=1 width=19) (actual time=0.004..0.004 rows=1 loops=11)

  • Index Cond: (cd_pessoa = f.cd_pessoa_filial)
  • Filter: (nr_cnpj IS NOT NULL)
  • Buffers: shared hit=33
24. 0.044 0.044 ↓ 0.0 0 11

Index Scan using pk_pessoa_fisica on pessoa_fisica pf (cost=0.42..2.64 rows=1 width=16) (actual time=0.004..0.004 rows=0 loops=11)

  • Index Cond: (cd_pessoa = f.cd_pessoa_filial)
  • Filter: (nr_cpf IS NOT NULL)
  • Buffers: shared hit=33
25. 0.008 0.022 ↑ 1.0 1 11

Materialize (cost=0.00..3.01 rows=1 width=4) (actual time=0.002..0.002 rows=1 loops=11)

  • Buffers: shared hit=3
26. 0.014 0.014 ↑ 1.0 1 1

Seq Scan on parametro_geral pg (cost=0.00..3.01 rows=1 width=4) (actual time=0.013..0.014 rows=1 loops=1)

  • Buffers: shared hit=3
27. 0.066 0.066 ↑ 1.0 1 11

Index Scan using pk_pessoa on pessoa p_2 (cost=0.42..2.64 rows=1 width=45) (actual time=0.006..0.006 rows=1 loops=11)

  • Index Cond: (cd_pessoa = f.cd_pessoa_filial)
  • Buffers: shared hit=44
28. 0.132 0.132 ↑ 1.0 1 11

Index Scan using pk_cidade on cidade ci_1 (cost=0.28..0.30 rows=1 width=20) (actual time=0.012..0.012 rows=1 loops=11)

  • Index Cond: (cd_cidade = p_2.cd_cidade)
  • Buffers: shared hit=33
29. 0.033 0.033 ↑ 1.0 1 11

Index Scan using pk_uf on uf (cost=0.14..0.16 rows=1 width=7) (actual time=0.003..0.003 rows=1 loops=11)

  • Index Cond: (cd_uf = ci_1.cd_uf)
  • Buffers: shared hit=22
30.          

SubPlan (for Nested Loop)

31. 0.011 11.055 ↑ 1.0 1 11

Limit (cost=27.79..27.80 rows=1 width=4) (actual time=1.005..1.005 rows=1 loops=11)

  • Buffers: shared hit=1,178
32. 0.022 11.044 ↑ 1.0 1 11

Unique (cost=27.79..27.80 rows=1 width=4) (actual time=1.004..1.004 rows=1 loops=11)

  • Buffers: shared hit=1,178
33. 0.066 11.022 ↑ 1.0 1 11

Sort (cost=27.79..27.79 rows=1 width=4) (actual time=1.002..1.002 rows=1 loops=11)

  • Sort Key: p_1_1.cd_pessoa
  • Sort Method: quicksort Memory: 25kB
  • Buffers: shared hit=1,178
34. 0.209 10.956 ↑ 1.0 1 11

Nested Loop Left Join (cost=1.13..27.78 rows=1 width=4) (actual time=0.017..0.996 rows=1 loops=11)

  • Filter: ((substr((pjm.nr_cnpj)::text, 1, 12) = (substr((pj.nr_cnpj)::text, 1, 8) || '0001'::text)) OR (pfm_1.nr_cpf = pf.nr_cpf))
  • Rows Removed by Filter: 10
  • Buffers: shared hit=1,178
35. 0.066 8.206 ↑ 1.0 11 11

Nested Loop Left Join (cost=0.84..24.06 rows=11 width=16) (actual time=0.011..0.746 rows=11 loops=11)

  • Buffers: shared hit=815
36. 0.121 7.777 ↑ 1.0 11 11

Nested Loop (cost=0.42..19.12 rows=11 width=4) (actual time=0.007..0.707 rows=11 loops=11)

  • Buffers: shared hit=452
37. 0.033 0.033 ↑ 1.0 11 11

Seq Scan on filial f_1 (cost=0.00..1.11 rows=11 width=4) (actual time=0.001..0.003 rows=11 loops=11)

  • Buffers: shared hit=11
38. 7.623 7.623 ↑ 1.0 1 121

Index Only Scan using pk_pessoa on pessoa p_1_1 (cost=0.42..1.64 rows=1 width=4) (actual time=0.063..0.063 rows=1 loops=121)

  • Index Cond: (cd_pessoa = f_1.cd_pessoa_filial)
  • Heap Fetches: 77
  • Buffers: shared hit=441
39. 0.363 0.363 ↓ 0.0 0 121

Index Scan using pk_pessoa_fisica on pessoa_fisica pfm_1 (cost=0.42..0.45 rows=1 width=16) (actual time=0.003..0.003 rows=0 loops=121)

  • Index Cond: (cd_pessoa = p_1_1.cd_pessoa)
  • Filter: (nr_cpf IS NOT NULL)
  • Buffers: shared hit=363
40. 2.541 2.541 ↑ 1.0 1 121

Index Scan using pk_pessoa_juridica on pessoa_juridica pjm (cost=0.29..0.31 rows=1 width=19) (actual time=0.021..0.021 rows=1 loops=121)

  • Index Cond: (cd_pessoa = p_1_1.cd_pessoa)
  • Filter: (nr_cnpj IS NOT NULL)
  • Buffers: shared hit=363
41. 339.156 339.156 ↑ 1.0 1 18,842

Index Scan using pk_ctrc_cte on ctrc_cte cte (cost=0.43..1.76 rows=1 width=8) (actual time=0.018..0.018 rows=1 loops=18,842)

  • Index Cond: (cd_ctrc = ct_4.cd_ctrc)
  • Buffers: shared hit=73,859 read=592
42. 0.027 0.081 ↓ 1.0 92 1

Hash (cost=2.91..2.91 rows=91 width=4) (actual time=0.081..0.081 rows=92 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 12kB
  • Buffers: shared hit=2
43. 0.054 0.054 ↓ 1.0 92 1

Seq Scan on agencia a (cost=0.00..2.91 rows=91 width=4) (actual time=0.025..0.054 rows=92 loops=1)

  • Buffers: shared hit=2
44. 263.788 263.788 ↑ 1.0 1 18,842

Index Scan using pk_movimento on movimento m_1 (cost=0.43..1.49 rows=1 width=8) (actual time=0.014..0.014 rows=1 loops=18,842)

  • Index Cond: (cd_movimento = ct_4.cd_movimento)
  • Buffers: shared hit=64,446 read=101
45. 508.734 508.734 ↑ 2.0 1 18,842

Index Scan using idx_titulo_cd_movimento on titulo t_2 (cost=0.43..0.48 rows=2 width=18) (actual time=0.027..0.027 rows=1 loops=18,842)

  • Index Cond: (cd_movimento = m_1.cd_movimento)
  • Buffers: shared hit=65,060 read=203
46. 1.347 23,635.814 ↓ 12.0 323 1

WindowAgg (cost=45,515,181.49..45,515,182.64 rows=27 width=547) (actual time=23,634.447..23,635.814 rows=323 loops=1)

  • Buffers: shared hit=1,174,824 read=14,799
47. 1.034 23,634.467 ↓ 12.0 323 1

Sort (cost=45,515,181.49..45,515,181.56 rows=27 width=375) (actual time=23,634.397..23,634.467 rows=323 loops=1)

  • Sort Key: t.vl_resultado DESC, t.nr_dias_geracao, t.vl_inadimplente, t.dt_inadimplente
  • Sort Method: quicksort Memory: 103kB
  • Buffers: shared hit=1,174,824 read=14,799
48. 0.502 23,633.433 ↓ 12.0 323 1

Hash Left Join (cost=45,509,161.62..45,515,180.85 rows=27 width=375) (actual time=23,004.694..23,633.433 rows=323 loops=1)

  • Hash Cond: (ci.cd_uf = u.cd_uf)
  • Buffers: shared hit=1,174,821 read=14,799
49. 104.060 23,632.890 ↓ 12.0 323 1

Hash Join (cost=45,509,159.39..45,515,178.54 rows=27 width=376) (actual time=23,004.628..23,632.890 rows=323 loops=1)

  • Hash Cond: (p.cd_pessoa = t.cd_pessoa_pagador)
  • Buffers: shared hit=1,174,820 read=14,799
50. 118.334 13,491.089 ↑ 1.1 179,629 1

Hash Left Join (cost=186.17..5,706.00 rows=190,215 width=44) (actual time=12,966.588..13,491.089 rows=179,629 loops=1)

  • Hash Cond: (p.cd_cidade = ci.cd_cidade)
  • Buffers: shared hit=2,675 read=500
51. 406.265 406.265 ↑ 1.1 179,629 1

Seq Scan on pessoa p (cost=0.00..5,020.15 rows=190,215 width=32) (actual time=0.013..406.265 rows=179,629 loops=1)

  • Buffers: shared hit=2,618 read=500
52. 2.088 12,966.490 ↑ 1.0 5,741 1

Hash (cost=114.41..114.41 rows=5,741 width=20) (actual time=12,966.490..12,966.490 rows=5,741 loops=1)

  • Buckets: 8,192 Batches: 1 Memory Usage: 368kB
  • Buffers: shared hit=57
53. 12,964.402 12,964.402 ↑ 1.0 5,741 1

Seq Scan on cidade ci (cost=0.00..114.41 rows=5,741 width=20) (actual time=12,944.915..12,964.402 rows=5,741 loops=1)

  • Buffers: shared hit=57
54. 0.458 10,037.741 ↓ 12.0 323 1

Hash (cost=45,508,972.88..45,508,972.88 rows=27 width=336) (actual time=10,037.741..10,037.741 rows=323 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 47kB
  • Buffers: shared hit=1,172,145 read=14,299
55. 0.164 10,037.283 ↓ 12.0 323 1

Subquery Scan on t (cost=45,507,975.75..45,508,972.88 rows=27 width=336) (actual time=9,204.237..10,037.283 rows=323 loops=1)

  • Buffers: shared hit=1,172,145 read=14,299
56. 124.883 10,037.119 ↓ 12.0 323 1

GroupAggregate (cost=45,507,975.75..45,508,972.61 rows=27 width=368) (actual time=9,204.233..10,037.119 rows=323 loops=1)

  • Group Key: pfm.cd_pessoa_matriz
  • Buffers: shared hit=1,172,145 read=14,299
57. 33.325 9,119.273 ↓ 697.9 18,842 1

Sort (cost=45,507,975.75..45,507,975.82 rows=27 width=250) (actual time=9,115.048..9,119.273 rows=18,842 loops=1)

  • Sort Key: pfm.cd_pessoa_matriz
  • Sort Method: quicksort Memory: 3,418kB
  • Buffers: shared hit=1,042,894 read=14,260
58. 34.653 9,085.948 ↓ 697.9 18,842 1

Nested Loop Left Join (cost=30,125.26..45,507,975.11 rows=27 width=250) (actual time=8,639.892..9,085.948 rows=18,842 loops=1)

  • Buffers: shared hit=1,042,894 read=14,260
59. 15.446 8,712.139 ↓ 697.9 18,842 1

Merge Left Join (cost=30,124.84..45,507,962.67 rows=27 width=244) (actual time=8,639.852..8,712.139 rows=18,842 loops=1)

  • Merge Cond: (ct.nr_embarque = ct_3.nr_embarque)
  • Buffers: shared hit=967,622 read=14,134
60. 28.664 3,646.719 ↓ 697.9 18,842 1

Sort (cost=30,124.41..30,124.48 rows=27 width=180) (actual time=3,632.267..3,646.719 rows=18,842 loops=1)

  • Sort Key: ct.nr_embarque
  • Sort Method: quicksort Memory: 3,418kB
  • Buffers: shared hit=828,874 read=1,022
61. 10.503 3,618.055 ↓ 697.9 18,842 1

Hash Join (cost=30,070.72..30,123.77 rows=27 width=180) (actual time=3,420.766..3,618.055 rows=18,842 loops=1)

  • Hash Cond: (cd.cd_uf = ud.cd_uf)
  • Buffers: shared hit=828,874 read=1,022
62. 15.687 3,607.488 ↓ 697.9 18,842 1

Nested Loop (cost=30,068.48..30,121.46 rows=27 width=184) (actual time=3,420.683..3,607.488 rows=18,842 loops=1)

  • Buffers: shared hit=828,873 read=1,022
63. 12.713 3,535.275 ↓ 697.9 18,842 1

Hash Join (cost=30,068.20..30,113.36 rows=27 width=184) (actual time=3,420.669..3,535.275 rows=18,842 loops=1)

  • Hash Cond: (co.cd_uf = uo.cd_uf)
  • Buffers: shared hit=772,347 read=1,022
64. 28.304 3,522.531 ↓ 697.9 18,842 1

Nested Loop (cost=30,065.96..30,111.05 rows=27 width=188) (actual time=3,420.619..3,522.531 rows=18,842 loops=1)

  • Buffers: shared hit=772,346 read=1,022
65. 16.580 3,437.701 ↓ 697.9 18,842 1

Hash Join (cost=30,065.68..30,102.95 rows=27 width=188) (actual time=3,420.599..3,437.701 rows=18,842 loops=1)

  • Hash Cond: (ct_2.cd_pessoa_pagador = ct.cd_pessoa_pagador)
  • Buffers: shared hit=715,820 read=1,022
66. 18.608 523.138 ↑ 2.0 763 1

HashAggregate (cost=17,695.60..17,711.18 rows=1,558 width=8) (actual time=522.595..523.138 rows=763 loops=1)

  • Group Key: ct_2.cd_pessoa_pagador
  • Buffers: shared hit=199,698
67. 21.113 504.530 ↓ 3.0 18,842 1

Nested Loop Left Join (cost=1.28..17,601.64 rows=6,264 width=12) (actual time=0.111..504.530 rows=18,842 loops=1)

  • Buffers: shared hit=199,698
68. 14.421 332.681 ↓ 3.3 18,842 1

Nested Loop Left Join (cost=0.85..14,703.83 rows=5,764 width=8) (actual time=0.081..332.681 rows=18,842 loops=1)

  • Buffers: shared hit=134,435
69. 27.111 205.208 ↓ 3.3 18,842 1

Nested Loop (cost=0.43..12,122.66 rows=5,764 width=8) (actual time=0.053..205.208 rows=18,842 loops=1)

  • Buffers: shared hit=75,982
70. 8.519 8.519 ↓ 3.3 18,842 1

CTE Scan on with_dados_titulo wdt_2 (cost=0.00..115.28 rows=5,764 width=4) (actual time=0.025..8.519 rows=18,842 loops=1)

71. 169.578 169.578 ↑ 1.0 1 18,842

Index Scan using pk_ctrc on ctrc ct_2 (cost=0.43..2.08 rows=1 width=12) (actual time=0.009..0.009 rows=1 loops=18,842)

  • Index Cond: (cd_ctrc = wdt_2.cd_ctrc)
  • Buffers: shared hit=75,982
72. 113.052 113.052 ↑ 1.0 1 18,842

Index Only Scan using pk_movimento on movimento m (cost=0.43..0.45 rows=1 width=4) (actual time=0.006..0.006 rows=1 loops=18,842)

  • Index Cond: (cd_movimento = ct_2.cd_movimento)
  • Heap Fetches: 10,687
  • Buffers: shared hit=58,453
73. 150.736 150.736 ↑ 2.0 1 18,842

Index Scan using idx_titulo_cd_movimento on titulo t_1 (cost=0.43..0.48 rows=2 width=12) (actual time=0.008..0.008 rows=1 loops=18,842)

  • Index Cond: (cd_movimento = m.cd_movimento)
  • Buffers: shared hit=65,263
74. 45.700 2,897.983 ↓ 697.9 18,842 1

Hash (cost=12,369.74..12,369.74 rows=27 width=196) (actual time=2,897.983..2,897.983 rows=18,842 loops=1)

  • Buckets: 32,768 (originally 1024) Batches: 1 (originally 1) Memory Usage: 2,642kB
  • Buffers: shared hit=516,122 read=1,022
75. 23.280 2,852.283 ↓ 697.9 18,842 1

Nested Loop (cost=12,195.98..12,369.74 rows=27 width=196) (actual time=1,990.559..2,852.283 rows=18,842 loops=1)

  • Buffers: shared hit=516,122 read=1,022
76. 28.496 2,565.215 ↓ 697.9 18,842 1

Nested Loop (cost=12,195.56..12,357.91 rows=27 width=192) (actual time=1,990.501..2,565.215 rows=18,842 loops=1)

  • Buffers: shared hit=427,736 read=1,021
77. 32.477 2,310.615 ↓ 697.9 18,842 1

Nested Loop (cost=12,195.14..12,345.82 rows=27 width=184) (actual time=1,990.432..2,310.615 rows=18,842 loops=1)

  • Join Filter: (wdt_1.cd_ctrc = ct.cd_ctrc)
  • Buffers: shared hit=364,904 read=971
78. 16.135 2,014.350 ↓ 697.9 18,842 1

Hash Join (cost=12,194.71..12,331.88 rows=27 width=146) (actual time=1,990.395..2,014.350 rows=18,842 loops=1)

  • Hash Cond: (wdt_1.cd_ctrc = ct_1.cd_ctrc)
  • Buffers: shared hit=288,922 read=971
79. 1,766.878 1,766.878 ↓ 3.3 18,842 1

CTE Scan on with_dados_titulo wdt_1 (cost=0.00..115.28 rows=5,764 width=68) (actual time=1,758.970..1,766.878 rows=18,842 loops=1)

  • Buffers: shared hit=212,940 read=971
80. 19.611 231.337 ↓ 3.3 18,842 1

Hash (cost=12,122.66..12,122.66 rows=5,764 width=78) (actual time=231.337..231.337 rows=18,842 loops=1)

  • Buckets: 32,768 (originally 8192) Batches: 1 (originally 1) Memory Usage: 1,314kB
  • Buffers: shared hit=75,982
81. 17.198 211.726 ↓ 3.3 18,842 1

Nested Loop (cost=0.43..12,122.66 rows=5,764 width=78) (actual time=0.060..211.726 rows=18,842 loops=1)

  • Buffers: shared hit=75,982
82. 43.792 43.792 ↓ 3.3 18,842 1

CTE Scan on with_dados_titulo wdt (cost=0.00..115.28 rows=5,764 width=68) (actual time=0.010..43.792 rows=18,842 loops=1)

83. 150.736 150.736 ↑ 1.0 1 18,842

Index Scan using pk_ctrc on ctrc ct_1 (cost=0.43..2.08 rows=1 width=10) (actual time=0.008..0.008 rows=1 loops=18,842)

  • Index Cond: (cd_ctrc = wdt.cd_ctrc)
  • Buffers: shared hit=75,982
84. 263.788 263.788 ↑ 1.0 1 18,842

Index Scan using pk_ctrc on ctrc ct (cost=0.43..0.50 rows=1 width=50) (actual time=0.014..0.014 rows=1 loops=18,842)

  • Index Cond: (cd_ctrc = ct_1.cd_ctrc)
  • Buffers: shared hit=75,982
85. 226.104 226.104 ↑ 1.0 1 18,842

Index Only Scan using pk_pessoa_filial_matriz on pessoa_filial_matriz pfm (cost=0.42..0.44 rows=1 width=8) (actual time=0.011..0.012 rows=1 loops=18,842)

  • Index Cond: (cd_pessoa = ct.cd_pessoa_pagador)
  • Heap Fetches: 6,355
  • Buffers: shared hit=62,832 read=50
86. 263.788 263.788 ↑ 1.0 1 18,842

Index Only Scan using pk_pessoa on pessoa p_1 (cost=0.42..0.44 rows=1 width=4) (actual time=0.014..0.014 rows=1 loops=18,842)

  • Index Cond: (cd_pessoa = ct.cd_pessoa_pagador)
  • Heap Fetches: 31,865
  • Buffers: shared hit=88,386 read=1
87. 56.526 56.526 ↑ 1.0 1 18,842

Index Scan using pk_cidade on cidade co (cost=0.28..0.30 rows=1 width=8) (actual time=0.003..0.003 rows=1 loops=18,842)

  • Index Cond: (cd_cidade = ct.cd_cidade_origem)
  • Buffers: shared hit=56,526
88. 0.017 0.031 ↑ 1.0 55 1

Hash (cost=1.55..1.55 rows=55 width=4) (actual time=0.031..0.031 rows=55 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 10kB
  • Buffers: shared hit=1
89. 0.014 0.014 ↑ 1.0 55 1

Seq Scan on uf uo (cost=0.00..1.55 rows=55 width=4) (actual time=0.007..0.014 rows=55 loops=1)

  • Buffers: shared hit=1
90. 56.526 56.526 ↑ 1.0 1 18,842

Index Scan using pk_cidade on cidade cd (cost=0.28..0.30 rows=1 width=8) (actual time=0.003..0.003 rows=1 loops=18,842)

  • Index Cond: (cd_cidade = ct.cd_cidade_destino)
  • Buffers: shared hit=56,526
91. 0.038 0.064 ↑ 1.0 55 1

Hash (cost=1.55..1.55 rows=55 width=4) (actual time=0.064..0.064 rows=55 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 10kB
  • Buffers: shared hit=1
92. 0.026 0.026 ↑ 1.0 55 1

Seq Scan on uf ud (cost=0.00..1.55 rows=55 width=4) (actual time=0.020..0.026 rows=55 loops=1)

  • Buffers: shared hit=1
93. 117.507 5,049.974 ↓ 1.3 7,291 1

GroupAggregate (cost=0.43..45,477,766.32 rows=5,744 width=68) (actual time=0.266..5,049.974 rows=7,291 loops=1)

  • Group Key: ct_3.nr_embarque
  • Buffers: shared hit=138,748 read=13,112
94. 2,117.369 2,117.369 ↑ 62.6 19,687 1

Index Scan using idx_ctrc_nr_embarque on ctrc ct_3 (cost=0.43..85,449.35 rows=1,232,376 width=20) (actual time=0.070..2,117.369 rows=19,687 loops=1)

  • Buffers: shared hit=4,834 read=8,893
95.          

SubPlan (for GroupAggregate)

96. 19.686 2,637.924 ↑ 1.0 1 19,686

Aggregate (cost=28.99..29.00 rows=1 width=32) (actual time=0.134..0.134 rows=1 loops=19,686)

  • Buffers: shared hit=74,835 read=4,194
97. 78.744 2,618.238 ↓ 0.0 0 19,686

Nested Loop (cost=0.57..28.99 rows=1 width=18) (actual time=0.133..0.133 rows=0 loops=19,686)

  • Join Filter: (c_1.cd_ctrc_produto = cp_1.cd_ctrc_produto)
  • Buffers: shared hit=74,835 read=4,194
98. 2,539.494 2,539.494 ↑ 1.0 1 19,686

Index Scan using idx_ctrc_produto_cd_ctrc on ctrc_produto cp_1 (cost=0.43..2.65 rows=1 width=4) (actual time=0.127..0.129 rows=1 loops=19,686)

  • Index Cond: (cd_ctrc = ct_3.cd_ctrc)
  • Buffers: shared hit=74,835 read=4,194
99. 0.000 0.000 ↓ 0.0 0 24,031

Nested Loop (cost=0.14..26.29 rows=4 width=22) (actual time=0.000..0.000 rows=0 loops=24,031)

100. 0.000 0.000 ↓ 0.0 0 24,031

Seq Scan on coleta_despesa cd_2 (cost=0.00..20.12 rows=4 width=22) (actual time=0.000..0.000 rows=0 loops=24,031)

  • Filter: (id_pago = 0)
101. 0.000 0.000 ↓ 0.0 0

Index Scan using pk_coleta on coleta c_1 (cost=0.14..1.54 rows=1 width=8) (never executed)

  • Index Cond: (cd_coleta = cd_2.cd_coleta)
102. 19.686 177.174 ↓ 0.0 0 19,686

Limit (cost=7.82..7.82 rows=1 width=36) (actual time=0.009..0.009 rows=0 loops=19,686)

  • Buffers: shared hit=59,079 read=25
103. 59.058 157.488 ↓ 0.0 0 19,686

Sort (cost=7.82..7.82 rows=1 width=36) (actual time=0.008..0.008 rows=0 loops=19,686)

  • Sort Key: cf__1.cd_ctrc DESC
  • Sort Method: quicksort Memory: 25kB
  • Buffers: shared hit=59,079 read=25
104. 0.000 98.430 ↓ 0.0 0 19,686

Nested Loop (cost=1.14..7.81 rows=1 width=36) (actual time=0.005..0.005 rows=0 loops=19,686)

  • Buffers: shared hit=59,079 read=25
105. 19.686 98.430 ↓ 0.0 0 19,686

Nested Loop (cost=0.85..5.29 rows=1 width=19) (actual time=0.005..0.005 rows=0 loops=19,686)

  • Buffers: shared hit=59,079 read=25
106. 78.744 78.744 ↓ 0.0 0 19,686

Index Scan using idx_ctrc_cd_ctrc_novo on ctrc c__1 (cost=0.43..2.65 rows=1 width=12) (actual time=0.004..0.004 rows=0 loops=19,686)

  • Index Cond: (cd_ctrc_novo = ct_3.cd_ctrc)
  • Filter: (id_operacao = 3)
  • Rows Removed by Filter: 0
  • Buffers: shared hit=59,079 read=25
107. 0.000 0.000 ↓ 0.0 0

Index Scan using pk_carta_frete on carta_frete cf__1 (cost=0.43..2.65 rows=1 width=11) (never executed)

  • Index Cond: (cd_ctrc = c__1.cd_ctrc)
108. 0.000 0.000 ↓ 0.0 0

Index Only Scan using pk_cliente on cliente cl__1 (cost=0.29..2.51 rows=1 width=4) (never executed)

  • Index Cond: (cd_pessoa = c__1.cd_pessoa_pagador)
  • Heap Fetches: 0
109. 339.156 339.156 ↑ 1.0 1 18,842

Index Scan using pk_ctrc_comissao on ctrc_comissao cc (cost=0.43..0.46 rows=1 width=10) (actual time=0.018..0.018 rows=1 loops=18,842)

  • Index Cond: (cd_ctrc = ct.cd_ctrc)
  • Buffers: shared hit=75,272 read=126
110.          

SubPlan (for GroupAggregate)

111. 36.882 571.671 ↑ 1.0 1 18,441

Aggregate (cost=28.99..29.00 rows=1 width=32) (actual time=0.030..0.031 rows=1 loops=18,441)

  • Buffers: shared hit=73,784 read=23
112. 147.528 534.789 ↓ 0.0 0 18,441

Nested Loop (cost=0.57..28.99 rows=1 width=18) (actual time=0.029..0.029 rows=0 loops=18,441)

  • Join Filter: (c.cd_ctrc_produto = cp.cd_ctrc_produto)
  • Buffers: shared hit=73,784 read=23
113. 387.261 387.261 ↑ 1.0 1 18,441

Index Scan using idx_ctrc_produto_cd_ctrc on ctrc_produto cp (cost=0.43..2.65 rows=1 width=4) (actual time=0.021..0.021 rows=1 loops=18,441)

  • Index Cond: (cd_ctrc = ct.cd_ctrc)
  • Buffers: shared hit=73,784 read=23
114. 0.000 0.000 ↓ 0.0 0 19,885

Nested Loop (cost=0.14..26.29 rows=4 width=22) (actual time=0.000..0.000 rows=0 loops=19,885)

115. 0.000 0.000 ↓ 0.0 0 19,885

Seq Scan on coleta_despesa cd_1 (cost=0.00..20.12 rows=4 width=22) (actual time=0.000..0.000 rows=0 loops=19,885)

  • Filter: (id_pago = 0)
116. 0.000 0.000 ↓ 0.0 0

Index Scan using pk_coleta on coleta c (cost=0.14..1.54 rows=1 width=8) (never executed)

  • Index Cond: (cd_coleta = cd_1.cd_coleta)
117. 18.441 221.292 ↓ 0.0 0 18,441

Limit (cost=7.82..7.82 rows=1 width=36) (actual time=0.012..0.012 rows=0 loops=18,441)

  • Buffers: shared hit=55,467 read=16
118. 36.882 202.851 ↓ 0.0 0 18,441

Sort (cost=7.82..7.82 rows=1 width=36) (actual time=0.011..0.011 rows=0 loops=18,441)

  • Sort Key: cf_.cd_ctrc DESC
  • Sort Method: quicksort Memory: 25kB
  • Buffers: shared hit=55,467 read=16
119. 0.000 165.969 ↓ 0.0 0 18,441

Nested Loop (cost=1.14..7.81 rows=1 width=36) (actual time=0.009..0.009 rows=0 loops=18,441)

  • Buffers: shared hit=55,467 read=16
120. 18.441 165.969 ↓ 0.0 0 18,441

Nested Loop (cost=0.85..5.29 rows=1 width=19) (actual time=0.009..0.009 rows=0 loops=18,441)

  • Buffers: shared hit=55,467 read=16
121. 147.528 147.528 ↓ 0.0 0 18,441

Index Scan using idx_ctrc_cd_ctrc_novo on ctrc c_ (cost=0.43..2.65 rows=1 width=12) (actual time=0.008..0.008 rows=0 loops=18,441)

  • Index Cond: (cd_ctrc_novo = ct.cd_ctrc)
  • Filter: (id_operacao = 3)
  • Rows Removed by Filter: 0
  • Buffers: shared hit=55,467 read=16
122. 0.000 0.000 ↓ 0.0 0

Index Scan using pk_carta_frete on carta_frete cf_ (cost=0.43..2.65 rows=1 width=11) (never executed)

  • Index Cond: (cd_ctrc = c_.cd_ctrc)
123. 0.000 0.000 ↓ 0.0 0

Index Only Scan using pk_cliente on cliente cl_ (cost=0.29..2.51 rows=1 width=4) (never executed)

  • Index Cond: (cd_pessoa = c_.cd_pessoa_pagador)
  • Heap Fetches: 0
124. 0.018 0.041 ↑ 1.0 55 1

Hash (cost=1.55..1.55 rows=55 width=7) (actual time=0.041..0.041 rows=55 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 11kB
  • Buffers: shared hit=1
125. 0.023 0.023 ↑ 1.0 55 1

Seq Scan on uf u (cost=0.00..1.55 rows=55 width=7) (actual time=0.014..0.023 rows=55 loops=1)

  • Buffers: shared hit=1