explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Xllj

Settings

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 112.823 311,942.758 ↓ 13.0 4,665 1

Sort (cost=9,316,555.27..9,316,556.17 rows=358 width=384) (actual time=311,881.987..311,942.758 rows=4,665 loops=1)

  • Sort Key: aggregate.transaction_date DESC, aggregate.client_settlement_uuid
  • Sort Method: quicksort Memory: 1229kB
2.          

CTE cumulative_paid

3. 4,175.886 21,007.483 ↓ 1.0 200,049 1

Sort (cost=63,205.26..63,695.38 rows=196,045 width=71) (actual time=19,089.102..21,007.483 rows=200,049 loops=1)

  • Sort Key: allocation.allocation_date
  • Sort Method: external merge Disk: 8840kB
4. 5,054.218 16,831.597 ↓ 1.0 200,049 1

WindowAgg (cost=33,027.97..37,929.10 rows=196,045 width=71) (actual time=9,495.075..16,831.597 rows=200,049 loops=1)

5. 4,739.084 11,777.379 ↓ 1.0 200,049 1

Sort (cost=33,027.97..33,518.09 rows=196,045 width=52) (actual time=9,495.000..11,777.379 rows=200,049 loops=1)

  • Sort Key: allocation.invoice_uuid, allocation.allocation_date
  • Sort Method: external merge Disk: 10104kB
6. 4,462.633 7,038.295 ↓ 1.0 200,049 1

Hash Join (cost=519.80..9,092.31 rows=196,045 width=52) (actual time=378.511..7,038.295 rows=200,049 loops=1)

  • Hash Cond: (allocation.debtor_remittance_uuid = remittance_1.uuid)
7. 2,197.289 2,197.289 ↑ 1.0 217,168 1

Seq Scan on debtor_remittance_allocation allocation (cost=0.00..5,797.68 rows=217,168 width=49) (actual time=0.024..2,197.289 rows=217,168 loops=1)

8. 88.016 378.373 ↑ 1.0 8,288 1

Hash (cost=416.20..416.20 rows=8,288 width=35) (actual time=378.367..378.373 rows=8,288 loops=1)

  • Buckets: 16384 Batches: 1 Memory Usage: 653kB
9. 185.924 290.357 ↑ 1.0 8,288 1

Hash Join (cost=31.48..416.20 rows=8,288 width=35) (actual time=11.443..290.357 rows=8,288 loops=1)

  • Hash Cond: (remittance_1.debtor_uuid = debtor_1.uuid)
10. 93.060 93.060 ↑ 1.0 8,288 1

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

  • Filter: (settlement_status = 'SUCCESSFUL'::capfin.settlement_status_enum)
  • Rows Removed by Filter: 893
11. 5.494 11.373 ↑ 1.0 599 1

