Support query result caching

[osscm]

Query Result Caching involves the temporary storage of previously executed query results to expedite future data retrieval requests. When a query is executed against a dataset, the result set is cached in memory or disk storage. Subsequent identical or similar queries can then be served directly from the cache rather than re-executing the query against the underlying data source.

This caching mechanism significantly improves query performance and reduces computational overhead, especially for frequently accessed or computationally intensive queries. Query Result Caching is a critical optimization technique employed in big data analytics platforms to enhance query response times, improve system scalability, and optimize resource utilization.

Trino users seeking this functionality often implement it within their application layer. Different commercial solutions offer variations of this feature to cater to diverse needs.

BigQuery: https://cloud.google.com/bigquery/docs/cached-results
Snowflake: https://docs.snowflake.com/en/user-guide/querying-persisted-results
Athena: https://aws.amazon.com/about-aws/whats-new/2022/11/amazon-athena-query-result-reuse-accelerate-queries/
SB: https://docs.starburst.io/latest/admin/cache-service.html

[osscm]

I think we can collaborate to define the scope and consider gradually implementing it, taking into account various use cases and the time/resources teams are willing to allocate. Since there may be multiple implementation approaches with varying limitations, certain options might be suitable for specific use cases.

We can add more subtasks to scope the individual tasks.

[YuriyGavrilov]

Talk about query caching https://youtu.be/t7ndqYUhKSA?si=1wg1TWIVG7FQZzBR at Microsoft