explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 0PU6 : Inadimplência Pós - Base - Query

Settings

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 4.633 1,614.274 ↓ 2.1 595 1

GroupAggregate (cost=52,863.25..52,873.29 rows=287 width=241) (actual time=1,608.205..1,614.274 rows=595 loops=1)

  • Group Key: (to_char(inad.data_pedido, 'YYYYMM'::text)), inst.codigo, cur.nome, cur.duracao_meses_titulo
  • Buffers: shared hit=21636, temp read=437 written=436
2.          

CTE inadimplente

3. 112.117 1,301.728 ↑ 7.2 7,504 1

GroupAggregate (cost=45,453.16..48,781.29 rows=54,343 width=56) (actual time=1,169.371..1,301.728 rows=7,504 loops=1)

  • Group Key: p.instituicao, p.codigo
  • Filter: (count(p.codigo) > 1)
  • Buffers: shared hit=6719, temp read=267 written=268
4. 160.482 1,189.611 ↑ 2.6 66,953 1

Sort (cost=45,453.16..45,894.63 rows=176,590 width=22) (actual time=1,169.351..1,189.611 rows=66,953 loops=1)

  • Sort Key: p.instituicao, p.codigo
  • Sort Method: external merge Disk: 2136kB
  • Buffers: shared hit=6719, temp read=267 written=268
5. 249.414 1,029.129 ↑ 2.6 66,953 1

Hash Join (cost=7,029.07..26,440.8 rows=176,590 width=22) (actual time=363.85..1,029.129 rows=66,953 loops=1)

  • Buffers: shared hit=6719
6. 416.001 416.001 ↑ 1.1 201,953 1

Seq Scan on parcela par (cost=0..16,775.05 rows=232,208 width=10) (actual time=0.011..416.001 rows=201,953 loops=1)

  • Filter: ((par.data_pagamento IS NULL) AND (par.data_vencimento < now()) AND ((par.status)::text = ANY ('{pago,atrasado,"não concluído",cancelado,"não confirmado",estornado,registrado,"não registrado"}'::text[])))
  • Buffers: shared hit=4731
7. 35.728 363.714 ↑ 1.4 22,299 1

Hash (cost=6,644.97..6,644.97 rows=30,728 width=20) (actual time=363.714..363.714 rows=22,299 loops=1)

  • Buffers: shared hit=1988
8. 58.558 327.986 ↑ 1.4 22,299 1

Hash Join (cost=3,911.49..6,644.97 rows=30,728 width=20) (actual time=146.457..327.986 rows=22,299 loops=1)

  • Buffers: shared hit=1988
9. 72.324 215.377 ↑ 1.0 55,427 1

Hash Join (cost=2,639.36..4,708.07 rows=57,198 width=20) (actual time=92.184..215.377 rows=55,427 loops=1)

  • Buffers: shared hit=1625
10. 51.070 51.070 ↑ 1.0 72,836 1

Seq Scan on pagamento pag (cost=0..1,675.15 rows=74,961 width=12) (actual time=0.007..51.07 rows=72,836 loops=1)

  • Filter: (pag.tipo_pagamento = 1)
  • Buffers: shared hit=735
11. 36.620 91.983 ↑ 1.0 55,427 1

Hash (cost=1,803.2..1,803.2 rows=55,744 width=16) (actual time=91.983..91.983 rows=55,427 loops=1)

  • Buffers: shared hit=890
12. 55.363 55.363 ↑ 1.0 55,427 1

Seq Scan on pedido p (cost=0..1,803.2 rows=55,744 width=16) (actual time=0.007..55.363 rows=55,427 loops=1)

  • Filter: ((p.status)::text = 'wc-completed'::text)
  • Buffers: shared hit=890
13. 33.313 54.051 ↑ 1.0 40,169 1

Hash (cost=767.06..767.06 rows=40,406 width=8) (actual time=54.051..54.051 rows=40,169 loops=1)

  • Buffers: shared hit=363
14. 20.738 20.738 ↑ 1.0 40,169 1

