explain.depesz.com

PostgreSQL's explain analyze made readable

Result: fBFH : teste

Settings
# exclusive inclusive rows x rows loops node
1. 3.431 451.955 ↓ 2.1 602 1

GroupAggregate (cost=54,500.44..54,510.42 rows=285 width=241) (actual time=448.042..451.955 rows=602 loops=1)

  • Group Key: (to_char(inad.data_pedido, 'YYYYMM'::text)), inst.codigo, cur.nome, cur.duracao_meses_titulo
2.          

CTE inadimplente

3. 15.245 380.696 ↑ 7.4 7,317 1

GroupAggregate (cost=45,294.26..48,614.78 rows=54,049 width=56) (actual time=359.262..380.696 rows=7,317 loops=1)

  • Group Key: p.instituicao, p.codigo
  • Filter: (count(p.codigo) > 1)
  • Rows Removed by Filter: 1599
4. 59.576 365.451 ↑ 2.7 65,945 1

Sort (cost=45,294.26..45,735.08 rows=176,327 width=22) (actual time=359.245..365.451 rows=65,945 loops=1)

  • Sort Key: p.instituicao, p.codigo
  • Sort Method: external merge Disk: 2104kB
5. 30.016 305.875 ↑ 2.7 65,945 1

Hash Join (cost=7,005.29..26,310.22 rows=176,327 width=22) (actual time=117.140..305.875 rows=65,945 loops=1)

  • Hash Cond: (par.codigo_forma_pagamento = forpag.codigo)
6. 158.895 158.895 ↑ 1.2 201,040 1

Seq Scan on parcela par (cost=0.00..16,671.12 rows=232,145 width=10) (actual time=0.010..158.895 rows=201,040 loops=1)

  • Filter: ((data_pagamento IS NULL) AND (data_vencimento < now()) AND ((status)::text = ANY ('{pago,atrasado,"não concluído",cancelado,"não confirmado",estornado,registrado,"não registrado"}'::text[])))
  • Rows Removed by Filter: 284311
7. 5.228 116.964 ↑ 1.4 22,257 1

Hash (cost=6,624.40..6,624.40 rows=30,471 width=20) (actual time=116.964..116.964 rows=22,257 loops=1)

  • Buckets: 32768 Batches: 1 Memory Usage: 1387kB
8. 12.841 111.736 ↑ 1.4 22,257 1

Hash Join (cost=3,897.19..6,624.40 rows=30,471 width=20) (actual time=64.585..111.736 rows=22,257 loops=1)

  • Hash Cond: (pag.codigo = forpag.codigo_pagamento)
9. 20.799 67.374 ↑ 1.0 55,372 1

Hash Join (cost=2,632.56..4,698.46 rows=57,055 width=20) (actual time=32.808..67.374 rows=55,372 loops=1)

  • Hash Cond: ((pag.codigo_pedido = p.codigo) AND (pag.instituicao = p.instituicao))
10. 14.006 14.006 ↑ 1.0 72,777 1

Seq Scan on pagamento pag (cost=0.00..1,672.95 rows=74,846 width=12) (actual time=0.008..14.006 rows=72,777 loops=1)

  • Filter: (tipo_pagamento = 1)
  • Rows Removed by Filter: 2460
11. 13.975 32.569 ↑ 1.0 55,372 1

Hash (cost=1,799.11..1,799.11 rows=55,563 width=16) (actual time=32.569..32.569 rows=55,372 loops=1)

  • Buckets: 65536 Batches: 1 Memory Usage: 3108kB
12. 18.594 18.594 ↑ 1.0 55,372 1

Seq Scan on pedido p (cost=0.00..1,799.11 rows=55,563 width=16) (actual time=0.007..18.594 rows=55,372 loops=1)

  • Filter: ((status)::text = 'wc-completed'::text)
  • Rows Removed by Filter: 17405
13. 19.033 31.521 ↑ 1.0 40,114 1

Hash (cost=763.17..763.17 rows=40,117 width=8) (actual time=31.521..31.521 rows=40,114 loops=1)

  • Buckets: 65536 Batches: 1 Memory Usage: 2079kB
