For lots of small queries, set optimizer = OFF is a good option to reduce the latency. But it is hard for me to make a decision which statement should be used?
Is there any server config to auto switch it on/off based on the statement accordingly?
The original design for GPORCA optimized for analytical queries on data warehouses; focused on queries that typically would take a few hours and trying to make them run in a few minutes.
For these long running queries, the time needed to compute an optimal plan is small compared with the duration of the query itself. However, for shorter queries, the time needed to find an optimal plan becomes more important for overall execution time, so this is an area that needs future development effort. This is all about finding balance and bottlenecks.
The problem is that anything you are going to run based on a statement is going to go through the planner -- how do you know that it's small unless you plan it?
Depending on how you manage your environment, you could have a dedicated resource queue, where they can have SET parameters, including OPTIMIZER=OFF.