Speed up a reasonable plan
A query already has a sensible plan and reads only what it needs, but it is still too slow, or runs so often that its total cost matters. The options run from reusing a previous answer to making each row cheaper to process. Each step pays roughly an order of magnitude more than the next, which is the reverse of how people usually approach it. Which applies here?
You will end with the largest available saving, from reusing an answer down to making the work itself faster.
Work in order, and stop at the first that applies.
Work through it
Does the exact query recur?
If yes, and the tables carry version tokens (snapshot IDs): cache the result, keyed on the query and the versions. It beats everything below by an order of magnitude.
Does the same shape recur with different parameters?
If yes: cache the plan, as prepared statements do. Choose deliberately whether the plan is built for the first parameter values seen or rebuilt per value: with skewed parameters, a plan built for a rare value can be terrible for a common one, and the default is rarely right.
Is the first bad estimate upstream of every point where the engine can re-plan?
Engines can revise a plan only at a barrier, where an intermediate result is complete and measured (for example, after a shuffle). If the estimate goes wrong before the first barrier: adaptive execution cannot help; fix the estimate (see Diagnose a bad plan). Check this before enabling anything adaptive.
Is skew visible at a barrier?
If yes: let the engine split skewed partitions or switch join strategy there. It is the cheapest and most reliable adaptive win.
Do operators process millions of rows each, with heavy per-row computation?
If yes: compiling the query to machine code may pay. Below that volume the compile time costs more than it saves: break-even rows = compile cost ÷ time saved per row, so a 50 ms compile saving 20 ns a row needs 2.5 million rows.
Does a sub-plan recur across different queries?
If yes: caching its result can pay, and whether it does depends on how often it recurs, which the query log answers, not the data.