explain.depesz.com

PostgreSQL's explain analyze made readable

Result: X3a1

Settings
# exclusive inclusive rows x rows loops node
1. 0.016 1,788.775 ↑ 1.4 114 1

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

2.          

CTE recursive_new_invoices

3. 0.014 1.073 ↓ 1.2 74 1

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

4. 0.063 1.049 ↑ 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.896..1.049 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.875 ↓ 0.0 0 1

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

6. 0.273 0.273 ↓ 1.5 6,408 1

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

  • Index Cond: (status = 'APPROVED'::capfin.invoice_status_enum)
7. 0.571 0.571 ↓ 1.0 8,367 1

Bitmap Index Scan on capfin_invoice_product_type_idx (cost=0.00..234.27 rows=8,246 width=0) (actual time=0.571..0.571 rows=8,367 loops=1)

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

SubPlan (for Bitmap Heap Scan)

9. 0.021 0.111 ↓ 0.0 0 37

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

10. 0.074 0.074 ↓ 0.0 0 37

Index Scan using client_settlement_invoice_allocation_invoice_uuid_idx on client_settlement_invoice_allocation allocation (cost=0.29..16.34 rows=3 width=16) (actual time=0.002..0.002 rows=0 loops=37)

  • Index Cond: (invoice_uuid = invoice_2.uuid)
11. 0.016 0.016 ↓ 0.0 0 8

Index Scan using client_settlement_pkey on client_settlement client_settlement_1 (cost=0.27..8.30 rows=1 width=16) (actual time=0.002..0.002 rows=0 loops=8)

  • Index Cond: (uuid = allocation.client_settlement_uuid)
  • Filter: (settlement_status = ANY ('{PENDING,INITIATED,SENT,PAID}'::capfin.advance_status_enum[]))
  • Rows Removed by Filter: 1
12. 0.010 0.010 ↓ 9.0 18 2

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

25. 0.364 0.364 ↓ 1.0 2,601 1

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

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

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

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

29. 0.079 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.094 0.094 ↑ 1.0 535 1

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

31. 1.367 16.448 ↓ 1.3 7,405 1

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

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

33. 7.317 14.320 ↓ 1.3 7,405 1

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

  • Group Key: invoice_settlements.invoice_uuid
34. 3.113 7.003 ↑ 1.0 7,405 1

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

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

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

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

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

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

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

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

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

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

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

41. 1.902 11.920 ↓ 1.3 7,405 1

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

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

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

  • Group Key: invoice_settlements_1.invoice_uuid
43. 1.466 3.985 ↑ 1.0 7,405 1

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

  • Hash Cond: (invoice_settlements_1.client_settlement_uuid = settlements_1.uuid)
44. 2.410 2.410 ↑ 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.022..2.410 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.109 ↓ 1.0 348 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 26kB
46. 0.067 0.067 ↓ 1.0 348 1

Seq Scan on client_settlement settlements_1 (cost=0.00..10.88 rows=340 width=20) (actual time=0.006..0.067 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. 48.693 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. 23.793 23.793 ↑ 1.0 48 7,931

Seq Scan on factor_client_debtor_contract fcdc (cost=0.00..1.48 rows=48 width=35) (actual time=0.001..0.003 rows=48 loops=7,931)

51. 8.054 16.108 ↑ 1.0 1 8,054

Hash (cost=8.30..8.30 rows=1 width=20) (actual time=0.002..0.002 rows=1 loops=8,054)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
52. 8.054 8.054 ↑ 1.0 1 8,054

Index Scan using client_debtor_contract_unique_as_of_constraint on client_debtor_contract cdc (cost=0.28..8.30 rows=1 width=20) (actual time=0.001..0.001 rows=1 loops=8,054)

  • Index Cond: ((client_debtor_uuid = capfin_invoice.client_debtor_uuid) AND (as_of_date <= CURRENT_DATE))
  • Filter: (effective_date <= CURRENT_DATE)
53. 0.000 120.810 ↓ 0.0 0 8,054

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

54. 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.007 0.019 ↑ 1.0 48 1

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

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

58. 0.010 1.110 ↓ 1.2 74 1

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

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

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

60. 1.048 1,484.285 ↑ 2.4 40 1

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

72. 0.341 0.341 ↓ 1.0 2,601 1

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

73. 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: 496
74. 0.102 0.301 ↓ 1.0 793 1

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

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

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

76. 0.081 0.175 ↑ 1.0 535 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 40kB
77. 0.094 0.094 ↑ 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.094 rows=535 loops=1)

78. 1.355 16.587 ↓ 1.3 7,405 1

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

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

80. 7.399 14.478 ↓ 1.3 7,405 1

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

  • Group Key: invoice_settlements_2.invoice_uuid
81. 3.138 7.079 ↑ 1.0 7,405 1

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

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

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

  • Hash Cond: (invoice_settlements_2.client_settlement_uuid = settlements_2.uuid)
83. 2.294 2.294 ↑ 1.0 8,859 1

Seq Scan on client_settlement_invoice_allocation invoice_settlements_2 (cost=0.00..664.16 rows=8,864 width=70) (actual time=0.006..2.294 rows=8,859 loops=1)

  • Filter: (item_type = 'DISCOUNT_FEE'::capfin.client_invoice_allocation_type)
  • Rows Removed by Filter: 14,595
84. 0.041 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
85. 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.006..0.072 rows=348 loops=1)

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

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

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

88. 1.912 11.960 ↓ 1.3 7,405 1

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

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

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

  • Group Key: invoice_settlements_3.invoice_uuid
90. 1.476 3.985 ↑ 1.0 7,405 1

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

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

  • Filter: (item_type = 'ADVANCE_AMOUNT'::capfin.client_invoice_allocation_type)
  • Rows Removed by Filter: 14,595