Hash (cost=23.99..23.99 rows=599 width=35) (actual time=11.367..11.373 rows=599 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 48kB
12. 5.879 5.879 ↑ 1.0 599 1

Seq Scan on debtor debtor_1 (cost=0.00..23.99 rows=599 width=35) (actual time=0.017..5.879 rows=599 loops=1)

13. 100.936 311,829.935 ↓ 13.0 4,665 1

Hash Right Join (cost=235,266.30..9,252,844.71 rows=358 width=384) (actual time=308,396.096..311,829.935 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
14. 92.898 20,472.744 ↑ 155.7 3,683 1

Hash Left Join (cost=66,594.07..9,075,563.87 rows=573,460 width=2,958) (actual time=17,139.775..20,472.744 rows=3,683 loops=1)

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

CTE factor_invoices_query

16. 124.969 16,595.251 ↓ 1.0 3,683 1

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

  • Hash Cond: (capfin_invoice.verification_uuid = invoice_verification_details.uuid)
17. 68.245 16,407.002 ↓ 1.0 3,683 1

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

  • Hash Cond: (capfin_invoice.term_code_uuid = term_code.uuid)
18. 1,334.656 16,329.898 ↓ 1.0 3,683 1

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

  • Merge Cond: ((debtor_remittance.client_uuid = capfin_invoice.client_uuid) AND (debtor_remittance_allocation.invoice_uuid = capfin_invoice.uuid))
19. 3,760.120 14,004.363 ↑ 1.4 137,110 1

GroupAggregate (cost=32,214.04..40,055.84 rows=196,045 width=197) (actual time=8,199.099..14,004.363 rows=137,110 loops=1)

  • Group Key: debtor_remittance.client_uuid, debtor_remittance_allocation.invoice_uuid
20. 4,248.635 10,244.243 ↓ 1.0 199,229 1

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

  • Sort Key: debtor_remittance.client_uuid, debtor_remittance_allocation.invoice_uuid
  • Sort Method: external merge Disk: 9960kB
21. 3,888.092 5,995.608 ↓ 1.0 200,049 1

Hash Join (cost=374.36..8,946.87 rows=196,045 width=45) (actual time=146.431..5,995.608 rows=200,049 loops=1)

  • Hash Cond: (debtor_remittance_allocation.debtor_remittance_uuid = debtor_remittance.uuid)
22. 1,961.232 1,961.232 ↑ 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.021..1,961.232 rows=217,168 loops=1)

23. 72.889 146.284 ↑ 1.0 8,288 1

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

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

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

  • Filter: (settlement_status = 'SUCCESSFUL'::capfin.settlement_status_enum)
  • Rows Removed by Filter: 893
25. 77.390 990.879 ↓ 1.0 3,683 1

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

  • Sort Key: capfin_invoice.client_uuid, capfin_invoice.uuid
  • Sort Method: quicksort Memory: 1966kB
26. 69.524 913.489 ↓ 1.0 3,683 1

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

  • Hash Cond: (capfin_invoice.client_invoice_upload_uuid = client_invoice_upload.uuid)
27. 61.535 809.251 ↓ 1.0 3,683 1

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

  • Hash Cond: (capfin_invoice.tpf_import_upload_status_id = tpf_import_upload_status.id)
28. 66.086 496.735 ↓ 1.0 3,683 1

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

  • Hash Cond: (capfin_invoice.client_uuid = client_2.uuid)
29. 74.462 174.671 ↓ 1.0 3,683 1

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

  • Hash Cond: (capfin_invoice.client_debtor_uuid = client_debtor.uuid)
30. 41.566 41.566 ↓ 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.031..41.566 rows=3,683 loops=1)

  • Index Cond: (product_type = 'FACTORING'::capfin.product_type)
31. 6.529 58.643 ↑ 1.0 703 1

Hash (cost=60.17..60.17 rows=703 width=55) (actual time=58.636..58.643 rows=703 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 65kB
32. 35.382 52.114 ↑ 1.0 703 1

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

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

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

34. 5.227 10.424 ↑ 1.0 599 1

Hash (cost=23.99..23.99 rows=599 width=35) (actual time=10.417..10.424 rows=599 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 48kB
35. 5.197 5.197 ↑ 1.0 599 1

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

36. 32.231 255.978 ↑ 1.0 2,333 1

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

  • Buckets: 4096 Batches: 1 Memory Usage: 219kB
37. 118.541 223.747 ↑ 1.0 2,333 1

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

38. 23.551 23.551 ↑ 1.0 2,333 1

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

39. 81.655 81.655 ↑ 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.022..0.035 rows=1 loops=2,333)

  • Index Cond: (uuid = client_2.division_uuid)
40. 122.548 250.981 ↑ 1.0 10,832 1

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

  • Buckets: 16384 Batches: 1 Memory Usage: 636kB
41. 128.433 128.433 ↑ 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..128.433 rows=10,832 loops=1)

42. 17.544 34.714 ↑ 1.0 1,650 1

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

  • Buckets: 2048 Batches: 1 Memory Usage: 107kB
43. 17.170 17.170 ↑ 1.0 1,650 1

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

44. 4.346 8.859 ↑ 1.0 532 1

Hash (cost=12.32..12.32 rows=532 width=25) (actual time=8.852..8.859 rows=532 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 40kB
45. 4.513 4.513 ↑ 1.0 532 1

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

46. 32.097 63.280 ↑ 1.0 3,626 1

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

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

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

48. 109.593 20,379.527 ↑ 155.7 3,683 1

Nested Loop Left Join (cost=1,581.33..8,693,886.52 rows=573,460 width=711) (actual time=17,139.426..20,379.527 rows=3,683 loops=1)

  • Join Filter: (advance_amount_settlements.purchased_date IS NULL)
49. 132.598 19,669.605 ↑ 155.7 3,683 1

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

  • Join Filter: (advance_amount_settlements.purchased_date IS NULL)
  • Rows Removed by Join Filter: 3099
50. 101.512 17,596.066 ↑ 155.7 3,683 1

Merge Right Join (cost=1,566.24..10,985.29 rows=573,460 width=706) (actual time=17,138.045..17,596.066 rows=3,683 loops=1)

  • Merge Cond: (invoice_settlements.invoice_uuid = factor_invoices_query.uuid)
51. 82.213 238.263 ↓ 1.2 3,099 1

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

  • Group Key: invoice_settlements.invoice_uuid
52. 57.892 156.050 ↑ 1.1 3,099 1

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

  • Sort Key: invoice_settlements.invoice_uuid
  • Sort Method: quicksort Memory: 532kB
53. 59.145 98.158 ↑ 1.1 3,099 1

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

  • Hash Cond: (invoice_settlements.client_settlement_uuid = settlements.uuid)
54. 35.990 35.990 ↑ 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.057..35.990 rows=4,125 loops=1)

  • Filter: (item_type = 'DISCOUNT_FEE'::capfin.client_invoice_allocation_type)
  • Rows Removed by Filter: 6205
