explain.depesz.com

PostgreSQL's explain analyze made readable

Result: S5Pe

Settings
# exclusive inclusive rows x rows loops node
1. 0.014 1,622.107 ↑ 1.4 114 1

Append (cost=16,764.36..354,909.23 rows=155 width=432) (actual time=89.862..1,622.107 rows=114 loops=1)

2.          

CTE recursive_new_invoices

3. 0.023 36.725 ↓ 1.2 74 1

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

4. 0.105 36.690 ↑ 1.1 37 1

Bitmap Heap Scan on capfin_invoice invoice_2 (cost=342.76..1,758.21 rows=40 width=46) (actual time=24.601..36.690 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 24.560 ↓ 0.0 0 1

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

6. 0.332 0.332 ↓ 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.332..0.332 rows=6,406 loops=1)

  • Index Cond: (status = 'APPROVED'::capfin.invoice_status_enum)
7. 24.195 24.195 ↑ 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=24.195..24.195 rows=8,054 loops=1)

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

SubPlan (for Bitmap Heap Scan)

9. 0.004 12.025 ↓ 0.0 0 37

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

10. 11.581 11.581 ↓ 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.303..0.313 rows=0 loops=37)

  • Index Cond: (invoice_uuid = invoice_2.uuid)
11. 0.440 0.440 ↓ 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.055..0.055 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.012 0.012 ↓ 9.0 18 2

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

25. 0.430 0.430 ↓ 1.0 2,601 1

Seq Scan on client (cost=0.00..66.92 rows=2,592 width=32) (actual time=0.005..0.430 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.105 0.253 ↓ 1.0 793 1

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

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

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

29. 0.096 0.190 ↑ 1.0 535 1

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

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

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

31. 1.597 21.198 ↓ 1.3 7,405 1

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

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

33. 7.181 18.932 ↓ 1.3 7,405 1

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

  • Group Key: invoice_settlements.invoice_uuid
34. 3.648 11.751 ↑ 1.0 7,405 1

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

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

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

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

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

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

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

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

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

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

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

41. 2.104 12.550 ↓ 1.3 7,405 1

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

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

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

  • Group Key: invoice_settlements_1.invoice_uuid
43. 1.529 4.001 ↑ 1.0 7,405 1

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

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

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

Hash (cost=10.88..10.88 rows=340 width=20) (actual time=0.113..0.113 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. 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. 32.708 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. 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. 0.000 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. 16.108 16.108 ↑ 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.002 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.014 0.024 ↑ 1.0 48 1

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

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

58. 0.015 36.771 ↓ 1.2 74 1

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

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

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

60. 3.213 1,283.218 ↑ 2.4 40 1

Hash Join (cost=59,395.65..198,769.87 rows=95 width=236) (actual time=1,025.800..1,283.218 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.501 1,259.110 ↓ 49.9 5,238 1

Hash Join (cost=58,265.09..197,637.61 rows=105 width=188) (actual time=1,004.849..1,259.110 rows=5,238 loops=1)

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

73. 0.457 0.457 ↓ 1.0 2,601 1

Seq Scan on client client_1 (cost=0.00..66.92 rows=2,592 width=32) (actual time=0.009..0.457 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.125 0.276 ↓ 1.0 793 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 58kB
76. 0.151 0.151 ↓ 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.151 rows=793 loops=1)

77. 0.095 0.187 ↑ 1.0 535 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 40kB
78. 0.092 0.092 ↑ 1.0 535 1

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

79. 1.623 16.861 ↓ 1.3 7,405 1

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

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

81. 7.193 14.526 ↓ 1.3 7,405 1

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

  • Group Key: invoice_settlements_2.invoice_uuid
82. 3.466 7.333 ↑ 1.0 7,405 1

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

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

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

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

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

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 25kB
86. 0.072 0.072 ↓ 1.0 348 1

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

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

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

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

89. 2.218 12.957 ↓ 1.3 7,405 1

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

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

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

  • Group Key: invoice_settlements_3.invoice_uuid
91. 1.443 4.024 ↑ 1.0 7,405 1

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

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

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

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 26kB
94. 0.066 0.066 ↓ 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.066 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. 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_1.as_of_date DESC NULLS LAST
  • Sort Method: quicksort Memory: 25kB
97. 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)
98. 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)

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 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)

102. 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
103. 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
104. 0.010 0.017 ↑ 1.0 48 1

Hash (cost=1.48..1.48 rows=48 width=21) (actual time=0.017..0.017 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.445 958.863 ↑ 1.1 5,238 1

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

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

Hash Right Join (cost=42,790.86..43,282.33 rows=5,581 width=74) (actual time=747.629..957.418 rows=5,238 loops=1)

  • Hash Cond: (dra.invoice_uuid = invoice.uuid)
108. 413.715 914.483 ↓ 15.3 299,489 1

HashAggregate (cost=39,489.01..39,733.52 rows=19,561 width=64) (actual time=741.959..914.483 rows=299,489 loops=1)

  • Group Key: dra.invoice_uuid, invoice_1.client_uuid
109. 64.654 500.768 ↓ 15.3 299,589 1

Hash Join (cost=24,165.42..39,244.50 rows=19,561 width=45) (actual time=226.177..500.768 rows=299,589 loops=1)

  • Hash Cond: ((dra.debtor_remittance_uuid = debtor_remittance.uuid) AND (invoice_1.client_uuid = debtor_remittance.client_uuid))
110. 135.718 432.336 ↑ 1.0 306,438 1

Hash Join (cost=23,653.47..37,103.01 rows=310,383 width=61) (actual time=222.331..432.336 rows=306,438 loops=1)

  • Hash Cond: (dra.invoice_uuid = invoice_1.uuid)
111. 76.134 76.134 ↑ 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..76.134 rows=306,438 loops=1)

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

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

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

114. 1.791 3.778 ↓ 1.0 10,721 1

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

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

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

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

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

  • Buckets: 8,192 Batches: 1 Memory Usage: 430kB
117. 4.642 4.642 ↑ 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.642 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.263 20.895 ↑ 1.0 7,405 1

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

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

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

120. 6.508 19.023 ↑ 1.0 7,405 1

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

  • Group Key: client_settlement_invoice_allocation.invoice_uuid
121.          

Initplan (for HashAggregate)

122. 0.000 2.092 ↓ 0.0 0 1

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

123. 2.092 2.092 ↓ 0.0 0 1

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

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

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

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

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

128. 0.049 0.124 ↓ 1.0 348 1

Hash (cost=11.85..11.85 rows=340 width=16) (actual time=0.124..0.124 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.007..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 : 33.872 ms
Execution time : 1,630.858 ms