explain.depesz.com

PostgreSQL's explain analyze made readable

Result: ZxgL

Settings
# exclusive inclusive rows x rows loops node
1. 0.015 1,734.132 ↑ 1.4 114 1

Append (cost=16,764.36..367,936.38 rows=155 width=432) (actual time=46.870..1,734.132 rows=114 loops=1)

2.          

CTE recursive_new_invoices

3. 0.014 1.058 ↓ 1.2 74 1

Recursive Union (cost=342.76..1,849.41 rows=60 width=552) (actual time=0.872..1.058 rows=74 loops=1)

4. 0.072 1.034 ↑ 1.1 37 1

Bitmap Heap Scan on capfin_invoice invoice_2 (cost=342.76..1,758.21 rows=40 width=46) (actual time=0.870..1.034 rows=37 loops=1)

  • Recheck Cond: ((status = 'APPROVED'::capfin.invoice_status_enum) AND (product_type = 'FACTORING'::capfin.product_type))
  • Filter: (NOT (SubPlan 1))
  • Heap Blocks: exact=32
5. 0.033 0.851 ↓ 0.0 0 1

BitmapAnd (cost=342.76..342.76 rows=79 width=0) (actual time=0.851..0.851 rows=0 loops=1)

6. 0.271 0.271 ↓ 1.5 6,408 1

Bitmap Index Scan on capfin_invoice_status_idx (cost=0.00..108.22 rows=4,240 width=0) (actual time=0.271..0.271 rows=6,408 loops=1)

  • Index Cond: (status = 'APPROVED'::capfin.invoice_status_enum)
7. 0.547 0.547 ↓ 1.0 8,367 1

Bitmap Index Scan on capfin_invoice_product_type_idx (cost=0.00..234.27 rows=8,246 width=0) (actual time=0.547..0.547 rows=8,367 loops=1)

  • Index Cond: (product_type = 'FACTORING'::capfin.product_type)
8.          

SubPlan (for Bitmap Heap Scan)

9. 0.021 0.111 ↓ 0.0 0 37

Nested Loop (cost=0.56..41.27 rows=3 width=0) (actual time=0.003..0.003 rows=0 loops=37)

10. 0.074 0.074 ↓ 0.0 0 37

Index Scan using client_settlement_invoice_allocation_invoice_uuid_idx on client_settlement_invoice_allocation allocation (cost=0.29..16.34 rows=3 width=16) (actual time=0.002..0.002 rows=0 loops=37)

  • Index Cond: (invoice_uuid = invoice_2.uuid)
11. 0.016 0.016 ↓ 0.0 0 8

Index Scan using client_settlement_pkey on client_settlement client_settlement_1 (cost=0.27..8.30 rows=1 width=16) (actual time=0.002..0.002 rows=0 loops=8)

  • Index Cond: (uuid = allocation.client_settlement_uuid)
  • Filter: (settlement_status = ANY ('{PENDING,INITIATED,SENT,PAID}'::capfin.advance_status_enum[]))
  • Rows Removed by Filter: 1
12. 0.010 0.010 ↓ 9.0 18 2

WorkTable Scan on recursive_new_invoices (cost=0.00..9.00 rows=2 width=552) (actual time=0.003..0.005 rows=18 loops=2)

  • Filter: (allocation_type = 'ADVANCE_AMOUNT'::capfin.client_invoice_allocation_type)
  • Rows Removed by Filter: 18
13. 1.012 298.944 ↓ 1.2 74 1

Hash Join (cost=14,914.95..154,287.62 rows=60 width=742) (actual time=46.869..298.944 rows=74 loops=1)

  • Hash Cond: (capfin_invoice.uuid = rinvoice.uuid)
14. 13.079 296.838 ↑ 1.0 8,054 1

Hash Left Join (cost=14,913.00..154,171.09 rows=8,246 width=2,958) (actual time=39.954..296.838 rows=8,054 loops=1)

  • Hash Cond: (discount_fee_settlements.factor_client_debtor_contract_uuid_agg[1] = purchased_fcdc.uuid)
15. 4.443 283.745 ↑ 1.0 8,054 1

Nested Loop Left Join (cost=14,910.92..148,848.85 rows=8,246 width=168) (actual time=39.893..283.745 rows=8,054 loops=1)

  • Join Filter: (advance_amount_settlements.purchased_date IS NULL)
16. 2.589 158.492 ↑ 1.0 8,054 1

Nested Loop Left Join (cost=14,905.06..100,382.99 rows=8,246 width=166) (actual time=39.830..158.492 rows=8,054 loops=1)

  • Join Filter: (advance_amount_settlements.purchased_date IS NULL)
  • Rows Removed by Join Filter: 7,405
17. 3.545 59.255 ↑ 1.0 8,054 1

Hash Left Join (cost=14,895.14..18,306.43 rows=8,246 width=163) (actual time=39.766..59.255 rows=8,054 loops=1)

  • Hash Cond: (capfin_invoice.uuid = advance_amount_settlements.uuid)