55. 1.697 3.023 ↑ 1.0 165 1

Hash (cost=6.15..6.15 rows=165 width=16) (actual time=3.016..3.023 rows=165 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 16kB
56. 1.326 1.326 ↑ 1.0 165 1

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

  • Filter: (settlement_status = ANY ('{SENT,PAID,INITIATED,AUTHORIZED,PENDING}'::capfin.advance_status_enum[]))
  • Rows Removed by Filter: 29
57. 69.543 17,256.291 ↑ 12.3 3,683 1

Materialize (cost=1,008.79..1,819.38 rows=45,297 width=642) (actual time=17,012.086..17,256.291 rows=3,683 loops=1)

58. 99.781 17,186.748 ↑ 12.3 3,683 1

Merge Left Join (cost=1,008.79..1,706.14 rows=45,297 width=642) (actual time=17,011.987..17,186.748 rows=3,683 loops=1)

  • Merge Cond: (factor_invoices_query.uuid = advance_amount_settlements.uuid)
59. 81.687 16,755.891 ↓ 1.0 3,683 1

Sort (cost=282.75..291.70 rows=3,578 width=606) (actual time=16,712.904..16,755.891 rows=3,683 loops=1)

  • Sort Key: factor_invoices_query.uuid
  • Sort Method: quicksort Memory: 614kB
60. 16,674.204 16,674.204 ↓ 1.0 3,683 1

CTE Scan on factor_invoices_query (cost=0.00..71.56 rows=3,578 width=606) (actual time=9,384.855..16,674.204 rows=3,683 loops=1)

61. 60.762 331.076 ↓ 1.2 3,099 1

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

  • Sort Key: advance_amount_settlements.uuid
  • Sort Method: quicksort Memory: 339kB
62. 53.086 270.314 ↓ 1.2 3,099 1

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

63. 57.032 217.228 ↓ 1.2 3,099 1

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

  • Sort Key: (min(settlements_1.issued_date))
  • Sort Method: quicksort Memory: 339kB
64. 59.610 160.196 ↓ 1.2 3,099 1

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

  • Group Key: invoice_settlements_1.invoice_uuid
65. 61.311 100.586 ↑ 1.1 3,099 1

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

  • Hash Cond: (invoice_settlements_1.client_settlement_uuid = settlements_1.uuid)
66. 36.479 36.479 ↑ 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.038..36.479 rows=4,125 loops=1)

  • Filter: (item_type = 'ADVANCE_AMOUNT'::capfin.client_invoice_allocation_type)
  • Rows Removed by Filter: 6205
67. 1.446 2.796 ↑ 1.0 165 1

Hash (cost=5.43..5.43 rows=165 width=20) (actual time=2.789..2.796 rows=165 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 17kB
68. 1.350 1.350 ↑ 1.0 165 1

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

  • Filter: (settlement_status <> 'CANCELED'::capfin.advance_status_enum)
  • Rows Removed by Filter: 29
69. 99.441 1,940.941 ↑ 1.0 1 3,683

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

70. 139.954 1,841.500 ↑ 1.0 1 3,683

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

  • Sort Key: cdc.as_of_date DESC NULLS LAST
  • Sort Method: quicksort Memory: 25kB
71. 798.825 1,701.546 ↑ 1.0 1 3,683

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

  • Hash Cond: (fcdc.client_debtor_contract_uuid = cdc.uuid)
72. 655.960 655.960 ↑ 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.184 rows=19 loops=3,565)

73. 132.588 246.761 ↑ 1.0 1 3,683

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
74. 114.173 114.173 ↑ 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.022..0.031 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)
75. 69.977 600.329 ↓ 0.0 0 3,683

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

76. 180.467 530.352 ↓ 0.0 0 3,683

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

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

