explain.depesz.com

PostgreSQL's explain analyze made readable

Result: icJc

Settings
# exclusive inclusive rows x rows loops node
1. 0.019 1,620.718 ↑ 1.4 114 1

Append (cost=16,764.36..354,749.00 rows=155 width=432) (actual time=49.976..1,620.718 rows=114 loops=1)

2.          

CTE recursive_new_invoices

3. 0.014 1.048 ↓ 1.2 74 1

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

4. 0.051 1.024 ↑ 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.880..1.024 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.031 0.862 ↓ 0.0 0 1

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

6. 0.284 0.284 ↓ 1.5 6,406 1

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

  • Index Cond: (status = 'APPROVED'::capfin.invoice_status_enum)
7. 0.547 0.547 ↑ 1.0 8,054 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,054 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.034 308.300 ↓ 1.2 74 1

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

25. 0.431 0.431 ↓ 1.0 2,601 1

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

26. 7.803 7.803 ↑ 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.003..0.003 rows=1 loops=2,601)

  • Index Cond: (uuid = client.division_uuid)
  • Heap Fetches: 537
27. 0.102 0.257 ↓ 1.0 793 1

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

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

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

29. 0.080 0.173 ↑ 1.0 535 1

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

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

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

31. 1.264 16.165 ↓ 1.3 7,405 1

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

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

33. 7.264 14.178 ↓ 1.3 7,405 1

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

  • Group Key: invoice_settlements.invoice_uuid
34. 3.033 6.914 ↑ 1.0 7,405 1

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

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

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

  • Hash Cond: (invoice_settlements.client_settlement_uuid = settlements.uuid)
36. 2.188 2.188 ↑ 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.004..2.188 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.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
38. 0.070 0.070 ↓ 1.0 348 1

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

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

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

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

41. 2.395 14.392 ↓ 1.3 7,405 1

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

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

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

  • Group Key: invoice_settlements_1.invoice_uuid
43. 1.732 4.729 ↑ 1.0 7,405 1

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

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

  • Filter: (item_type = 'ADVANCE_AMOUNT'::capfin.client_invoice_allocation_type)
  • Rows Removed by Filter: 14,595
45. 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
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. 8.054 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.762 88.594 ↑ 1.0 1 8,054

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

  • Hash Cond: (fcdc.client_debtor_contract_uuid = cdc.uuid)
50. 31.724 31.724 ↑ 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.004 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 128.864 ↓ 0.0 0 8,054

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

54. 16.108 128.864 ↓ 0.0 0 8,054

