explain.depesz.com

PostgreSQL's explain analyze made readable

Result: t8GH

Settings
# exclusive inclusive rows x rows loops node
1. 5,941.963 51,622.612 ↑ 1.0 1,834,049 1

Finalize GroupAggregate (cost=867,140.06..1,434,158.82 rows=1,851,868 width=505) (actual time=38,982.894..51,622.612 rows=1,834,049 loops=1)

  • Group Key: c.contacorrente, c.pessoa, c.matriculaaluno, c.centroreceita, c.parcela, c.nrdocumento, c.multa, c.juro, c.valordesconto, c.valor, c.datavencimento, c.situacao, c.tipoorigem, (CASE WHEN ((c.tipoorigem)::text = 'OUT'::text) THEN 'OUTROS'::text WHEN ((c.tipoorigem)::text = 'MDI'::text) THEN 'MATERIAL DIDATICO'::text WHEN ((c.tipoorigem)::text = 'MAT'::text) THEN 'MATRICULA'::text WHEN ((c.tipoorigem)::text = 'MEN'::text) THEN 'MENSALIDADE'::text WHEN ((c.tipoorigem)::text = 'BCC'::text) THEN 'BOLSA CUSTEADA'::text WHEN ((c.tipoorigem)::text = 'NCR'::text) THEN 'NEGOCIACAO'::text WHEN ((c.tipoorigem)::text = 'IRE'::text) THEN 'REQUERIMENTO'::text WHEN ((c.tipoorigem)::text = 'REQ'::text) THEN 'REQUERIMENTO'::text ELSE 'CLASSIFICAR'::text END), c.codorigem, c.data, c.tipopessoa, c.valorrecebido, c.tipodesconto, c.unidadeensino, c.valordescontorecebido, c.descontoinstituicao, c.descontoconvenio, c.convenio, c.tipoboleto, c.turma, c.dataprocessamentovalorreceber, c.valorrecebercalculado, c.valorjurocalculado, c.valormultacalculado, c.valorbasecontareceber, c.datacompetencia, c.unidadeensinofinanceira, c.valordescontocalculado, c.tipoparcelaimportacao, r.codigo, n.codigo, p.nome, c.codigoant, tr.identificadorturma, ((rb.datarecebimento)::date), fp.nome, cr.descricao, dp.nome, rb.tiporecebimento, ps.codigo, ps4.nome, n2.codigo
2. 3,096.917 45,680.649 ↓ 1.2 1,834,049 1

