explain.depesz.com

PostgreSQL's explain analyze made readable

Result: S5Yr : Optimization for: plan #Xllj

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 134.701 32,044.862 ↓ 13.0 4,665 1

Sort (cost=9,130,719.11..9,130,720.00 rows=358 width=384) (actual time=32,006.158..32,044.862 rows=4,665 loops=1)

  • Sort Key: aggregate.transaction_date DESC, aggregate.client_settlement_uuid
  • Sort Method: quicksort Memory: 1229kB
2. 90.530 31,910.161 ↓ 13.0 4,665 1

Hash Right Join (cost=113,125.51..9,130,703.92 rows=358 width=384) (actual time=28,699.279..31,910.161 rows=4,665 loops=1)

  • Hash Cond: (factor_invoices_query.uuid = aggregate.invoice_uuid)
  • Join Filter: (aggregate.invoice_id_alt IS NULL)
  • Rows Removed by Join Filter: 1488
3. 106.015 22,292.737 ↑ 155.7 3,683 1

Hash Left Join (cost=66,594.07..9,075,563.87 rows=573,460 width=2,958) (actual time=19,172.357..22,292.737 rows=3,683 loops=1)

  • Hash Cond: ((array_agg(invoice_settlements.factor_client_debtor_contract_uuid))[1] = purchased_fcdc.uuid)
4.          

CTE factor_invoices_query

5. 172.298 18,555.802 ↓ 1.0 3,683 1

Hash Left Join (cost=52,848.30..65,011.31 rows=3,578 width=1,029) (actual time=9,589.638..18,555.802 rows=3,683 loops=1)

  • Hash Cond: (capfin_invoice.verification_uuid = invoice_verification_details.uuid)
6. 92.706 18,308.778 ↓ 1.0 3,683 1

Hash Left Join (cost=52,731.71..63,590.32 rows=3,578 width=949) (actual time=9,514.811..18,308.778 rows=3,683 loops=1)

  • Hash Cond: (capfin_invoice.term_code_uuid = term_code.uuid)
7. 1,685.420 18,204.929 ↓ 1.0 3,683 1

Merge Right Join (cost=52,712.74..63,522.18 rows=3,578 width=940) (actual time=9,503.624..18,204.929 rows=3,683 loops=1)

  • Merge Cond: ((debtor_remittance.client_uuid = capfin_invoice.client_uuid) AND (debtor_remittance_allocation.invoice_uuid = capfin_invoice.uuid))
8. 4,702.723 15,441.858 ↑ 1.4 137,110 1

GroupAggregate (cost=32,214.04..40,055.84 rows=196,045 width=197) (actual time=8,245.341..15,441.858 rows=137,110 loops=1)

  • Group Key: debtor_remittance.client_uuid, debtor_remittance_allocation.invoice_uuid
9. 4,695.088 10,739.135 ↓ 1.0 199,229 1

Sort (cost=32,214.04..32,704.15 rows=196,045 width=45) (actual time=8,245.277..10,739.135 rows=199,229 loops=1)

  • Sort Key: debtor_remittance.client_uuid, debtor_remittance_allocation.invoice_uuid
  • Sort Method: external merge Disk: 9960kB
10. 3,886.754 6,044.047 ↓ 1.0 200,049 1

Hash Join (cost=374.36..8,946.87 rows=196,045 width=45) (actual time=151.245..6,044.047 rows=200,049 loops=1)

  • Hash Cond: (debtor_remittance_allocation.debtor_remittance_uuid = debtor_remittance.uuid)
11. 2,006.104 2,006.104 ↑ 1.0 217,168 1

Seq Scan on debtor_remittance_allocation (cost=0.00..5,797.68 rows=217,168 width=45) (actual time=0.019..2,006.104 rows=217,168 loops=1)

12. 75.012 151.189 ↑ 1.0 8,288 1

Hash (cost=270.76..270.76 rows=8,288 width=32) (actual time=151.181..151.189 rows=8,288 loops=1)

  • Buckets: 16384 Batches: 1 Memory Usage: 646kB
13. 76.177 76.177 ↑ 1.0 8,288 1

Seq Scan on debtor_remittance (cost=0.00..270.76 rows=8,288 width=32) (actual time=0.016..76.177 rows=8,288 loops=1)

  • Filter: (settlement_status = 'SUCCESSFUL'::capfin.settlement_status_enum)
  • Rows Removed by Filter: 893
14. 92.155 1,077.651 ↓ 1.0 3,683 1

Sort (cost=20,498.70..20,507.65 rows=3,578 width=812) (actual time=1,041.188..1,077.651 rows=3,683 loops=1)

  • Sort Key: capfin_invoice.client_uuid, capfin_invoice.uuid
  • Sort Method: quicksort Memory: 1966kB
15. 108.146 985.496 ↓ 1.0 3,683 1

Hash Left Join (cost=19,058.24..20,287.51 rows=3,578 width=812) (actual time=453.625..985.496 rows=3,683 loops=1)

  • Hash Cond: (capfin_invoice.client_invoice_upload_uuid = client_invoice_upload.uuid)