Sort (cost=5.85..5.86 rows=3 width=30) (actual time=0.016..0.016 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.014..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.082 ↓ 1.2 74 1

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

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

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

60. 3.326 1,312.399 ↑ 2.4 40 1

Hash Join (cost=59,235.42..198,609.64 rows=95 width=236) (actual time=1,041.560..1,312.399 rows=40 loops=1)

  • Hash Cond: (invoice.uuid = existing_settlements.target_uuid)
  • Join Filter: ((COALESCE((sum(((dra.gross_amount + dra.deduction_amount) + dra.discount_amount))), '0'::numeric) - existing_settlements.amount) <> '0'::numeric)
  • Rows Removed by Join Filter: 5,198
61. 3.599 1,289.003 ↓ 49.9 5,238 1

Hash Join (cost=58,104.86..197,477.38 rows=105 width=188) (actual time=1,021.473..1,289.003 rows=5,238 loops=1)

  • Hash Cond: (capfin_invoice_1.uuid = invoice.uuid)
62. 12.640 307.882 ↑ 1.0 8,054 1

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

  • Hash Cond: (discount_fee_settlements_1.factor_client_debtor_contract_uuid_agg[1] = purchased_fcdc_1.uuid)
63. 3.690 295.224 ↑ 1.0 8,054 1

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

  • Join Filter: (advance_amount_settlements_1.purchased_date IS NULL)
64. 6.603 162.670 ↑ 1.0 8,054 1

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

  • Join Filter: (advance_amount_settlements_1.purchased_date IS NULL)
  • Rows Removed by Join Filter: 7,405
65. 3.666 59.419 ↑ 1.0 8,054 1

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

  • Hash Cond: (capfin_invoice_1.uuid = advance_amount_settlements_1.uuid)
66. 4.154 41.990 ↑ 1.0 8,054 1

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

  • Hash Cond: (capfin_invoice_1.uuid = discount_fee_settlements_1.uuid)
67. 1.949 21.493 ↑ 1.0 8,054 1

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

  • Hash Cond: (capfin_invoice_1.term_code_uuid = term_code_1.uuid)
68. 1.924 19.370 ↑ 1.0 8,054 1

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

  • Hash Cond: (capfin_invoice_1.client_debtor_uuid = client_debtor_1.uuid)
69. 1.952 17.185 ↑ 1.0 8,054 1

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

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

  • Index Cond: (product_type = 'FACTORING'::capfin.product_type)
71. 0.373 8.581 ↓ 1.0 2,601 1

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

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

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

73. 0.445 0.445 ↓ 1.0 2,601 1

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

74. 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: 537
75. 0.106 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
76. 0.155 0.155 ↓ 1.0 793 1

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

77. 0.084 0.174 ↑ 1.0 535 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 40kB
78. 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.005..0.090 rows=535 loops=1)

79. 1.284 16.343 ↓ 1.3 7,405 1

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

  • Buckets: 8,192 Batches: 1 Memory Usage: 993kB
80. 0.738 15.059 ↓ 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=6.233..15.059 rows=7,405 loops=1)

81. 7.237 14.321 ↓ 1.3 7,405 1

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

  • Group Key: invoice_settlements_2.invoice_uuid
82. 3.096 7.084 ↑ 1.0 7,405 1

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

  • Sort Key: invoice_settlements_2.invoice_uuid
  • Sort Method: quicksort Memory: 1,234kB
83. 1.594 3.988 ↑ 1.0 7,405 1

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

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

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

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 25kB
86. 0.074 0.074 ↓ 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.074 rows=348 loops=1)

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

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

  • Buckets: 8,192 Batches: 1 Memory Usage: 488kB
88. 0.820 12.573 ↓ 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.287..12.573 rows=7,405 loops=1)

89. 1.990 11.753 ↓ 1.3 7,405 1

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

  • Sort Key: (min(settlements_3.issued_date))
  • Sort Method: quicksort Memory: 771kB
90. 5.800 9.763 ↓ 1.3 7,405 1

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

  • Group Key: invoice_settlements_3.invoice_uuid
91. 1.431 3.963 ↑ 1.0 7,405 1

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

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

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

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 26kB
94. 0.068 0.068 ↓ 1.0 348 1

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

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

96. 8.054 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_1.as_of_date DESC NULLS LAST
  • Sort Method: quicksort Memory: 25kB
97. 40.762 88.594 ↑ 1.0 1 8,054

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

  • Hash Cond: (fcdc_1.client_debtor_contract_uuid = cdc_1.uuid)
98. 31.724 31.724 ↑ 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.004 rows=48 loops=7,931)

99. 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
100. 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)
101. 0.000 128.864 ↓ 0.0 0 8,054

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

102. 8.054 128.864 ↓ 0.0 0 8,054

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

  • Sort Key: reference_rate_1.effective_date DESC NULLS LAST, reference_rate_1.term
  • Sort Method: quicksort Memory: 25kB
103. 120.810 120.810 ↑ 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.015 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
104. 0.011 0.018 ↑ 1.0 48 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 11kB
105. 0.007 0.007 ↑ 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.007 rows=48 loops=1)

106. 1.386 977.522 ↑ 1.1 5,238 1

Hash (cost=43,122.10..43,122.10 rows=5,581 width=74) (actual time=977.522..977.522 rows=5,238 loops=1)

  • Buckets: 8,192 Batches: 1 Memory Usage: 458kB
