explain.depesz.com

PostgreSQL's explain analyze made readable

Result: kuGd : Inadimplência - Parcelas Pagos x Parcelas Não Pagos - gráfico

Settings
# exclusive inclusive rows x rows loops node
1. 0.097 26,169.262 ↑ 67.0 45 1

Sort (cost=251,401.54..251,409.07 rows=3,013 width=278) (actual time=26,169.253..26,169.262 rows=45 loops=1)

  • Sort Key: inad.data_pedido
  • Sort Method: quicksort Memory: 29kB
  • Buffers: shared hit=1352178, temp read=4228 written=4225
2.          

CTE inadimplente

3. 62.348 26,032.864 ↑ 15.0 20,423 1

Sort (cost=214,155.33..214,921.97 rows=306,658 width=230) (actual time=26,016.195..26,032.864 rows=20,423 loops=1)

  • Sort Key: ((to_date(to_char(now(), 'YYYYMMDD'::text), 'YYYYMMDD'::text) - to_date(to_char(min(parc.data_vencimento), 'YYYYMMDD'::text), 'YYYYMMDD'::text)))
  • Sort Method: quicksort Memory: 2364kB
  • Buffers: shared hit=1352178, temp read=4228 written=4225
4. 1,497.356 25,970.516 ↑ 15.0 20,423 1

GroupAggregate (cost=100,721.69..119,121.17 rows=306,658 width=230) (actual time=24,091.232..25,970.516 rows=20,423 loops=1)

  • Group Key: (to_char(ped.data_pedido, 'YYYY-MM'::text)), ped.codigo, inst.nome
  • Buffers: shared hit=1352178, temp read=4228 written=4225
5. 5,352.232 24,473.160 ↑ 1.2 251,050 1

Sort (cost=100,721.69..101,488.33 rows=306,658 width=188) (actual time=24,091.189..24,473.16 rows=251,050 loops=1)

  • Sort Key: (to_char(ped.data_pedido, 'YYYY-MM'::text)), ped.codigo, inst.nome
  • Sort Method: external merge Disk: 15968kB
  • Buffers: shared hit=1352178, temp read=4228 written=4225
6. 2,437.765 19,120.928 ↑ 1.2 251,050 1

Nested Loop (cost=7,055.77..16,173.53 rows=306,658 width=188) (actual time=3,639.732..19,120.928 rows=251,050 loops=1)

  • Buffers: shared hit=1352178, temp read=2232 written=2220
7. 938.518 9,542.907 ↓ 572.1 446,266 1

Hash Join (cost=7,055.48..15,090.29 rows=780 width=180) (actual time=3,639.625..9,542.907 rows=446,266 loops=1)

  • Buffers: shared hit=9407, temp read=2232 written=2220
8. 1,342.281 8,603.934 ↓ 572.1 446,266 1

Hash Join (cost=7,018.53..15,049.25 rows=780 width=180) (actual time=3,639.162..8,603.934 rows=446,266 loops=1)

  • Buffers: shared hit=9384, temp read=2232 written=2220
9. 2,206.060 7,261.301 ↓ 572.1 446,266 1

