Persisted Queries: Use locale param to filter the internal SQL query results in order to improve performance - Status changed to: Investigating

[#1]

Request for Feature Enhancement (RFE) Summary:	Use locale param to filter the internal SQL query results in order to improve performance
Use-case:	On any multilanguage project, the same type of content fragments will be potentially created under each language. When a persisted query is executed to retrieve all the content fragments for a specific model, filtering also by dates and locale, the locale is not used on the internal SQL, causing the query to be really slow. Sample internal SQL Query: SELECT main.* FROM [dam:IndexedFragmentData] AS main WHERE ISDESCENDANTNODE(main, '/content/dam') AND main.[@string@model] = '/conf/mysite/settings/dam/cfm/models/news' AND (name() = 'master') AND ((main.[calendar@publicationDate] >= cast('2023-07-05T08:30:57.266Z' AS date) AND main.[calendar@publicationDate] <= cast('2023-07-08T08:30:57.266Z' AS date))) ORDER BY main.[jcr:path] OPTION (INDEX TAG[contentFragments], TRAVERSAL FAIL) As a workaround, we have built a new version of the persisted query where we specify the path to the specific language as a filter, then the query only takes a few milliseconds, but this should be part of the internal functionality when a locale is used, the front might know the language but not the content path.
Current/Experienced Behavior:	Locale is not used in the internal SQL filter
Improved/Expected Behavior:	Locale should be used as part of the internal SQL filter
Environment Details (AEM version/service pack, any other specifics if applicable):	AEM CS 2023.4
Customer-name/Organization name:	Real Madrid Club de Futbol
Screenshot (if applicable):
Code package (if applicable):

[#2]

@Adep1 

Thanks for proposing this idea

This has been reported to the engineering under the internal reference SITES-14946. The product team will triage this request to verify feasibility based on the prioritization model. This post will be updated according to the Jira request status.