107. 35.186 976.136 ↑ 1.1 5,238 1

Hash Right Join (cost=42,640.02..43,122.10 rows=5,581 width=74) (actual time=774.039..976.136 rows=5,238 loops=1)

  • Hash Cond: (dra.invoice_uuid = invoice.uuid)
108. 411.921 935.356 ↓ 15.6 299,489 1

HashAggregate (cost=39,338.18..39,578.02 rows=19,187 width=64) (actual time=768.380..935.356 rows=299,489 loops=1)

  • Group Key: dra.invoice_uuid, invoice_1.client_uuid
109. 68.993 523.435 ↓ 15.6 299,589 1

Hash Join (cost=24,165.42..39,098.34 rows=19,187 width=45) (actual time=241.228..523.435 rows=299,589 loops=1)

  • Hash Cond: ((dra.debtor_remittance_uuid = debtor_remittance.uuid) AND (invoice_1.client_uuid = debtor_remittance.client_uuid))
110. 137.173 450.599 ↓ 1.0 306,438 1

Hash Join (cost=23,653.47..36,988.01 rows=304,447 width=61) (actual time=237.314..450.599 rows=306,438 loops=1)

  • Hash Cond: (dra.invoice_uuid = invoice_1.uuid)
111. 77.957 77.957 ↓ 1.0 306,438 1

Seq Scan on debtor_remittance_allocation dra (cost=0.00..12,535.36 rows=304,447 width=45) (actual time=0.006..77.957 rows=306,438 loops=1)

  • Filter: (allocation_type = 'ELIGIBLE'::capfin.repayment_allocation_type_enum)
  • Rows Removed by Filter: 101,431
112. 107.078 235.469 ↓ 1.0 440,203 1

Hash (cost=18,151.54..18,151.54 rows=440,154 width=32) (actual time=235.469..235.469 rows=440,203 loops=1)

  • Buckets: 524,288 Batches: 1 Memory Usage: 31,609kB
113. 128.391 128.391 ↓ 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.006..128.391 rows=440,203 loops=1)

114. 1.831 3.843 ↓ 1.0 10,721 1

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

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

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

  • Filter: (settlement_status = 'SUCCESSFUL'::capfin.settlement_status_enum)
  • Rows Removed by Filter: 989
116. 0.974 5.594 ↑ 1.1 5,238 1

Hash (cost=3,232.08..3,232.08 rows=5,581 width=42) (actual time=5.594..5.594 rows=5,238 loops=1)

  • Buckets: 8,192 Batches: 1 Memory Usage: 430kB
117. 4.620 4.620 ↑ 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.074..4.620 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
118. 1.091 20.070 ↑ 1.0 7,405 1

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

  • Buckets: 8,192 Batches: 1 Memory Usage: 455kB
119. 0.770 18.979 ↑ 1.0 7,405 1

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

120. 5.674 18.209 ↑ 1.0 7,405 1

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

  • Group Key: client_settlement_invoice_allocation.invoice_uuid
121.          

Initplan (for HashAggregate)

122. 0.000 2.088 ↓ 0.0 0 1

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

123. 2.088 2.088 ↓ 0.0 0 1

Seq Scan on client_settlement_invoice_allocation csia (cost=0.00..752.11 rows=29 width=32) (actual time=2.087..2.088 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
124. 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[]))
125. 3.698 10.447 ↑ 1.0 20,008 1

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

  • One-Time Filter: (NOT $21)
126. 4.854 6.749 ↑ 1.0 20,008 1

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

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

128. 0.053 0.128 ↓ 1.0 348 1

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

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

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

  • Filter: (settlement_status = ANY ('{PENDING,INITIATED,SENT,PAID}'::capfin.advance_status_enum[]))
  • Rows Removed by Filter: 52
Planning time : 10.107 ms
Execution time : 1,630.687 ms