query is reaching memory limit and auto-finalizing, is there a way to optimize the query and prevent this from happening?

[tareddy]

Query : index=INDEXA earliest=-7d@d latest=@d sourcetype=GHI "service=randomservice" (api_name=API1 OR api_name=API2 )
| stats first(_time) as _time, values(XYZ) AS XYZ, values(PQR) AS PQR by ABC | fillnull PQR value="NULL"
| search XYZ=DEF | timechart span=1d count by PQR

[sideview]

If this search,

DEF <index and sourcetype and service terms to narrow down to just DEF events> | stats count by ABC 

returns relatively quickly, and with only a few hundred or thousand rows,

and especially if the number of events coming out of your main stats clause is being radically filtered down by the | search XYZ="def" clause,

then you should absolutely switch this to a subsearch use case.

index=INDEXA earliest=-7d@d latest=@d sourcetype=GHI "service=randomservice" (api_name=API1 OR api_name=API2 ) [DEF <index and sourcetype and service terms to narrow down to just DEF events> | fields ABC | dedup ABC]    
| stats first(_time) as _time, values(XYZ) AS XYZ, values(PQR) AS PQR by ABC | fillnull PQR value="NULL" 
| timechart span=1d count by PQR

[tareddy]

Thanks sideview, this answer greatly improved the search/storage efficiency. However your query is working accurately only for a maximum time range of about 24 hours. I need to search over a period of a week. I am getting "[subsearch]: Search auto-finalized after time limit (60 seconds) reached" and consequently i am not able to obtain the accurate results. Do you have any suggestions or any other way to optimize the query?