18. 4.093 40.871 ↑ 1.0 8,054 1

Hash Left Join (cost=13,556.24..16,945.89 rows=8,246 width=127) (actual time=24.910..40.871 rows=8,054 loops=1)

  • Hash Cond: (capfin_invoice.uuid = discount_fee_settlements.uuid)
19. 2.056 20.217 ↑ 1.0 8,054 1

Hash Left Join (cost=12,025.02..15,393.02 rows=8,246 width=63) (actual time=8.338..20.217 rows=8,054 loops=1)

  • Hash Cond: (capfin_invoice.term_code_uuid = term_code.uuid)
20. 1.961 17.985 ↑ 1.0 8,054 1

Hash Left Join (cost=12,004.98..15,351.17 rows=8,246 width=70) (actual time=8.156..17.985 rows=8,054 loops=1)

  • Hash Cond: (capfin_invoice.client_debtor_uuid = client_debtor.uuid)
21. 1.957 15.775 ↑ 1.0 8,054 1

Hash Join (cost=11,973.18..15,297.61 rows=8,246 width=66) (actual time=7.899..15.775 rows=8,054 loops=1)

  • Hash Cond: (capfin_invoice.client_uuid = client.uuid)
22. 5.936 5.936 ↑ 1.0 8,054 1

Index Scan using capfin_invoice_product_type_idx on capfin_invoice (cost=0.42..3,211.47 rows=8,246 width=126) (actual time=0.011..5.936 rows=8,054 loops=1)

  • Index Cond: (product_type = 'FACTORING'::capfin.product_type)
23. 0.363 7.882 ↓ 1.0 2,601 1

Hash (cost=11,940.36..11,940.36 rows=2,592 width=16) (actual time=7.881..7.882 rows=2,601 loops=1)

  • Buckets: 4,096 Batches: 1 Memory Usage: 154kB
24. 1.970 7.519 ↓ 1.0 2,601 1

Nested Loop (cost=0.43..11,940.36 rows=2,592 width=16) (actual time=0.015..7.519 rows=2,601 loops=1)

25. 0.347 0.347 ↓ 1.0 2,601 1

Seq Scan on client (cost=0.00..66.92 rows=2,592 width=32) (actual time=0.006..0.347 rows=2,601 loops=1)

26. 5.202 5.202 ↑ 1.0 1 2,601

Index Only Scan using division_uuid_key on division (cost=0.43..4.58 rows=1 width=16) (actual time=0.002..0.002 rows=1 loops=2,601)

  • Index Cond: (uuid = client.division_uuid)
  • Heap Fetches: 506
27. 0.097 0.249 ↓ 1.0 793 1

