0043080: Performance: HQL Query validated has inefficient query to get property of preference

Description

In 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 Reproduce

With 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 Solution

The 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 [^]

Tags

Performance

Attached Files

Relationships [ Relation Graph ] [ Dependency Graph ]

Notes
There are no notes attached to this issue.

Issue History
Date Modified	Username	Field	Change
2020-01-31 11:17	ALopetegui	New Issue
2020-01-31 11:17	ALopetegui	Assigned To	=> Retail
2020-01-31 11:17	ALopetegui	OBNetwork customer	=> Gold
2020-01-31 11:17	ALopetegui	Triggers an Emergency Pack	=> No
2020-01-31 11:27	ALopetegui	Tag Attached: Performance
2020-01-31 11:29	ALopetegui	Priority	normal => high