16. 88.527 848.006 ↓ 1.0 3,683 1

Hash Left Join (cost=18,974.11..20,170.49 rows=3,578 width=804) (actual time=424.248..848.006 rows=3,683 loops=1)

  • Hash Cond: (capfin_invoice.tpf_import_upload_status_id = tpf_import_upload_status.id)
17. 183.559 562.971 ↓ 1.0 3,683 1

Hash Join (cost=18,364.39..19,531.10 rows=3,578 width=796) (actual time=227.635..562.971 rows=3,683 loops=1)

  • Hash Cond: (capfin_invoice.client_uuid = client_2.uuid)
18. 97.652 187.380 ↓ 1.0 3,683 1

Hash Left Join (cost=69.38..1,186.90 rows=3,578 width=763) (actual time=35.567..187.380 rows=3,683 loops=1)

  • Hash Cond: (capfin_invoice.client_debtor_uuid = client_debtor.uuid)
19. 54.229 54.229 ↓ 1.0 3,683 1

Index Scan using capfin_invoice_product_type_idx on capfin_invoice (cost=0.42..1,068.77 rows=3,578 width=724) (actual time=0.040..54.229 rows=3,683 loops=1)

  • Index Cond: (product_type = 'FACTORING'::capfin.product_type)
20. 6.107 35.499 ↑ 1.0 703 1

