Result: Cqgl : Optimization for: plan #gfML

Settings

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 0.001 102,956.922 ↑ 7,600.0 1 1

(cost=14,272,253.09..14,278,116.50 rows=7,600 width=278) (actual time=102,956.922..102,956.922 rows=1 loops=1)

2.

CTE loan_info

3. 0.000 278.682 ↓ 48.5 90,822 1

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

• Workers Planned: 2
• Workers Launched: 2
4. 23.221 285.044 ↓ 38.8 30,274 3 / 3

(cost=4,678.51..216,708.57 rows=780 width=21) (actual time=19.537..285.044 rows=30,274 loops=3)

• Join Filter: (l_1.id = n.loan_id)
5. 14.275 172.287 ↓ 11.4 22,384 3 / 3

(cost=4,678.09..215,108.43 rows=1,972 width=12) (actual time=19.521..172.287 rows=22,384 loops=3)

6. 6.752 68.476 ↓ 3.9 22,384 3 / 3

(cost=4,677.66..198,546.21 rows=5,675 width=4) (actual time=19.500..68.476 rows=22,384 loops=3)

• Hash Cond: (l_1.product_id = po.id)
7. 56.800 61.705 ↑ 1.2 24,567 3 / 3

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

• Recheck Cond: ((state)::text = ANY ('{GRANTED,LATE,FULLY_PAID}'::text[]))
• Heap Blocks: exact=14,665
8. 4.905 4.905 ↓ 1.4 94,662 1 / 3

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

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

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

• Buckets: 1,024 Batches: 1 Memory Usage: 9kB
10. 0.005 0.015 ↑ 1.0 1 3 / 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 / 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. 89.536 89.536 ↑ 1.0 1 67,152 / 3

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

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

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

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

CTE original_instalment

15. 6,992.118 88,648.332 ↑ 1.0 47,768,645 1

(cost=12,068,054.51..12,442,260.23 rows=49,894,096 width=44) (actual time=75,877.459..88,648.332 rows=47,768,645 loops=1)

16. 31,212.996 81,656.214 ↑ 1.0 49,555,910 1

(cost=12,068,054.51..12,192,789.75 rows=49,894,096 width=44) (actual time=75,877.458..81,656.214 rows=49,555,910 loops=1)

• Sort Key: pp_1.loan_id, i_1.number
• Sort Method: external merge Disk: 1,561,640kB
17. 28,329.258 50,443.218 ↑ 1.0 49,555,910 1

(cost=83,135.64..2,618,843.14 rows=49,894,096 width=44) (actual time=568.139..50,443.218 rows=49,555,910 loops=1)

• Hash Cond: (i_1.payment_plan_id = pp_1.id)
18. 21,549.149 21,549.149 ↑ 1.0 49,555,925 1

Seq Scan on instalment i_1 (cost=0.00..1,590,740.96 rows=49,894,096 width=32) (actual time=1.480..21,549.149 rows=49,555,925 loops=1)

19. 280.105 564.811 ↓ 1.0 1,850,732 1

(cost=53,262.84..53,262.84 rows=1,820,784 width=8) (actual time=564.811..564.811 rows=1,850,732 loops=1)

• Buckets: 524,288 Batches: 8 Memory Usage: 13,153kB
20. 284.706 284.706 ↓ 1.0 1,850,732 1

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

21.

CTE loan_settlement

22. 0.002 0.053 ↑ 1.0 1 1

(cost=1.71..18.13 rows=1 width=70) (actual time=0.051..0.053 rows=1 loops=1)

23. 0.002 0.041 ↑ 1.0 1 1

(cost=1.28..17.44 rows=1 width=45) (actual time=0.040..0.041 rows=1 loops=1)

24. 0.002 0.029 ↑ 1.0 1 1

(cost=0.85..16.89 rows=1 width=37) (actual time=0.028..0.029 rows=1 loops=1)

25. 0.018 0.018 ↑ 1.0 1 1

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

• Index Cond: ((uuid)::text = '8c08aa9673414a1883a4ea90cc01110b'::text)
• Filter: ((state)::text = 'PAID'::text)
26. 0.009 0.009 ↑ 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=0.009..0.009 rows=1 loops=1)

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

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