Hash (cost=21.91..21.91 rows=791 width=36) (actual time=0.249..0.249 rows=793 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 58kB
28. 0.152 0.152 ↓ 1.0 793 1

Seq Scan on client_debtor (cost=0.00..21.91 rows=791 width=36) (actual time=0.007..0.152 rows=793 loops=1)

29. 0.077 0.176 ↑ 1.0 535 1

Hash (cost=13.35..13.35 rows=535 width=25) (actual time=0.176..0.176 rows=535 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 40kB
30. 0.099 0.099 ↑ 1.0 535 1

Seq Scan on term_code (cost=0.00..13.35 rows=535 width=25) (actual time=0.005..0.099 rows=535 loops=1)

31. 1.319 16.561 ↓ 1.3 7,405 1

Hash (cost=1,458.95..1,458.95 rows=5,782 width=112) (actual time=16.561..16.561 rows=7,405 loops=1)

  • Buckets: 8,192 Batches: 1 Memory Usage: 993kB
32. 0.747 15.242 ↓ 1.3 7,405 1

Subquery Scan on discount_fee_settlements (cost=1,203.34..1,458.95 rows=5,782 width=112) (actual time=6.239..15.242 rows=7,405 loops=1)

33. 7.329 14.495 ↓ 1.3 7,405 1

GroupAggregate (cost=1,203.34..1,401.13 rows=5,782 width=112) (actual time=6.238..14.495 rows=7,405 loops=1)

  • Group Key: invoice_settlements.invoice_uuid
34. 3.076 7.166 ↑ 1.0 7,405 1

Sort (cost=1,203.34..1,222.66 rows=7,728 width=54) (actual time=6.222..7.166 rows=7,405 loops=1)

  • Sort Key: invoice_settlements.invoice_uuid
  • Sort Method: quicksort Memory: 1,234kB
35. 1.576 4.090 ↑ 1.0 7,405 1

Hash Join (cost=16.59..704.27 rows=7,728 width=54) (actual time=0.129..4.090 rows=7,405 loops=1)

  • Hash Cond: (invoice_settlements.client_settlement_uuid = settlements.uuid)
36. 2.396 2.396 ↑ 1.0 8,859 1

Seq Scan on client_settlement_invoice_allocation invoice_settlements (cost=0.00..664.16 rows=8,864 width=70) (actual time=0.005..2.396 rows=8,859 loops=1)

  • Filter: (item_type = 'DISCOUNT_FEE'::capfin.client_invoice_allocation_type)
  • Rows Removed by Filter: 14,595
37. 0.040 0.118 ↓ 1.0 348 1

Hash (cost=12.34..12.34 rows=340 width=16) (actual time=0.118..0.118 rows=348 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 25kB
38. 0.078 0.078 ↓ 1.0 348 1

Seq Scan on client_settlement settlements (cost=0.00..12.34 rows=340 width=16) (actual time=0.007..0.078 rows=348 loops=1)

  • Filter: (settlement_status = ANY ('{SENT,PAID,INITIATED,AUTHORIZED,PENDING}'::capfin.advance_status_enum[]))
  • Rows Removed by Filter: 52
39. 1.357 14.839 ↓ 1.3 7,405 1

Hash (cost=1,266.62..1,266.62 rows=5,782 width=52) (actual time=14.838..14.839 rows=7,405 loops=1)

  • Buckets: 8,192 Batches: 1 Memory Usage: 488kB
40. 1.015 13.482 ↓ 1.3 7,405 1

Subquery Scan on advance_amount_settlements (cost=1,194.34..1,266.62 rows=5,782 width=52) (actual time=11.815..13.482 rows=7,405 loops=1)

41. 2.004 12.467 ↓ 1.3 7,405 1

Sort (cost=1,194.34..1,208.80 rows=5,782 width=52) (actual time=11.813..12.467 rows=7,405 loops=1)

  • Sort Key: (min(settlements_1.issued_date))
  • Sort Method: quicksort Memory: 771kB
42. 6.606 10.463 ↓ 1.3 7,405 1

HashAggregate (cost=760.77..833.05 rows=5,782 width=52) (actual time=7.289..10.463 rows=7,405 loops=1)

  • Group Key: invoice_settlements_1.invoice_uuid
43. 1.443 3.857 ↑ 1.0 7,405 1

Hash Join (cost=15.12..702.81 rows=7,728 width=26) (actual time=0.133..3.857 rows=7,405 loops=1)

  • Hash Cond: (invoice_settlements_1.client_settlement_uuid = settlements_1.uuid)
44. 2.309 2.309 ↑ 1.0 8,859 1

Seq Scan on client_settlement_invoice_allocation invoice_settlements_1 (cost=0.00..664.16 rows=8,864 width=38) (actual time=0.022..2.309 rows=8,859 loops=1)

  • Filter: (item_type = 'ADVANCE_AMOUNT'::capfin.client_invoice_allocation_type)
  • Rows Removed by Filter: 14,595
45. 0.040 0.105 ↓ 1.0 348 1

Hash (cost=10.88..10.88 rows=340 width=20) (actual time=0.105..0.105 rows=348 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 26kB
46. 0.065 0.065 ↓ 1.0 348 1

Seq Scan on client_settlement settlements_1 (cost=0.00..10.88 rows=340 width=20) (actual time=0.005..0.065 rows=348 loops=1)

  • Filter: (settlement_status <> 'CANCELED'::capfin.advance_status_enum)
  • Rows Removed by Filter: 52
47. 0.000 96.648 ↑ 1.0 1 8,054

Limit (cost=9.93..9.93 rows=1 width=55) (actual time=0.012..0.012 rows=1 loops=8,054)

48. 16.108 96.648 ↑ 1.0 1 8,054

Sort (cost=9.93..9.93 rows=1 width=55) (actual time=0.012..0.012 rows=1 loops=8,054)

  • Sort Key: cdc.as_of_date DESC NULLS LAST
  • Sort Method: quicksort Memory: 25kB
49. 40.639 80.540 ↑ 1.0 1 8,054

Hash Join (cost=8.31..9.92 rows=1 width=55) (actual time=0.006..0.010 rows=1 loops=8,054)

  • Hash Cond: (fcdc.client_debtor_contract_uuid = cdc.uuid)
50. 23.793 23.793 ↑ 1.0 48 7,931

Seq Scan on factor_client_debtor_contract fcdc (cost=0.00..1.48 rows=48 width=35) (actual time=0.001..0.003 rows=48 loops=7,931)

51. 8.054 16.108 ↑ 1.0 1 8,054

Hash (cost=8.30..8.30 rows=1 width=20) (actual time=0.002..0.002 rows=1 loops=8,054)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
52. 8.054 8.054 ↑ 1.0 1 8,054

Index Scan using client_debtor_contract_unique_as_of_constraint on client_debtor_contract cdc (cost=0.28..8.30 rows=1 width=20) (actual time=0.001..0.001 rows=1 loops=8,054)

  • Index Cond: ((client_debtor_uuid = capfin_invoice.client_debtor_uuid) AND (as_of_date <= CURRENT_DATE))
  • Filter: (effective_date <= CURRENT_DATE)
53. 0.000 120.810 ↓ 0.0 0 8,054

Limit (cost=5.85..5.86 rows=1 width=30) (actual time=0.015..0.015 rows=0 loops=8,054)

54. 8.054 120.810 ↓ 0.0 0 8,054

Sort (cost=5.85..5.86 rows=3 width=30) (actual time=0.015..0.015 rows=0 loops=8,054)

  • Sort Key: reference_rate.effective_date DESC NULLS LAST, reference_rate.term
  • Sort Method: quicksort Memory: 25kB
55. 112.756 112.756 ↑ 3.0 1 8,054

Seq Scan on reference_rate (cost=0.00..5.84 rows=3 width=30) (actual time=0.013..0.014 rows=1 loops=8,054)

  • Filter: ((effective_date <= CURRENT_DATE) AND (term >= GREATEST(fcdc.minimum_tenor, ((GREATEST(CASE WHEN (term_code.net_days IS NOT NULL) THEN CASE WHEN ((term_code.eom_code)::text = 'E'::text) THEN (((date_trunc('month'::text, ((capfin_invoice.transaction_date + term_code.net_days))::timestamp with time zone) + '1 mon'::interval) - '1 day'::interval))::date ELSE (capfin_invoice.transaction_date + term_code.net_days) END ELSE NULL::date END, capfin_invoice.due_date) + COALESCE(client_debtor.buffer_days, 0)) - CURRENT_DATE))) AND (type = capfin.reference_rate_type(fcdc.rate_type)))
  • Rows Removed by Filter: 15
56. 0.007 0.014 ↑ 1.0 48 1

Hash (cost=1.48..1.48 rows=48 width=21) (actual time=0.014..0.014 rows=48 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 11kB
57. 0.007 0.007 ↑ 1.0 48 1

Seq Scan on factor_client_debtor_contract purchased_fcdc (cost=0.00..1.48 rows=48 width=21) (actual time=0.002..0.007 rows=48 loops=1)

58. 0.010 1.094 ↓ 1.2 74 1

Hash (cost=1.20..1.20 rows=60 width=552) (actual time=1.094..1.094 rows=74 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 15kB
59. 1.084 1.084 ↓ 1.2 74 1

CTE Scan on recursive_new_invoices rinvoice (cost=0.00..1.20 rows=60 width=552) (actual time=0.874..1.084 rows=74 loops=1)

60. 1.011 1,435.173 ↑ 2.4 40 1

Hash Join (cost=72,330.41..211,797.02 rows=95 width=236) (actual time=1,185.963..1,435.173 rows=40 loops=1)

  • Hash Cond: (capfin_invoice_1.uuid = existing_settlements.target_uuid)
61. 12.717 291.227 ↑ 1.0 8,054 1

Hash Left Join (cost=14,913.00..154,171.09 rows=8,246 width=2,958) (actual time=38.530..291.227 rows=8,054 loops=1)

  • Hash Cond: (discount_fee_settlements_1.factor_client_debtor_contract_uuid_agg[1] = purchased_fcdc_1.uuid)
62. 1.139 278.495 ↑ 1.0 8,054 1

Nested Loop Left Join (cost=14,910.92..148,848.85 rows=8,246 width=168) (actual time=38.474..278.495 rows=8,054 loops=1)

  • Join Filter: (advance_amount_settlements_1.purchased_date IS NULL)
63. 2.205 156.546 ↑ 1.0 8,054 1

Nested Loop Left Join (cost=14,905.06..100,382.99 rows=8,246 width=166) (actual time=38.424..156.546 rows=8,054 loops=1)

  • Join Filter: (advance_amount_settlements_1.purchased_date IS NULL)
  • Rows Removed by Join Filter: 7,405
64. 3.491 57.693 ↑ 1.0 8,054 1

Hash Left Join (cost=14,895.14..18,306.43 rows=8,246 width=163) (actual time=38.362..57.693 rows=8,054 loops=1)

  • Hash Cond: (capfin_invoice_1.uuid = advance_amount_settlements_1.uuid)
65. 4.010 40.407 ↑ 1.0 8,054 1

Hash Left Join (cost=13,556.24..16,945.89 rows=8,246 width=127) (actual time=24.549..40.407 rows=8,054 loops=1)

  • Hash Cond: (capfin_invoice_1.uuid = discount_fee_settlements_1.uuid)
66. 1.987 20.384 ↑ 1.0 8,054 1

Hash Left Join (cost=12,025.02..15,393.02 rows=8,246 width=63) (actual time=8.524..20.384 rows=8,054 loops=1)

  • Hash Cond: (capfin_invoice_1.term_code_uuid = term_code_1.uuid)
67. 1.836 18.228 ↑ 1.0 8,054 1

Hash Left Join (cost=12,004.98..15,351.17 rows=8,246 width=70) (actual time=8.349..18.228 rows=8,054 loops=1)

  • Hash Cond: (capfin_invoice_1.client_debtor_uuid = client_debtor_1.uuid)
68. 2.087 16.141 ↑ 1.0 8,054 1

Hash Join (cost=11,973.18..15,297.61 rows=8,246 width=66) (actual time=8.090..16.141 rows=8,054 loops=1)

  • Hash Cond: (capfin_invoice_1.client_uuid = client_1.uuid)
69. 5.989 5.989 ↑ 1.0 8,054 1

Index Scan using capfin_invoice_product_type_idx on capfin_invoice capfin_invoice_1 (cost=0.42..3,211.47 rows=8,246 width=126) (actual time=0.016..5.989 rows=8,054 loops=1)

  • Index Cond: (product_type = 'FACTORING'::capfin.product_type)
70. 0.421 8.065 ↓ 1.0 2,601 1

Hash (cost=11,940.36..11,940.36 rows=2,592 width=16) (actual time=8.065..8.065 rows=2,601 loops=1)

  • Buckets: 4,096 Batches: 1 Memory Usage: 154kB
71. 0.000 7.644 ↓ 1.0 2,601 1

Nested Loop (cost=0.43..11,940.36 rows=2,592 width=16) (actual time=0.016..7.644 rows=2,601 loops=1)

72. 0.336 0.336 ↓ 1.0 2,601 1

Seq Scan on client client_1 (cost=0.00..66.92 rows=2,592 width=32) (actual time=0.005..0.336 rows=2,601 loops=1)

73. 7.803 7.803 ↑ 1.0 1 2,601

Index Only Scan using division_uuid_key on division division_1 (cost=0.43..4.58 rows=1 width=16) (actual time=0.003..0.003 rows=1 loops=2,601)

  • Index Cond: (uuid = client_1.division_uuid)
  • Heap Fetches: 506
74. 0.099 0.251 ↓ 1.0 793 1

Hash (cost=21.91..21.91 rows=791 width=36) (actual time=0.251..0.251 rows=793 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 58kB
75. 0.152 0.152 ↓ 1.0 793 1

Seq Scan on client_debtor client_debtor_1 (cost=0.00..21.91 rows=791 width=36) (actual time=0.008..0.152 rows=793 loops=1)

76. 0.079 0.169 ↑ 1.0 535 1

Hash (cost=13.35..13.35 rows=535 width=25) (actual time=0.169..0.169 rows=535 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 40kB
77. 0.090 0.090 ↑ 1.0 535 1

Seq Scan on term_code term_code_1 (cost=0.00..13.35 rows=535 width=25) (actual time=0.004..0.090 rows=535 loops=1)

78. 1.275 16.013 ↓ 1.3 7,405 1

Hash (cost=1,458.95..1,458.95 rows=5,782 width=112) (actual time=16.013..16.013 rows=7,405 loops=1)

  • Buckets: 8,192 Batches: 1 Memory Usage: 993kB
79. 0.760 14.738 ↓ 1.3 7,405 1

Subquery Scan on discount_fee_settlements_1 (cost=1,203.34..1,458.95 rows=5,782 width=112) (actual time=5.974..14.738 rows=7,405 loops=1)

80. 7.129 13.978 ↓ 1.3 7,405 1

GroupAggregate (cost=1,203.34..1,401.13 rows=5,782 width=112) (actual time=5.973..13.978 rows=7,405 loops=1)

  • Group Key: invoice_settlements_2.invoice_uuid
81. 3.011 6.849 ↑ 1.0 7,405 1

Sort (cost=1,203.34..1,222.66 rows=7,728 width=54) (actual time=5.958..6.849 rows=7,405 loops=1)

  • Sort Key: invoice_settlements_2.invoice_uuid
  • Sort Method: quicksort Memory: 1,234kB
82. 1.517 3.838 ↑ 1.0 7,405 1

Hash Join (cost=16.59..704.27 rows=7,728 width=54) (actual time=0.126..3.838 rows=7,405 loops=1)

  • Hash Cond: (invoice_settlements_2.client_settlement_uuid = settlements_2.uuid)
83. 2.206 2.206 ↑ 1.0 8,859 1

Seq Scan on client_settlement_invoice_allocation invoice_settlements_2 (cost=0.00..664.16 rows=8,864 width=70) (actual time=0.007..2.206 rows=8,859 loops=1)

  • Filter: (item_type = 'DISCOUNT_FEE'::capfin.client_invoice_allocation_type)
  • Rows Removed by Filter: 14,595
84. 0.040 0.115 ↓ 1.0 348 1

Hash (cost=12.34..12.34 rows=340 width=16) (actual time=0.115..0.115 rows=348 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 25kB
85. 0.075 0.075 ↓ 1.0 348 1

Seq Scan on client_settlement settlements_2 (cost=0.00..12.34 rows=340 width=16) (actual time=0.008..0.075 rows=348 loops=1)

  • Filter: (settlement_status = ANY ('{SENT,PAID,INITIATED,AUTHORIZED,PENDING}'::capfin.advance_status_enum[]))
  • Rows Removed by Filter: 52
86. 1.008 13.795 ↓ 1.3 7,405 1

Hash (cost=1,266.62..1,266.62 rows=5,782 width=52) (actual time=13.795..13.795 rows=7,405 loops=1)

  • Buckets: 8,192 Batches: 1 Memory Usage: 488kB
87. 1.062 12.787 ↓ 1.3 7,405 1

Subquery Scan on advance_amount_settlements_1 (cost=1,194.34..1,266.62 rows=5,782 width=52) (actual time=11.163..12.787 rows=7,405 loops=1)

88. 1.889 11.725 ↓ 1.3 7,405 1

Sort (cost=1,194.34..1,208.80 rows=5,782 width=52) (actual time=11.161..11.725 rows=7,405 loops=1)

  • Sort Key: (min(settlements_3.issued_date))
  • Sort Method: quicksort Memory: 771kB
89. 5.902 9.836 ↓ 1.3 7,405 1

HashAggregate (cost=760.77..833.05 rows=5,782 width=52) (actual time=6.683..9.836 rows=7,405 loops=1)

  • Group Key: invoice_settlements_3.invoice_uuid
90. 1.481 3.934 ↑ 1.0 7,405 1

Hash Join (cost=15.12..702.81 rows=7,728 width=26) (actual time=0.147..3.934 rows=7,405 loops=1)

  • Hash Cond: (invoice_settlements_3.client_settlement_uuid = settlements_3.uuid)
91. 2.348 2.348 ↑ 1.0 8,859 1

Seq Scan on client_settlement_invoice_allocation invoice_settlements_3 (cost=0.00..664.16 rows=8,864 width=38) (actual time=0.025..2.348 rows=8,859 loops=1)

  • Filter: (item_type = 'ADVANCE_AMOUNT'::capfin.client_invoice_allocation_type)
  • Rows Removed by Filter: 14,595
92. 0.040 0.105 ↓ 1.0 348 1

Hash (cost=10.88..10.88 rows=340 width=20) (actual time=0.105..0.105 rows=348 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 26kB
93. 0.065 0.065 ↓ 1.0 348 1

Seq Scan on client_settlement settlements_3 (cost=0.00..10.88 rows=340 width=20) (actual time=0.005..0.065 rows=348 loops=1)

  • Filter: (settlement_status <> 'CANCELED'::capfin.advance_status_enum)
  • Rows Removed by Filter: 52
94. 8.054 96.648 ↑ 1.0 1 8,054

Limit (cost=9.93..9.93 rows=1 width=55) (actual time=0.012..0.012 rows=1 loops=8,054)

95. 8.054 88.594 ↑ 1.0 1 8,054

Sort (cost=9.93..9.93 rows=1 width=55) (actual time=0.011..0.011 rows=1 loops=8,054)

  • Sort Key: cdc_1.as_of_date DESC NULLS LAST
  • Sort Method: quicksort Memory: 25kB
96. 40.639 80.540 ↑ 1.0 1 8,054

Hash Join (cost=8.31..9.92 rows=1 width=55) (actual time=0.006..0.010 rows=1 loops=8,054)

  • Hash Cond: (fcdc_1.client_debtor_contract_uuid = cdc_1.uuid)
97. 23.793 23.793 ↑ 1.0 48 7,931

Seq Scan on factor_client_debtor_contract fcdc_1 (cost=0.00..1.48 rows=48 width=35) (actual time=0.001..0.003 rows=48 loops=7,931)

98. 8.054 16.108 ↑ 1.0 1 8,054

Hash (cost=8.30..8.30 rows=1 width=20) (actual time=0.002..0.002 rows=1 loops=8,054)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
99. 8.054 8.054 ↑ 1.0 1 8,054

Index Scan using client_debtor_contract_unique_as_of_constraint on client_debtor_contract cdc_1 (cost=0.28..8.30 rows=1 width=20) (actual time=0.001..0.001 rows=1 loops=8,054)

  • Index Cond: ((client_debtor_uuid = capfin_invoice_1.client_debtor_uuid) AND (as_of_date <= CURRENT_DATE))
  • Filter: (effective_date <= CURRENT_DATE)
100. 8.054 120.810 ↓ 0.0 0 8,054

Limit (cost=5.85..5.86 rows=1 width=30) (actual time=0.015..0.015 rows=0 loops=8,054)

101. 8.054 112.756 ↓ 0.0 0 8,054

Sort (cost=5.85..5.86 rows=3 width=30) (actual time=0.014..0.014 rows=0 loops=8,054)

  • Sort Key: reference_rate_1.effective_date DESC NULLS LAST, reference_rate_1.term
  • Sort Method: quicksort Memory: 25kB
102. 104.702 104.702 ↑ 3.0 1 8,054

Seq Scan on reference_rate reference_rate_1 (cost=0.00..5.84 rows=3 width=30) (actual time=0.012..0.013 rows=1 loops=8,054)

  • Filter: ((effective_date <= CURRENT_DATE) AND (term >= GREATEST(fcdc_1.minimum_tenor, ((GREATEST(CASE WHEN (term_code_1.net_days IS NOT NULL) THEN CASE WHEN ((term_code_1.eom_code)::text = 'E'::text) THEN (((date_trunc('month'::text, ((capfin_invoice_1.transaction_date + term_code_1.net_days))::timestamp with time zone) + '1 mon'::interval) - '1 day'::interval))::date ELSE (capfin_invoice_1.transaction_date + term_code_1.net_days) END ELSE NULL::date END, capfin_invoice_1.due_date) + COALESCE(client_debtor_1.buffer_days, 0)) - CURRENT_DATE))) AND (type = capfin.reference_rate_type(fcdc_1.rate_type)))
  • Rows Removed by Filter: 15
103. 0.007 0.015 ↑ 1.0 48 1

Hash (cost=1.48..1.48 rows=48 width=21) (actual time=0.015..0.015 rows=48 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 11kB
104. 0.008 0.008 ↑ 1.0 48 1

Seq Scan on factor_client_debtor_contract purchased_fcdc_1 (cost=0.00..1.48 rows=48 width=21) (actual time=0.002..0.008 rows=48 loops=1)

105. 0.048 1,142.935 ↑ 2.4 40 1

Hash (cost=57,416.22..57,416.22 rows=95 width=122) (actual time=1,142.935..1,142.935 rows=40 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 12kB
106. 29.433 1,142.887 ↑ 2.4 40 1

Merge Left Join (cost=56,584.34..57,416.22 rows=95 width=122) (actual time=757.329..1,142.887 rows=40 loops=1)

  • Merge Cond: (invoice.uuid = dra.invoice_uuid)
  • Filter: ((COALESCE((sum(((dra.gross_amount + dra.deduction_amount) + dra.discount_amount))), '0'::numeric) - existing_settlements.amount) <> '0'::numeric)
  • Rows Removed by Filter: 5,198
107. 2.492 28.704 ↓ 55.1 5,238 1

Sort (cost=4,380.42..4,380.65 rows=95 width=90) (actual time=27.985..28.704 rows=5,238 loops=1)

  • Sort Key: invoice.uuid
  • Sort Method: quicksort Memory: 929kB
108. 1.425 26.212 ↓ 55.1 5,238 1

Hash Join (cost=1,130.98..4,377.29 rows=95 width=90) (actual time=20.006..26.212 rows=5,238 loops=1)

  • Hash Cond: (invoice.uuid = existing_settlements.target_uuid)
109. 4.867 4.867 ↑ 1.1 5,238 1

Index Scan using capfin_invoice_product_type_idx on capfin_invoice invoice (cost=0.42..3,232.08 rows=5,581 width=42) (actual time=0.072..4.867 rows=5,238 loops=1)

  • Index Cond: (product_type = 'FACTORING'::capfin.product_type)
  • Filter: (status = ANY ('{CLOSED,CHARGED_BACK}'::capfin.invoice_status_enum[]))
  • Rows Removed by Filter: 2,816
110. 1.050 19.920 ↑ 1.0 7,405 1

Hash (cost=1,037.32..1,037.32 rows=7,459 width=48) (actual time=19.920..19.920 rows=7,405 loops=1)

  • Buckets: 8,192 Batches: 1 Memory Usage: 455kB
111. 0.792 18.870 ↑ 1.0 7,405 1

Subquery Scan on existing_settlements (cost=869.50..1,037.32 rows=7,459 width=48) (actual time=15.400..18.870 rows=7,405 loops=1)

112. 5.741 18.078 ↑ 1.0 7,405 1

HashAggregate (cost=869.50..962.73 rows=7,459 width=48) (actual time=15.399..18.078 rows=7,405 loops=1)

  • Group Key: client_settlement_invoice_allocation.invoice_uuid
113.          

Initplan (for HashAggregate)

114. 0.001 2.157 ↓ 0.0 0 1

Nested Loop (cost=0.27..800.67 rows=25 width=0) (actual time=2.157..2.157 rows=0 loops=1)

115. 2.156 2.156 ↓ 0.0 0 1

Seq Scan on client_settlement_invoice_allocation csia (cost=0.00..752.11 rows=29 width=32) (actual time=2.156..2.156 rows=0 loops=1)

  • Filter: ((invoice_uuid IS NOT NULL) AND (uuid = client_settlement_uuid) AND (item_type = ANY ('{REBATE,UNDERPAYMENT,CHARGE_BACK}'::capfin.client_invoice_allocation_type[])))
  • Rows Removed by Filter: 23,454
116. 0.000 0.000 ↓ 0.0 0

Index Scan using client_settlement_pkey on client_settlement client_settlement_2 (cost=0.27..1.67 rows=1 width=16) (never executed)

  • Index Cond: (uuid = csia.uuid)
  • Filter: (settlement_status = ANY ('{PENDING,INITIATED,SENT,PAID}'::capfin.advance_status_enum[]))
117. 3.768 10.180 ↑ 1.0 20,008 1

Result (cost=16.10..683.86 rows=20,446 width=26) (actual time=2.301..10.180 rows=20,008 loops=1)

  • One-Time Filter: (NOT $21)
118. 4.185 6.412 ↑ 1.0 20,008 1

Hash Join (cost=16.10..683.86 rows=20,446 width=26) (actual time=0.141..6.412 rows=20,008 loops=1)

  • Hash Cond: (client_settlement_invoice_allocation.client_settlement_uuid = client_settlement.uuid)
119. 2.098 2.098 ↓ 1.0 23,454 1

Seq Scan on client_settlement_invoice_allocation (cost=0.00..605.53 rows=23,453 width=42) (actual time=0.004..2.098 rows=23,454 loops=1)

120. 0.052 0.129 ↓ 1.0 348 1

Hash (cost=11.85..11.85 rows=340 width=16) (actual time=0.129..0.129 rows=348 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 25kB
121. 0.077 0.077 ↓ 1.0 348 1

Seq Scan on client_settlement (cost=0.00..11.85 rows=340 width=16) (actual time=0.006..0.077 rows=348 loops=1)

  • Filter: (settlement_status = ANY ('{PENDING,INITIATED,SENT,PAID}'::capfin.advance_status_enum[]))
  • Rows Removed by Filter: 52
122. 60.667 1,084.750 ↓ 15.3 299,433 1

Materialize (cost=52,203.93..52,986.37 rows=19,561 width=48) (actual time=722.253..1,084.750 rows=299,433 loops=1)

123. 250.032 1,024.083 ↓ 15.3 299,433 1

GroupAggregate (cost=52,203.93..52,741.85 rows=19,561 width=64) (actual time=722.249..1,024.083 rows=299,433 loops=1)

  • Group Key: dra.invoice_uuid, invoice_1.client_uuid
124. 200.089 774.051 ↓ 15.3 299,534 1

Sort (cost=52,203.93..52,252.83 rows=19,561 width=45) (actual time=722.236..774.051 rows=299,534 loops=1)

  • Sort Key: dra.invoice_uuid, invoice_1.client_uuid
  • Sort Method: external merge Disk: 16,600kB
125. 264.685 573.962 ↓ 15.3 299,589 1

Hash Join (cost=20,689.47..50,809.65 rows=19,561 width=45) (actual time=194.371..573.962 rows=299,589 loops=1)

  • Hash Cond: ((invoice_1.uuid = dra.invoice_uuid) AND (invoice_1.client_uuid = debtor_remittance.client_uuid))
126. 115.043 115.043 ↓ 1.0 440,203 1

Seq Scan on capfin_invoice invoice_1 (cost=0.00..18,151.54 rows=440,154 width=32) (actual time=0.009..115.043 rows=440,203 loops=1)

127. 76.685 194.234 ↓ 1.1 299,589 1

Hash (cost=13,935.02..13,935.02 rows=283,897 width=45) (actual time=194.234..194.234 rows=299,589 loops=1)

  • Buckets: 65,536 Batches: 8 Memory Usage: 3,398kB
128. 52.542 117.549 ↓ 1.1 299,589 1

Hash Join (cost=485.19..13,935.02 rows=283,897 width=45) (actual time=3.163..117.549 rows=299,589 loops=1)

  • Hash Cond: (dra.debtor_remittance_uuid = debtor_remittance.uuid)
129. 61.869 61.869 ↑ 1.0 306,438 1

Seq Scan on debtor_remittance_allocation dra (cost=0.00..12,634.78 rows=310,383 width=45) (actual time=0.006..61.869 rows=306,438 loops=1)

  • Filter: (allocation_type = 'ELIGIBLE'::capfin.repayment_allocation_type_enum)
  • Rows Removed by Filter: 101,431
130. 1.322 3.138 ↓ 1.0 10,721 1

Hash (cost=351.34..351.34 rows=10,708 width=32) (actual time=3.138..3.138 rows=10,721 loops=1)

  • Buckets: 16,384 Batches: 1 Memory Usage: 799kB
131. 1.816 1.816 ↓ 1.0 10,721 1

Seq Scan on debtor_remittance (cost=0.00..351.34 rows=10,708 width=32) (actual time=0.005..1.816 rows=10,721 loops=1)

  • Filter: (settlement_status = 'SUCCESSFUL'::capfin.settlement_status_enum)
  • Rows Removed by Filter: 989
Planning time : 9.795 ms
Execution time : 1,737.420 ms