explain.depesz.com

PostgreSQL's explain analyze made readable

Result: gfML

Settings

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 0.001 114,882.055 ↑ 7,600.0 1 1

Unique (cost=14,710,662.95..14,716,525.57 rows=7,600 width=278) (actual time=114,882.054..114,882.055 rows=1 loops=1)

2.          

CTE loan_info

3. 0.000 6,648.348 ↓ 48.5 90,822 1

Gather (cost=5,678.51..217,895.67 rows=1,871 width=21) (actual time=78.631..6,648.348 rows=90,822 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
4. 5,358.197 6,661.708 ↓ 38.8 30,274 3

Nested Loop (cost=4,678.51..216,708.57 rows=780 width=21) (actual time=75.509..6,661.708 rows=30,274 loops=3)

  • Join Filter: (l_1.id = n.loan_id)
5. 658.211 1,303.272 ↓ 11.4 22,384 3

Nested Loop (cost=4,678.09..215,108.43 rows=1,972 width=12) (actual time=73.157..1,303.272 rows=22,384 loops=3)

6. 7.129 645.032 ↓ 3.9 22,384 3

Hash Join (cost=4,677.66..198,546.21 rows=5,675 width=4) (actual time=72.275..645.032 rows=22,384 loops=3)

  • Hash Cond: (l_1.product_id = po.id)
7. 571.103 637.885 ↑ 1.2 24,567 3

Parallel Bitmap Heap Scan on loan l_1 (cost=4,669.49..198,400.40 rows=28,378 width=8) (actual time=72.228..637.885 rows=24,567 loops=3)

  • Recheck Cond: ((state)::text = ANY ('{GRANTED,LATE,FULLY_PAID}'::text[]))
  • Heap Blocks: exact=15652
8. 66.782 66.782 ↓ 1.4 94,655 1

Bitmap Index Scan on ix_loan_state (cost=0.00..4,652.47 rows=68,106 width=0) (actual time=66.782..66.782 rows=94,655 loops=1)

  • Index Cond: ((state)::text = ANY ('{GRANTED,LATE,FULLY_PAID}'::text[]))
9. 0.003 0.018 ↑ 1.0 1 3

Hash (cost=8.15..8.15 rows=1 width=4) (actual time=0.018..0.018 rows=1 loops=3)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
10. 0.005 0.015 ↑ 1.0 1 3

Bitmap Heap Scan on product po (cost=4.14..8.15 rows=1 width=4) (actual time=0.015..0.015 rows=1 loops=3)

  • Recheck Cond: ((slug)::text = 'cpsg-premium'::text)
  • Heap Blocks: exact=1
11. 0.010 0.010 ↑ 1.0 1 3

Bitmap Index Scan on ix_product_slug (cost=0.00..4.14 rows=1 width=0) (actual time=0.010..0.010 rows=1 loops=3)

  • Index Cond: ((slug)::text = 'cpsg-premium'::text)
12. 0.029 0.029 ↑ 1.0 1 67,152

Index Scan using ix_payment_plan_loan_id on payment_plan pp (cost=0.43..2.91 rows=1 width=8) (actual time=0.028..0.029 rows=1 loops=67,152)

  • Index Cond: (loan_id = l_1.id)
  • Filter: active
  • Rows Removed by Filter: 0
13. 0.239 0.239 ↑ 8.0 1 67,152

Index Scan using ix_note_loan_id on note n (cost=0.43..0.71 rows=8 width=17) (actual time=0.234..0.239 rows=1 loops=67,152)

  • Index Cond: (loan_id = pp.loan_id)
14.          

CTE original_instalment

15. 6,628.201 92,985.276 ↑ 1.0 47,768,387 1

Unique (cost=12,506,507.35..12,880,711.02 rows=49,893,822 width=52) (actual time=80,173.178..92,985.276 rows=47,768,387 loops=1)

16. 33,083.172 86,357.075 ↑ 1.0 49,555,625 1

Sort (cost=12,506,507.35..12,631,241.91 rows=49,893,822 width=52) (actual time=80,173.176..86,357.075 rows=49,555,625 loops=1)

  • Sort Key: pp_1.loan_id, i_1.number, i_1.created
  • Sort Method: external merge Disk: 2036784kB
17. 29,925.729 53,273.903 ↑ 1.0 49,555,625 1

Hash Join (cost=83,135.64..2,716,279.00 rows=49,893,822 width=52) (actual time=924.410..53,273.903 rows=49,555,625 loops=1)

  • Hash Cond: (i_1.payment_plan_id = pp_1.id)
18. 22,428.208 22,428.208 ↑ 1.0 49,555,640 1

Seq Scan on instalment i_1 (cost=0.00..1,590,732.22 rows=49,893,822 width=40) (actual time=0.681..22,428.208 rows=49,555,640 loops=1)

19. 316.523 919.966 ↓ 1.0 1,850,723 1

Hash (cost=53,262.84..53,262.84 rows=1,820,784 width=8) (actual time=919.966..919.966 rows=1,850,723 loops=1)

  • Buckets: 524288 Batches: 8 Memory Usage: 13153kB
20. 603.443 603.443 ↓ 1.0 1,850,723 1

Seq Scan on payment_plan pp_1 (cost=0.00..53,262.84 rows=1,820,784 width=8) (actual time=0.008..603.443 rows=1,850,723 loops=1)

21.          

CTE loan_settlement

22. 0.002 5.619 ↑ 1.0 1 1

Nested Loop (cost=1.71..18.13 rows=1 width=70) (actual time=5.617..5.619 rows=1 loops=1)

23. 0.002 4.341 ↑ 1.0 1 1

Nested Loop (cost=1.28..17.44 rows=1 width=45) (actual time=4.340..4.341 rows=1 loops=1)

24. 0.003 2.977 ↑ 1.0 1 1

Nested Loop (cost=0.85..16.89 rows=1 width=37) (actual time=2.976..2.977 rows=1 loops=1)

25. 1.779 1.779 ↑ 1.0 1 1

Index Scan using ix_payment_uuid on payment p (cost=0.43..8.45 rows=1 width=37) (actual time=1.778..1.779 rows=1 loops=1)

  • Index Cond: ((uuid)::text = '8c08aa9673414a1883a4ea90cc01110b'::text)
  • Filter: ((state)::text = 'PAID'::text)
26. 1.195 1.195 ↑ 1.0 1 1

Index Scan using settlement_payment_pkey on settlement_payment sp (cost=0.43..8.45 rows=1 width=8) (actual time=1.195..1.195 rows=1 loops=1)

  • Index Cond: (id = p.id)
27. 1.362 1.362 ↑ 1.0 1 1

Index Scan using settlement_pkey on settlement s_1 (cost=0.42..0.55 rows=1 width=8) (actual time=1.362..1.362 rows=1 loops=1)

  • Index Cond: (id = sp.settlement_id)
28. 1.276 1.276 ↑ 6.0 1 1

Index Scan using ix_settlement_instalment_dist_settlement_id on settlement_instalment_dist sid (cost=0.42..0.63 rows=6 width=37) (actual time=1.275..1.276 rows=1 loops=1)

  • Index Cond: (settlement_id = sp.settlement_id)
  • Filter: active
29.          

CTE renegotiation_info

30. 14.378 168.626 ↑ 1.4 88,162 1

Unique (cost=27,828.00..28,454.85 rows=125,370 width=37) (actual time=147.223..168.626 rows=88,162 loops=1)

31. 33.656 154.248 ↑ 1.4 88,162 1

Sort (cost=27,828.00..28,141.42 rows=125,370 width=37) (actual time=147.221..154.248 rows=88,162 loops=1)

  • Sort Key: cia.instalment_id
  • Sort Method: quicksort Memory: 9960kB
32. 61.721 120.592 ↑ 1.4 88,162 1

Hash Join (cost=4,934.81..17,211.77 rows=125,370 width=37) (actual time=19.105..120.592 rows=88,162 loops=1)

  • Hash Cond: (cia.renegotiation_id = cr.id)
33. 39.826 39.826 ↑ 1.0 365,116 1

Seq Scan on collection_instalment_affected cia (cost=0.00..11,316.70 rows=365,770 width=8) (actual time=0.011..39.826 rows=365,116 loops=1)

34. 2.221 19.045 ↓ 1.0 12,253 1

Hash (cost=4,782.21..4,782.21 rows=12,208 width=37) (actual time=19.045..19.045 rows=12,253 loops=1)

  • Buckets: 16384 Batches: 1 Memory Usage: 990kB
35. 16.824 16.824 ↓ 1.0 12,253 1

Seq Scan on collection_renegotiation cr (cost=0.00..4,782.21 rows=12,208 width=37) (actual time=0.010..16.824 rows=12,253 loops=1)

  • Filter: ((state)::text = ANY ('{RUNNING,DONE}'::text[]))
  • Rows Removed by Filter: 23460
36. 0.024 114,882.054 ↑ 781,682.0 1 1

Sort (cost=1,583,583.29..1,585,537.50 rows=781,682 width=278) (actual time=114,882.054..114,882.054 rows=1 loops=1)

  • Sort Key: i.number, l.cod_contrato
  • Sort Method: quicksort Memory: 25kB
37. 0.648 114,882.030 ↑ 781,682.0 1 1

Hash Left Join (cost=4,134.62..1,405,543.39 rows=781,682 width=278) (actual time=103,864.262..114,882.030 rows=1 loops=1)

  • Hash Cond: (s.instalment_id = ri.instalment_id)
38. 3,642.810 114,682.936 ↑ 1,247.0 1 1

Hash Join (cost=60.10..1,372,152.67 rows=1,247 width=256) (actual time=103,665.169..114,682.936 rows=1 loops=1)

  • Hash Cond: ((oi.loan_id = l.loan_id) AND (oi.number = i.number))
39. 104,340.816 104,340.816 ↑ 1.0 47,768,387 1

CTE Scan on original_instalment oi (cost=0.00..997,876.44 rows=49,893,822 width=44) (actual time=80,173.180..104,340.816 rows=47,768,387 loops=1)

40. 0.006 6,699.310 ↑ 1.0 1 1

Hash (cost=60.08..60.08 rows=1 width=228) (actual time=6,699.310..6,699.310 rows=1 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
41. 16.857 6,699.304 ↑ 1.0 1 1

Hash Join (cost=8.62..60.08 rows=1 width=228) (actual time=5,527.682..6,699.304 rows=1 loops=1)

  • Hash Cond: ((l.loan_id = s.loan_id) AND (l.active_payment_plan_id = i.payment_plan_id))
42. 6,675.978 6,675.978 ↓ 48.5 90,822 1

CTE Scan on loan_info l (cost=0.00..37.42 rows=1,871 width=126) (actual time=78.634..6,675.978 rows=90,822 loops=1)

43. 0.003 6.469 ↑ 1.0 1 1

Hash (cost=8.61..8.61 rows=1 width=110) (actual time=6.469..6.469 rows=1 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
44. 0.003 6.466 ↑ 1.0 1 1

Nested Loop (cost=0.56..8.61 rows=1 width=110) (actual time=6.463..6.466 rows=1 loops=1)

45. 5.622 5.622 ↑ 1.0 1 1

CTE Scan on loan_settlement s (cost=0.00..0.02 rows=1 width=90) (actual time=5.620..5.622 rows=1 loops=1)

46. 0.841 0.841 ↑ 1.0 1 1

Index Scan using instalment_pkey on instalment i (cost=0.56..8.58 rows=1 width=29) (actual time=0.841..0.841 rows=1 loops=1)

  • Index Cond: (id = s.instalment_id)
  • Filter: (active AND ((state)::text = 'PAID'::text))
47. 14.387 198.446 ↑ 1.4 88,162 1

Hash (cost=2,507.40..2,507.40 rows=125,370 width=4) (actual time=198.446..198.446 rows=88,162 loops=1)

  • Buckets: 131072 Batches: 1 Memory Usage: 4124kB
48. 184.059 184.059 ↑ 1.4 88,162 1

CTE Scan on renegotiation_info ri (cost=0.00..2,507.40 rows=125,370 width=4) (actual time=147.226..184.059 rows=88,162 loops=1)

Planning time : 41.478 ms
Execution time : 115,382.779 ms