• Index Cond: (id = sp.settlement_id)
28. 0.010 0.010 ↑ 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=0.009..0.010 rows=1 loops=1)

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

CTE renegotiation_info

30. 14.073 153.211 ↑ 1.4 88,186 1

(cost=27,831.16..28,458.10 rows=125,387 width=37) (actual time=132.836..153.211 rows=88,186 loops=1)

31. 32.480 139.138 ↑ 1.4 88,186 1

(cost=27,831.16..28,144.63 rows=125,387 width=37) (actual time=132.834..139.138 rows=88,186 loops=1)

• Sort Key: cia.instalment_id
• Sort Method: quicksort Memory: 9,962kB
32. 54.629 106.658 ↑ 1.4 88,186 1

(cost=4,934.81..17,213.38 rows=125,387 width=37) (actual time=15.777..106.658 rows=88,186 loops=1)

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

Seq Scan on collection_instalment_affected cia (cost=0.00..11,318.18 rows=365,818 width=8) (actual time=0.010..36.300 rows=365,140 loops=1)

34. 1.890 15.729 ↓ 1.0 12,255 1

(cost=4,782.21..4,782.21 rows=12,208 width=37) (actual time=15.729..15.729 rows=12,255 loops=1)

• Buckets: 16,384 Batches: 1 Memory Usage: 990kB
35. 13.839 13.839 ↓ 1.0 12,255 1

Seq Scan on collection_renegotiation cr (cost=0.00..4,782.21 rows=12,208 width=37) (actual time=0.011..13.839 rows=12,255 loops=1)

• Filter: ((state)::text = ANY ('{RUNNING,DONE}'::text[]))
• Rows Removed by Filter: 23,460
36. 0.010 102,956.921 ↑ 781,788.0 1 1

(cost=1,583,620.96..1,585,575.43 rows=781,788 width=278) (actual time=102,956.921..102,956.921 rows=1 loops=1)

• Sort Key: i.number, l.cod_contrato
• Sort Method: quicksort Memory: 25kB
37. 0.607 102,956.911 ↑ 781,788.0 1 1

(cost=4,135.18..1,405,555.92 rows=781,788 width=278) (actual time=92,207.779..102,956.911 rows=1 loops=1)

• Hash Cond: (s.instalment_id = ri.instalment_id)
38. 4,649.590 102,774.497 ↑ 1,247.0 1 1

(cost=60.10..1,372,160.21 rows=1,247 width=256) (actual time=92,025.366..102,774.497 rows=1 loops=1)

• Hash Cond: ((oi.loan_id = l.loan_id) AND (oi.number = i.number))
39. 97,802.395 97,802.395 ↑ 1.0 47,768,645 1

CTE Scan on original_instalment oi (cost=0.00..997,881.92 rows=49,894,096 width=44) (actual time=75,877.461..97,802.395 rows=47,768,645 loops=1)

40. 0.007 322.512 ↑ 1.0 1 1

(cost=60.08..60.08 rows=1 width=228) (actual time=322.512..322.512 rows=1 loops=1)

• Buckets: 1,024 Batches: 1 Memory Usage: 9kB
41. 15.753 322.505 ↑ 1.0 1 1

(cost=8.62..60.08 rows=1 width=228) (actual time=196.241..322.505 rows=1 loops=1)

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

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

43. 0.003 0.073 ↑ 1.0 1 1

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

• Buckets: 1,024 Batches: 1 Memory Usage: 9kB
44. 0.002 0.070 ↑ 1.0 1 1

(cost=0.56..8.61 rows=1 width=110) (actual time=0.068..0.070 rows=1 loops=1)

45. 0.055 0.055 ↑ 1.0 1 1

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

46. 0.013 0.013 ↑ 1.0 1 1

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

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

(cost=2,507.74..2,507.74 rows=125,387 width=4) (actual time=181.807..181.807 rows=88,186 loops=1)

• Buckets: 131,072 Batches: 1 Memory Usage: 4,125kB
48. 168.774 168.774 ↑ 1.4 88,186 1

CTE Scan on renegotiation_info ri (cost=0.00..2,507.74 rows=125,387 width=4) (actual time=132.838..168.774 rows=88,186 loops=1)

Planning time Execution time : 2.668 ms : 103,365.739 ms