Gather Merge (cost=867,140.06..1,172,582.38 rows=1,543,224 width=501) (actual time=38,982.866..45,680.649 rows=1,834,049 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
3. 2,408.469 42,583.732 ↑ 1.3 611,350 3 / 3

Partial GroupAggregate (cost=866,140.03..993,456.01 rows=771,612 width=501) (actual time=38,390.980..42,583.732 rows=611,350 loops=3)

  • Group Key: c.contacorrente, c.pessoa, c.matriculaaluno, c.centroreceita, c.parcela, c.nrdocumento, c.multa, c.juro, c.valordesconto, c.valor, c.datavencimento, c.situacao, c.tipoorigem, (CASE WHEN ((c.tipoorigem)::text = 'OUT'::text) THEN 'OUTROS'::text WHEN ((c.tipoorigem)::text = 'MDI'::text) THEN 'MATERIAL DIDATICO'::text WHEN ((c.tipoorigem)::text = 'MAT'::text) THEN 'MATRICULA'::text WHEN ((c.tipoorigem)::text = 'MEN'::text) THEN 'MENSALIDADE'::text WHEN ((c.tipoorigem)::text = 'BCC'::text) THEN 'BOLSA CUSTEADA'::text WHEN ((c.tipoorigem)::text = 'NCR'::text) THEN 'NEGOCIACAO'::text WHEN ((c.tipoorigem)::text = 'IRE'::text) THEN 'REQUERIMENTO'::text WHEN ((c.tipoorigem)::text = 'REQ'::text) THEN 'REQUERIMENTO'::text ELSE 'CLASSIFICAR'::text END), c.codorigem, c.data, c.tipopessoa, c.valorrecebido, c.tipodesconto, c.unidadeensino, c.valordescontorecebido, c.descontoinstituicao, c.descontoconvenio, c.convenio, c.tipoboleto, c.turma, c.dataprocessamentovalorreceber, c.valorrecebercalculado, c.valorjurocalculado, c.valormultacalculado, c.valorbasecontareceber, c.datacompetencia, c.unidadeensinofinanceira, c.valordescontocalculado, c.tipoparcelaimportacao, r.codigo, n.codigo, p.nome, c.codigoant, tr.identificadorturma, ((rb.datarecebimento)::date), fp.nome, cr.descricao, dp.nome, rb.tiporecebimento, ps.codigo, ps4.nome, n2.codigo
4. 13,020.237 40,175.263 ↑ 1.3 617,123 3 / 3

Sort (cost=866,140.03..868,069.06 rows=771,612 width=478) (actual time=38,390.933..40,175.263 rows=617,123 loops=3)

  • Sort Key: c.contacorrente, c.pessoa, c.matriculaaluno, c.centroreceita, c.parcela, c.nrdocumento, c.multa, c.juro, c.valordesconto, c.valor, c.datavencimento, c.situacao, c.tipoorigem, (CASE WHEN ((c.tipoorigem)::text = 'OUT'::text) THEN 'OUTROS'::text WHEN ((c.tipoorigem)::text = 'MDI'::text) THEN 'MATERIAL DIDATICO'::text WHEN ((c.tipoorigem)::text = 'MAT'::text) THEN 'MATRICULA'::text WHEN ((c.tipoorigem)::text = 'MEN'::text) THEN 'MENSALIDADE'::text WHEN ((c.tipoorigem)::text = 'BCC'::text) THEN 'BOLSA CUSTEADA'::text WHEN ((c.tipoorigem)::text = 'NCR'::text) THEN 'NEGOCIACAO'::text WHEN ((c.tipoorigem)::text = 'IRE'::text) THEN 'REQUERIMENTO'::text WHEN ((c.tipoorigem)::text = 'REQ'::text) THEN 'REQUERIMENTO'::text ELSE 'CLASSIFICAR'::text END), c.codorigem, c.data, c.tipopessoa, c.valorrecebido, c.tipodesconto, c.unidadeensino, c.valordescontorecebido, c.descontoinstituicao, c.descontoconvenio, c.convenio, c.tipoboleto, c.turma, c.dataprocessamentovalorreceber, c.valorrecebercalculado, c.valorjurocalculado, c.valormultacalculado, c.valorbasecontareceber, c.datacompetencia, c.unidadeensinofinanceira, c.valordescontocalculado, c.tipoparcelaimportacao, r.codigo, n.codigo, p.nome, c.codigoant, tr.identificadorturma, ((rb.datarecebimento)::date), fp.nome, cr.descricao, dp.nome, rb.tiporecebimento, ps.codigo, ps4.nome, n2.codigo
  • Sort Method: external merge Disk: 194096kB
  • Worker 0: Sort Method: external merge Disk: 197112kB
  • Worker 1: Sort Method: external merge Disk: 185472kB
5. 716.287 27,155.026 ↑ 1.3 617,123 3 / 3

Parallel Hash Left Join (cost=580,573.31..624,530.46 rows=771,612 width=478) (actual time=23,787.639..27,155.026 rows=617,123 loops=3)

  • Hash Cond: (n2.pessoacomissionada = ps4.codigo)
6. 563.098 24,682.033 ↑ 1.3 617,123 3 / 3

Parallel Hash Left Join (cost=563,668.31..588,238.69 rows=771,612 width=417) (actual time=22,029.218..24,682.033 rows=617,123 loops=3)

  • Hash Cond: (c.pessoa = ps.codigo)
7. 518.298 23,962.191 ↑ 1.3 617,123 3 / 3

Hash Left Join (cost=546,763.31..569,308.19 rows=771,612 width=388) (actual time=21,869.733..23,962.191 rows=617,123 loops=3)

  • Hash Cond: (cr.departamento = dp.codigo)
8. 985.019 23,443.801 ↑ 1.3 617,123 3 / 3

Merge Left Join (cost=546,760.11..567,191.90 rows=771,612 width=375) (actual time=21,869.605..23,443.801 rows=617,123 loops=3)

  • Merge Cond: ((c.codorigem)::text = (((n2.codigo)::character varying)::text))
  • Join Filter: ((c.tipoorigem)::text = 'NCR'::text)
  • Rows Removed by Join Filter: 20910
9. 4,597.292 22,058.331 ↑ 1.3 617,123 3 / 3

Sort (cost=536,438.64..538,367.67 rows=771,612 width=367) (actual time=21,486.822..22,058.331 rows=617,123 loops=3)

  • Sort Key: c.codorigem
  • Sort Method: external merge Disk: 151056kB
  • Worker 0: Sort Method: external merge Disk: 153408kB
  • Worker 1: Sort Method: external merge Disk: 144352kB
10. 507.675 17,461.039 ↑ 1.3 617,123 3 / 3

Hash Left Join (cost=67,617.03..331,754.07 rows=771,612 width=367) (actual time=3,043.597..17,461.039 rows=617,123 loops=3)

  • Hash Cond: (c.centroreceita = cr.codigo)
11. 453.038 16,953.309 ↑ 1.3 617,123 3 / 3

Hash Left Join (cost=67,615.13..329,512.08 rows=771,612 width=342) (actual time=3,043.517..16,953.309 rows=617,123 loops=3)

  • Hash Cond: (rb.formapagamento = fp.codigo)
12. 513.276 16,484.115 ↑ 1.3 617,123 3 / 3

Hash Left Join (cost=67,613.82..326,872.13 rows=771,612 width=333) (actual time=3,027.333..16,484.115 rows=617,123 loops=3)

  • Hash Cond: (c.turma = tr.codigo)
13. 599.458 15,967.777 ↑ 1.3 617,123 3 / 3

Parallel Hash Left Join (cost=67,409.95..324,641.00 rows=771,612 width=324) (actual time=3,024.203..15,967.777 rows=617,123 loops=3)

  • Hash Cond: (c.codigo = r.contareceber)
14. 2,340.772 14,553.148 ↑ 1.3 617,123 3 / 3

Parallel Hash Join (cost=40,482.03..294,437.62 rows=771,612 width=189) (actual time=2,208.554..14,553.148 rows=617,123 loops=3)

  • Hash Cond: (c.codigo = rb.contareceber)
15. 10,006.961 10,006.961 ↑ 1.3 906,304 3 / 3

Parallel Seq Scan on contareceber c (cost=0.00..246,491.58 rows=1,133,058 width=169) (actual time=0.018..10,006.961 rows=906,304 loops=3)

16. 305.822 2,205.415 ↑ 1.3 617,123 3 / 3

Parallel Hash (cost=30,836.88..30,836.88 rows=771,612 width=24) (actual time=2,205.414..2,205.415 rows=617,123 loops=3)

  • Buckets: 2097152 Batches: 1 Memory Usage: 132384kB
17. 1,899.593 1,899.593 ↑ 1.3 617,123 3 / 3

Parallel Seq Scan on contareceberrecebimento rb (cost=0.00..30,836.88 rows=771,612 width=24) (actual time=21.324..1,899.593 rows=617,123 loops=3)

  • Filter: (formapagamentonegociacaorecebimento > 0)
  • Rows Removed by Filter: 1941
18. 73.412 815.171 ↑ 1.8 76,268 3 / 3

Parallel Hash (cost=25,245.54..25,245.54 rows=134,591 width=143) (actual time=815.171..815.171 rows=76,268 loops=3)

  • Buckets: 262144 Batches: 1 Memory Usage: 46304kB
19. 37.410 741.759 ↑ 1.8 76,268 3 / 3

Parallel Hash Left Join (cost=20,814.01..25,245.54 rows=134,591 width=143) (actual time=549.287..741.759 rows=76,268 loops=3)

  • Hash Cond: (n.responsavel = p.codigo)
20. 68.598 554.476 ↑ 1.8 76,268 3 / 3

Parallel Hash Left Join (cost=3,909.01..7,987.24 rows=134,591 width=118) (actual time=398.583..554.476 rows=76,268 loops=3)

  • Hash Cond: (r.negociacaocontareceber = n.codigo)
21. 103.940 103.940 ↑ 1.8 76,268 3 / 3

Parallel Seq Scan on contarecebernegociado r (cost=0.00..3,724.91 rows=134,591 width=22) (actual time=16.263..103.940 rows=76,268 loops=3)

22. 22.508 381.938 ↑ 1.8 27,276 3 / 3

Parallel Hash (cost=3,307.34..3,307.34 rows=48,134 width=96) (actual time=381.938..381.938 rows=27,276 loops=3)

  • Buckets: 131072 Batches: 1 Memory Usage: 12128kB
23. 359.430 359.430 ↑ 1.8 27,276 3 / 3

Parallel Seq Scan on negociacaocontareceber n (cost=0.00..3,307.34 rows=48,134 width=96) (actual time=15.519..359.430 rows=27,276 loops=3)

24. 63.242 149.873 ↑ 1.3 160,071 3 / 3

Parallel Hash (cost=14,403.89..14,403.89 rows=200,089 width=29) (actual time=149.873..149.873 rows=160,071 loops=3)

  • Buckets: 524288 Batches: 1 Memory Usage: 34944kB
25. 86.631 86.631 ↑ 1.3 160,071 3 / 3

Parallel Seq Scan on pessoa p (cost=0.00..14,403.89 rows=200,089 width=29) (actual time=0.022..86.631 rows=160,071 loops=3)

26. 1.174 3.062 ↑ 1.0 4,794 3 / 3

Hash (cost=143.94..143.94 rows=4,794 width=13) (actual time=3.062..3.062 rows=4,794 loops=3)

  • Buckets: 8192 Batches: 1 Memory Usage: 290kB
27. 1.888 1.888 ↑ 1.0 4,794 3 / 3

Seq Scan on turma tr (cost=0.00..143.94 rows=4,794 width=13) (actual time=0.025..1.888 rows=4,794 loops=3)

28. 0.017 16.156 ↑ 1.0 14 3 / 3

Hash (cost=1.14..1.14 rows=14 width=17) (actual time=16.155..16.156 rows=14 loops=3)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
29. 16.139 16.139 ↑ 1.0 14 3 / 3

Seq Scan on formapagamento fp (cost=0.00..1.14 rows=14 width=17) (actual time=16.134..16.139 rows=14 loops=3)

30. 0.021 0.055 ↑ 1.0 40 3 / 3

Hash (cost=1.40..1.40 rows=40 width=29) (actual time=0.055..0.055 rows=40 loops=3)

  • Buckets: 1024 Batches: 1 Memory Usage: 11kB
31. 0.034 0.034 ↑ 1.0 40 3 / 3

Seq Scan on centroreceita cr (cost=0.00..1.40 rows=40 width=29) (actual time=0.025..0.034 rows=40 loops=3)

32. 323.168 400.451 ↓ 1.4 116,750 3 / 3

Sort (cost=10,321.47..10,526.04 rows=81,827 width=8) (actual time=382.763..400.451 rows=116,750 loops=3)

  • Sort Key: (((n2.codigo)::character varying)::text)
  • Sort Method: quicksort Memory: 6908kB
  • Worker 0: Sort Method: quicksort Memory: 6908kB
  • Worker 1: Sort Method: quicksort Memory: 6908kB
33. 77.283 77.283 ↑ 1.0 81,827 3 / 3

Seq Scan on negociacaocontareceber n2 (cost=0.00..3,644.27 rows=81,827 width=8) (actual time=0.039..77.283 rows=81,827 loops=3)

34. 0.029 0.092 ↑ 1.0 98 3 / 3

Hash (cost=1.98..1.98 rows=98 width=21) (actual time=0.091..0.092 rows=98 loops=3)

  • Buckets: 1024 Batches: 1 Memory Usage: 14kB
35. 0.063 0.063 ↑ 1.0 98 3 / 3

Seq Scan on departamento dp (cost=0.00..1.98 rows=98 width=21) (actual time=0.041..0.063 rows=98 loops=3)

36. 64.430 156.744 ↑ 1.3 160,071 3 / 3

Parallel Hash (cost=14,403.89..14,403.89 rows=200,089 width=29) (actual time=156.744..156.744 rows=160,071 loops=3)

  • Buckets: 524288 Batches: 1 Memory Usage: 34944kB
37. 92.314 92.314 ↑ 1.3 160,071 3 / 3

Parallel Seq Scan on pessoa ps (cost=0.00..14,403.89 rows=200,089 width=29) (actual time=0.026..92.314 rows=160,071 loops=3)

38. 73.957 1,756.706 ↑ 1.3 160,071 3 / 3

Parallel Hash (cost=14,403.89..14,403.89 rows=200,089 width=29) (actual time=1,756.706..1,756.706 rows=160,071 loops=3)

  • Buckets: 524288 Batches: 1 Memory Usage: 34944kB
39. 1,682.749 1,682.749 ↑ 1.3 160,071 3 / 3

Parallel Seq Scan on pessoa ps4 (cost=0.00..14,403.89 rows=200,089 width=29) (actual time=0.056..1,682.749 rows=160,071 loops=3)

Planning time : 447.469 ms
Execution time : 52,422.005 ms