The larger question to answer first is whether we want to implement
something that is deterministic...
How about just dropping the whole concept of "hinting" and provide a way for someone to say "use this plan, or die trying." Maybe require it be used in conjunction with named PREPAREd statements:
You mean taking away the entire concept of query planning and cost estimation? Thats like replacing the optimizer with DBA decision and I am not at all comfortable with that idea. That are only my thoughts though.
PREPARE s1 (USING /path/to/plan_def_on_server_or_something_similar) AS SELECT ...;
Aside from whole-plan specification I can definitely see where join/where specification could be useful if it can overcome the current limitation of not being able to calculate inter-table estimations.
Prepare plans use a generic plan for the execution. Replacing it with a totally user defined plan does not seem to be clean.
The crux is that IMHO planner hints are a bad way of trying to circumvent the need for cross-column statistics. We should do cross-column statistics done and ignore planner hints completely.