explain.depesz.com

PostgreSQL's explain analyze made readable

Result: dKDh

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

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

2.          

CTE recursive_new_invoices

3. 0.015 1.048 ↓ 1.2 74 1

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

4. 0.064 1.023 ↑ 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.868..1.023 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.032 0.848 ↓ 0.0 0 1

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

6. 0.273 0.273 ↓ 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.273..0.273 rows=6,408 loops=1)

  • Index Cond: (status = 'APPROVED'::capfin.invoice_status_enum)
7. 0.543 0.543 ↓ 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.543..0.543 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.009 292.437 ↓ 1.2 74 1

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

  • Hash Cond: (capfin_invoice.client_uuid = client.uuid)
22. 5.885 5.885 ↑ 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.885 rows=8,054 loops=1)

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

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

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

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

25. 0.338 0.338 ↓ 1.0 2,601 1

Seq Scan on client (cost=0.00..66.92 rows=2,592 width=32) (actual time=0.006..0.338 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: 498
27. 0.105 0.256 ↓ 1.0 793 1

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

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

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

29. 0.083 0.178 ↑ 1.0 535 1

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

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

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

31. 1.255 16.154 ↓ 1.3 7,405 1

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

  • Buckets: 8,192 Batches: 1 Memory Usage: 993kB
32. 0.748 14.899 ↓ 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=5.984..14.899 rows=7,405 loops=1)

33. 7.281 14.151 ↓ 1.3 7,405 1

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

  • Group Key: invoice_settlements.invoice_uuid
34. 3.013 6.870 ↑ 1.0 7,405 1

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

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

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

  • Hash Cond: (invoice_settlements.client_settlement_uuid = settlements.uuid)
36. 2.268 2.268 ↑ 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.268 rows=8,859 loops=1)

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

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

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

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

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

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

  • Buckets: 8,192 Batches: 1 Memory Usage: 488kB
40. 0.896 12.327 ↓ 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=10.912..12.327 rows=7,405 loops=1)

41. 1.841 11.431 ↓ 1.3 7,405 1

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

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

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

  • Group Key: invoice_settlements_1.invoice_uuid
43. 1.445 3.852 ↑ 1.0 7,405 1

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

  • Hash Cond: (invoice_settlements_1.client_settlement_uuid = settlements_1.uuid)
44. 2.300 2.300 ↑ 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.021..2.300 rows=8,859 loops=1)

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

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 26kB
46. 0.064 0.064 ↓ 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.064 rows=348 loops=1)

  • Filter: (settlement_status <> 'CANCELED'::capfin.advance_status_enum)
  • Rows Removed by Filter: 52
47. 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)

48. 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.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. 16.108 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. 104.702 104.702 ↑ 3.0 1 8,054

Seq Scan on reference_rate (cost=0.00..5.84 rows=3 width=30) (actual time=0.013..0.013 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.008 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
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.011 1.084 ↓ 1.2 74 1

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

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

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

60. 0.946 1,431.764 ↑ 2.4 40 1

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

  • Hash Cond: (capfin_invoice_1.client_uuid = client_1.uuid)
69. 5.847 5.847 ↑ 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.847 rows=8,054 loops=1)

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

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

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

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

72. 0.331 0.331 ↓ 1.0 2,601 1

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

73. 5.202 5.202 ↑ 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.002..0.002 rows=1 loops=2,601)

  • Index Cond: (uuid = client_1.division_uuid)
  • Heap Fetches: 498
74. 0.104 0.261 ↓ 1.0 793 1

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

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

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

76. 0.085 0.180 ↑ 1.0 535 1

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

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

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

78. 1.296 15.934 ↓ 1.3 7,405 1

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

  • Buckets: 8,192 Batches: 1 Memory Usage: 993kB
79. 0.756 14.638 ↓ 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.940..14.638 rows=7,405 loops=1)

80. 7.076 13.882 ↓ 1.3 7,405 1

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

  • Group Key: invoice_settlements_2.invoice_uuid
81. 2.995 6.806 ↑ 1.0 7,405 1

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

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

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

  • Hash Cond: (invoice_settlements_2.client_settlement_uuid = settlements_2.uuid)
83. 2.222 2.222 ↑ 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.006..2.222 rows=8,859 loops=1)

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

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

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

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

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

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

  • Buckets: 8,192 Batches: 1 Memory Usage: 488kB
87. 0.884 12.153 ↓ 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=10.769..12.153 rows=7,405 loops=1)

88. 1.814 11.269 ↓ 1.3 7,405 1

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

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

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

  • Group Key: invoice_settlements_3.invoice_uuid
90. 1.439 3.816 ↑ 1.0 7,405 1

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

  • Hash Cond: (invoice_settlements_3.client_settlement_uuid = settlements_3.uuid)
91. 2.270 2.270 ↑ 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.022..2.270 rows=8,859 loops=1)

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

Hash (cost=10.88..10.88 rows=340 width=20) (actual time=0.107..0.107 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. 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)

101. 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_1.effective_date DESC NULLS LAST, reference_rate_1.term
  • Sort Method: quicksort Memory: 25kB
102. 112.756 112.756 ↑ 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.013..0.014 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.046 1,139.797 ↑ 2.4 40 1

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

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

Merge Left Join (cost=56,584.34..57,416.22 rows=95 width=122) (actual time=753.058..1,139.751 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.482 28.247 ↓ 55.1 5,238 1

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

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

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

  • Hash Cond: (invoice.uuid = existing_settlements.target_uuid)
109. 4.829 4.829 ↑ 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.070..4.829 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.091 19.624 ↑ 1.0 7,405 1

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

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

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

112. 5.373 17.759 ↑ 1.0 7,405 1

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

  • Group Key: client_settlement_invoice_allocation.invoice_uuid
113.          

Initplan (for HashAggregate)

114. 0.000 2.162 ↓ 0.0 0 1

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

115. 2.162 2.162 ↓ 0.0 0 1

Seq Scan on client_settlement_invoice_allocation csia (cost=0.00..752.11 rows=29 width=32) (actual time=2.162..2.162 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.845 10.224 ↑ 1.0 20,008 1

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

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

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

  • Hash Cond: (client_settlement_invoice_allocation.client_settlement_uuid = client_settlement.uuid)
119. 2.054 2.054 ↓ 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.005..2.054 rows=23,454 loops=1)

120. 0.040 0.117 ↓ 1.0 348 1

Hash (cost=11.85..11.85 rows=340 width=16) (actual time=0.117..0.117 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.007..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.074 1,082.215 ↓ 15.3 299,433 1

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

123. 251.766 1,022.141 ↓ 15.3 299,433 1

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

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

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

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

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

  • Hash Cond: ((invoice_1.uuid = dra.invoice_uuid) AND (invoice_1.client_uuid = debtor_remittance.client_uuid))
126. 113.418 113.418 ↓ 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..113.418 rows=440,203 loops=1)

127. 79.957 196.449 ↓ 1.1 299,589 1

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

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

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

  • Hash Cond: (dra.debtor_remittance_uuid = debtor_remittance.uuid)
129. 61.340 61.340 ↑ 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.005..61.340 rows=306,438 loops=1)

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

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

  • Buckets: 16,384 Batches: 1 Memory Usage: 799kB
131. 1.818 1.818 ↓ 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.818 rows=10,721 loops=1)

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