[
https://issues.apache.org/jira/browse/FINERACT-3001?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Piotr Wargulak reassigned FINERACT-3001:
----------------------------------------
Assignee: Piotr Wargulak
> 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
> Assignee: Piotr Wargulak
> Priority: Major
>
> 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)