Seq Scan on reference_rate (cost=0.00..5.48 rows=3 width=30) (actual time=0.066..0.095 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
78. 0.160 0.319 ↑ 1.0 19 1

Hash (cost=1.19..1.19 rows=19 width=21) (actual time=0.312..0.319 rows=19 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
79. 0.159 0.159 ↑ 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.014..0.159 rows=19 loops=1)

80. 69.974 291,256.255 ↓ 186.6 4,665 1

Hash (cost=168,671.92..168,671.92 rows=25 width=322) (actual time=291,256.249..291,256.255 rows=4,665 loops=1)

  • Buckets: 8192 (originally 1024) Batches: 1 (originally 1) Memory Usage: 673kB
81. 152.074 291,186.281 ↓ 186.6 4,665 1

Nested Loop Left Join (cost=50,667.49..168,671.92 rows=25 width=322) (actual time=23,218.560..291,186.281 rows=4,665 loops=1)

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

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

83. 148.154 8,675.508 ↓ 186.6 4,665 1

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

84. 159.668 8,457.379 ↓ 186.6 4,665 1

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

85. 8,232.401 8,232.401 ↓ 186.6 4,665 1

CTE Scan on aggregate (cost=45,763.75..45,875.08 rows=25 width=216) (actual time=108.304..8,232.401 rows=4,665 loops=1)

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

CTE valid_settlements

87. 1.534 1.534 ↑ 1.0 165 1

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

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

CTE invoice_settlement_amounts

89. 230.959 767.885 ↓ 1.1 11,298 1

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

90. 226.363 349.444 ↑ 1.3 8,199 1

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

  • Hash Cond: (allocation_1.client_settlement_uuid = client_settlement_1.uuid)
91. 116.909 116.909 ↑ 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.020..116.909 rows=10,330 loops=1)

92. 1.812 6.172 ↑ 1.0 165 1

Hash (cost=3.30..3.30 rows=165 width=16) (actual time=6.165..6.172 rows=165 loops=1)

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

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

94. 71.170 187.482 ↓ 516.7 1,550 2

Hash Join (cost=354.00..2,693.76 rows=3 width=104) (actual time=39.603..93.741 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))
95. 37.930 37.930 ↓ 3.0 1,550 2

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

  • Filter: (item_type = 'ADVANCE_AMOUNT'::text)
  • Rows Removed by Filter: 4100
96. 38.050 78.382 ↑ 1.0 4,125 1

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

  • Buckets: 8192 Batches: 1 Memory Usage: 347kB
97. 40.332 40.332 ↑ 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.025..40.332 rows=4,125 loops=1)

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

CTE supplemental_detail

99. 26.158 241.277 ↑ 1.2 1,262 1

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

100. 46.801 136.924 ↑ 1.2 1,144 1

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

101. 25.301 44.363 ↑ 1.2 1,144 1

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

  • Hash Cond: (allocation_2.client_settlement_uuid = client_settlement_2.uuid)
102. 12.809 12.809 ↑ 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.017..12.809 rows=1,317 loops=1)

103. 4.454 6.253 ↑ 1.0 165 1

Hash (cost=3.30..3.30 rows=165 width=16) (actual time=6.246..6.253 rows=165 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 16kB
104. 1.799 1.799 ↑ 1.0 165 1

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

105. 45.760 45.760 ↑ 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.028..0.040 rows=1 loops=1,144)

  • Index Cond: (uuid = allocation_2.debtor_remittance_allocation_uuid)
  • Heap Fetches: 0
106. 0.864 77.386 ↑ 1.1 49 1

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

  • Hash Cond: (allocation_3.client_settlement_uuid = client_settlement_3.uuid)
107. 37.191 73.896 ↑ 1.0 52 1

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

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

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

109. 0.651 1.134 ↑ 1.0 52 1

Hash (cost=1.52..1.52 rows=52 width=54) (actual time=1.127..1.134 rows=52 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 13kB
110. 0.483 0.483 ↑ 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.483 rows=52 loops=1)

111. 1.317 2.626 ↑ 1.0 165 1

Hash (cost=3.30..3.30 rows=165 width=16) (actual time=2.620..2.626 rows=165 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 16kB
112. 1.309 1.309 ↑ 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.309 rows=165 loops=1)

113. 0.809 0.809 ↑ 2.4 69 1

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

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

CTE aggregate

115. 172.711 8,066.777 ↓ 1.9 9,545 1

Append (cost=1,113.92..14,380.78 rows=4,948 width=216) (actual time=79.061..8,066.777 rows=9,545 loops=1)

116. 225.621 1,900.677 ↓ 1,700.0 5,100 1

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

117. 207.545 1,527.156 ↓ 1,700.0 5,100 1

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

118. 104.095 1,156.411 ↓ 1,700.0 5,100 1

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

  • Hash Cond: (invoice_amounts.invoice_uuid = invoice.uuid)