Hash (cost=60.17..60.17 rows=703 width=55) (actual time=35.491..35.499 rows=703 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 65kB
21. 11.792 29.392 ↑ 1.0 703 1

Hash Left Join (cost=31.48..60.17 rows=703 width=55) (actual time=11.676..29.392 rows=703 loops=1)

  • Hash Cond: (client_debtor.debtor_uuid = debtor_2.uuid)
22. 5.967 5.967 ↑ 1.0 703 1

Seq Scan on client_debtor (cost=0.00..19.03 rows=703 width=36) (actual time=0.015..5.967 rows=703 loops=1)

23. 5.607 11.633 ↑ 1.0 599 1

Hash (cost=23.99..23.99 rows=599 width=35) (actual time=11.625..11.633 rows=599 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 48kB
24. 6.026 6.026 ↑ 1.0 599 1

Seq Scan on debtor debtor_2 (cost=0.00..23.99 rows=599 width=35) (actual time=0.018..6.026 rows=599 loops=1)

25. 23.541 192.032 ↑ 1.0 2,333 1

Hash (cost=18,265.85..18,265.85 rows=2,333 width=49) (actual time=192.024..192.032 rows=2,333 loops=1)

  • Buckets: 4096 Batches: 1 Memory Usage: 219kB
26. 83.666 168.491 ↑ 1.0 2,333 1

Nested Loop (cost=0.43..18,265.85 rows=2,333 width=49) (actual time=0.068..168.491 rows=2,333 loops=1)

27. 21.834 21.834 ↑ 1.0 2,333 1

Seq Scan on client client_2 (cost=0.00..60.33 rows=2,333 width=32) (actual time=0.023..21.834 rows=2,333 loops=1)

28. 62.991 62.991 ↑ 1.0 1 2,333

Index Scan using division_uuid_key on division (cost=0.43..7.79 rows=1 width=33) (actual time=0.017..0.027 rows=1 loops=2,333)

  • Index Cond: (uuid = client_2.division_uuid)
29. 97.717 196.508 ↑ 1.0 10,832 1

Hash (cost=474.32..474.32 rows=10,832 width=16) (actual time=196.500..196.508 rows=10,832 loops=1)

  • Buckets: 16384 Batches: 1 Memory Usage: 636kB
30. 98.791 98.791 ↑ 1.0 10,832 1

Seq Scan on tpf_import_upload_status (cost=0.00..474.32 rows=10,832 width=16) (actual time=0.018..98.791 rows=10,832 loops=1)

31. 14.803 29.344 ↑ 1.0 1,650 1

Hash (cost=63.50..63.50 rows=1,650 width=24) (actual time=29.337..29.344 rows=1,650 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 107kB
32. 14.541 14.541 ↑ 1.0 1,650 1

Seq Scan on client_invoice_upload (cost=0.00..63.50 rows=1,650 width=24) (actual time=0.019..14.541 rows=1,650 loops=1)

33. 5.715 11.143 ↑ 1.0 532 1

Hash (cost=12.32..12.32 rows=532 width=25) (actual time=11.135..11.143 rows=532 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 40kB
34. 5.428 5.428 ↑ 1.0 532 1

Seq Scan on term_code (cost=0.00..12.32 rows=532 width=25) (actual time=0.031..5.428 rows=532 loops=1)

35. 39.864 74.726 ↑ 1.0 3,626 1

Hash (cost=71.26..71.26 rows=3,626 width=50) (actual time=74.718..74.726 rows=3,626 loops=1)

  • Buckets: 4096 Batches: 1 Memory Usage: 292kB
36. 34.862 34.862 ↑ 1.0 3,626 1

Seq Scan on invoice_verification_details (cost=0.00..71.26 rows=3,626 width=50) (actual time=0.082..34.862 rows=3,626 loops=1)

37. 105.379 22,186.315 ↑ 155.7 3,683 1

Nested Loop Left Join (cost=1,581.33..8,693,886.52 rows=573,460 width=711) (actual time=19,171.922..22,186.315 rows=3,683 loops=1)

  • Join Filter: (advance_amount_settlements.purchased_date IS NULL)
38. 126.840 21,532.169 ↑ 155.7 3,683 1

Nested Loop Left Join (cost=1,575.84..5,529,820.97 rows=573,460 width=709) (actual time=19,171.720..21,532.169 rows=3,683 loops=1)

  • Join Filter: (advance_amount_settlements.purchased_date IS NULL)
  • Rows Removed by Join Filter: 3099
39. 95.948 19,596.976 ↑ 155.7 3,683 1

Merge Right Join (cost=1,566.24..10,985.29 rows=573,460 width=706) (actual time=19,170.753..19,596.976 rows=3,683 loops=1)

  • Merge Cond: (invoice_settlements.invoice_uuid = factor_invoices_query.uuid)
40. 83.426 244.479 ↓ 1.2 3,099 1

GroupAggregate (cost=557.45..645.61 rows=2,532 width=112) (actual time=133.502..244.479 rows=3,099 loops=1)

  • Group Key: invoice_settlements.invoice_uuid
41. 55.968 161.053 ↑ 1.1 3,099 1

Sort (cost=557.45..566.22 rows=3,508 width=54) (actual time=133.455..161.053 rows=3,099 loops=1)

  • Sort Key: invoice_settlements.invoice_uuid
  • Sort Method: quicksort Memory: 532kB
42. 63.054 105.085 ↑ 1.1 3,099 1

Hash Join (cost=8.21..350.89 rows=3,508 width=54) (actual time=2.996..105.085 rows=3,099 loops=1)

  • Hash Cond: (invoice_settlements.client_settlement_uuid = settlements.uuid)
43. 39.096 39.096 ↑ 1.0 4,125 1

Seq Scan on client_settlement_invoice_allocation invoice_settlements (cost=0.00..292.12 rows=4,125 width=70) (actual time=0.028..39.096 rows=4,125 loops=1)

  • Filter: (item_type = 'DISCOUNT_FEE'::capfin.client_invoice_allocation_type)
  • Rows Removed by Filter: 6205
44. 1.493 2.935 ↑ 1.0 165 1

Hash (cost=6.15..6.15 rows=165 width=16) (actual time=2.927..2.935 rows=165 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 16kB
45. 1.442 1.442 ↑ 1.0 165 1

Seq Scan on client_settlement settlements (cost=0.00..6.15 rows=165 width=16) (actual time=0.022..1.442 rows=165 loops=1)

  • Filter: (settlement_status = ANY ('{SENT,PAID,INITIATED,AUTHORIZED,PENDING}'::capfin.advance_status_enum[]))
  • Rows Removed by Filter: 29
46. 63.227 19,256.549 ↑ 12.3 3,683 1

Materialize (cost=1,008.79..1,819.38 rows=45,297 width=642) (actual time=19,037.181..19,256.549 rows=3,683 loops=1)

47. 93.862 19,193.322 ↑ 12.3 3,683 1

Merge Left Join (cost=1,008.79..1,706.14 rows=45,297 width=642) (actual time=19,037.164..19,193.322 rows=3,683 loops=1)

  • Merge Cond: (factor_invoices_query.uuid = advance_amount_settlements.uuid)
48. 94.242 18,737.906 ↓ 1.0 3,683 1

Sort (cost=282.75..291.70 rows=3,578 width=606) (actual time=18,702.944..18,737.906 rows=3,683 loops=1)

  • Sort Key: factor_invoices_query.uuid
  • Sort Method: quicksort Memory: 614kB
49. 18,643.664 18,643.664 ↓ 1.0 3,683 1

CTE Scan on factor_invoices_query (cost=0.00..71.56 rows=3,578 width=606) (actual time=9,589.715..18,643.664 rows=3,683 loops=1)

50. 59.415 361.554 ↓ 1.2 3,099 1

Sort (cost=726.04..732.37 rows=2,532 width=52) (actual time=334.192..361.554 rows=3,099 loops=1)

  • Sort Key: advance_amount_settlements.uuid
  • Sort Method: quicksort Memory: 339kB
51. 53.212 302.139 ↓ 1.2 3,099 1

Subquery Scan on advance_amount_settlements (cost=551.26..582.91 rows=2,532 width=52) (actual time=217.901..302.139 rows=3,099 loops=1)

52. 63.741 248.927 ↓ 1.2 3,099 1

Sort (cost=551.26..557.59 rows=2,532 width=52) (actual time=217.887..248.927 rows=3,099 loops=1)

  • Sort Key: (min(settlements_1.issued_date))
  • Sort Method: quicksort Memory: 339kB
53. 72.208 185.186 ↓ 1.2 3,099 1

HashAggregate (cost=376.47..408.12 rows=2,532 width=52) (actual time=153.783..185.186 rows=3,099 loops=1)

  • Group Key: invoice_settlements_1.invoice_uuid
54. 68.682 112.978 ↑ 1.1 3,099 1

Hash Join (cost=7.49..350.16 rows=3,508 width=26) (actual time=4.181..112.978 rows=3,099 loops=1)

  • Hash Cond: (invoice_settlements_1.client_settlement_uuid = settlements_1.uuid)
55. 40.180 40.180 ↑ 1.0 4,125 1

Seq Scan on client_settlement_invoice_allocation invoice_settlements_1 (cost=0.00..292.12 rows=4,125 width=38) (actual time=0.040..40.180 rows=4,125 loops=1)

  • Filter: (item_type = 'ADVANCE_AMOUNT'::capfin.client_invoice_allocation_type)
  • Rows Removed by Filter: 6205
56. 2.406 4.116 ↑ 1.0 165 1

Hash (cost=5.43..5.43 rows=165 width=20) (actual time=4.108..4.116 rows=165 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 17kB
57. 1.710 1.710 ↑ 1.0 165 1

Seq Scan on client_settlement settlements_1 (cost=0.00..5.43 rows=165 width=20) (actual time=0.014..1.710 rows=165 loops=1)

  • Filter: (settlement_status <> 'CANCELED'::capfin.advance_status_enum)
  • Rows Removed by Filter: 29
58. 95.758 1,808.353 ↑ 1.0 1 3,683

Limit (cost=9.60..9.60 rows=1 width=55) (actual time=0.483..0.491 rows=1 loops=3,683)

59. 125.222 1,712.595 ↑ 1.0 1 3,683

Sort (cost=9.60..9.60 rows=1 width=55) (actual time=0.465..0.465 rows=1 loops=3,683)

  • Sort Key: cdc.as_of_date DESC NULLS LAST
  • Sort Method: quicksort Memory: 25kB
60. 735.388 1,587.373 ↑ 1.0 1 3,683

Hash Join (cost=8.32..9.59 rows=1 width=55) (actual time=0.340..0.431 rows=1 loops=3,683)

  • Hash Cond: (fcdc.client_debtor_contract_uuid = cdc.uuid)
61. 631.005 631.005 ↑ 1.0 19 3,565

Seq Scan on factor_client_debtor_contract fcdc (cost=0.00..1.19 rows=19 width=35) (actual time=0.011..0.177 rows=19 loops=3,565)

62. 114.173 220.980 ↑ 1.0 1 3,683

Hash (cost=8.30..8.30 rows=1 width=20) (actual time=0.060..0.060 rows=1 loops=3,683)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
63. 106.807 106.807 ↑ 1.0 1 3,683

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.019..0.029 rows=1 loops=3,683)

  • Index Cond: ((client_debtor_uuid = factor_invoices_query.client_debtor_uuid) AND (as_of_date <= ('now'::cstring)::date))
  • Filter: (effective_date <= ('now'::cstring)::date)
64. 69.977 548.767 ↓ 0.0 0 3,683

Limit (cost=5.50..5.50 rows=1 width=30) (actual time=0.148..0.149 rows=0 loops=3,683)

65. 154.686 478.790 ↓ 0.0 0 3,683

Sort (cost=5.50..5.50 rows=3 width=30) (actual time=0.130..0.130 rows=0 loops=3,683)

  • Sort Key: reference_rate.effective_date DESC NULLS LAST, reference_rate.term
  • Sort Method: quicksort Memory: 25kB
66. 324.104 324.104 ↑ 1.5 2 3,683

Seq Scan on reference_rate (cost=0.00..5.48 rows=3 width=30) (actual time=0.059..0.088 rows=2 loops=3,683)

  • Filter: ((effective_date <= ('now'::cstring)::date) AND (term >= GREATEST(fcdc.minimum_tenor, (factor_invoices_query.buffered_projected_due_date - ('now'::cstring)::date))) AND (type = capfin.reference_rate_type(fcdc.rate_type)))
  • Rows Removed by Filter: 14
67. 0.149 0.407 ↑ 1.0 19 1

Hash (cost=1.19..1.19 rows=19 width=21) (actual time=0.399..0.407 rows=19 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
68. 0.258 0.258 ↑ 1.0 19 1

Seq Scan on factor_client_debtor_contract purchased_fcdc (cost=0.00..1.19 rows=19 width=21) (actual time=0.012..0.258 rows=19 loops=1)

69. 57.255 9,526.894 ↓ 186.6 4,665 1

Hash (cost=46,531.14..46,531.14 rows=25 width=309) (actual time=9,526.885..9,526.894 rows=4,665 loops=1)

  • Buckets: 8192 (originally 1024) Batches: 1 (originally 1) Memory Usage: 673kB
70. 138.266 9,469.639 ↓ 186.6 4,665 1

Nested Loop Left Join (cost=45,781.86..46,531.14 rows=25 width=309) (actual time=119.059..9,469.639 rows=4,665 loops=1)

  • Join Filter: (aggregate.type = ANY ('{REBATE,UNDERPAYMENT}'::capfin.transaction_history_types[]))
  • Rows Removed by Join Filter: 249
71. 146.769 8,309.738 ↓ 186.6 4,665 1

Nested Loop Left Join (cost=45,764.73..46,102.23 rows=25 width=258) (actual time=118.665..8,309.738 rows=4,665 loops=1)

72. 135.254 8,102.324 ↓ 186.6 4,665 1

Nested Loop Left Join (cost=45,764.46..46,094.42 rows=25 width=255) (actual time=118.634..8,102.324 rows=4,665 loops=1)

73. 141.585 7,911.090 ↓ 186.6 4,665 1

Nested Loop Left Join (cost=45,764.17..46,086.52 rows=25 width=255) (actual time=118.604..7,911.090 rows=4,665 loops=1)

74. 7,708.860 7,708.860 ↓ 186.6 4,665 1

CTE Scan on aggregate (cost=45,763.75..45,875.08 rows=25 width=216) (actual time=118.572..7,708.860 rows=4,665 loops=1)

  • Filter: (client_uuid = '978e42e7-b4a5-4eb0-8267-1dec686b1881'::uuid)
  • Rows Removed by Filter: 4880
75.          

CTE valid_settlements

76. 2.091 2.091 ↑ 1.0 165 1

Seq Scan on client_settlement (cost=0.00..5.43 rows=165 width=43) (actual time=0.016..2.091 rows=165 loops=1)

  • Filter: (settlement_status = ANY ('{SENT,PAID}'::capfin.advance_status_enum[]))
  • Rows Removed by Filter: 29
77.          

CTE invoice_settlement_amounts

78. 247.975 795.543 ↓ 1.1 11,298 1

Recursive Union (cost=5.36..27,610.15 rows=10,360 width=118) (actual time=6.608..795.543 rows=11,298 loops=1)

79. 234.218 366.558 ↑ 1.3 8,199 1

Hash Join (cost=5.36..465.35 rows=10,330 width=118) (actual time=6.587..366.558 rows=8,199 loops=1)

  • Hash Cond: (allocation_1.client_settlement_uuid = client_settlement_1.uuid)
80. 125.842 125.842 ↑ 1.0 10,330 1

Seq Scan on client_settlement_invoice_allocation allocation_1 (cost=0.00..266.30 rows=10,330 width=58) (actual time=0.019..125.842 rows=10,330 loops=1)

81. 1.569 6.498 ↑ 1.0 165 1

Hash (cost=3.30..3.30 rows=165 width=16) (actual time=6.490..6.498 rows=165 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 16kB
82. 4.929 4.929 ↑ 1.0 165 1

CTE Scan on valid_settlements client_settlement_1 (cost=0.00..3.30 rows=165 width=16) (actual time=0.036..4.929 rows=165 loops=1)

83. 66.105 181.010 ↓ 516.7 1,550 2

Hash Join (cost=354.00..2,693.76 rows=3 width=104) (actual time=42.507..90.505 rows=1,550 loops=2)

  • Hash Cond: ((invoice_settlement_amounts.invoice_uuid = discount.invoice_uuid) AND (invoice_settlement_amounts.client_settlement_uuid = discount.client_settlement_uuid))
84. 30.568 30.568 ↓ 3.0 1,550 2

WorkTable Scan on invoice_settlement_amounts (cost=0.00..2,324.25 rows=516 width=66) (actual time=0.288..15.284 rows=1,550 loops=2)

  • Filter: (item_type = 'ADVANCE_AMOUNT'::text)
  • Rows Removed by Filter: 4100
85. 42.813 84.337 ↑ 1.0 4,125 1

Hash (cost=292.12..292.12 rows=4,125 width=38) (actual time=84.330..84.337 rows=4,125 loops=1)

  • Buckets: 8192 Batches: 1 Memory Usage: 347kB
86. 41.524 41.524 ↑ 1.0 4,125 1

Seq Scan on client_settlement_invoice_allocation discount (cost=0.00..292.12 rows=4,125 width=38) (actual time=0.023..41.524 rows=4,125 loops=1)

  • Filter: (item_type = 'DISCOUNT_FEE'::capfin.client_invoice_allocation_type)
  • Rows Removed by Filter: 6205
87.          

CTE supplemental_detail

88. 21.404 210.420 ↑ 1.2 1,262 1

Append (cost=5.78..3,767.40 rows=1,532 width=92) (actual time=4.785..210.420 rows=1,262 loops=1)

89. 41.943 111.528 ↑ 1.2 1,144 1

Nested Loop (cost=5.78..3,599.00 rows=1,317 width=89) (actual time=4.771..111.528 rows=1,144 loops=1)

90. 23.659 39.841 ↑ 1.2 1,144 1

Hash Join (cost=5.36..53.64 rows=1,317 width=53) (actual time=4.662..39.841 rows=1,144 loops=1)

  • Hash Cond: (allocation_2.client_settlement_uuid = client_settlement_2.uuid)
91. 11.717 11.717 ↑ 1.0 1,317 1

Seq Scan on client_settlement_passthrough_allocation allocation_2 (cost=0.00..30.17 rows=1,317 width=53) (actual time=0.152..11.717 rows=1,317 loops=1)

92. 1.489 4.465 ↑ 1.0 165 1

Hash (cost=3.30..3.30 rows=165 width=16) (actual time=4.457..4.465 rows=165 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 16kB
93. 2.976 2.976 ↑ 1.0 165 1

CTE Scan on valid_settlements client_settlement_2 (cost=0.00..3.30 rows=165 width=16) (actual time=0.011..2.976 rows=165 loops=1)

94. 29.744 29.744 ↑ 1.0 1 1,144

Index Only Scan using debtor_remittance_allocation_pkey on debtor_remittance_allocation remittance_allocation_1 (cost=0.42..2.68 rows=1 width=16) (actual time=0.017..0.026 rows=1 loops=1,144)

  • Index Cond: (uuid = allocation_2.debtor_remittance_allocation_uuid)
  • Heap Fetches: 0
95. 1.123 76.791 ↑ 1.1 49 1

Hash Join (cost=7.53..148.96 rows=52 width=78) (actual time=6.493..76.791 rows=49 loops=1)

  • Hash Cond: (allocation_3.client_settlement_uuid = client_settlement_3.uuid)
96. 36.966 72.890 ↑ 1.0 52 1

Hash Join (cost=2.17..142.88 rows=52 width=58) (actual time=3.682..72.890 rows=52 loops=1)

  • Hash Cond: (client_adjustment.uuid = allocation_3.adjustment_uuid)
97. 35.027 35.027 ↑ 1.0 4,213 1

Seq Scan on client_adjustment (cost=0.00..119.13 rows=4,213 width=36) (actual time=0.021..35.027 rows=4,213 loops=1)

98. 0.422 0.897 ↑ 1.0 52 1

Hash (cost=1.52..1.52 rows=52 width=54) (actual time=0.889..0.897 rows=52 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 13kB
99. 0.475 0.475 ↑ 1.0 52 1

Seq Scan on client_settlement_fee_allocation allocation_3 (cost=0.00..1.52 rows=52 width=54) (actual time=0.014..0.475 rows=52 loops=1)

100. 1.370 2.778 ↑ 1.0 165 1

Hash (cost=3.30..3.30 rows=165 width=16) (actual time=2.770..2.778 rows=165 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 16kB
101. 1.408 1.408 ↑ 1.0 165 1

CTE Scan on valid_settlements client_settlement_3 (cost=0.00..3.30 rows=165 width=16) (actual time=0.010..1.408 rows=165 loops=1)

102. 0.697 0.697 ↑ 2.4 69 1

CTE Scan on valid_settlements client_settlement_4 (cost=0.00..4.12 rows=163 width=116) (actual time=0.022..0.697 rows=69 loops=1)

  • Filter: ((remittance_fee IS NOT NULL) AND (remittance_fee <> '0'::numeric))
  • Rows Removed by Filter: 96
103.          

CTE aggregate

104. 175.526 7,557.480 ↓ 1.9 9,545 1

Append (cost=1,113.92..14,380.78 rows=4,948 width=216) (actual time=88.083..7,557.480 rows=9,545 loops=1)

105. 216.794 1,836.616 ↓ 1,700.0 5,100 1

Nested Loop (cost=1,113.92..1,375.73 rows=3 width=202) (actual time=88.068..1,836.616 rows=5,100 loops=1)

106. 190.112 1,487.222 ↓ 1,700.0 5,100 1

Nested Loop (cost=1,113.64..1,374.11 rows=3 width=150) (actual time=88.023..1,487.222 rows=5,100 loops=1)

107. 107.226 1,174.710 ↓ 1,700.0 5,100 1

Hash Join (cost=1,113.49..1,373.30 rows=3 width=130) (actual time=87.983..1,174.710 rows=5,100 loops=1)

  • Hash Cond: (invoice_amounts.invoice_uuid = invoice.uuid)
108. 986.239 986.239 ↓ 24.6 5,100 1

CTE Scan on invoice_settlement_amounts invoice_amounts (cost=0.00..259.00 rows=207 width=130) (actual time=6.632..986.239 rows=5,100 loops=1)

  • Filter: (item_type = ANY ('{INVOICE_PURCHASE,REBATE,UNDERPAYMENT,CHARGE_BACK}'::text[]))
  • Rows Removed by Filter: 6198
109. 37.830 81.245 ↓ 1.0 3,683 1

Hash (cost=1,068.77..1,068.77 rows=3,578 width=16) (actual time=81.237..81.245 rows=3,683 loops=1)

  • Buckets: 4096 Batches: 1 Memory Usage: 205kB
110. 43.415 43.415 ↓ 1.0 3,683 1

Index Scan using capfin_invoice_product_type_idx on capfin_invoice invoice (cost=0.42..1,068.77 rows=3,578 width=16) (actual time=0.036..43.415 rows=3,683 loops=1)

  • Index Cond: (product_type = 'FACTORING'::capfin.product_type)
111. 122.400 122.400 ↑ 1.0 1 5,100

Index Scan using client_settlement_pkey on client_settlement client_settlement_5 (cost=0.14..0.26 rows=1 width=36) (actual time=0.015..0.024 rows=1 loops=5,100)

  • Index Cond: (uuid = invoice_amounts.client_settlement_uuid)
112. 132.600 132.600 ↑ 1.0 1 5,100

Index Only Scan using tpf_factor_client_pkey on client (cost=0.28..0.52 rows=1 width=16) (actual time=0.014..0.026 rows=1 loops=5,100)

  • Index Cond: (uuid = client_settlement_5.client_uuid)
  • Heap Fetches: 0
113. 63.615 5,280.480 ↑ 1.1 3,183 1

Subquery Scan on *SELECT* 2 (cost=406.40..12,931.55 rows=3,459 width=172) (actual time=243.809..5,280.480 rows=3,183 loops=1)

114. 68.210 5,216.865 ↑ 1.1 3,183 1

Hash Join (cost=406.40..12,896.96 rows=3,459 width=149) (actual time=243.792..5,216.865 rows=3,183 loops=1)

  • Hash Cond: (allocation_4.client_settlement_uuid = client_settlement_6.uuid)
115. 2,476.758 5,098.514 ↑ 1.0 3,459 1

Hash Join (cost=328.24..12,771.24 rows=3,459 width=77) (actual time=193.624..5,098.514 rows=3,459 loops=1)

  • Hash Cond: (invoice_1.uuid = invoice_fee.invoice_uuid)
116. 2,428.429 2,428.429 ↑ 1.0 266,995 1

Seq Scan on capfin_invoice invoice_1 (cost=0.00..10,405.95 rows=266,995 width=25) (actual time=0.022..2,428.429 rows=266,995 loops=1)

117. 29.945 193.327 ↑ 1.0 3,459 1

Hash (cost=285.00..285.00 rows=3,459 width=68) (actual time=193.319..193.327 rows=3,459 loops=1)

  • Buckets: 4096 Batches: 1 Memory Usage: 370kB
118. 58.081 163.382 ↑ 1.0 3,459 1

Hash Join (cost=162.85..285.00 rows=3,459 width=68) (actual time=77.516..163.382 rows=3,459 loops=1)

  • Hash Cond: (allocation_4.invoice_fee_uuid = invoice_fee.uuid)
119. 27.827 27.827 ↑ 1.0 3,459 1

Seq Scan on client_settlement_invoice_fee_allocation allocation_4 (cost=0.00..74.59 rows=3,459 width=54) (actual time=0.014..27.827 rows=3,459 loops=1)

120. 39.749 77.474 ↑ 1.0 4,349 1

Hash (cost=108.49..108.49 rows=4,349 width=46) (actual time=77.466..77.474 rows=4,349 loops=1)

  • Buckets: 8192 Batches: 1 Memory Usage: 399kB
121. 37.725 37.725 ↑ 1.0 4,349 1

Seq Scan on invoice_fee (cost=0.00..108.49 rows=4,349 width=46) (actual time=0.013..37.725 rows=4,349 loops=1)

122. 1.332 50.141 ↑ 1.0 165 1

Hash (cost=76.09..76.09 rows=165 width=36) (actual time=50.134..50.141 rows=165 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 19kB
123. 21.130 48.809 ↑ 1.0 165 1

Hash Join (cost=5.36..76.09 rows=165 width=36) (actual time=38.570..48.809 rows=165 loops=1)

  • Hash Cond: (client_1.uuid = client_settlement_6.client_uuid)
124. 20.287 20.287 ↑ 1.0 2,333 1

Seq Scan on client client_1 (cost=0.00..60.33 rows=2,333 width=16) (actual time=0.021..20.287 rows=2,333 loops=1)

125. 5.858 7.392 ↑ 1.0 165 1

Hash (cost=3.30..3.30 rows=165 width=36) (actual time=7.384..7.392 rows=165 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 19kB
126. 1.534 1.534 ↑ 1.0 165 1

CTE Scan on valid_settlements client_settlement_6 (cost=0.00..3.30 rows=165 width=36) (actual time=0.008..1.534 rows=165 loops=1)

127. 27.950 264.858 ↑ 1.2 1,262 1

Hash Join (cost=7.37..58.61 rows=1,486 width=216) (actual time=8.074..264.858 rows=1,262 loops=1)

  • Hash Cond: (supplemental_detail.client_settlement_uuid = client_settlement_7.uuid)
128. 233.674 233.674 ↑ 1.2 1,262 1

CTE Scan on supplemental_detail (cost=0.00..30.64 rows=1,532 width=116) (actual time=4.803..233.674 rows=1,262 loops=1)

129. 1.598 3.234 ↑ 1.0 194 1

Hash (cost=4.94..4.94 rows=194 width=36) (actual time=3.226..3.234 rows=194 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 21kB
130. 1.636 1.636 ↑ 1.0 194 1

Seq Scan on client_settlement client_settlement_7 (cost=0.00..4.94 rows=194 width=36) (actual time=0.022..1.636 rows=194 loops=1)

131. 60.645 60.645 ↓ 0.0 0 4,665

Index Scan using debtor_remittance_allocation_pkey on debtor_remittance_allocation remittance_allocation (cost=0.42..8.44 rows=1 width=55) (actual time=0.011..0.013 rows=0 loops=4,665)

  • Index Cond: (uuid = aggregate.debtor_remittance_allocation_uuid)
132. 55.980 55.980 ↓ 0.0 0 4,665

Index Scan using debtor_remittance_pkey on debtor_remittance remittance (cost=0.29..0.31 rows=1 width=32) (actual time=0.011..0.012 rows=0 loops=4,665)

  • Index Cond: (uuid = remittance_allocation.debtor_remittance_uuid)
133. 60.645 60.645 ↓ 0.0 0 4,665

Index Scan using tpf_factor_debtor_pkey on debtor (cost=0.28..0.30 rows=1 width=35) (actual time=0.011..0.013 rows=0 loops=4,665)

  • Index Cond: (uuid = remittance.debtor_uuid)
134. 93.300 1,021.635 ↓ 0.0 0 4,665

Subquery Scan on paid (cost=17.13..17.14 rows=1 width=67) (actual time=0.216..0.219 rows=0 loops=4,665)

  • Filter: (aggregate.invoice_uuid = paid.invoice_uuid)
135. 97.965 928.335 ↓ 0.0 0 4,665

Limit (cost=17.13..17.13 rows=1 width=71) (actual time=0.198..0.199 rows=0 loops=4,665)

136. 111.960 830.370 ↓ 0.0 0 4,665

Sort (cost=17.13..17.13 rows=1 width=71) (actual time=0.178..0.178 rows=0 loops=4,665)

  • Sort Key: cp.allocation_date DESC
  • Sort Method: quicksort Memory: 25kB
137. 102.630 718.410 ↓ 0.0 0 4,665

Subquery Scan on cp (cost=17.08..17.12 rows=1 width=71) (actual time=0.151..0.154 rows=0 loops=4,665)

  • Filter: (cp.allocation_date <= aggregate.transaction_date)
  • Rows Removed by Filter: 0
138. 121.290 615.780 ↓ 0.0 0 4,665

WindowAgg (cost=17.08..17.10 rows=1 width=71) (actual time=0.130..0.132 rows=0 loops=4,665)

139. 111.960 494.490 ↓ 0.0 0 4,665

Sort (cost=17.08..17.08 rows=1 width=52) (actual time=0.104..0.106 rows=0 loops=4,665)

  • Sort Key: allocation.allocation_date
  • Sort Method: quicksort Memory: 25kB
140. 120.558 382.530 ↓ 0.0 0 4,665

Nested Loop (cost=0.98..17.07 rows=1 width=52) (actual time=0.065..0.082 rows=0 loops=4,665)

141. 120.435 237.915 ↓ 0.0 0 4,665

Nested Loop (cost=0.71..16.75 rows=1 width=49) (actual time=0.042..0.051 rows=0 loops=4,665)

142. 93.300 93.300 ↓ 0.0 0 4,665

Index Scan using debtor_remittance_allocation_invoice_uuid_idx on debtor_remittance_allocation allocation (cost=0.42..8.44 rows=1 width=49) (actual time=0.018..0.020 rows=0 loops=4,665)

  • Index Cond: (invoice_uuid = aggregate.invoice_uuid)
143. 24.180 24.180 ↑ 1.0 1 930

Index Scan using debtor_remittance_pkey on debtor_remittance remittance_1 (cost=0.29..8.30 rows=1 width=32) (actual time=0.018..0.026 rows=1 loops=930)

  • Index Cond: (uuid = allocation.debtor_remittance_uuid)
  • Filter: (settlement_status = 'SUCCESSFUL'::capfin.settlement_status_enum)
  • Rows Removed by Filter: 0
144. 24.057 24.057 ↑ 1.0 1 891

Index Scan using tpf_factor_debtor_pkey on debtor debtor_1 (cost=0.28..0.30 rows=1 width=35) (actual time=0.016..0.027 rows=1 loops=891)

  • Index Cond: (uuid = remittance_1.debtor_uuid)
Planning time : 35.563 ms
Execution time : 32,088.820 ms