Piotr Wargulak created FINERACT-3001:
----------------------------------------
Summary: Working Capital loan tables miss indexes on per-loan
columns (m_wc_loan_charge.loan_id and three foreign keys on PostgreSQL)
Key: FINERACT-3001
URL: https://issues.apache.org/jira/browse/FINERACT-3001
Project: Apache Fineract
Issue Type: Improvement
Reporter: Piotr Wargulak
Queries that read or delete Working Capital loan rows by loan or by transaction
scan the whole table on these columns:
* {{m_wc_loan_charge.loan_id}} has no index and no foreign key on any database
({{{}0042_wc_loan_charge.xml{}}});
* {{m_wc_loan_disbursement_detail.wc_loan_id}} ({{{}0006{}}}),
{{m_wc_loan_transaction_relation.from_loan_transaction_id}} /
{{to_loan_transaction_id}} ({{{}0034{}}}) and {{to_loan_charge_id}}
({{{}0044{}}}) have only a foreign key. MySQL and MariaDB create an index for a
foreign key; PostgreSQL does not.
The cost grows with the portfolio: in our measurements of nightly WC COB on
PostgreSQL, per-loan reads on these tables were the part that grew with
portfolio size * loan age.
*Proposed fix:*
one changelog part adding the five indexes, {{CREATE INDEX CONCURRENTLY}} on
PostgreSQL (as in {{{}0177_acc_journal_entry_index.xml{}}}) and a plain
{{CREATE INDEX}} on MySQL/MariaDB, where the explicit index takes over from the
one backing the foreign key.
--
This message was sent by Atlassian Jira
(v10.3.26#10030026)
