Support query hints

[weiatwork]

As Trino becomes more and more complex, it's hard to provide a one-size-fits-all experience for different kinds of workloads and query shapes. Trino provides many useful configuration options, but they either have to be stored in static config files or configured at the session level. Moreover, for specific query tuning needs, Trino currently does not provide a way to specify the join strategy for one specific join node or the overall join order for a single query. I understand the CBO work helps Trino make better query plans by itself, but it happens that the query plan is suboptimal and we want to fine tune certain parts of the query planning due to lack of statistics, inaccurate estimation etc.

Many major databases provide the query hints feature: Oracle, SQL Server, MySQL, and even Spark SQL. Trino should also consider having equivalent functionalities to provide seasoned users better ways to work with the query engine.

Someone else also brought this up previously: #1261

[weiatwork]

@martint Would you mind to shed some insights here?

[weiatwork]

@martint Can you please provide some feedback? Thanks.

[martint]

This would certainly be valuable. Some things to think about are:

• What's the syntax
• What exactly do hints control? Are they a way to provide information about, say, statistics, when the engine can't determine that on its own or a way to force the engine to pick a different strategy for some action
• How do we deal with backward compatibility -- what happens when a hint is no longer effective or if we want to remove it?

It'd be worth doing a survey of what other databases do around hints and then we can start thinking about how we may want to implement them in Trino.

[UlfR]

Documentation how hints are implemented in other databases:

1. Spark https://spark.apache.org/docs/3.0.0/sql-ref-syntax-qry-select-hints.html
2. Impala https://impala.apache.org/docs/build/html/topics/impala_hints.html
3. Oracle https://docs.oracle.com/cd/B12037_01/server.101/b10752/hintsref.htm

for ex. to use for types of join distributions:
select * from a join /* +partitioned */ b on a.id = b.a_id join /* +broadcast */ c on b.id = c.b_id

[amoghmargoor]

@martint I have created a doc with the survey and proposal for the hint framework: https://docs.google.com/document/d/13B1uvbopaDO09uSg5DTP1a1GUPFSvr7XDxMyWop_hPM/edit?usp=sharing
Please review it and see if it makes sense to have it for Trino.

[d33play]

Are there any updates on this issue?