14. 12.488 12.488 ↑ 1.0 40,114 1

Seq Scan on forma_pagamento forpag (cost=0.00..763.17 rows=40,117 width=8) (actual time=0.005..12.488 rows=40,114 loops=1)

15. 22.566 448.524 ↓ 24.9 7,104 1

Sort (cost=5,885.66..5,886.37 rows=285 width=245) (actual time=448.003..448.524 rows=7,104 loops=1)

  • Sort Key: (to_char(inad.data_pedido, 'YYYYMM'::text)), inst.codigo, cur.nome, cur.duracao_meses_titulo
  • Sort Method: quicksort Memory: 1309kB
16. 5.320 425.958 ↓ 24.9 7,104 1

Hash Join (cost=2,553.14..5,874.04 rows=285 width=245) (actual time=383.473..425.958 rows=7,104 loops=1)

  • Hash Cond: ((inad.instituicao = cur.instituicao) AND (ofert.codigo_curso = cur.codigo))
17. 2.483 420.225 ↓ 24.9 7,104 1

Hash Join (cost=2,519.19..5,837.88 rows=285 width=178) (actual time=383.042..420.225 rows=7,104 loops=1)

  • Hash Cond: ((inad.instituicao = ofert.instituicao) AND (pedofer.codigo_oferta = ofert.codigo) AND (pedofer.versao = ofert.versao))
18. 1.742 417.467 ↓ 24.9 7,104 1

Hash Join (cost=2,499.55..5,816.00 rows=285 width=178) (actual time=382.756..417.467 rows=7,104 loops=1)

  • Hash Cond: (inad.instituicao = inst.codigo)
19. 9.337 415.717 ↓ 24.9 7,104 1

Hash Join (cost=2,477.62..5,793.31 rows=285 width=56) (actual time=382.740..415.717 rows=7,104 loops=1)

  • Hash Cond: ((inad.instituicao = pedofer.instituicao) AND (inad.codigo_pedido = pedofer.codigo_pedido))
20. 383.274 383.274 ↑ 7.4 7,317 1

CTE Scan on inadimplente inad (cost=0.00..1,080.98 rows=54,049 width=48) (actual time=359.265..383.274 rows=7,317 loops=1)

21. 13.798 23.106 ↓ 1.0 68,372 1

Hash (cost=1,119.05..1,119.05 rows=68,305 width=16) (actual time=23.106..23.106 rows=68,372 loops=1)

  • Buckets: 131072 Batches: 2 Memory Usage: 2631kB
22. 9.308 9.308 ↓ 1.0 68,372 1

Seq Scan on pedido_oferta pedofer (cost=0.00..1,119.05 rows=68,305 width=16) (actual time=0.007..9.308 rows=68,372 loops=1)

23. 0.005 0.008 ↑ 265.0 2 1

Hash (cost=15.30..15.30 rows=530 width=122) (actual time=0.008..0.008 rows=2 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
24. 0.003 0.003 ↑ 265.0 2 1

Seq Scan on instituicao inst (cost=0.00..15.30 rows=530 width=122) (actual time=0.003..0.003 rows=2 loops=1)

25. 0.155 0.275 ↓ 1.1 522 1

Hash (cost=10.96..10.96 rows=496 width=16) (actual time=0.275..0.275 rows=522 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 33kB
26. 0.120 0.120 ↓ 1.1 522 1

Seq Scan on oferta ofert (cost=0.00..10.96 rows=496 width=16) (actual time=0.005..0.120 rows=522 loops=1)

27. 0.211 0.413 ↑ 1.0 558 1

Hash (cost=25.58..25.58 rows=558 width=59) (actual time=0.413..0.413 rows=558 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 59kB
28. 0.202 0.202 ↑ 1.0 558 1

Seq Scan on curso cur (cost=0.00..25.58 rows=558 width=59) (actual time=0.007..0.202 rows=558 loops=1)

Planning time : 11.451 ms
Execution time : 454.120 ms