Gather (cost=6,997.49..15,022.06 rows=780 width=180) (actual time=3,638.801..7,261.301 rows=446,266 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
  • Buffers: shared hit=9378, temp read=2232 written=2220
10. 933.675 5,055.241 ↓ 457.7 148,755 3 / 3

Hash Join (cost=5,997.49..13,944.06 rows=325 width=180) (actual time=3,463.578..5,055.241 rows=148,755 loops=3)

  • Buffers: shared hit=9378, temp read=2232 written=2220
11. 658.066 658.066 ↑ 1.2 151,148 3 / 3

Seq Scan on parcela parc (cost=0..7,240.18 rows=187,506 width=38) (actual time=0.006..658.066 rows=151,148 loops=3)

  • Filter: ((parc.status)::text <> 'renegociado'::text)
  • Buffers: shared hit=4731
12. 93.095 3,463.500 ↓ 499.2 34,941 3 / 3

Hash (cost=5,996.61..5,996.61 rows=70 width=150) (actual time=3,463.5..3,463.5 rows=34,941 loops=3)

  • Buffers: shared hit=4551, temp read=2232 written=2220
13. 195.451 3,370.405 ↓ 499.2 34,941 3 / 3

Hash Join (cost=5,077.33..5,996.61 rows=70 width=150) (actual time=3,094.002..3,370.405 rows=34,941 loops=3)

  • Buffers: shared hit=4551, temp read=2232 written=2220
14. 81.688 81.688 ↑ 1.0 40,169 3 / 3

Seq Scan on forma_pagamento forpag (cost=0..767.06 rows=40,406 width=8) (actual time=0.011..81.688 rows=40,169 loops=3)

  • Buffers: shared hit=1089
15. 288.790 3,093.266 ↓ 504.2 65,542 3 / 3

Hash (cost=5,075.71..5,075.71 rows=130 width=150) (actual time=3,093.266..3,093.266 rows=65,542 loops=3)

  • Buffers: shared hit=3462, temp read=1026 written=1740
16. 923.896 2,804.476 ↓ 504.2 65,542 3 / 3

Hash Join (cost=2,076.5..5,075.71 rows=130 width=150) (actual time=443.292..2,804.476 rows=65,542 loops=3)

  • Buffers: shared hit=3462, temp read=1026 written=1020
17. 612.027 1,437.542 ↑ 1.0 72,836 3 / 3

Hash Join (cost=21.93..1,895.42 rows=74,961 width=134) (actual time=0.028..1,437.542 rows=72,836 loops=3)

  • Buffers: shared hit=2208
18. 825.503 825.503 ↑ 1.0 72,836 3 / 3

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

  • Filter: (pag.tipo_pagamento = 1)
  • Buffers: shared hit=2205
19. 0.004 0.012 ↑ 265.0 2 3 / 3

Hash (cost=15.3..15.3 rows=530 width=122) (actual time=0.012..0.012 rows=2 loops=3)

  • Buffers: shared hit=3
20. 0.008 0.008 ↑ 265.0 2 3 / 3

Seq Scan on instituicao inst (cost=0..15.3 rows=530 width=122) (actual time=0.006..0.008 rows=2 loops=3)

  • Buffers: shared hit=3
21. 271.199 443.038 ↓ 1.0 65,542 3 / 3

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

  • Buffers: shared hit=1254, temp written=429
22. 171.839 171.839 ↓ 1.0 65,542 3 / 3

Seq Scan on pedido_oferta pof (cost=0..1,072.63 rows=65,463 width=16) (actual time=0.013..171.839 rows=65,542 loops=3)

  • Buffers: shared hit=1254
23. 0.184 0.352 ↑ 1.1 507 1

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

  • Buffers: shared hit=6
24. 0.168 0.168 ↑ 1.1 507 1

Seq Scan on oferta ofe (cost=0..11.47 rows=547 width=16) (actual time=0.006..0.168 rows=507 loops=1)

  • Buffers: shared hit=6
25. 0.199 0.455 ↑ 1.0 558 1

Hash (cost=28.58..28.58 rows=558 width=8) (actual time=0.455..0.455 rows=558 loops=1)

  • Buffers: shared hit=23
26. 0.256 0.256 ↑ 1.0 558 1

Seq Scan on curso curs (cost=0..28.58 rows=558 width=8) (actual time=0.011..0.256 rows=558 loops=1)

  • Buffers: shared hit=23
27. 7,140.256 7,140.256 ↑ 1.0 1 446,266

Index Scan using pk_pedido on pedido ped (cost=0.29..0.39 rows=1 width=16) (actual time=0.016..0.016 rows=1 loops=446,266)

  • Index Cond: ((ped.codigo = pag.codigo_pedido) AND (ped.instituicao = pag.instituicao))
  • Filter: ((ped.status)::text = 'wc-completed'::text)
  • Buffers: shared hit=1342771
28. 15.181 26,169.165 ↑ 67.0 45 1

HashAggregate (cost=36,252.73..36,305.46 rows=3,013 width=278) (actual time=26,169.111..26,169.165 rows=45 loops=1)

  • Group Key: inad.data_pedido, $4, inad.nome_instituicao
  • Buffers: shared hit=1352178, temp read=4228 written=4225
29. 31.461 26,153.984 ↑ 19.3 1,560 1

GroupAggregate (cost=33,137.41..35,122.7 rows=30,134 width=250) (actual time=26,108.68..26,153.984 rows=1,560 loops=1)

  • Group Key: inad.dias_vencidos, inad.data_pedido, inad.nome_instituicao
  • Buffers: shared hit=1352178, temp read=4228 written=4225
30.          

Initplan (for GroupAggregate)

31. 31.888 37.184 ↑ 1.0 1 1

Aggregate (cost=6,899.81..6,899.82 rows=1 width=32) (actual time=37.183..37.184 rows=1 loops=1)

32. 5.296 5.296 ↑ 15.0 20,423 1

CTE Scan on inadimplente inadimplente (cost=0..6,133.16 rows=306,658 width=32) (actual time=0.001..5.296 rows=20,423 loops=1)

33. 30.582 26,085.339 ↑ 5.4 18,911 1

Sort (cost=26,237.59..26,493.14 rows=102,219 width=218) (actual time=26,071.442..26,085.339 rows=18,911 loops=1)

  • Sort Key: inad.dias_vencidos, inad.data_pedido, inad.nome_instituicao
  • Sort Method: quicksort Memory: 2246kB
  • Buffers: shared hit=1352178, temp read=4228 written=4225
34. 26,054.757 26,054.757 ↑ 5.4 18,911 1

CTE Scan on inadimplente inad (cost=0..6,899.8 rows=102,219 width=218) (actual time=26,017.096..26,054.757 rows=18,911 loops=1)

  • Filter: (inad.dias_vencidos >= 30)
  • Buffers: shared hit=1352178, temp read=4228 written=4225
Planning time : 29.551 ms
Execution time : 26,171.79 ms