Subquery cache roadmap

[sopel39]

#21888 introduces subquery cache feature to Trino engine. However, there are many follow up items to improve the performance and hit rate.

Here are the roadmap items:

Tasks

Beta Give feedback

1. Prune casted partition columns #22116
   subquery-cache +1
   
2. Order split scheduling between stages for common subqueries #22117
   subquery-cache +1
   
3. Implement cache prioritization #22118
   subquery-cache +1
4. Add ttl for cached data #22119
   subquery-cache +1
5. Normalize conjunct order in CanonicalSubplanExtractor #22120
   subquery-cache +1
6. Add support for (partial) Limit operator to subquery cache #22121
   subquery-cache +1
   
7. Add support for Join operator to subquery cache #22122
   subquery-cache +1
8. Cache subplan in intermediate stages #22165
   subquery-cache +1
9. Simplify redundant EXISTS #22167
   subquery-cache +1
10. Cache data asynchroniously #22168
    subquery-cache +1
11. Add support for multiple ChooseAlternativeNodes at worker level #22579
    subquery-cache +1

[chenjian2664]

@sopel39 I am interested in contributing to this topic, but I am not very familiar with the tasks mentioned above. Could you guide me on how to start and suggest which task or tasks might be suitable to start

[osscm]

Thanks a lot @sopel39 !

We had discussed a couple of times on the old issue about the implementation and approach.

As discussed we were also looking into the same problem, and would be more than happy to contribute.
Please share your thoughts, where do you think I can work on.

May be,

#22116
Or
#22165
Or

Whatsoever task you think.

Thanks.

[sopel39]

Hi @osscm

Take into consideration that it will probably take some time to land this PR. I'm extracting smaller PRs atm. However, I think we should also progress with the improvements.

#22116 and #22165 are both important. Especially #22116 will improve cache hit rate for string partition types, which are fairy common.

However, I would start with something simpler like #22120 to familiarize with the concepts (code can still change in the process of review)

I would start with something simpler like #22121 to familiarize with the concepts (code can still change in the process of review)

[sug-ghosh]

Hi @sopel39

I am interested to contribute to this issue. Going through the code and implementation in understanding the issue. Can you please guide me which sub-task I can take up and able to contribute.

[sopel39]

@sug-ghosh I think we would need to sync. Ping me on slack please

[hackeryang]

Relevant PR: #21888