119. 979.777 979.777 ↓ 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.334..979.777 rows=5,100 loops=1)

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

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

  • Buckets: 4096 Batches: 1 Memory Usage: 205kB
121. 37.331 37.331 ↓ 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.029..37.331 rows=3,683 loops=1)

  • Index Cond: (product_type = 'FACTORING'::capfin.product_type)
122. 163.200 163.200 ↑ 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.021..0.032 rows=1 loops=5,100)

  • Index Cond: (uuid = invoice_amounts.client_settlement_uuid)
123. 147.900 147.900 ↑ 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.016..0.029 rows=1 loops=5,100)

  • Index Cond: (uuid = client_settlement_5.client_uuid)
  • Heap Fetches: 0
124. 55.652 5,699.737 ↑ 1.1 3,183 1

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

125. 77.835 5,644.085 ↑ 1.1 3,183 1

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

  • Hash Cond: (allocation_4.client_settlement_uuid = client_settlement_6.uuid)
126. 2,636.316 5,513.790 ↑ 1.0 3,459 1

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

  • Hash Cond: (invoice_1.uuid = invoice_fee.invoice_uuid)
127. 2,583.925 2,583.925 ↑ 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.035..2,583.925 rows=266,995 loops=1)

128. 49.771 293.549 ↑ 1.0 3,459 1

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

  • Buckets: 4096 Batches: 1 Memory Usage: 370kB
129. 106.993 243.778 ↑ 1.0 3,459 1

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

  • Hash Cond: (allocation_4.invoice_fee_uuid = invoice_fee.uuid)
130. 38.001 38.001 ↑ 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.013..38.001 rows=3,459 loops=1)

131. 51.936 98.784 ↑ 1.0 4,349 1

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

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

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

133. 1.583 52.460 ↑ 1.0 165 1

Hash (cost=76.09..76.09 rows=165 width=36) (actual time=52.453..52.460 rows=165 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 19kB
134. 20.466 50.877 ↑ 1.0 165 1

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

  • Hash Cond: (client_1.uuid = client_settlement_6.client_uuid)
135. 27.672 27.672 ↑ 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..27.672 rows=2,333 loops=1)

136. 1.323 2.739 ↑ 1.0 165 1

Hash (cost=3.30..3.30 rows=165 width=36) (actual time=2.733..2.739 rows=165 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 19kB
137. 1.416 1.416 ↑ 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.416 rows=165 loops=1)

138. 26.131 293.652 ↑ 1.2 1,262 1

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

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

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

140. 1.498 3.175 ↑ 1.0 194 1

Hash (cost=4.94..4.94 rows=194 width=36) (actual time=3.169..3.175 rows=194 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 21kB
141. 1.677 1.677 ↑ 1.0 194 1

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

142. 65.310 65.310 ↓ 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.012..0.014 rows=0 loops=4,665)

  • Index Cond: (uuid = aggregate.debtor_remittance_allocation_uuid)
143. 69.975 69.975 ↓ 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.012..0.015 rows=0 loops=4,665)

  • Index Cond: (uuid = remittance_allocation.debtor_remittance_uuid)
144. 65.310 65.310 ↓ 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.014 rows=0 loops=4,665)

  • Index Cond: (uuid = remittance.debtor_uuid)
145. 102.630 282,139.200 ↓ 0.0 0 4,665

Subquery Scan on paid (cost=4,902.76..4,902.77 rows=1 width=80) (actual time=60.476..60.480 rows=0 loops=4,665)

  • Filter: (aggregate.invoice_uuid = paid.invoice_uuid)
146. 135.285 282,036.570 ↓ 0.0 0 4,665

Limit (cost=4,902.76..4,902.76 rows=1 width=84) (actual time=60.457..60.458 rows=0 loops=4,665)

147. 167.940 281,901.285 ↓ 0.0 0 4,665

Sort (cost=4,902.76..4,903.58 rows=327 width=84) (actual time=60.429..60.429 rows=0 loops=4,665)

  • Sort Key: cp.allocation_date DESC
  • Sort Method: quicksort Memory: 25kB
148. 281,733.345 281,733.345 ↓ 0.0 0 4,665

CTE Scan on cumulative_paid cp (cost=0.00..4,901.12 rows=327 width=84) (actual time=59.197..60.393 rows=0 loops=4,665)

  • Filter: ((allocation_date <= aggregate.transaction_date) AND (invoice_uuid = aggregate.invoice_uuid))
  • Rows Removed by Filter: 200049
Planning time : 23.275 ms
Execution time : 312,021.905 ms