0038579: A missing index creates slowness deleting invoices

Description

The delete of invoices takes too long having analytics installed and the obanaly_fact_order with thousands of rows.

Steps To Reproduce

-Execute the delete of an invoice on an environment with big amount of data both on c_invoice and obanaly_fact_order tables...

openbravo=# explain analyze delete from c_invoice where c_invoice_id='BF4FB8533D864288B7C9834F988D40FA';
                                                          QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------
Delete on c_invoice (cost=0.56..8.57 rows=1 width=6) (actual time=0.177..0.177 rows=0 loops=1)
   -> Index Scan using c_invoice_key on c_invoice (cost=0.56..8.57 rows=1 width=6) (actual time=0.055..0.056 rows=1 loops=1)
         Index Cond: ((c_invoice_id)::text = 'BF4FB8533D864288B7C9834F988D40FA'::text)
Trigger for constraint obanaly_fact_disc_inv_fk: time=3.157 calls=1
Trigger for constraint obanaly_fact_order_inv_fk: time=129267.366 calls=1
Trigger for constraint aprm_pending_payinv_invoice: time=0.198 calls=1
Trigger for constraint c_currencyratedocinv: time=0.104 calls=1
Trigger for constraint c_debt_payment_c_invoice: time=0.112 calls=1
Trigger for constraint c_invoice_ccommissionrun: time=5.052 calls=1
Trigger for constraint c_invoice_discount_c_invoice: time=0.170 calls=1
Trigger for constraint c_invoice_reverse_c_invoice: time=0.096 calls=1
Trigger for constraint c_invoice_reverse_reversed_c_i: time=0.247 calls=1
Trigger for constraint c_invoicelinetax_c_invoice: time=1.807 calls=1
Trigger for constraint c_invoicetax_c_invoice: time=1.337 calls=1
Trigger for constraint fin_c_invoice_orig_payment: time=1.241 calls=1
Trigger for constraint fin_c_invoice_payment: time=0.100 calls=1
Trigger for constraint i_invoice_c_invoice: time=0.084 calls=1
Trigger for constraint m_inout_c_invoice: time=0.588 calls=1
Trigger for constraint m_rappel_invoice_c_invoice: time=0.086 calls=1
Trigger for constraint c_invoiceline_c_invoice: time=0.138 calls=1
Trigger c_invoice_trg: time=0.076 calls=1
Total runtime: 129282.500 ms

AS you can see most of the time is consumed on:

Trigger for constraint obanaly_fact_order_inv_fk: time=129267.366 calls=1

Tags

No tags attached.

Attached Files

38579.diff [^] (624 bytes) 2018-05-18 09:59 [Show Content] [Hide Content]

diff --git a/src-db/database/model/tables/OBANALY_FACT_ORDER.xml b/src-db/database/model/tables/OBANALY_FACT_ORDER.xml
--- a/src-db/database/model/tables/OBANALY_FACT_ORDER.xml
+++ b/src-db/database/model/tables/OBANALY_FACT_ORDER.xml
@@ -478,6 +478,9 @@
       <index name="OBANALY_FACT_INVLINE_IDX" unique="false">
         <index-column name="C_INVOICELINE_ID"/>
       </index>
+      <index name="OBANALY_FACT_INVOICE_IDX" unique="false">
+        <index-column name="C_INVOICE_ID"/>
+      </index>
       <index name="OBANALY_FACT_OFFER_IDX" unique="false">
         <index-column name="M_OFFER_ID"/>
       </index>

Relationships [ Relation Graph ] [ Dependency Graph ]

Notes
(0104769) hgbot (developer) 2018-05-29 13:54	Repository: erp/pmods/org.openbravo.client.analytics Changeset: a52b8a4d10dc16ca9038269cf3598a2b313c126a Author: Carlos Aristu <carlos.aristu <at> openbravo.com> Date: Tue May 29 13:51:35 2018 +0200 URL: http://code.openbravo.com/erp/pmods/org.openbravo.client.analytics/rev/a52b8a4d10dc16ca9038269cf3598a2b313c126a [^] fixes issue 38579: A missing index creates slowness deleting invoices --- M src-db/database/model/tables/OBANALY_FACT_ORDER.xml ---

(0104877) alostale (viewer) 2018-06-01 13:42	reviewed

Issue History
Date Modified	Username	Field	Change
2018-05-18 09:19	egoitz	New Issue
2018-05-18 09:19	egoitz	Assigned To	=> platform
2018-05-18 09:19	egoitz	Resolution time	=> 1527458400
2018-05-18 09:19	egoitz	OBNetwork customer	=> Yes
2018-05-18 09:59	egoitz	File Added: 38579.diff
2018-05-29 13:54	hgbot	Checkin
2018-05-29 13:54	hgbot	Note Added: 0104769
2018-05-29 13:54	hgbot	Status	new => resolved
2018-05-29 13:54	hgbot	Resolution	open => fixed
2018-05-29 13:54	hgbot	Fixed in SCM revision	=> http://code.openbravo.com/erp/pmods/org.openbravo.client.analytics/rev/a52b8a4d10dc16ca9038269cf3598a2b313c126a [^]
2018-05-29 13:55	caristu	Assigned To	platform => caristu
2018-05-29 13:55	caristu	Review Assigned To	=> alostale
2018-06-01 13:42	alostale	Note Added: 0104877
2018-06-01 13:42	alostale	Status	resolved => closed