Seq Scan on forma_pagamento forpag (cost=0..767.06 rows=40,406 width=8) (actual time=0.006..20.738 rows=40,169 loops=1)

  • Buffers: shared hit=363
15. 75.733 1,609.641 ↓ 25.2 7,235 1

Sort (cost=4,081.95..4,082.67 rows=287 width=245) (actual time=1,608.177..1,609.641 rows=7,235 loops=1)

  • Sort Key: (to_char(inad.data_pedido, 'YYYYMM'::text)), inst.codigo, cur.nome, cur.duracao_meses_titulo
  • Sort Method: quicksort Memory: 1326kB
  • Buffers: shared hit=21636, temp read=437 written=436
16. 39.005 1,533.908 ↓ 25.2 7,235 1

Hash Join (cost=2,112.72..4,070.23 rows=287 width=245) (actual time=1,279.152..1,533.908 rows=7,235 loops=1)

  • Buffers: shared hit=21636, temp read=437 written=436
17. 5.750 1,494.379 ↓ 25.2 7,235 1

Hash Join (cost=2,075.77..4,031.06 rows=287 width=178) (actual time=1,278.605..1,494.379 rows=7,235 loops=1)

  • Buffers: shared hit=21613, temp read=437 written=436
18. 11.705 1,488.260 ↓ 25.2 7,235 1

Nested Loop (cost=2,054.72..4,007.76 rows=287 width=178) (actual time=1,278.22..1,488.26 rows=7,235 loops=1)

  • Buffers: shared hit=21607, temp read=437 written=436
19. 24.286 1,454.850 ↓ 25.2 7,235 1

Hash Join (cost=2,054.57..3,959.45 rows=287 width=56) (actual time=1,278.202..1,454.85 rows=7,235 loops=1)

  • Buffers: shared hit=7137, temp read=437 written=436
20. 1,321.928 1,321.928 ↑ 7.2 7,504 1

CTE Scan on inadimplente inad (cost=0..1,086.86 rows=54,343 width=48) (actual time=1,169.375..1,321.928 rows=7,504 loops=1)

  • Buffers: shared hit=6719, temp read=267 written=268
21. 66.609 108.636 ↓ 1.0 65,542 1

Hash (cost=1,072.63..1,072.63 rows=65,463 width=16) (actual time=108.636..108.636 rows=65,542 loops=1)

  • Buffers: shared hit=418, temp written=143
22. 42.027 42.027 ↓ 1.0 65,542 1

Seq Scan on pedido_oferta pedofer (cost=0..1,072.63 rows=65,463 width=16) (actual time=0.011..42.027 rows=65,542 loops=1)

  • Buffers: shared hit=418
23. 21.705 21.705 ↑ 1.0 1 7,235

Index Scan using pk_instituicao on instituicao inst (cost=0.15..0.17 rows=1 width=122) (actual time=0.003..0.003 rows=1 loops=7,235)

  • Index Cond: (inst.codigo = inad.instituicao)
  • Buffers: shared hit=14470
24. 0.195 0.369 ↑ 1.1 507 1

Hash (cost=11.47..11.47 rows=547 width=16) (actual time=0.369..0.369 rows=507 loops=1)

  • Buffers: shared hit=6
25. 0.174 0.174 ↑ 1.1 507 1

Seq Scan on oferta ofert (cost=0..11.47 rows=547 width=16) (actual time=0.007..0.174 rows=507 loops=1)

  • Buffers: shared hit=6
26. 0.260 0.524 ↑ 1.0 558 1

Hash (cost=28.58..28.58 rows=558 width=59) (actual time=0.524..0.524 rows=558 loops=1)

  • Buffers: shared hit=23
27. 0.264 0.264 ↑ 1.0 558 1

Seq Scan on curso cur (cost=0..28.58 rows=558 width=59) (actual time=0.012..0.264 rows=558 loops=1)

  • Buffers: shared hit=23
Planning time : 2.59 ms
Execution time : 1,698.665 ms