Openbravo Issue Tracking System - Retail Modules
View Issue Details
IDProjectCategoryView StatusDate SubmittedLast Update
0043080Retail ModulesWeb POSpublic2020-01-31 11:172020-01-31 11:29
ReporterALopetegui 
Assigned ToRetail 
PriorityhighSeveritymajorReproducibilityhave not tried
StatusnewResolutionopen 
PlatformOS5OS Version
Product Versionpi 
Target VersionFixed in Version 
Merge Request Status
Review Assigned To
OBNetwork customer
Support ticket
Regression level
Regression date
Regression introduced in release
Regression introduced by commit
Triggers an Emergency PackNo
Summary0043080: Performance: HQL Query validated has inefficient query to get property of preference
DescriptionIn ProcessHQLQueryValidated class there is a function to get the property of the preferences doing this query:

select property from ad_preference where property like '%EntitySelector_BPartnerFilter_bpName_s' limit 1

This query makes a sequential scan on ad_preference. This query is very frequent and makes too much read from memory/disk.

This is the explain plan
https://explain.depesz.com/s/JJny [^]
Steps To ReproduceWith more than 100K ad_preferences and heavy searching concurrency, the query could be a problem. To test it just launch a query with more than 100K preferences.

Proposed SolutionThe solution is to create a gin index in ad_preference on column property in order to find with and contains index.

CREATE INDEX <index-name> ON ad_preference USING GIN (property gin_trgm_ops);

This is the explain plan after the index:
https://explain.depesz.com/s/RS8c [^]
Additional Information
TagsPerformance
Relationships
Attached Files
Issue History
Date ModifiedUsernameFieldChange
2020-01-31 11:17ALopeteguiNew Issue
2020-01-31 11:17ALopeteguiAssigned To => Retail
2020-01-31 11:17ALopeteguiTriggers an Emergency Pack => No
2020-01-31 11:27ALopeteguiTag Attached: Performance
2020-01-31 11:29ALopeteguiPrioritynormal => high

There are no notes attached to this issue.