92. 0.042 0.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
93. 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
94. 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)

95. 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
96. 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)
97. 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)

98. 8.054 16.108 ↑ 1.0 1 8,054

Hash (cost=8.30..8.30 rows=1 width=20) (actual time=0.002..0.002 rows=1 loops=8,054)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
99. 8.054 8.054 ↑ 1.0 1 8,054

Index Scan using client_debtor_contract_unique_as_of_constraint on client_debtor_contract cdc_1 (cost=0.28..8.30 rows=1 width=20) (actual time=0.001..0.001 rows=1 loops=8,054)

  • Index Cond: ((client_debtor_uuid = capfin_invoice_1.client_debtor_uuid) AND (as_of_date <= CURRENT_DATE))
  • Filter: (effective_date <= CURRENT_DATE)
100. 0.000 120.810 ↓ 0.0 0 8,054

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

101. 8.054 120.810 ↓ 0.0 0 8,054

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

  • Sort Key: reference_rate_1.effective_date DESC NULLS LAST, reference_rate_1.term
  • Sort Method: quicksort Memory: 25kB
102. 112.756 112.756 ↑ 3.0 1 8,054

Seq Scan on reference_rate reference_rate_1 (cost=0.00..5.84 rows=3 width=30) (actual time=0.013..0.014 rows=1 loops=8,054)

  • Filter: ((effective_date <= CURRENT_DATE) AND (term >= GREATEST(fcdc_1.minimum_tenor, ((GREATEST(CASE WHEN (term_code_1.net_days IS NOT NULL) THEN CASE WHEN ((term_code_1.eom_code)::text = 'E'::text) THEN (((date_trunc('month'::text, ((capfin_invoice_1.transaction_date + term_code_1.net_days))::timestamp with time zone) + '1 mon'::interval) - '1 day'::interval))::date ELSE (capfin_invoice_1.transaction_date + term_code_1.net_days) END ELSE NULL::date END, capfin_invoice_1.due_date) + COALESCE(client_debtor_1.buffer_days, 0)) - CURRENT_DATE))) AND (type = capfin.reference_rate_type(fcdc_1.rate_type)))
  • Rows Removed by Filter: 15
103. 0.008 0.015 ↑ 1.0 48 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 11kB
104. 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)

105. 0.057 1,181.671 ↑ 2.4 40 1

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

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

Merge Left Join (cost=56,584.34..57,416.22 rows=95 width=122) (actual time=787.976..1,181.614 rows=40 loops=1)

  • Merge Cond: (invoice.uuid = dra.invoice_uuid)
  • Filter: ((COALESCE((sum(((dra.gross_amount + dra.deduction_amount) + dra.discount_amount))), '0'::numeric) - existing_settlements.amount) <> '0'::numeric)
  • Rows Removed by Filter: 5,198
107. 2.625 30.119 ↓ 55.1 5,238 1

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

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

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

  • Hash Cond: (invoice.uuid = existing_settlements.target_uuid)
109. 5.013 5.013 ↑ 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.078..5.013 rows=5,238 loops=1)

  • Index Cond: (product_type = 'FACTORING'::capfin.product_type)
  • Filter: (status = ANY ('{CLOSED,CHARGED_BACK}'::capfin.invoice_status_enum[]))
  • Rows Removed by Filter: 2,816
110. 1.097 20.969 ↑ 1.0 7,405 1

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

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

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

112. 6.193 19.086 ↑ 1.0 7,405 1

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

  • Group Key: client_settlement_invoice_allocation.invoice_uuid
113.          

Initplan (for HashAggregate)

114. 0.001 2.286 ↓ 0.0 0 1

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

115. 2.285 2.285 ↓ 0.0 0 1

Seq Scan on client_settlement_invoice_allocation csia (cost=0.00..752.11 rows=29 width=32) (actual time=2.285..2.285 rows=0 loops=1)

  • Filter: ((invoice_uuid IS NOT NULL) AND (uuid = client_settlement_uuid) AND (item_type = ANY ('{REBATE,UNDERPAYMENT,CHARGE_BACK}'::capfin.client_invoice_allocation_type[])))
  • Rows Removed by Filter: 23,454
116. 0.000 0.000 ↓ 0.0 0

Index Scan using client_settlement_pkey on client_settlement client_settlement_2 (cost=0.27..1.67 rows=1 width=16) (never executed)

  • Index Cond: (uuid = csia.uuid)
  • Filter: (settlement_status = ANY ('{PENDING,INITIATED,SENT,PAID}'::capfin.advance_status_enum[]))
117. 3.949 10.607 ↑ 1.0 20,008 1

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

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

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

  • Hash Cond: (client_settlement_invoice_allocation.client_settlement_uuid = client_settlement.uuid)
119. 2.177 2.177 ↓ 1.0 23,454 1

Seq Scan on client_settlement_invoice_allocation (cost=0.00..605.53 rows=23,453 width=42) (actual time=0.005..2.177 rows=23,454 loops=1)

120. 0.042 0.119 ↓ 1.0 348 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 25kB
121. 0.077 0.077 ↓ 1.0 348 1

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

  • Filter: (settlement_status = ANY ('{PENDING,INITIATED,SENT,PAID}'::capfin.advance_status_enum[]))
  • Rows Removed by Filter: 52
122. 61.311 1,121.527 ↓ 15.3 299,433 1

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

123. 254.947 1,060.216 ↓ 15.3 299,433 1

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

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

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

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

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

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

127. 85.543 205.491 ↓ 1.1 299,589 1

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

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

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

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

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

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

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

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

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