Postgres query planning on a per-statement basis

Question

When executing a statement in Postgres you can set specific query planner configuration parameters as described in the documentation.

These can be set on a system level by the administrator or on a user level in a specific session. I now have the issue that the application does not support setting parameters in the user session, and I don't want to set it globally, since this would affect all queries.

Is it possible to set a query planning parameter via hints in the SQL itself (like in Oracle) or create a profile on a certain SQL statement to include certain parameters?

For context, the parameter I am talking about is plan_cache_mode. When it is set to auto, a certain prepared statement is using a generic plan that has very bad performance (~8s execution time). As soon as I force custom plans via force_custom_plan the execution time drops to ~10ms. I know that better indexes or statistics might change the generic plan, but I am looking for an easier solution than diving into SQL optimization.

• Bergi: If your application does not support setting parameters in the session, how would it support hints?
• Flourid: That is true, I don't know yet if the devs will be able to to that, it was just a route I thought might be worthwile pursuing.

Answer 1

You can put your SQL statement into a function:

CREATE FUNCTION myquery() RETURNS TABLE (...)
 LANGUAGE sql SET plan_cache_mode = force_custom_plan AS
$$SELECT ...$$;

that would override the default parameter value while the function executes.

• Flourid: It's a nice idea, but sadly incompatible with the DAL the developers use. But thanks!
• Laurenz Albe: I have no idea what a DAL is, but if it can execute SELECT ..., it can certainly also execute SELECT * FROM myquery().
• Flourid: It's a data abstraction layer and we can't change how it accesses the data (else we could insert the change in the variable itself). I just have control over the database where a SQL query arrives that I would like to tune.
• Laurenz Albe: Looks like you have to work around the DAL then. That is sometimes necessary for good performance. Tuning queries is the alternative, but that very often also meens rewriting them.
• Bergi: ...or call the function from a view, certainly the DAL can access views?

Answer 2

There is a 3rd party extension to add hints, pg_hint_plan, but I don't see anything there that would hint the plan_cache_mode. So you would have to dig into the details of the good and bad plan, and hint those details. And if you can't control the DAL, how are you going to inject hints, anyway?

I would consider changing plan_cache_mode globally (or at least for a particular user and/or database) to be a low-risk change. Have you tried it and experienced a problem?

• Flourid: Thanks for the answer, but yes, I don't know if the developers will be able to insert hints into the SQLs. Setting plan_cache_mode globally (doesn't really matter if global or per session, since theres only one application user anyway) is what I'm doing right now, but I've experienced issues in the past where other SQLs where then slower because they would profit more from generic plans.