Openbravo Issue Tracking System - Openbravo ERP
View Issue Details
IDProjectCategoryView StatusDate SubmittedLast Update
0038289Openbravo ERPI. Performancepublic2018-04-06 13:592018-04-13 10:53
Reporterngarcia 
Assigned Toalostale 
PriorityhighSeverityminorReproducibilityalways
StatusclosedResolutionfixed 
PlatformOS5OS Version
Product Version 
Target VersionFixed in Version3.0PR18Q3 
Merge Request Status
Review Assigned Tocaristu
OBNetwork customer
Web browser
ModulesCore
Support ticket
Regression level
Regression date
Regression introduced in release
Regression introduced by commit
Triggers an Emergency PackNo
Summary0038289: Log Clean Up Process takes too long to delete the records in AD_PROCESS_REQUEST window with Process Groups
DescriptionLog Clean Up Process takes too long to delete the records in AD_PROCESS_REQUEST window with Process Groups

Having an environment with around 700000 records to be deleted in ad_process_request (status = 'COM' created < now()-15), the following query lasts 22 hours

delete from AD_Process_Request where Created<now()-15 and Status='COM' and AD_Client_ID=<>;

Result of Explain Analyze was:

'Delete on ad_process_request (cost=0.00..380218.30 rows=718678 width=6) (actual time=46464.380..46464.380 rows=0 loops=1)' ' -> Seq Scan on ad_process_request (cost=0.00..380218.30 rows=718678 width=6) (actual time=0.031..2275.472 rows=720995 loops=1)' ' Filter: (((status)::text = 'COM'::text) AND ((ad_client_id)::text = '101C8C9C4A8D4C84857E3F5FBD722757'::text) AND (created < (now() - 15::numeric)))' ' Rows Removed by Filter: 162711' 'Trigger for constraint ad_process_request_group: time=79140555.117 calls=720995' 'Trigger for constraint ad_process_run_ad_process_requ: time=27889.646 calls=720995' 'Trigger ad_process_request_trg: time=12800.440 calls=720995' 'Total runtime: 79215399.399 ms'

Steps To ReproduceAsk for an environment in which the problem can be reproduced in case is needed
Proposed SolutionCreate an index similar to

CREATE INDEX ad_process_reqgroup_idx
  ON ad_process_request
  USING btree
  (ad_process_request_group_id COLLATE pg_catalog."default")
   WHERE ad_process_request_group_id IS NOT NULL
Additional Information
TagsPerformance
Relationships
related to defect 0038291 closed caristu Modules Log Clean Up process doesn't finish deleting C_IMPORT_ENTRY_ARCHIVE table with big amount of data 
Attached Files
Issue History
Date ModifiedUsernameFieldChange
2018-04-06 13:59ngarciaNew Issue
2018-04-06 13:59ngarciaAssigned To => platform
2018-04-06 13:59ngarciaModules => Core
2018-04-06 13:59ngarciaTriggers an Emergency Pack => No
2018-04-06 13:59ngarciaIssue Monitored: networkb
2018-04-06 14:00ngarciaPriorityurgent => high
2018-04-06 14:00ngarciaSeveritymajor => minor
2018-04-09 10:10PracticsIssue Monitored: Practics
2018-04-13 09:23alostaleTag Attached: Performance
2018-04-13 09:23alostaleRelationship addedrelated to 0038291
2018-04-13 09:32alostaleAssigned Toplatform => alostale
2018-04-13 09:32alostaleReview Assigned To => caristu
2018-04-13 09:35hgbotCheckin
2018-04-13 09:35hgbotNote Added: 0103870
2018-04-13 09:35hgbotStatusnew => resolved
2018-04-13 09:35hgbotResolutionopen => fixed
2018-04-13 09:35hgbotFixed in SCM revision => http://code.openbravo.com/erp/devel/pi/rev/0b70358a2e29459502143a08f3ba30ae440be92f [^]
2018-04-13 10:53caristuNote Added: 0103871
2018-04-13 10:53caristuStatusresolved => closed
2018-04-13 10:53caristuFixed in Version => 3.0PR18Q3

Notes
(0103870)
hgbot   
2018-04-13 09:35   
Repository: erp/devel/pi
Changeset: 0b70358a2e29459502143a08f3ba30ae440be92f
Author: Asier Lostalé <asier.lostale <at> openbravo.com>
Date: Fri Apr 13 09:35:13 2018 +0200
URL: http://code.openbravo.com/erp/devel/pi/rev/0b70358a2e29459502143a08f3ba30ae440be92f [^]

fixed 38289: slow Log Clean Up with records in AD_PROCESS_REQUEST using Groups

  Added index to ad_process_request.ad_process_request_group_id to prevent seq
  scans when deleing on this table having requests linked to groups.

---
M src-db/database/model/tables/AD_PROCESS_REQUEST.xml
---
(0103871)
caristu   
2018-04-13 10:53   
Reviewed